牛客SQL（19-20）

19 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select last_name , first_name , dept_name
from employees em
left join dept_emp de on em.emp_no = de.emp_no
left join departments des on de.dept_no = des.dept_no;

20 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

找到最晚和最早发工资的日期

解法一：order by

select (select salary from salaries
where emp_no = 10001
order by to_date desc limit 1) -
(select salary from salaries
where emp_no = 10001
order by to_date limit 1) growth;

解法二： select max/min

select (select salary from salaries
where emp_no = 10001
and to_date = (select max(to_date) from salaries
where emp_no = 10001)) -
(select salary from salaries where emp_no = 10001
and to_date = (select min(to_date) from salaries where emp_no = 10001)) growth;