数据库概论——SQL练习二（employees数据库）

系统

MySQL 8.0.19
MySQL Workbench 8.0

准备工作

1.下载employees数据库：https://github.com/datacharmer/test_db

2.将整个文件夹放到工作的目录下

　　例如：mac - 放在桌面上，则文件夹的路径为Desktop/test_db-master/
　　　　  windows - 放在D盘上，则文件夹的路径为D:/test_db-master/ 

3.修正文件路径信息

　　找到employees.sql文件，在文件尾部找到所有的source行，更改文件路径
　　例如：把source load_departments.dump改为source Desktop/test_db-master/load_departments.dump

4.安装employees数据库

　　在MySQL中执行：source  Desktop/test_db-master/employees.sql

5.测试employees数据库是否正确安装

　　在MySQL中执行：source Desktop/test_db-master/test_employees_md5.sql
　　如果正确安装，应当输出数据库中的表格信息

tips:

 (1)MySQL Workbench中使用ctrl+enter（mac：command+return）可以快捷运行sql语句

 (2)在Workbench中找到(mac)"MySQLWorkbench"或(Linux)"Edit" -> "Preferences" -> "SQL Editor" -> "MySQL Session"，可以设置timeout的时间，以避免出现"Error Code: 2013. Lost connection to MySQL server during query"的报错

employees库表结构

问题和解答

1.查询每个部门的经理信息。输出格式为（部门名，经理姓，经理名），按部门名升序输出。

select departments.dept_name, employees.last_name, employees.first_name
from departments, dept_manager, employees
where departments.dept_no = dept_manager.dept_no
    and dept_manager.emp_no = employees.emp_no
order by departments.dept_name asc;

2. 找出那些至少拥有三个title并且至少在两个部门工作过的员工，要求按升序输出前十个员工号。

select emp_no
from
    (select @rownum:=@rownum+1 as rownum, emp_no
    from
        (select emp_no
        from employees join titles using(emp_no)
            join dept_emp using(emp_no)
        group by employees.emp_no
        having count(titles.title)>=3
            and count(dept_emp.dept_no)>=2
        order by employees.emp_no asc) as d1
        , (select @rownum:=0) as t
    ) as d2
where rownum<=10;

 注:(1) MySQL不支持oracle的rownum，需要手动加上@rownum，简易版本如下

1 select @rownum:=@rownum+1 as rownum, * 
2 from (select @rownum:=0) as r, my_table ;

　　(2) 本题代码思路：三重子查询
　　　　　　内层：查询符合题目要求的员工并按员工号排序，命名为d1（derived table 1）
　　　　　　中层：在d1的基础上添加含有rownum列的表t，命名为d2
　　　　　　外层：输出rownum<=10的行的员工号

　　(3) 每个派生表必须拥有自己的名字（即使用不到），否则会收到“1248 - Every derived table must have its own alias”的提示

3. 找出比其部门经理工资高的员工，要求输出列是（员工号，经理员工号），按员工号升序列出前十个。注意，有些员工会在多个部门任职，部门经理也会轮换，所以两者在同一部门工作的日期必须有交集才算。

select emp_no, mng_no
from
    (
    select @rownum:=@rownum+1 as rownum, emp_no, mng_no
    from
        (
        select distinct es.emp_no emp_no, ms.emp_no mng_no
        from
            (
            select s1.emp_no, dm.dept_no, s1.salary, s1.from_date, s1.to_date
            from salaries s1 join dept_manager dm on
                (s1.emp_no = dm.emp_no
                and s1.from_date >= dm.from_date
                and s1.to_date <= dm.to_date)
            ) ms 
            join
            (
            select s2.emp_no, de.dept_no, s2.salary, s2.from_date, s2.to_date
            from salaries s2 join dept_emp de on
                (s2.emp_no = de.emp_no
                and s2.from_date >= de.from_date
                and s2.to_date <= de.to_date)
            ) es
            on 
            (ms.dept_no = es.dept_no and ms.salary < es.salary
            and ((ms.from_date <= es.from_date and es.from_date <= ms.to_date)
                or (ms.from_date <= es.to_date and es.to_date <= ms.to_date))
            )
        order by es.emp_no
        ) as d2,
        (select @rownum:=0) as t
    ) as d3
where rownum <= 10;

完整代码

 问题比较复杂，用了四重子查询（由内而外分别记为1234层），其中34层与第2问的思路相同。

　　第1层：(1)合并经理与工资表，记为ms；(2)合并员工与工资表，记为es
　　第2层：合并ms和es，并按照es.emp_no升序排列，命名为d2（derived table layer 2）
　　第3层：在d2的基础上添加含有rownum列的表t，命名为d3
　　第4层：输出rownum<=10的行

关于按照工作日期合并的问题：

　　第1层：保证工资表的时间段包含于经理表/员工表的时间段，并按照前者输出
　　　　　 即，s1.from_date >= dm.from_date and s1.to_date <= dm.to_date
　　　　　　　 (s1=salaries, dm=dept_manager)
　　第2层：保证ms与es的时间段有交集，即es的开始时间或结束时间包含于ms的时间段内
　　　　　 即，(ms.from_date <= es.from_date and es.from_date <= ms.to_date)
　　　　　　   or (ms.from_date <= es.to_date and es.to_date <= ms.to_date)

