#数据库SQL实战#【Day7】

--------------------------------------------------

学习用，欢迎讨论。

--------------------------------------------------

查看详细题目的方法：

复制以下题目内容；

打开网站：https://www.nowcoder.com/ta/sql；

Ctrl+F查找刚刚复制的题目即可。

--------------------------------------------------

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

方法一：直接连接四张表，两次使用salaries表，分别记录员工和manager的薪水

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

方法二：首先分别连接dept_emp表和salaries表、dept_manager表和salaries表记录员工及其薪水信息、manager及其薪水信息。再通过dept_no连接这两张表，并限制salary条件。

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

--------------------------------------------------

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

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

直接通过dept_no和emp_no字段连接三张表，进而进行查询。

--------------------------------------------------

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

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

两次使用salaries表，第一次当做涨薪后的工资表、第二次当做涨薪前的工资表。本题的关键是对“每年”的理解，上述代码是用的是“薪水开始年份相差一年或者薪水结束年份相差一年”。感觉怪怪的。。。这些都是语言解释上的差别，和SQL语言本身并无多大关系。

--------------------------------------------------

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

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

题目要求查找满足要求的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部。

首先将分类下电影数量大于等于5部的分类找出来构成表cc。再将cc表和film_category表、film表连接。就得到上述代码，并通过测试。

以下代码先查找满足要求的分类名称以及电影数目，在从中选择分类下电影数目大于等于5部的分类。但是没有能通过测试，有时间再思考一下。

select * from
(
    select c.name, count(f.film_id) as t
    from category as c
    inner join film_category as fc on c.category_id = fc.category_id
    inner join film as f on fc.film_id = f.film_id
    where f.description like '%robot%'
)
where t >= 5

--------------------------------------------------