SQL刷题第五天 牛客

题一：查找当前薪水详情以及部门编号dept_no

有一个全部员工的薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

有一个各个部门的领导表dept_manager简况如下:

dept_no	emp_no	to_date
d001	10001	9999-01-01
d002	10003	9999-01-01

请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:

emp_no	salary	from_date	to_date	dept_no
10001	88958	2002-06-22	9999-01-01	d001
10003	43311	2001-12-01	9999-01-01	d002

 方法（左连接+升序排序）：

select s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
    from salaries s left join dept_manager d
    on s.emp_no = d.emp_no
    where d.dept_no is not null
    order by s.emp_no

题二：查找所有员工的last_name和first_name以及对应部门编号dept_no

有一个员工表，employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

有一个部门表，dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d002	1989-08-03	9999-01-01

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下:

last_name	first_name	dept_no
Facello	Georgi	d001
Simmel	Bezalel	d002
Bamford	Parto	NULL
Koblick	Chirstian	NULL

 方法（左连接）：

select e.last_name ,e.first_name ,d.dept_no  
    from employees e left join dept_emp d
    on e.emp_no  = d.emp_no 

题三：获取所有员工当前的manager

有一个员工表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 e.emp_no,m.emp_no as manager
    from dept_emp e left join dept_manager m
    on e.dept_no=m.dept_no 
    WHERE e.emp_no!=m.emp_no
    AND e.to_date='9999-01-01'
    AND m.to_date='9999-01-01'

题四：统计出当前各个title类型对应的员工当前薪水对应的平均工资

有一个员工职称表titles简况如下:

emp_no	title	from_date	to_date
10001	Senior Engineer	1986-06-26	9999-01-01
10003	Senior Engineer	2001-12-01	9999-01-01
10004	Senior Engineer	1995-12-01	9999-01-01
10006	Senior Engineer	2001-08-02	9999-01-01
10007	Senior Staff	1996-02-11	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	1986-06-26	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	74057	1995-12-01	9999-01-01
10006	43311	2001-08-02	9999-01-01
10007	88070	2002-02-07	9999-01-01

请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序，以上例子输出如下:

title	avg(s.salary)
Senior Engineer	62409.2500
Senior Staff	88070.0000

 方法（连接）：

select t.title,avg(s.salary) as average
from titles as t
join salaries as s
on t.emp_no = s.emp_no
group by t.title
order by average

题五：查找所有员工的last_name和first_name以及对应的dept_name

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

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Chirstian	Koblick	M	1986-12-01

有一个部门表departments表简况如下:

dept_no	dept_name
d001	Marketing
d002	Finance
d003	Human Resources

有一个，部门员工关系表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	1990-08-05	9999-01-01

请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工，以上例子输出如下:

last_name	first_name	dept_name
Facello	Georgi	Marketing
Simmel	Bezalel	Marketing
Bamford	Parto	Finance
Koblick	Chirstian	NULL

方法（左连接+内左连接）：

SELECT
    last_name,
    first_name,
    dept_name
FROM
    employees AS e
    LEFT JOIN (
        SELECT
            emp_no,
            dept_name
        FROM
            dept_emp AS de
            LEFT JOIN departments AS d ON de.dept_no = d.dept_no
    ) k ON e.emp_no = k.emp_no;