数据库SQL实战题解

Posted on | Post modified: | In
Words count in article: 11.1k

本文为数据库SQL实战刷题笔记，刷题平台为牛客网。题量为61道。网站OJ编译器为sqlite3.7.9。

1 查找最晚入职员工的所有信息

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
查找最晚入职员工的所有信息
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`));

输入描述:
无
输出描述:
emp_no	birth_date	first_name	last_name	gender	hire_date

解题思路

1
2
3
SELECT * 
FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees);
1
2
3
SELECT * 
FROM employees
ORDER BY hire_date DESC LIMIT 0, 1;

2 查找入职员工时间排名倒数第三的员工所有信息

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
查找入职员工时间排名倒数第三的员工所有信息
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`));

输入描述:
无
输出描述:
emp_no	birth_date	first_name	last_name	gender	hire_date

解题思路

1
2
3
SELECT * 
FROM employees 
ORDER BY hire_date DESC LIMIT 2,1;
1
2
3
SELECT * 
FROM employees 
WHERE hire_date = (SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);

3 查找当前薪水详情以及部门编号dept_no

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号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`));

输入描述:
无
输出描述:
emp_no	salary	from_date	to_date	dept_no

解题思路

1
2
3
4
5
6
SELECT s.*, d.dept_no
FROM salaries as s 
    INNER JOIN dept_manager as d 
    ON s.emp_no = d.emp_no
WHERE s.to_date='9999-01-01'
    AND d.to_date='9999-01-01';

4 查找所有已经分配部门的员工的last_name和first_name

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
查找所有已经分配部门的员工的last_name和first_name
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`));
输入描述:
无
输出描述:
last_name	first_name	dept_no

解题思路

1
2
3
4
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e 
    INNER JOIN dept_emp AS d
    ON e.emp_no = d.emp_no

5 查找所有员工的last_name和first_name以及对应部门编号dept_no

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
查找所有员工的last_name和first_name以及对应部门编号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`));
输入描述:
无
输出描述:
last_name	first_name	dept_no

解题思路

  • INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
  • LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
  • RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
1
2
3
4
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e 
    LEFT JOIN dept_emp AS d
    ON e.emp_no = d.emp_no

6 查找所有员工入职时候的薪水情况

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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`));
输入描述:
无
输出描述:
emp_no	salary

解题思路

  • 在测试数据中，salaries中的emp_no不唯一（因为号码为emp_no的员工会有多次涨薪的可能，所有在salaries中对应的记录不止一条）。
  • employees.emp_no唯一，即salaries的数据会多于employees。
1
2
3
4
5
6
# 利用INNER JOIN连接两张表
SELECT e.emp_no, s.salary
FROM employees AS e 
    INNER JOIN salaries AS s
    ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC;
1
2
3
4
5
6
# 直接用逗号并列查询两张表
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
ORDER BY e.emp_no DESC;

7 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

题目描述

1
2
3
4
5
6
7
8
9
10
11
查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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`));
输入描述:
无
输出描述:
emp_no	t

解题思路

  • 用COUNT函数和GROUP BY语句可以统计同一emp_no值的记录条数。
  • 用AS语句将COUNT(emp_no) 的值转换为t。
  • COUNT函数不可用WHERE语句，因此使用HAVING语句。

  • 涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可。

  • 注意： 严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负。

1
2
3
4
SELECT emp_no, COUNT(*) AS t
FROM salaries
GROUP BY emp_no
HAVING t > 15;

8 找出所有员工当前薪水salary情况

题目描述

1
2
3
4
5
6
7
8
9
10
11
找出所有员工当前(to_date='9999-01-01')具体的薪水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`));
输入描述:
无
输出描述:
salary

解题思路

1
2
3
4
SELECT DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC;

