牛客——数据库实战（1~30）

1. 查找最晚入职员工的所有信息

select * from employees where hire_date = (select max(hire_date) from employees)

2. 查找入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date desc limit 2,1

3. 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

这里特别注意 from 后面要先放 salaries, 再放 dept_manager。

select s.*, d.dept_no 
from salaries s, dept_manager d
where d.to_date='9999-01-01' 
and s.to_date='9999-01-01' 
and d.emp_no = s.emp_no

4. 查找所有已经分配部门的员工的last_name和first_name

select last_name, first_name, d.dept_no 
from employees e, dept_emp d 
where e.emp_no = d.emp_no

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no

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

6. 查找所有员工入职时候的薪水情况

select e.emp_no, s.salary from employees e, salaries s 
where e.hire_date = s.from_date 
and e.emp_no = s.emp_no 
order by e.emp_no desc

7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

select emp_no, count(*) t from salaries group by emp_no 
having count(*) > 15

8. 找出所有员工当前具体的薪水salary情况

select salary from salaries 
where to_date='9999-01-01' 
group by salary order by salary desc

9. 获取所有部门当前manager的当前薪水情况

select d.dept_no, d.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

10. 获取所有非manager的员工emp_no

select emp_no from employees 
where emp_no not in (select emp_no from dept_manager)

11. 获取所有员工当前的manager

select de.emp_no, dm.emp_no as manager_no 
from dept_emp de, dept_manager dm 
where de.to_date='9999-01-01' 
and dm.to_date='9999-01-01' 
and de.emp_no != dm.emp_no 
and de.dept_no = dm.dept_no

12. 获取所有部门中当前员工薪水最高的相关信息

select d.dept_no, d.emp_no, max(salary)
from dept_emp d, salaries s 
where d.to_date = '9999-01-01' 
and s.to_date = '9999-01-01' 
and d.emp_no = s.emp_no 
group by d.dept_no

13. 从titles表获取按照title进行分组

select title, count(*) t from titles 
group by title having t >=2

14. 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。

select title, count(distinct emp_no) t from titles 
group by title having t >=2

15. 查找employees表所有emp_no为奇数

select * from employees 
where emp_no%2 != 0 
and last_name != 'Mary' 
order by hire_date desc

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

select title, avg(salary) from titles t, salaries s 
where t.to_date = '9999-01-01' 
and s.to_date = '9999-01-01' 
and t.emp_no = s.emp_no 
group by title

17. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from salaries 
where to_date = '9999-01-01'
order by salary desc limit 1,1

18. 查找当前薪水排名第二多的员工编号emp_no

select e.emp_no, max(s.salary), e.last_name, e.first_name 
from employees e, salaries s 
where s.to_date='9999-01-01' 
and e.emp_no = s.emp_no 
and s.salary not in (select max(salary) from salaries where to_date='9999-01-01')

19. 查找所有员工的last_name和first_name以及对应的dept_name

select last_name, first_name, dept_name 
from employees e 
left join dept_emp de 
on e.emp_no = de.emp_no 
left join departments dp 
on de.dept_no = dp.dept_no

20. 查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

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

21. 查找所有员工自入职以来的薪水涨幅情况

这道题有点难度

SELECT a.emp_no,( b.salary - c.salary ) as 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

22. 统计各个部门对应员工涨幅的次数总和

select d.dept_no, d.dept_name, count(*) as sum 
from departments d,dept_emp as de, salaries as s 
where d.dept_no=de.dept_no 
and de.emp_no=s.emp_no 
group by d.dept_no

select dp.dept_no, dp.dept_name, count(*) sum 
from departments dp 
inner join dept_emp de on dp.dept_no = de.dept_no 
inner join salaries s on de.emp_no = s.emp_no 
group by dp.dept_no 

23. 对所有员工的当前薪水按照salary进行按照1-N的排名

select s1.emp_no,s1.salary,	count(distinct s2.salary) as rank 
from salaries s1, salaries s2 
where s1.to_date='9999-01-01' 
and s2.to_date='9999-01-01' 
and s1.salary <= s2.salary 
group by s1.emp_no 
order by s1.salary desc, s1.emp_no

本题的主要思想是复用salaries表进行比较排名，具体思路如下：
1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。

2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。

3、千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()。

4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果。

24. 获取所有非manager员工当前的薪水情况

select de.dept_no, de.emp_no, s.salary 
from dept_emp de, salaries s 
where de.emp_no not in (select emp_no from dept_manager) 
and de.emp_no = s.emp_no 
and de.to_date = '9999-01-01' 
and s.to_date = '9999-01-01' 

25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

26. 汇总各个部门当前员工的title类型的分配数目

select de.dept_no, dp.dept_name, t.title, count(t.title) count 
from titles t inner join dept_emp de 
on t.emp_no = de.emp_no and t.to_date = '9999-01-01' and de.to_date = '9999-01-01' 
inner join departments dp on de.dept_no = dp.dept_no 
group by de.dept_no, t.title

如果只按dept_no分组，那么每个dept_no只会出现一条记录，则不能体现同一dept_no中的不同title。GROUP BY de.dept_no, t.title 的作用是同时将de.dept_no和t.title分组，即要两者都相同才能判别为相同的分组，只要一个不同就算不同的分组。

27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no

select s1.emp_no, s1.from_date, (s1.salary-s2.salary) salary_growth 
from salaries s1, salaries s2 
where s1.emp_no = s2.emp_no 
and s1.salary -s2.salary > 5000 
and strftime('%Y', s1.to_date) - strftime('%Y', s2.to_date) = 1 
order by salary_growth desc

28. 查找描述信息中包括robot的电影对应的分类名称以及电影数目

SELECT c.name AS name, COUNT(f.film_id) AS amount
FROM film AS f, film_category AS fc, category AS c,
(SELECT category_id FROM film_category GROUP BY category_id HAVING COUNT(category_id) >= 5) AS cc
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.category_id = cc.category_id

先将描述信息包含 “robot” 的电影分类及数量统计出来，再针对每一个分类，在 film_category 总表中该分类下所有电影的数量需要 >=5，最后筛选出满足条件的统计结果输出。

29. 使用join查询方式找出没有分类的电影id以及名称

select f.film_id, f.title from film f left join film_category fc 
on f.film_id = fc.film_id 
where fc.category_id is null

30. 使用子查询的方式找出属于Action分类的所有电影对应的title,description

select f.title,f.description from film f 
where f.film_id in (select fc.film_id from film_category fc where fc.category_id in 
                   (select c.category_id from category c where c.name = 'Action'))