练习SQL利器，牛客网SQL实战题库，25~32题

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

题目描述：
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date))

考虑过程：
①提取de.emp_no,de.dept_no,s.salary获得一个员工薪水表
②提取dm.emp_no,dm.dept_no,s.salary获得一个经理薪水表
③合并INNER JOIN获得一个目标薪水表
④选出员工薪水大于经理的列

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

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

题目描述：
汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

解题思路：
①这是一个三表连接加上分组的问题
②先将titles和dept_emp连接，然后将departments 填进去
③讲dept_no和title分组统计

SELECT dept.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS dept 
ON t.emp_no = dept.emp_no AND dept.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp 
ON dept.dept_no = dp.dept_no
GROUP BY dept.dept_no, t.title

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

题目描述：
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

参考高赞答案，自己编写的发生了未知错误。

image.png

28.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

题目描述：
题目描述与编写信息在此
谁能让我看看数据长啥样啊~~

image.png

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

题目描述与编写信息在此
解题思路：两种方法
①第一种，连接表之后用NOT IN
②第二种，识别分类id是NULL

SELECT f.film_id,f.title
FROM film f LEFT JOIN film_category fc
ON f.film_id = fc.film_id
WHERE f.film_id NOT IN (SELECT film_id FROM film_category);



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 INNER JOIN film_category fc
ON f.film_id = fc.film_id
WHERE fc.category_id IN (SELECT category_id FROM category
                        WHERE name="Action")

31.获取select * from employees对应的执行计划

EXPLAIN SELECT * FROM employees

可以使用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节。
SQLite Explain

32.将employees表的所有员工的last_name和first_name拼接起来作为Name

题目描述：
将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
CREATE TABLE employees ( emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

SELECT last_name||" "||first_name AS Name
FROM employees

注：有些版本还可以用CONCAT