9 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=’9999-01-01’

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'
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`));
输入描述:
无
输出描述:
dept_no	emp_no	salary

解题思路

1
2
3
4
5
6
7
# 使用联结。
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_manager AS d
    INNER JOIN salaries AS s
    ON d.emp_no = s.emp_no 
    AND d.to_date = '9999-01-01'
    AND s.to_date = '9999-01-01';
1
2
3
4
5
6
# 使用逗号并列查询
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_manager AS d, salaries AS s
WHERE d.emp_no = s.emp_no 
    AND d.to_date = '9999-01-01'
    AND s.to_date = '9999-01-01';

10 获取所有非manager的员工emp_no

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
获取所有非manager的员工emp_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`));
输入描述:
无
输出描述:
emp_no

解题思路

1
2
3
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);
1
2
3
4
5
SELECT e.emp_no
FROM employees AS e
    LEFT OUTER JOIN dept_manager AS d
    ON e.emp_no = d.emp_no
WHERE dept_no IS NULL;

11 获取所有员工当前的manager

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 `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`));
输入描述:
无
输出描述:
emp_no	manager_no

解题思路

  • 要输出自己的经历，部门要相同 de.dept_no = dm.dept_no。
1
2
3
4
5
6
SELECT de.emp_no, dm.emp_no
FROM dept_emp AS de, dept_manager AS dm
WHERE de.dept_no = dm.dept_no
    AND de.to_date = '9999-01-01'
    AND dm.to_date = '9999-01-01'
    AND de.emp_no <> dm.emp_no;
1
2
3
4
5
6
7
SELECT de.emp_no, dm.emp_no
FROM dept_emp AS de 
    INNER JOIN dept_manager AS dm
    ON de.dept_no = dm.dept_no
WHERE de.to_date = '9999-01-01'
    AND dm.to_date = '9999-01-01'
    AND de.emp_no <> dm.emp_no;

12 获取所有部门中当前员工薪水最高的相关信息

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的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 `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`));
输入描述:
无
输出描述:
dept_no	emp_no	salary

解题思路

  • 使用GROUP BY d.dept_no 将每个部门分为一组，用MAX函数选取每组中工资最高者
1
2
3
4
5
6
7
8
SELECT d.dept_no, d.emp_no, MAX(s.salary) AS salary
FROM dept_emp AS d 
    INNER JOIN salaries AS s
    ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01' 
    AND s.to_date = '9999-01-01'
GROUP BY d.dept_no
ORDER by d.dept_no;
1
2
3
4
5
6
7
SELECT d.dept_no, d.emp_no, MAX(s.salary) AS salary
FROM dept_emp AS d, salaries AS s
WHERE d.emp_no = s.emp_no
    AND d.to_date = '9999-01-01' 
    AND s.to_date = '9999-01-01'
GROUP BY d.dept_no
ORDER by d.dept_no;

13 从titles表获取按照title进行分组

题目描述

1
2
3
4
5
6
7
8
9
10
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
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);
输入描述:
无
输出描述:
title	t

解题思路

1
2
3
4
SELECT title, COUNT(*) AS t
FROM titles
GROUP BY title
HAVING t >= 2;

14 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

题目描述

1
2
3
4
5
6
7
8
9
10
11
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_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);
输入描述:
无
输出描述:
title	t

解题思路

1
2
3
4
SELECT title, COUNT(DISTINCT emp_no) AS t
FROM titles 
GROUP BY title
HAVING t >= 2;

15 查找employees表

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
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`));
输入描述:
无
输出描述:
emp_no	birth_date	first_name	last_name	gender	hire_date

解题思路

1
2
3
4
5
SELECT *
FROM employees
WHERE emp_no % 2 = 1
    AND last_name != 'Mary'
ORDER BY hire_date DESC;

16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
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`));
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);
输入描述:
无
输出描述:
title	avg

解题思路

1
2
3
4
5
6
7
SELECT t.title, Avg(s.salary)
FROM titles AS t INNER JOIN 
    salaries AS s
    ON t.emp_no = s.emp_no
WHERE t.to_date = '9999-01-01' 
    AND s.to_date = '9999-01-01'
