SQL练习和Java作业

1、

有一个员工表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1995-12-03	9999-01-01

第一行表示为员工编号为10001的部门是d001部门。

有一个部门经理表dept_manager简况如下:

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01
d002	10003	1990-08-05	9999-01-01

第一行表示为d001部门的经理是编号为10002的员工。

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:

emp_no	manager
10001	10002

SELECT t1.emp_no,t2.emp_no as manager from dept_emp t1 JOIN dept_manager t2 USING (dept_no) where t1.emp_no <> t2.emp_no;
 

2、

如下有一张商品表（goods），字段依次为：商品id、商品名、商品质量

id	name	weight
1	A1	100
2	A2	20
3	B3	29
4	T1	60
5	G2	33
6	C0	55

还有一张交易表（trans），字段依次为：交易id、商品id、这个商品购买个数

id	goods_id	count
1	3	10
2	1	44
3	6	9
4	1	2
5	2	65
6	5	23
7	3	20
8	2	16
9	4	5
10	1	3

查找购买个数超过20,质量小于50的商品，按照商品id升序排序,如:

id	name	weight	total
2	A2	20	81
3	B3	29	30
5	G2	33	23

select goods_id as id,sum(count) as total from trans GROUP BY goods_id;
select * from goods t1 join(select goods_id as id,sum(count) as total from trans GROUP BY goods_id) t2 using (id) where t2.total > 20 and t1.weight < 50 ORDER BY id;

Java作业

读取文件，统计每个班级的人数，并将结果写入到另一个文件中

package com.shujia.za.homework9;

import java.io.*;
import java.util.HashMap;
import java.util.Set;

public class Test11 {
    public static void main(String[] args) {
        BufferedReader br = null;
        BufferedWriter bw = null;
        HashMap<String, Integer> map = new HashMap<>();
        try {
            br = new BufferedReader(new FileReader("E:\\IDEAprojects\\bigdata\\students.txt"));
            bw = new BufferedWriter(new FileWriter("E:\\IDEAprojects\\bigdata\\count.txt"));
            String s = null;
            //按行读取文件
            while ((s = br.readLine()) != null) {
                //将读取的一行数据以","分开
                String[] split = s.split(",");
                //判断map集合是否有这个班级，没有就添加，有就人数加一
                if (!map.containsKey(split[4])) {
                    map.put(split[4], 1);
                } else {
                    map.put(split[4], map.get(split[4]) + 1);
                }
            }
            //遍历集合，并写入
            Set<String> set = map.keySet();
            for (String s2 : set) {
                Integer i2 = map.get(s2);
                bw.write(s2 + ":" + i2);
                bw.newLine();
                bw.flush;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bw != null) {
                try {
                    bw.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }

}

2022-05-20作业

一、SQL题

select '80后' as '年龄段',SUM(CREDIT) as '信用卡余额' from TableA join TableB USING (NO) where birth like "198%"
union
select '90后' as '年龄段',SUM(CREDIT) as '信用卡余额' from TableA join TableB USING (NO) where birth like "199%"; 

union是将两个select语句查询出的结果拼接起来，union不包含重复行，union all包含重复行。

使用union必须select结果具有相同数量的列，且union按照select字段的顺序进行拼接，不会按照列名进行匹配。

二、Java程序题

使用java程序模拟hadoop切分文件，统计students文件中，姓名包含'白'汉字的人数。

package com.shujia.za.homework9;
import java.io.*;
import java.util.ArrayList;

public class Test12 {
    public static void main(String[] args) {
        //将文件切分，写入blocks
        try {
            int index = 0;
            BufferedReader br = new BufferedReader(new FileReader("E:\\IDEAprojects\\bigdata\\students.txt"));
            BufferedWriter bw = new BufferedWriter(new FileWriter("E:\\IDEAprojects\\bigdata\\blocks\\block--" + index));
            ArrayList<String> list = new ArrayList<>();
            int offset = 0;
            int num = 0;
            String line = null;
            while ((line = br.readLine()) != null) {
                list.add(line);
                offset++;
                num = 128 * index;
                if (offset == 140) {
                    for (int i = num; i <= num + 127; i++) {
                        String s = list.get(i);
                        bw.write(s);
                        bw.newLine();
                        bw.flush();
                    }
                    offset = 12;
                    index++;
                    bw = new BufferedWriter(new FileWriter("E:\\IDEAprojects\\bigdata\\blocks\\block--" + index));
                }
            }
            for (int i = list.size() - offset; i < list.size(); i++) {
                String s = list.get(i);
                bw.write(s);
                bw.newLine();
                bw.flush();
            }
            bw.close();
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

===========================================================================
package com.shujia.za.homework9;
import java.io.*;

public class Test13 {
    public static void main(String[] args) {
        //获取名字中有“白”的学生人数
        File file = new File("E:\\IDEAprojects\\bigdata\\blocks");
        File[] files = file.listFiles();
        int index = 0;
        for (File s : files) {
            try {
                BufferedReader br = new BufferedReader(new FileReader(s));
                String line = null;
                while ((line = br.readLine()) != null) {
                    String[] split = line.split(",");
                    if (split[1].contains("白")) {
                        index++;
                    }
                }
                br.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        System.out.println("名字中含有“白”的有："+index);
    }
}