（二）牛客网SQL
select * from employees
order by hire_date desc limit 0,1;
select * from employees
order by hire_date desc limit 2,1;
select salaries.*,dept_manager.dept_no from salaries,dept_manager
on salaries.emp_no = dept_manager.emp_no
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01';
4.查找所有已经分配部门的员工的last_name和first_name以及dept_no
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees,dept_emp
on employees.emp_no = dept_emp.emp_no;
5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees
left join dept_emp
on dept_emp.emp_no=employees.emp_no;
6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
select employees.emp_no,salaries.salary from employees
left join salaries
where employees.emp_no=salaries.emp_no
and employees.hire_date = salaries.from_date
order by employees.emp_no desc;
7.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
select emp_no,count(emp_no) as t
from salaries
group by emp_no
having count(emp_no)>15;
8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
select distinct salary from salaries
where to_date='9999-01-01'
order by salary desc;
select d.dept_no,s.emp_no,s.salary
from dept_manager d,salaries s
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
and d.emp_no=s.emp_no;
select emp_no
from employees
where emp_no
not in (select emp_no from dept_manager);
select de.emp_no,dm.emp_no as manager_no
from dept_emp as de
inner join dept_manager as dm
on de.dept_no=dm.dept_no
where dm.to_date='9999-01-01'
and de.to_date='9999-01-01'
and de.emp_no <> dm.emp_no;
select e.dept_no,e.emp_no,MAX(s.salary)
from dept_emp e inner join salaries s
on e.emp_no = s.emp_no
where s.to_date='9999-01-01' and e.to_date = '9999-01-01'
GROUP BY e.dept_no
13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
select title,count(title) t
from titles
group by title
having t >= 2
select title,count(distinct emp_no) t
from titles
group by title
having t>= 2
15.查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)
select * from employees
where emp_no%2=1
and last_name<>"Mary"
order by hire_date desc
select t.title,avg(s.salary)
from titles t
join salaries s
on t.emp_no=s.emp_no
where t.to_date='9999-01-01'
and s.to_date='9999-01-01'
group by t.title
17.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no,salary
from salaries
order by salary desc limit 1,1
18.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗
select e.emp_no,max(s.salary) as salary,e.last_name,e.first_name
from employees e
join salaries s
on e.emp_no=s.emp_no
where s.salary not in (
select max(salary)
from salaries
19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select e.last_name,e.first_name,d.dept_name
from employees e
left join dept_emp de
on e.emp_no=de.emp_no
left join departments d
on de.dept_no=d.dept_no
20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)
select max(salary)-min(salary) growth
from salaries
where emp_no="10001"
select a.emp_no,(b.salary-c.salary) growth
from employees as a
inner join salaries as b
on a.emp_no=b.emp_no
and b.to_date="9999-01-01"
inner join salaries as c
on a.emp_no=c.emp_no
and a.hire_date=c.from_date
order by growth asc
22.统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum
select d.dept_no,d.dept_name,count(*)sum
from departments d
inner join dept_emp de
on d.dept_no=de.dept_no
inner join salaries s
on s.emp_no=de.emp_no
group by de.dept_no
23.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
select emp_no,salary,dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
24.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
select de.dept_no,e.emp_no,s.salary
from dept_emp de
inner join employees e
on e.emp_no=de.emp_no
inner join salaries s
on de.emp_no=s.emp_no
where s.to_date="9999-01-01"
and de.emp_no not in (
select emp_no from dept_manager
)