GROUP BY t.title;
1
2
3
4
5
6
SELECT t.title, Avg(s.salary)
FROM titles AS t, salaries AS s
WHERE t.emp_no = s.emp_no
    AND t.to_date = '9999-01-01' 
    AND s.to_date = '9999-01-01'
GROUP BY t.title;

17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

题目描述

1
2
3
4
5
6
7
8
9
10
11
获取当前（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`));
输入描述:
无
输出描述:
emp_no	salary

解题思路

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

18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
查找当前薪水(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`));
输入描述:
无
输出描述:
emp_no	salary	last_name	first_name

解题思路

1
2
3
4
5
6
7
8
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e 
    INNER JOIN salaries AS s
    ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
    AND s.salary NOT IN (SELECT MAX(salary)
                     FROM salaries
                    WHERE to_date='9999-01-01');
1
2
3
4
5
6
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e 
    INNER JOIN salaries AS s
    ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
    AND s.salary < (SELECT MAX(salary) FROM salaries);

19 查找所有员工的last_name和first_name以及对应的dept_name

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
查找所有员工的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`));
输入描述:
无
输出描述:
last_name	first_name	dept_name

解题思路

  • 第一次LEFT JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工
  • 第二次LEFT JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
1
2
3
SELECT e.last_name, e.first_name, d.dept_name
FROM (employees AS e LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no)
    LEFT JOIN departments AS d ON de.dept_no = d.dept_no;

20 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

题目描述

1
2
3
4
5
6
7
8
9
10
11
查找员工编号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`));
输入描述:
无
输出描述:
growth

解题思路

  • 最大to_date的salary 减去最小值to_date的salary
1
2
3
4
SELECT (
        (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1)-
        (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date LIMIT 1) 
    )AS growth;
  • 最大salary-最小salary，不严禁，有可能最后最大salary不在当前日期取得，最小salary不在最早的日期取得。
1
2
3
SELECT (MAX(salary)- MIN(salary)) AS growth 
FROM salaries
WHERE emp_no = 10001;

21 查找所有员工自入职以来的薪水涨幅情况

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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`));
输入描述:
无
输出描述:
emp_no	growth

解题思路

  • 建立两张表，一张存放员工当前工资，另一张存放员工入职时的工资。
1
2
3
4
5
6
7
SELECT present.emp_no, (present.salary - pass.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees  e, salaries  s 
      WHERE e.emp_no = s.emp_no AND s.to_date='9999-01-01') AS present,
    (SELECT s.emp_no, s.salary FROM employees  e, salaries  s 
     WHERE e.emp_no = s.emp_no AND s.from_date=e.hire_date) AS pass
WHERE present.emp_no = pass.emp_no
ORDER BY growth;
1
2
3
4
5
6
7
8
9
# 内层用LEFT JOIN，内层用INNER JOIN
SELECT present.emp_no, (present.salary - pass.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees AS e LEFT JOIN salaries AS s 
      ON e.emp_no = s.emp_no WHERE s.to_date='9999-01-01') AS present
    INNER JOIN
    (SELECT s.emp_no, s.salary FROM employees AS e LEFT JOIN salaries AS s 
     ON e.emp_no = s.emp_no WHERE s.from_date=e.hire_date) AS pass
ON present.emp_no = pass.emp_no
ORDER BY growth;

22 统计各个部门对应员工涨幅的次数总和

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数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`));
输入描述:
无
输出描述:
dept_no	dept_name	sum

解题思路

  • 用INNER JOIN联结dept_emp和salaries表，得到所有部门所有员工的记录总数，在联结departmens表，找到dept_no和dept_name的对应关系。
1
2
3
4
SELECT dp.dept_no, dp.dept_name, COUNT(s.salary) AS sum
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no)
      INNER JOIN departments AS dp ON de.dept_no = dp.dept_no  
