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

列表类型是否为NULL含义actor_idsmallint(5)not null主键idfirst_namevarchar(45)not null名字last_namevarchar(45)not null姓氏last_updatetimestampnot null最后更新时间，默认是系统的当前时间 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批量插入如下数据

actor_idfirst_namelast_namelast_update1PENELOPEGUINESS2006-02-15 12:34:332NICKWAHLBERG2006-02-15 12:34:33 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操作)

actor_idfirst_namelast_namelast_update‘3’‘ED’‘CHASE’‘2006-02-15 12:34:33’ 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表结构如下：

列表类型是否为NULL含义first_namevarchar(45)not null名字last_namevarchar(45)not null姓氏 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