mysql练习题

SQL练习
题目来源 https://www.nowcoder.com/activity/oj
需要用到的表

departments.png dept_emp.png dept_manager.png employees.png salaries.png titles.png user_key_action.png user_profile.png user_view_action.png

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;