GROUP BY dp.dept_no;
1
2
3
4
SELECT dp.dept_no, dp.dept_name, COUNT(s.salary) AS sum
FROM dept_emp AS de, salaries AS s, departments AS dp
WHERE de.emp_no = s.emp_no AND de.dept_no = dp.dept_no  
GROUP BY dp.dept_no;

23 对所有员工的薪水按照salary进行按照1-N的排名

题目描述

1
2
3
4
5
6
7
8
9
10
11
对所有员工的当前(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`));
输入描述:
无
输出描述:
emp_no	salary	rank

解题思路

  • 复用salaries表进行比较排名，从两张相同的salaries表（分别为s1与s2）进行对比分析。
  • 先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
  • s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary。
1
2
3
4
5
6
7
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;

24 获取所有非manager员工当前的薪水情况

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
获取所有非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`));
输入描述:
无
输出描述:
dept_no	emp_no	salary

解题思路

1
2
3
4
5
6
SELECT de.dept_no, s.emp_no, s.salary
FROM (employees AS e INNER JOIN salaries AS s 
      ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01')
    INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no 
                        FROM dept_manager WHERE to_date='9999-01-01');

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

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
获取员工其当前的薪水比其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`));
输入描述:
无
输出描述:
emp_no	manager_no	emp_salary	manager_salary

解题思路

  • 创建两张表，一张保存员工的工资，另一张保存经理的工资。
  • 找出同一部门，员工比经理工资高的输出。
1
2
3
4
5
6
7
8
SELECT s1.emp_no AS emp_no, s2.emp_no AS manager_no, s1.salary AS emp_salary, s2.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 s1,
    (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 s2
WHERE s1.dept_no = s2.dept_no AND s1.salary > s2.salary;

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

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
汇总各个部门当前员工的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);
输入描述:
无
输出描述:
dept_no	dept_name	title	count

解题思路

  • 用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组。
1
2
3
4
5
SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM (dept_emp de INNER JOIN titles t 
      ON de.emp_no = t.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01')
    INNER JOIN departments dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no, t.title;

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

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
给出每个员工每年薪水涨幅超过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`));
输入描述:
无
输出描述:
emp_no	from_date	salary_growth

解题思路

  • 每年的薪资涨幅，转化为strftime(‘%Y’, s2.to_date) - strftime(‘%Y’, s1.to_date) = 1
  • 这种需要“穷举”每两行的关系的题目，需要用那张表的两个副本（s2.emp_no = s1.emp_no是“穷举”操作，因为要考虑差值，就需要所有的两两组合。
1
2
3
4
5
6
7
SELECT s1.emp_no, s2.from_date, (s2.salary-s1.salary) AS salary_growth
FROM salaries s1, salaries s2
WHERE s1.emp_no = s2.emp_no
    AND (strftime('%Y', s2.to_date) - strftime('%Y', s1.to_date) = 1
         OR strftime('%Y', s2.from_date) - strftime('%Y', s1.from_date) = 1 )
    AND salary_growth > 5000
ORDER BY salary_growth DESC;

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

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
film表
字段	说明
film_id	电影id
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));
category表
字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

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

输入描述:
无
输出描述:
无

解题思路

1
2
3
4
5
6
7
8
9
SELECT c.name AS name, COUNT(f.film_id) AS amount
FROM film f, film_category fc, category c,
    (SELECT category_id
     FROM film_category GROUP BY category_id
     HAVING COUNT(film_id)>=5) AS cc
WHERE f.description LIKE '%robot%'
    AND f.film_id = fc.film_id
    AND fc.category_id = c.category_id
    AND c.category_id = cc.category_id;

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

[题目描述]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
film表
字段	说明
film_id	电影id
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));
category表
字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

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

输入描述:
无
输出描述:
无

解题思路

  • 用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
  • 再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
1
2
3
4
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

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
film表
字段	说明
film_id	电影id
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));
category表
字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

使用子查询的方式找出属于Action分类的所有电影对应的title,description
输入描述:
无
输出描述:
无

解题思路

