25、获取员工其当前的薪水比其manager当前薪水还高的相关信息
这道题有三张表,员工表、经理表、工资表,首先是需要把几张表联结,然后获取员工和经理对应,再根据薪水条件过滤。
说的轻巧,那么应该怎么去写这个语句呢。
本题主要思想是创建两张表(一张记录当前所有员工的工资,另一张只记录部门经理的工资)进行比较,具体思路如下:
1、先用INNER JOIN连接salaries和demp_emp,建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager,建立当前所有部门经理的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工,并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
思路真是妙啊!反正是非常的清晰,来自牛客讨论区的大佬ciphersaw,反正我觉得只要在牛客刷过SQL练习的都知道这个人!
虽然我现在是SQL菜鸟,但是跟着大佬好走路不是!
下面给出语句:
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 AS s
INNER JOIN dept_emp AS 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 AS s
INNER JOIN dept_manager AS 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类型的分配数目
这道题首先看到输出结构我们就明白是什么意思了,也就是说将员工表先按照部门分组再按照title分组即可,然后COUNT计数。
SELECT de.dept_no,
dp.dept_name,
t.title,
COUNT(t.title) AS count
FROM dept_emp AS de
INNER JOIN departments AS dp
ON de.dept_no = dp.dept_no
INNER JOIN titles AS t
ON de.emp_no = t.emp_no
AND de.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
GROUP BY de.dept_no, t.title
27、给出每个员工每年薪水涨幅超过5000的员工编号emp_no
这道题的话重点在于求涨幅,之前我们好像做过类似的题目,要点就在于一表复用。题眼是每年,这里还要把日期字段处理一下。
SELECT s2.emp_no,
s2.from_date,
(s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1,
salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND s2.salary - s1.salary > 5000
AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
ORDER BY salary_growth DESC
-- 条件:每年薪水涨幅都超过5000
-- 技巧:复用salaries表
28、查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5
首先这道题,包括robot要用到 LIKE ‘%robot%’。其次我们要求电影名称对应的分类以及分类对应的名称,这些信息分散在不同的表中。原始表有三张,这里我们还构造了一个临时表满足过滤条件。
SELECT c.name, COUNT(f.film_id)
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 ci
-- 构造一个表即满足各分类电影数量>=5的条件
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.category_id = ci.category_id
这个题解我们发现没有用ON联结,而是在WHERE里联结的。
29、使用join查询方式找出没有分类的电影id以及名称
这道题其实明摆着可以用子查询NOT IN的,但是这里说用JOIN,那么我们想到IS NULL。
SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN film_category AS fc
ON f.film_id = fc.film_id
WHERE fc.category_id IS NULL
30、使用子查询的方式找出属于Action分类的所有电影对应的title,description
子查询么那么就是层层嵌套咯。
-- 1.找出属于Action分类的对应的分类名称
-- 2.找出该名称对应的电影id
-- 3.找出该电影id对应的电影名称和描述
SELECT f.title, f.description
FROM film AS f
WHERE f.film_id IN (SELECT fc.film_id
FROM film_category AS fc
WHERE fc.category_id IN (SELECT c.category_id
FROM category AS c
WHERE c.name = 'Action'))