SQL 编程及解析

整理自牛客网sql编程区
银行好像主要要学sql了。。🤑
每天分享攒rp 只为小论文能中..😭

员工系列

建表语句

员工表

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

部门员工表

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, -- '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

职称表

CREATE TABLE `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

题目

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

select * from employees
order by hire_date desc #按照入职时间【降序】排列
limit 1 #限制查询数量为 1 

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

select * from employees
order by hire_date desc #按照入职时间【降序】排列
limit 2,1 #limit m n 代表从结果的第 m+1 条开始查询 n 条（m≥0）

3 查找各个部门当前领导当前薪水详情以及其对应部门编号

select s.* ,d.dept_no
from salaries as s 
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 以及 dept_no

SELECT e.last_name, e.first_name, d.dept_no
FROM dept_emp AS d
INNER JOIN employees AS e
ON e.emp_no = d.emp_no;

5 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

SELECT e.last_name, e.first_name, d.dept_no 
FROM employees e
LEFT JOIN dept_emp d #左连接以左表为主表，可以查询右表（dept_emp）不存在的记录
ON e.emp_no = d.emp_no

6 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按emp_no进行逆序

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

SELECT emp_no, COUNT(emp_no) AS t 
FROM salaries 
GROUP BY emp_no HAVING t > 15 # 工资表按照员工号分组即可统计工资变动次数

8 找出所有员工当前具体的薪水salary情况，相同的薪水只显示一次,并逆序显示

SELECT DISTINCT salary # 去重
FROM salaries 
WHERE to_date = '9999-01-01' # 限制当前时间
ORDER BY salary DESC # 逆序

9 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary

SELECT d.dept_no, d.emp_no, s.salary 
FROM salaries AS s INNER 
JOIN dept_manager AS d 
ON d.emp_no = s.emp_no
AND d.to_date = '9999-01-01' # 当前manager
AND s.to_date = '9999-01-01' # 当前薪水

10 获取所有非manager的员工emp_no

SELECT emp_no 
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager) # 不在 （所有 manager 的员工号）

11 获取所有员工当前manager，如果员工是manager的话不显示

SELECT e.emp_no, m.emp_no AS manager_no 
FROM dept_emp AS e 
INNER JOIN dept_manager AS m
ON e.dept_no = m.dept_no 
WHERE m.to_date = '9999-01-01' 
AND e.to_date = '9999-01-01' 
AND e.emp_no <> m.emp_no # 员工不能是 manager

12 获取所有部门中当前员工当前薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s 
INNER JOIN dept_emp As d
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

13 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

SELECT title, COUNT(title) AS t 
FROM titles
GROUP BY title HAVING t >= 2

14 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略

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

15 查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列

select * from employees
where last_name not like 'Mary'
and emp_no % 2 = 1
order by hire_date DESC

16 统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

SELECT t.title, avg(s.salary)
FROM salaries as s 
INNER JOIN titles as t
ON s.emp_no = t.emp_no
AND s.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
GROUP BY title

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

select emp_no, salary
from salaries
where salary = (
    select salary from salaries 
    group by salary
    order by salary desc limit 1,1 #查询出第二多的薪水
) 
and to_date = '9999-01-01'

18 查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不使用 order by

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)

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

SELECT em.last_name, em.first_name, dp.dept_name
FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no)
LEFT JOIN departments AS dp 
ON de.dept_no = dp.dept_no # 用左连接显示无部门员工

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

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 ASC LIMIT 1) #升序查最低工资
) AS growth

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

select a.emp_no, (b.salary - c.salary) as growth
from employees as a
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01' # b 查询当前工资
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date # c 查询入职工资
order by growth asc

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

SELECT de.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 de.dept_no

23 对所有员工的当前薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

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 s1.salary DESC, s1.emp_no ASC 

24 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary

SELECT de.dept_no, s.emp_no, s.salary 
FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no = e.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) # 非manager员工

25 获取员工其当前的薪水比其manager当前薪水还高的相关信息，结果第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary

SELECT e.emp_no, m.emp_no AS manager_no, e.salary AS emp_salary, m.salary AS manager_salary
FROM 
(SELECT s.salary, s.emp_no, de.dept_no FROM salaries s 
INNER JOIN dept_emp de 
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS e, #员工相关信息
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s 
INNER JOIN dept_manager dm 
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS m # manager 相关信息
WHERE e.dept_no = m.dept_no  # 为一个部门
AND e.salary > m.salary # 员工工资更高

26 汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前员工的当前title以及该类型title对应的数目count

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

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

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

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2 #s1涨薪前，s2涨薪后
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000 #涨幅超过5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

28 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数

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

29 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

SELECT * FROM salaries 
INDEXED BY idx_emp_no 
WHERE emp_no = 10005

30 构造一个触发器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 audit_log AFTER INSERT ON employees_test
BEGIN #触发器执行的内容写出 BEGIN与END 之间
    INSERT INTO audit VALUES (NEW.ID, NEW.NAME); #可以使用 NEW 与OLD 关键字访问触发后或触发前的记录
END;

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

DELETE FROM titles_test WHERE id NOT IN #不是最小id的都删除
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)#最小的id

32 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

UPDATE titles_test 
SET to_date = NULL, from_date = '2001-01-01' 
WHERE to_date = '9999-01-01';

33 将id=5及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现

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

REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

34 将titles_test表名修改为titles_2017

ALTER TABLE titles_test RENAME TO titles_2017

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

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(emp_no) REFERENCES employees_test(id)
);

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

create table emp_bonus(
emp_no int not null,
btype smallint not null);

UPDATE salaries SET salary = salary * 1.1 
WHERE emp_no IN (SELECT emp_no FROM emp_bonus)
AND to_date = '9999-01-01'

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

在 SQLite 中用 “||” 符号连接字符串，无法使用concat函数

SELECT "select count(*) from " || name || ";"
FROM sqlite_master #系统表 sqlite_master 可以获得所有表的索引
WHERE type = 'table' #字段 name 是表名，对于自己创建的表，字段 type 永远是 'table'

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

SELECT last_name || "'" || first_name FROM employees

39 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1) 

40 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

SQLite的聚合函数 group_concat(X,Y)，X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。

SELECT dept_no, group_concat(emp_no) AS employees 
FROM dept_emp 
GROUP BY dept_no

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

SELECT AVG(salary) AS avg_salary 
FROM salaries 
WHERE to_date = '9999-01-01' #当前员工
AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01') #排除最大工资
AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01') # 排除最小工资

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

SELECT * 
FROM employees
limit 5,5 #从第6（5+1）条开始，显示5条

43 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received

select de.emp_no, de.dept_no, eb.btype, eb.received
from dept_emp as de 
left join emp_bonus as eb
on de.emp_no = eb.emp_no

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

在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录

SELECT * FROM employees WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no) 

45 获取有奖金的员工相关信息，给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype #根据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 AS e 
INNER JOIN emp_bonus AS b 
ON e.emp_no = b.emp_no
INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no 
AND s.to_date = '9999-01-01'

46 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

SELECT e1.first_name 
FROM 
(SELECT e2.first_name, (SELECT COUNT(*) FROM employees AS e3 WHERE e3.first_name <= e2.first_name) AS rowid 
 FROM employees AS e2) AS e1
WHERE e1.rowid % 2 = 1

电影系列

建表语句

电影表

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); #电影id和分类id对应关系的最后更新时间

题目

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

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

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

SELECT f.film_id, f.title 
FROM film f 
LEFT JOIN film_category fc #使用左连接查询右表为null的记录
ON f.film_id = fc.film_id 
WHERE fc.category_id IS NULL

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

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 #对应Action分类号的电影号
(select c.category_id from category as c where c.name = 'Action')); #属于Action分类的分类号

4 创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime('now','localtime'))

CREATE TABLE 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'))
);

5 对于表actor批量插入如下数据

INSERT INTO actor
VALUES 
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');        

6 对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

insert or ignore into actor 
values(3,'ED','CHASE','2006-02-15 12:34:33');

7 创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

actor_name表结构如下：

create table actor_name as 
select first_name,last_name from actor;

8 针对表actor，对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

9 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor 

10 在actor表的last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000-00-00 00:00:00'

alter table actor 
add `create_date` datetime not null default '0000-00-00 00:00:00'

其它

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

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt