MySQL 练习(员工数据库)

Posted on Edited on In Views:
员工数据库查询练习

SQL 查询练习

员工数据库

  • 查询每个部门的经理信息。输出格式为（部门名，经理姓，经理名），按部门名升序输出
1
2
3
4
select D.dept_name, E.last_name, E.first_name
from (departments D left join dept_manager DM on D.dept_no = DM.dept_no)
                    left join employees E on DM.emp_no = E.emp_no
order by D.dept_name asc
  • 找出那些至少拥有三个title并且至少在两个部门工作过的员工，要求按升序输出前十个员工号
    • count 的时候需要去重
1
2
3
4
5
6
7
select E.emp_no
from employees E inner join dept_emp DE on E.emp_no = DE.emp_no
                 inner join titles T on E.emp_no = T.emp_no
group by E.emp_no
having count(distinct T.title) >= 3 and count(distinct DE.dept_no) >= 2
order by E.emp_no asc
limit 10
  • 找出比其部门经理工资高的员工，要求输出列是（员工号，经理员工号），按员工号升序，员工号相同的按经理员工号升序，列出前十个。注意：有些员工会在多个部门任职，部门经理也会轮换，所以两者在同一部门工作的日期必须有交集。而任期内工资也可能是变化的，所以还必须考虑工资日期的交集。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select distinct DE.emp_no, DM.emp_no
from (dept_emp DE inner join salaries S1 on(
    /* 工资在部门任期之内 */
    DE.emp_no = S1.emp_no
    and S1.from_date >= DE.from_date
    and S1.to_date <= DE.to_date
)),
(dept_manager DM inner join salaries S2 on(
    DM.emp_no = S2.emp_no
    and S2.from_date >= DM.from_date
    and S2.to_date <= DM.to_date
))
where DM.dept_no = DE.dept_no        /* 部门经理 */
    and S1.salary > S2.salary        /* 工资更高 */
    /* 工资时间交集 */
    and (S1.from_date <= S2.to_date and S2.from_date <= S1.to_date)
    /* 工作时间交集 */
    and (DM.from_date <= DE.to_date and DE.from_date <= DM.to_date)
order by DE.emp_no asc, DM.emp_no asc
limit 10
  • 将每个部门的员工的姓串接在一列中，要求以逗号分隔，按姓升序排序，仅包括前5个员工。输出格式为（部门号，员工姓），按部门号升序输出。
1
2
3
4
5
6
7
8
select DE.dept_no, substring_index(
    group_concat(E.last_name order by E.last_name asc separator ','),
    ',',
    5
)
from dept_emp DE inner join employees E on DE.emp_no = E.emp_no
group by DE.dept_no
order by DE.dept_no asc
  • 列出每个部门中的最高和最低工资的员工及其工资。输出格式为（部门号，最高工资员工号，最高工资，最低工资员工号，最低工资）。注意：员工某一时期的工资，日期范围要和在该部门任职日期范围有交集，才能算作该部门的工资
  • 先找出最高工资，再找到人
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/* 超时了 */
with TMP as (
select S.salary salary, E.emp_no emp_no, DE.dept_no dept_no
from dept_emp DE inner join employees E on DE.emp_no = E.emp_no
                 inner join salaries S on (
                     E.emp_no = S.emp_no and
                     /* 工资在部门任期之内 */
                     S.from_date >= DE.from_date and S.to_date <= DE.to_date
                 )
)

select t_max.dept_no, t3.emp_no, max_salary, t4.emp_no, min_salary
from
/* 先求出最高工资 */
(
    select t1.dept_no dept_no, max(t1.salary) max_salary
    from TMP t1
    group by t1.dept_no
) t_max
inner join
/* 最低工资 */
(
    select t2.dept_no dept_no, min(t2.salary) min_salary
    from TMP t2
    group by t2.dept_no
) t_min
on t_max.dept_no = t_min.dept_no
/* 再连接 */
inner join TMP t3
on t3.dept_no = t_max.dept_no and t3.salary = t_max.max_salary
inner join TMP t4
on t4.dept_no = t_min.dept_no and t4.salary = t_min.min_salary
  • 最大最小同时求，这样就不会超时
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with TMP as (
select S.salary salary, E.emp_no emp_no, DE.dept_no dept_no
from dept_emp DE inner join employees E on DE.emp_no = E.emp_no
                 inner join salaries S on (
                     E.emp_no = S.emp_no and
                     /* 工资在部门任期之内 */
                     S.from_date >= DE.from_date and S.to_date <= DE.to_date
                 )
)

select t_m.dept_no, t3.emp_no, max_salary, t4.emp_no, min_salary
from
/* 先求出最高最低工资 */
(
    select t1.dept_no dept_no, max(t1.salary) max_salary, min(t1.salary) min_salary
    from TMP t1
    group by t1.dept_no
) t_m
/* 再连接 */
inner join TMP t3
on t3.dept_no = t_m.dept_no and t3.salary = t_m.max_salary
inner join TMP t4
on t4.dept_no = t_m.dept_no and t4.salary = t_m.min_salary
  • 查询最高工资所在的部门中的最低工资是多少？输出格式为（部门号，最高工资，最低工资），按部门号升序输出。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
with TMP as (
select S.salary salary, E.emp_no emp_no, DE.dept_no dept_no
from dept_emp DE inner join employees E on DE.emp_no = E.emp_no
                 inner join salaries S on (
                     E.emp_no = S.emp_no and
                     /* 工资在部门任期之内 */
                     S.from_date >= DE.from_date and S.to_date <= DE.to_date
                 )
)

select t_m.dept_no, max_salary msalary, min_salary nsalary
from (
    select t1.dept_no dept_no, max(t1.salary) max_salary, min(t1.salary) min_salary
    from TMP t1
    group by t1.dept_no
) t_m
order by max_salary desc
limit 1
  • 选做。比较男女职工的平均工资差异。一个人的生涯平均工资这样计算：先将某段时间内的年薪换算成日薪（统一用一年360天），再乘以在这个年薪水平下所工作的天数，得到收入，将所有不同时段的收入加起来，再除以总天数，这样就得到总平均日薪了，求所有男女员工的平均日薪也是类似做法。输出格式为（男职工平均日薪，女职工平均日薪，平均日薪差）。注意：to_date是'9999-01-01'的统一用最大的from_date+一年 代替，即'2003-08-01'
1
/* TODO */
  • 11027号员工有过三个不同的title， 将其按如下表格形式输出
emp_no tenure1 tenure2 tenure3
11027 1986-05-07~1991-05-07Engineer ... ... 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
/* MySQL 字符串拼接不能直接用 +, 需要使用 concat(,) */
select emp_no,
    concat(
        DATE_FORMAT(T1.from_date,'%Y-%m-%d'),
        '~',
        DATE_FORMAT(T1.to_date,'%Y-%m-%d'),
        '\n',
        T1.title
    ) tenure1,
    concat(
        DATE_FORMAT(T2.from_date,'%Y-%m-%d'),
        '~',
        DATE_FORMAT(T2.to_date,'%Y-%m-%d'),
        '\n',
        T2.title
    ) tenure2,
    concat(
        DATE_FORMAT(T3.from_date,'%Y-%m-%d'),
        '~',
        DATE_FORMAT(T3.to_date,'%Y-%m-%d'),
        '\n',
        T3.title
    ) tenure3
from titles T1 join titles T2 using(emp_no) join titles T3 using(emp_no)
where emp_no = 11027 and T1.from_date < T2.from_date and T2.from_date < T3.from_date