1
2
3
4
5
SELECT f.title, f.description
FROM (film f INNER JOIN film_category fc
      ON f.film_id = fc.film_id)
      INNER JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Action';

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

题目描述

1
2
3
4
5
获取select * from employees对应的执行计划
输入描述:
无
输出描述:
无

解题思路

  • 可以用 “EXPLAIN” 关键字短语，用于描述表的细节。
1
EXPLAIN SELECT * FROM employees;

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

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
将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`));
输入描述:
无
输出描述:
Name

解题思路

  • MySQL用的 SELECT Concat(last_name, ‘ ‘, first_name) AS Name
  • 本题SQLite数据库只支持用连接符号”||”来连接字符串
1
2
SELECT (last_name||" "||first_name) AS Name
FROM employees;

33 创建一个actor表，包含如下列信息

[创建一个actor表，包含如下列信息]

1
2
3
4
5
6
7
列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	timestamp	not null	最后更新时间，默认是系统的当前时间
输入描述:
无

解题思路

1
2
3
4
5
6
7
CREATE TABLE actor(
actor_id	smallint(5)	not null,
first_name	varchar(45)	not null,
last_name	varchar(45)	not null,
last_update	timestamp	not null DEFAULT (datetime('now','localtime')),
PRIMARY KEY (actor_id)
);

34 批量插入数据

题目描述

1
2
3
4
5
6
7
8
9
10
11
对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33
输入描述:
无

解题思路

  • insert into 表名 (列名可选) values(数据1),(数据2)
1
2
INSERT INTO actor(actor_id, first_name, last_name, last_update)
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'), (2,'NICK','WAHLBERG','2006-02-15 12:34:33');
  • 使用UNION SELECT插入
1
2
3
INSERT INTO actor(actor_id, first_name, last_name, last_update)
SELECT 1, 'PENELOPE',	'GUINESS',	'2006-02-15 12:34:33'
UNION  SELECT 2,	'NICK',	'WAHLBERG',	'2006-02-15 12:34:33';

35 批量插入数据，不使用replace操作

题目描述

1
2
3
4
5
6
7
8
对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id	first_name	last_name	last_update
'3'	'ED'	'CHASE'	'2006-02-15 12:34:33'

解题思路

1
2
INSERT OR IGNORE INTO actor
VALUES('3',	'ED',	'CHASE',	'2006-02-15 12:34:33');
  • 若使用MySQL，则把OR去掉，即INSERT IGNORE INTO。

36 创建一个actor_name表

题目描述

1
2
3
4
5
6
7
8
9
对于如下表actor，其对应的数据为:
actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

解题思路

1
2
3
4
5
6
CREATE TABLE actor_name(
first_name	varchar(45)	not null,
last_name	varchar(45)	not null
);
INSERT INTO actor_name
SELECT first_name, last_name FROM actor;

37 对first_name创建唯一索引uniq_idx_firstname

题目描述

1
2
3
4
5
6
7
针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

解题思路

  • 创建唯一索引 CREATE UNIQUE INDEX。
  • 创建普通索引 CREATE INDEX。
1
2
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

38 针对actor表创建视图actor_name_view

题目描述

1
2
3
4
5
6
针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

解题思路

1
2
3
CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor;
  • 也可以直接在视图名后面跟字段名。
1
2
3
4
CREATE VIEW actor_name_view 
(first_name_v,last_name_v)
AS
SELECT first_name , last_name FROM actor;

39 针对上面的salaries表emp_no字段创建索引idx_emp_no

题目描述

1
2
3
4
5
6
7
8
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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`));
create index idx_emp_no on salaries(emp_no);

解题思路

  • MYSQL中强制索引查询使用：FORCE INDEX(indexname);
  • SQLite中强制索引查询使用：INDEXED BY indexname;
1
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005;

40 在last_update后面新增加一列名字为create_date

题目描述

1
2
3
4
5
6
7
存在actor表，包含如下列信息：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'

