SQL练习8-牛客网21-30题-自增列显示排名

链接：www.nowcoder.com/ta/sql?page…

注意
$\color{red}{第23题}$ 自增列显示排名
$\color{red}{第25题}$ 同一个薪资表里查员工和经理的薪资，再比较

表结构：
employees (emp_no,birth_date,first_name,last_name,gender,hire_date)
salaries (emp_no,salary,from_date,to_date)
departments (dept_no,dept_name)
dept_emp (emp_no,dept_no,from_date,to_date)
dept_manager (dept_no,emp_no,from_date,to_date)
titles (emp_no,title,from_date,to_date)

21. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
    （注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）

select a.emp_no, (a.salary-b.salary) growth from
(select emp_no, salary from salaries where to_date='9999-01-01') a join
(select s.emp_no, s.salary from salaries s join employees e on s.emp_no=e.emp_no and s.from_date=e.hire_date) b
on a.emp_no=b.emp_no
order by growth

22. 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum

select ds.dept_no, ds.dept_name, count(s.salary)
from salaries s
join dept_emp de on s.emp_no=de.emp_no
join departments ds on de.dept_no=ds.dept_no
group by ds.dept_no

23. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
错误答案：

select emp_no, salary, (select count(salary) from salaries s2 where s1.salary>s2.salary and to_date='9999-01-01') rank
from salaries s1
where to_date='9999-01-01'
order by salary, emp_no

修正答案：

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

24. 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

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

25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
    结果第一列给出员工的emp_no，
    第二列给出其manager的manager_no，
    第三列给出该员工当前的薪水emp_salary,
    第四列给该员工对应的manager当前的薪水manager_salary

select t1.emp_no, t2.emp_no as manager_no, t1.salary as emp_salary , t2.salary as manager_salary from
    (select de.emp_no, de.dept_no, s.salary from dept_emp de 
    join salaries s on de.emp_no=s.emp_no
    where de.to_date='9999-01-01' and s.to_date='9999-01-01') t1
inner join 
    (select dm.emp_no, dm.dept_no, s.salary from dept_manager dm 
    join salaries s on dm.emp_no=s.emp_no
    where dm.to_date='9999-01-01' and s.to_date='9999-01-01') t2
on t1.dept_no=t2.dept_no
where t1.salary>t2.salary

26. 汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = '9999-01-01')员工的当前(titles.to_date = '9999-01-01')title以及该类型title对应的数目count
    (注：因为员工可能有离职，所有dept_emp里面to_date不为'9999-01-01'就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 '9999-01-01'，那么这个可能是员工之前的职位信息，也不计入统计)

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

27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
    提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
    (数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

select s1.emp_no, s2.from_date, (s2.salary-s1.salary) as salary_growth
from salaries s1
left join salaries s2 on s1.emp_no=s2.emp_no and s1.to_date=s2.from_date
where s2.salary-s1.salary>5000
order by salary_growth desc

表结构：
film (film_id,title,description)
category (category_id,name,last_update)
film_category (film_id,category_id,last_update)

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

28. 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部 错误：

select c.name, count(f.film_id)
from film f
left join film_category fc on f.film_id = fc.film_id
left join category c on c.category_id = fc.category_id
where f.description like '%robot%' and count(fc.category_id)>=5
group by fc.category_id

修正：

select c.name, count(f.film_id)
from film f
left join film_category fc on f.film_id = fc.film_id
left join category c on c.category_id = fc.category_id
where f.description like '%robot%' 
and fc.category_id = (SELECT category_id FROM film_category GROUP BY category_id HAVING count(film_id)>=5)
group by fc.category_id

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 category_id is null

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

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