《SQL 实战》题解（17-32题）

最新推荐文章于 2022-10-29 13:25:22 发布

bm1998

最新推荐文章于 2022-10-29 13:25:22 发布

阅读量275

点赞数

分类专栏： # 《SQL 实战》文章标签： SQL

本文链接：https://blog.youkuaiyun.com/bm1998/article/details/107827771

版权

《SQL 实战》专栏收录该内容

3 篇文章

订阅专栏

文章目录

SQL17
SQL18
SQL19
SQL20
SQL21
SQL22
SQL23
SQL24
SQL25
SQL26
SQL27
SQL28
SQL29
SQL30
SQL31
SQL32

SQL17

题目

获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

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`));

题解

SELECT emp_no, salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
LIMIT 1,1;

SQL18

题目

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成

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`));
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`));

题解

SELECT e.emp_no, MAX(s.salary), e.last_name, e.first_name
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no
AND s.to_date='9999-01-01'
AND s.salary < (
    SELECT MAX(salary)
    FROM salaries
    WHERE to_date='9999-01-01'
);

SQL19

题目

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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 `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`));

题解

先内连接 departments 表和 dept_emp 表形成新表 a，再左连接 employees 表和 a 表

SELECT e.last_name, e.first_name, x.dept_name
FROM employees AS e LEFT JOIN (
    SELECT de.emp_no, d.dept_name
    FROM departments AS d, dept_emp AS de
    WHERE d.dept_no = de.dept_no
) AS x
on e.emp_no = x.emp_no;

SQL20

题目

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

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`));

题解

SELECT (a.salary - b.salary) AS growth
FROM 
-- 当前薪水
(
    SELECT salary, MAX(to_date)
    FROM salaries
    WHERE emp_no = 10001
) AS a,
-- 入职薪水
(
    SELECT salary, MIN(from_date)
    FROM salaries
    WHERE emp_no = 10001
) AS b;

SQL21

题目

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

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`));
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`));

题解

SELECT a.emp_no, (a.salary - b.salary) AS growth
FROM 
-- 当前工资
(
    SELECT emp_no, salary 
    FROM salaries 
    WHERE to_date='9999-01-01'
) AS a, 
-- 入职工资
(
    SELECT e.emp_no, s.salary 
    FROM employees AS e, salaries AS s 
    WHERE e.emp_no = s.emp_no
    AND e.hire_date = s.from_date
) AS b
WHERE a.emp_no = b.emp_no
ORDER BY growth;

SQL22

题目

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

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 `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`));

题解

SELECT a.dept_no, a.dept_name, COUNT(*) AS sum
FROM salaries AS s, 
(
    SELECT de.emp_no, d.dept_no, d.dept_name
    FROM departments AS d, dept_emp AS de
    WHERE d.dept_no = de.dept_no
) AS a
WHERE s.emp_no = a.emp_no
GROUP BY a.dept_no;

SQL23

题目

对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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`));

题解

使用 COUNT(DISTINCT s2.salary) 和 s1.salary <= s2.salary 可以得到当前工资的排名

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS 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 rank, s1.emp_no

SQL24

题目

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

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 `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`));
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`));

题解

不需要 employees 表

SELECT de.dept_no, s.emp_no, s.salary
FROM dept_emp AS de, salaries AS s
WHERE de.emp_no = s.emp_no
AND s.to_date = '9999-01-01'
AND s.emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
);

SQL25

题目

获取员工其当前的薪水比其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`));

题解

SELECT a.emp_no, b.emp_no AS manager_no, a.salary AS emp_salary, b.salary AS manager_salary
FROM
-- 员工工资
(
    SELECT de.emp_no, de.dept_no ,s.salary
    FROM dept_emp AS de, salaries AS s
    WHERE de.emp_no = s.emp_no
    AND de.to_date = '9999-01-01'
    AND s.to_date = '9999-01-01'
) AS a,
-- 经理工资
(
    SELECT dm.emp_no, dm.dept_no ,s.salary
    FROM dept_manager AS dm, salaries AS s
    WHERE dm.emp_no = s.emp_no
    AND dm.to_date = '9999-01-01'
    AND s.to_date = '9999-01-01'
) AS b
WHERE a.dept_no = b.dept_no
AND a.salary > b.salary

SQL26

题目

汇总各个部门当前员工的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’，那么这个可能是员工之前的职位信息，也不计入统计)

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);

题解

SELECT dt.dept_no, d.dept_name, dt.title, COUNT(dt.title)
FROM departments AS d,
(
    SELECT de.emp_no, de.dept_no, t.title
    FROM dept_emp AS de, titles AS t
    WHERE de.emp_no = t.emp_no
    AND de.to_date = '9999-01-01'
    AND t.to_date = '9999-01-01'
) AS dt
WHERE d.dept_no = dt.dept_no
GROUP BY dt.dept_no, dt.title

SQL27

题目

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)

(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

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`));

题解

SELECT s1.emp_no, s1.from_date, (s1.salary - s2.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND salary_growth > 5000
AND strftime('%Y', s1.from_date) - strftime('%Y', s2.from_date) = 1
ORDER BY salary_growth DESC

SQL28

题目

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

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

题解

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

SQL29

题目

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

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

题解

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

SQL30

题目

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

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

题解

SELECT title, description
FROM film
WHERE film_id IN (
    SELECT film_id
    FROM film_category
    WHERE category_id = (
        SELECT category_id
        FROM category
        WHERE name = 'Action'
    )
)

SQL31

题目

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

题解

EXPLAIN SELECT * FROM employees

SQL32

题目

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)

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`));

题解

-- MYSQL
SELECT CONCAT(CONCAT(last_name," "),first_name) AS name FROM employees
-- sqllite
SELECT last_name || " " || first_name AS name FROM employees