解题思路

  • 题目给的日期格式是错的。
  • 使用ALTER TABLE更新表。
1
2
ALTER TABLE actor
ADD create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

41 构造一个触发器audit_log

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

解题思路

  • 用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
  • 触发器执行的内容写出 BEGIN与END 之间
  • 可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
1
2
3
4
CREATE TRIGGER audit_log AFTER INSERT ON employees_test
BEGIN 
    INSERT INTO audit VALUES(NEW.ID, NEW.NAME);
END;

42 删除emp_no重复的记录，只保留最小的id对应的记录

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解题思路

  • 先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id.
  • 然后用 DELETE FROM … WHERE … NOT IN … 语句删除 “非每个分组最小id对应的所有记录”
1
2
3
DELETE FROM titles_test 
WHERE id NOT IN 
(SELECT MIN(id) FROM titles_test GROUP BY emp_no);

43 将所有to_date为9999-01-01的全部更新为NULL

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解题思路

1
2
3
4
UPDATE titles_test 
SET to_date = NULL,
    from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解题思路

1
2
3
UPDATE titles_test 
SET emp_no=10005
WHERE id=5 AND emp_no=10001;

45 将titles_test表名修改为titles_2017

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
将titles_test表名修改为titles_2017。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解题思路

1
ALTER TABLE titles_test RENAME TO titles_2017;

46 在audit表上创建外键约束，其emp_no对应employees_test表的主键id

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

解题思路

  • MySQL使用ALTER TABLE tablename ADD FOREIGN KEY … REFERENCES。
  • sqlite只能先删除表，在建立表。
1
2
3
4
5
DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

47 如何获取emp_v和employees有相同的数据no

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据？
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`));
输出格式:
emp_no	birth_date	first_name	last_name	gender	hire_date

解题思路

1
2
3
SELECT e.* 
FROM employees e, emp_v ev
WHERE e.emp_no = ev.emp_no;
1
2
3
SELECT e.* 
FROM employees e INNER JOIN emp_v ev
ON e.emp_no = ev.emp_no;

48 将所有获取奖金的员工当前的薪水增加10%

题目描述

1
2
3
4
5
6
7
8
9
10
将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));

解题思路

1
2
3
4
UPDATE salaries
SET salary = salary + salary * 0.1
WHERE emp_no IN (SELECT emp_no FROM emp_bonus)
    AND to_date='9999-01-01';

49 针对库中的所有表生成select count(*)对应的SQL语句

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
针对库中的所有表生成select count(*)对应的SQL语句
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));
输出格式:
cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;
select count(*) from salaries;
select count(*) from titles;
select count(*) from emp_bonus;

解题思路

  • 列出数据库中所有表名：SELECT name FROM sqlite_master WHERE type=’table’
1
2
SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table';

50 将employees表中的所有员工的last_name和first_name通过(‘)连接起来

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
将employees表中的所有员工的last_name和first_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`));
输出格式:
name
Facello'Georgi

解题思路

1
2
SELECT last_name || "'" || first_name AS name
FROM employees;

51 查找字符串’10,A,B’ 中逗号’,’出现的次数cnt

题目描述

1
查找字符串'10,A,B' 中逗号','出现的次数cnt。

解题思路

  • 用replace函数将原串中出现的子串用空串替换
  • 再用原串长度减去替换后字符串的长度（使用length）函数获得串长度
  • 最后除以子串的长度
1
SELECT (length('10,A,B')-length(replace('10,A,B', ',', ''))) / length(',') AS cnt;

52 获取Employees中的first_name

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
获取Employees中的first_name，查询按照first_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`));
输出格式：
first_name
Chirstian
Tzvetan
Bezalel
Duangkaew

解题思路

  • substr(X,Y,Z) 或 substr(X,Y) 函数。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。
1
2
3
SELECT first_name 
FROM employees
ORDER BY substr(first_name, -2);
1
2
3
SELECT first_name 
FROM employees
ORDER BY substr(first_name, length(first_name)-1);

53 按照dept_no进行汇总

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
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`));
输出格式:
dept_no	employees
d001	10001,10002
d002	10006
d003	10005
d004	10003,10004
d005	10007,10008,10010
d006	10009,10010