4. 将每个部门的员工的姓串接在一列中，要求以逗号分隔，按姓升序排序，仅包括前5个员工。输出格式为（部门号，员工姓），按部门号升序输出。

create view dept_emp_name
as
    (select distinct de.dept_no, emp.last_name
    from dept_emp de join employees emp using(emp_no)
    );
select dept_no, group_concat(last_name order by last_name asc separator ‘, ‘)
from
    (
    select d2.dept_no, d2.last_name
    from dept_emp_name d1 left join dept_emp_name d2
        on d1.dept_no = d2.dept_no
        and d1.last_name <= d2.last_name
    group by d2.dept_no, d2.last_name
    having count(d1.last_name)<=5
    ) d
group by dept_no
order by dept_no asc;

完整代码

思路:(1)创建视图create view dept_emp_name便于重复使用(dept_no, last_name)
　　 (2)双重循环实现“排序-取前五-拼接”
　　　　 内层：按照last_name的字典序把dept_emp_no跟自己做个连接，
　　　　　　　 筛选出排在前五的last_name
　　　　 外层：按照dept_no排序并分类，用group_concat()排序并连接last_name
　　　　　　   group_concat(last_name order by last_name asc separator ‘, ‘)

注：删除视图drop view dept_emp_name

5. 列出每个部门中的最高和最低工资的员工姓名及其工资。输出格式为（部门号，最高工资员工号，最高工资，最低工资员工号，最低工资），按部门号升序输出。

step 1:创建视图“部门-员工-工资”

create view dept_emp_salary
as 
    (select de.dept_no, de.emp_no, s.salary
    from dept_emp de join salaries s
        on de.emp_no = s.emp_no
        and de.from_date <= s.from_date and s.to_date <= de.to_date
    );

step 2：分别查询(部门，最高工资员工号，最高工资)和(部门，最低工资员工号，最低工资)，然后按相同部门号合并

select s_max.dept_no, s_max.emp_no max_sal_emp_no, s_max.salary max_sal,
    s_min.emp_no min_sal_emp_no, s_min.salary min_sal
from
    (select d1.dept_no, d1.emp_no, d1.salary
    from dept_emp_salary d1
    where not exists
        (select *
        from dept_emp_salary d2
        where d1.dept_no = d2.dept_no
            and d1.salary < d2.salary
        )
    ) s_max
    join
    (select d1.dept_no, d1.emp_no, d1.salary
    from dept_emp_salary d1
    where not exists
        (select *
        from dept_emp_salary d2
        where d1.dept_no = d2.dept_no
            and d1.salary > d2.salary
        )
    ) s_min
    on s_max.dept_no = s_min.dept_no
order by s_max.dept_no asc;

方法一：not exists

select s_max.dept_no, s_max.emp_no max_sal_emp_no, s_max.salary max_sal,
    s_min.emp_no min_sal_emp_no, s_min.salary min_sal
from
    (select d1.dept_no, d1.emp_no, d1.salary
    from dept_emp_salary d1
    where d1.salary >= all
        (select d2.salary
        from dept_emp_salary d2
        where d1.dept_no = d2.dept_no
        )
    ) s_max
    join
    (select d1.dept_no, d1.emp_no, d1.salary
    from dept_emp_salary d1
    where d1.salary <= all
        (select d2.salary
        from dept_emp_salary d2
        where d1.dept_no = d2.dept_no
        )
    ) s_min
    on s_max.dept_no = s_min.dept_no
order by s_max.dept_no asc;

方法二：all

　　注：(1)方法一用时436.8sec，方法二用时491.7sec，可见not exists方法略优于all方法，差别不大
　　　　(2)如果使用默认timeout(30sec)则会遇到报错"Error Code: 2013. Lost connection to MySQL server during query"，解决方法见上面的tips

6. 查询最高工资所在的部门中的最低工资是多少？输出格式为（部门号，最高工资，最低工资），按部门号升序输出。

step 1:创建视图

create view dept_salary
as 
    (select de.dept_no, s.salary
    from dept_emp de join salaries s
        on de.emp_no = s.emp_no
        and de.from_date <= s.from_date and s.to_date <= de.to_date
    );

创建视图“dept_salary”

create view dept_maxsal
as
    (select d1.dept_no, d1.salary
    from dept_salary
    where d1.salary >= all
        (select d2.salary
        from dept_salary d2)
    );

创建视图“dept_maxsal”

step 2:按照dept_maxsal中得到的信息，找对应部门的最低工资

select dm.dept_no, dm.salary max_sal, ds.salary min_sal
from dept_maxsal dm join dept_salary ds
    on dm.dept_no = ds.dept_no
where ds.salary <= all
    (select d.salary
    from dept_salary d
    where d.dept_no = ds.dept_no
    )
order by dm.dept_no asc;

主体

注：虽然结果只有一行，但还是需要加上那句order by以符合题意。

7(选做). 比较男女职工的平均工资差异。一个人的生涯平均工资这样计算：先将某段时间内的年薪换算成日薪（统一用一年360天），再乘以在这个年薪水平下所工作的天数，得到收入，将所有不同时段的收入加起来，再除以总天数，这样就得到总平均日薪了，求所有男女员工的平均日薪也是类似做法。输出格式为（男职工平均日薪，女职工平均日薪，平均日薪差）。

(待完成) 

8(选做). 11027号员工有过三个不同的title， 将其按如下表格形式输出：

(待完成)