SQL练习
题目来源 https://www.nowcoder.com/activity/oj
需要用到的表
1.查找employees里最晚入职员工的所有信息
a.
select * from employees
where hire_date =
(select max(hire_date) from employees);
b.
select * from employees order by hire_date desc LIMIT 1; --limit 0,1;
2.查找employees里入职员工时间排名倒数第三的员工所有信息
a.
SELECT * from employees order by hire_date desc LIMIT 2,1;
b.
Select * from employees where hire_date =
(SELECT DISTINCT hire_date from employees order by hire_date desc LIMIT 2,1);
LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。
3.查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列
SELECT dept_manager .dept_no,salaries .salary ,salaries .from_date ,salaries .to_date ,dept_manager .dept_no
FROM dept_manager,salaries
WHERE dept_manager .emp_no = salaries .emp_no
AND dept_manager .to_date ='9999-01-01'
AND salaries .to_date ='9999-01-01';
4.查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示
SELECT employees.first_name,employees.last_name,dept_emp.dept_no
from employees,dept_emp
where employees.emp_no=dept_emp.emp_no;
5.查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工
SELECT employees.first_name,employees.last_name,dept_emp.dept_no
from employees left JOIN dept_emp on employees.emp_no=dept_emp.emp_no
where dept_emp .dept_no =null ;
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
7.查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
SELECT emp_no,COUNT(*) as t
from salaries
group by emp_no
having count(*)>=15;
8.找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
a.
SELECT distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc;
b.
select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;
10.找出所有非部门领导的员工emp_no
a.
SELECT employees .emp_no
from employees left join dept_manager on
employees.emp_no =dept_manager .emp_no
where dept_no is null;
b.
select emp_no from employees
where emp_no not in(select emp_no from dept_manager);
11.获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示
SELECT dept_emp .emp_no,dept_manager .emp_no
from dept_emp join dept_manager on
dept_emp .dept_no =dept_manager .dept_no
where dept_emp.to_date ='9999-01-01'
and dept_manager .to_date ='9999-01-01'
and dept_emp .emp_no <>dept_manager .emp_no ;
12.获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列
a.
SELECT dept_emp.dept_no ,dept_emp .emp_no ,MAX (salary)
from dept_emp join salaries on
dept_emp .emp_no =salaries .emp_no
where dept_emp.to_date ='9999-01-01'
and salaries.to_date ='9999-01-01'
group by dept_no
order by dept_no asc;
--假如某部门有最高薪资有多位员工，这里仅显示一条；
b.
SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
FROM
//创建maxsalary表用于存放当前每个部门薪水的最大值
(SELECT d.dept_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no) AS maxsalary,
//创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
(SELECT d.dept_no, s.emp_no, s.salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
) AS currentsalary
//限定条件为两表的dept_no和salary均相等
WHERE currentsalary.dept_no = maxsalary.dept_no
AND currentsalary.salary = maxsalary.salary
//最后以currentsalary.dept_no排序输出符合要求的记录表
ORDER BY currentsalary.dept_no
15.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
SELECT * from employees
where last_name <>'Mary' and emp_no%2=1
order by hire_date DESC ;
16.统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序；
SELECT title ,AVG (salary)
from titles join salaries on titles .emp_no =salaries .emp_no
where titles .to_date ='9999-01-01' and salaries .to_date ='9999-01-01'
GROUP by title
17.获取薪水第二多的员工的emp_no以及其对应的薪水salary
a.
select emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary DESC
limit 1,1;
b.
select emp_no,max(salary)
from salaries
where salary < (select max(salary) from salaries)
（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)
18.请你查找薪水排名第二多的员工编号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 AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN
(SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select es.last_name,es.first_name,ds.dept_name from
employees as es
left join dept_emp as de on es.emp_no=de.emp_no
left join departments as ds on de.dept_no=ds.dept_no;