解题思路

  • SQLite的聚合函数group_concat(X, Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
1
2
3
4
SELECT dept_no, group_concat(emp_no, ',') AS employees
FROM dept_emp
GROUP BY dept_no
ORDER BY dept_no;

54 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

题目描述

1
2
3
4
5
6
7
8
9
查找排除当前最大、最小salary之后的员工的平均工资avg_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`));
输出格式:
avg_salary
69462.5555555556

解题思路

1
2
3
4
5
SELECT Avg(salary) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
    AND salary NOT IN (SELECT MIN(salary) FROM salaries)
    AND salary NOT IN (SELECT MAX(salary) FROM salaries);

55 分页查询employees表，每5行一页，返回第2页的数据

题目描述

1
2
3
4
5
6
7
8
9
分页查询employees表，每5行一页，返回第2页的数据
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`));

解题思路

  • LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0）
  • LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回
1
2
3
SELECT * 
FROM employees
LIMIT 5, 5;
1
2
3
SELECT * 
FROM employees
LIMIT 5 OFFSET 5;

56 获取所有员工的emp_no

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示
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`));
输出格式:
e.emp_no	dept_no	btype	recevied

解题思路

  • 题目缺了一个emp_bonus表，如下。
1
2
3
4
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
1
2
3
SELECT e.emp_no, de.dept_no, eb.btype, eb.recevied
FROM (employees e INNER JOIN dept_emp de ON e.emp_no = de.emp_no)
LEFT JOIN emp_bonus eb ON e.emp_no = eb.emp_no;

57 使用含有关键字exists查找未分配具体部门的员工的所有信息

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
使用含有关键字exists查找未分配具体部门的员工的所有信息。
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 `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`));
输出格式:
emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22

解题思路

1
2
3
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no);
1
2
3
4
SELECT e.*
FROM employees e LEFT JOIN dept_emp de
ON e.emp_no = de.emp_no
WHERE de.emp_no IS NULL;

58 获取employees中的行数据，且这些行也存在于emp_v中

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
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`));
获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
输出格式:
emp_no	birth_date	first_name	last_name	gender	hire_date

解题思路

1
2
3
SELECT e.*
FROM employees e INNER JOIN emp_v ev
ON e.emp_no = ev.emp_no;
1
2
3
SELECT e.*
FROM employees e, emp_v ev
WHERE e.emp_no = ev.emp_no;
1
SELECT * FROM emp_v

59 获取有奖金的员工相关信息。

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
获取有奖金的员工相关信息。
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 `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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
输出格式:
emp_no	first_name	last_name	btype	salary	bonus

解题思路

  • 使用CASE语句。
1
2
3
4
5
6
7
SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, 
(CASE eb.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM (employees e INNER JOIN salaries s ON e.emp_no = s.emp_no AND s.to_date='9999-01-01')
INNER JOIN emp_bonus eb ON e.emp_no = eb.emp_no;

60 统计salary的累计和running_total

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
题目描述
按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
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`));
输出格式:
emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

解题思路

  • running_total由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和。
1
2
3
4
SELECT s1.emp_no, s1.salary, 
(SELECT SUM(s2.salary) FROM salaries s2
WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total
FROM salaries s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no;

61 对于employees表中，给出奇数行的first_name

题目描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
对于employees表中，给出奇数行的first_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`));
输出格式:
first_name
Georgi
Chirstian
Anneke
Tzvetan
Saniya
Mary

解题思路

  • 题目隐含了first_name是排序的。
1
2
3
4
SELECT e1.first_name
FROM employees e1
WHERE (SELECT COUNT(*) FROM employees e2 
      WHERE e2.first_name <= e1.first_name) % 2 = 1;
Donate comment here
------------The End------------
0%