牛客网SQL实战网址：/ta/sql
持续更新——记录自己在牛客网SQL的做题过程
更新进度：61题，-4-3，更完了
1.查找最晚入职员工的所有信息
应该考虑的问题：
①最晚入职的当天未必就一个人，也许有多人，使用排序并限制得只能取得指定数量的结果
SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);
注：日期最大的就是最晚的，日期较早就是较小。
2.查找入职员工时间排名倒数第三的员工所有信息
应该考虑的问题：
①取的是日期倒数第三的人，不是倒数第三的人
SELECT emp_no, birth_date, first_name, last_name, gender, hire_dateFROM employeesWHERE hire_date=(SELECT DISTINCT hire_dateFROM employeesORDER BY hire_date DESCLIMIT 2,1)；
注：
①牛客网网友EricZeng的严谨写法，可以学习。
②LIMIT是从0开始计数。
3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
应该考虑的问题：
①薪水表是按年发放的，所以要过滤掉以前的薪水，只保留现在还在的当前领导的薪水
SELECT s.*,d.dept_no FROM salaries AS s,dept_manager AS dWHERE s.emp_no = d.emp_no AND s.to_date='9999-01-01'AND d.to_date='9999-01-01'
注：
①用AND可以加条件
②表的顺序和你FROM的顺序还有WHERE的顺序需要一致
4.查找所有已经分配部门的员工的last_name和first_name
应该考虑的问题：
①因为是查找已分配部门的员工，所以dept_no不应该为空，应该使用内连接。
SELECT emp.last_name,emp.first_name,dept.dept_no FROM dept_emp dept,employees empWHERE emp.emp_no = dept.emp_no
注：表连接。
5.查找所有员工的last_name和first_name以及对应部门编号dept_no
应该考虑的问题：
①没有具体分配的员工也要展示，也就是要保证employees表的完整
②内连接已经不能满足需求，需要用左连接或者又连接
SELECT e.last_name,e.first_name,d.dept_no FROM employees e LEFT JOIN dept_emp dON e.emp_no=d.emp_no;
注：
①内连接（INNER JOIN）两边表任何一边缺失都不显示。
②左连接（LEFT JOIN)，右边表可以无对应数据。
③右连接（RIGHT JOIN），左边表可以无对应数据。
6. 查找所有员工入职时候的薪水情况
应该考虑的问题：
①因为员工会有多次涨薪，所以salaries.emp_no 不唯一，这时我们就应该确定具体确定这个薪水的时间，也就是这个入职时间hire_date
SELECT e.emp_no, s.salary FROM employees AS e, salaries AS sWHERE e.emp_no = s.emp_no AND e.hire_date = s.from_dateORDER BY e.emp_no DESC
7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
应该考虑的问题：
①需要利用分组函数GROUP BY对emp_no进行分组。
②需要利用分组限定条件限定t值
SELECT emp_no,COUNT(emp_no) as t FROM salariesGROUP BY emp_no HAVING t>15
注：①好像是先选择列和计算，然后再进行分组和再一次计算，也就是按照语句的顺序进行。
8.找出所有员工当前具体的薪水salary情况
SELECT DISTINCT salary FROM salaries WHERE to_date='9999-01-01'ORDER BY salary DESC或SELECT DISTINCT salary FROM salaries WHERE to_date='9999-01-01'GROUP BY salaryORDER BY salary DESC;
注：
①大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。
②在不同记录数较小时，count group by性能普遍高于count distinct，尤其对于text类型表现的更明显。而对于不同记录数较大的场景，count group by性能反而低于直接count distinct（牛客网网友—啊啥水果的总结）
9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary
应该考虑的问题：
①同一emp_no会在salaries表中对应多条涨薪记录
SELECT d.dept_no,d.emp_no,s.salaryFROM dept_manager d,salaries sWHERE d.emp_no = s.emp_no AND d.to_date = '9999-01-01'AND s.to_date = '9999-01-01'
10.获取所有非manager的员工emp_no
SELECT e.emp_noFROM employees eWHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
注：
①子查询在这里用到了。
②牛客网的wasrehpic说，用LEFT JOIN，然后筛选NULL也行，wasrehpic写的解析很棒，给了我很多帮助。
11.获取所有员工当前的manager
需要考虑的问题：
①两个表应该以dept_no进行联结而不是emp_no。
②需要注意如果当前的manager是自己的话结果不显示。
SELECT e.emp_no,m.emp_no AS manager_noFROM dept_emp e,dept_manager mWHERE e.dept_no = m.dept_noAND e.to_date='9999-01-01'AND m.to_date='9999-01-01'AND e.emp_no != m.emp_no
注：①这里用到了重命名AS
12.获取所有部门中当前员工薪水最高的相关信息
需要考虑的问题：
①需要用到分组函数GROUPBY，并以部门进行分组。
②需要用到MAX函数取出最大的薪水值
③所以也就是一个分组加最大值函数
SELECT d.dept_no,d.emp_no,MAX(s.salary)FROM dept_emp d,salaries sWHERE d.emp_no = s.emp_noAND d.to_date='9999-01-01'AND s.to_date='9999-01-01'GROUP BY d.dept_no
注：考虑的时候应该先考虑分组然后再考虑每个取值的函数，所以想着最好是倒着去想，想完了再正着去写。
13. 从titles表获取按照title进行分组
需要考虑的问题：WHERE后边不可跟COUNT()函数
SELECT title,COUNT(title) AS tFROM titlesGROUP BY titleHAVING t>=2
14.从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。
需要考虑的问题：一个title内可能存在三个emp_no,其中两个有可能是一样的，所以就需要去重统计。
SELECT title,COUNT(DISTINCT emp_no) AS tFROM titlesGROUP BY titleHAVING t>1
15.查找employees表
SELECT *FROM employeesWHERE emp_no % 2 = 1AND last_name <> 'Mary'ORDER BY hire_date DESC;
16.统计出当前各个title类型对应的员工当前薪水对应的平均工资
SELECT t.title,AVG(s.salary) AS avgFROM titles t,salaries sWHERE t.emp_no = s.emp_noAND t.to_date='9999-01-01'AND s.to_date='9999-01-01'GROUP BY t.title;
17.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
SELECT emp_no,salaryFROM salariesWHERE to_date='9999-01-01'ORDER BY salary DESCLIMIT 1,1;
注：在做题讨论时候，有的人说薪水第一的有可能哟多个，所以要去重后选择第二位，但是还有一个问题，如果有两个人并列薪水第一，那么你选择的第二其实是薪水第三多的人，所以其实得看一下数据集再确定，很明显，牛客网给定的数据集只有一个第一，所以怎么写都行。
18.查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
#wasrehpic的答案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_noWHERE s.to_date = '9999-01-01'AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
19.查找所有员工的last_name和first_name以及对应的dept_name
SELECT emp.last_name, emp.first_name, dep.dept_nameFROM employees AS emp LEFT JOIN dept_emp AS dept ON emp.emp_no = dept.emp_noLEFT JOIN departments AS dep ON dept.dept_no = dep.dept_no
注：多表连接（超过两个表）
20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
SELECT MAX(salary)-MIN(salary) AS growthFROM salariesWHERE emp_no="10001"
21.查找所有员工自入职以来的薪水涨幅情况
思考过程：
①创建第一个表，存储现在的薪水和员工号
②创建第二个表，存储入职的薪水和员工号
③将两个表连接
④计算涨薪
#wasrehpic答案，自己没写出来SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growthFROM (SELECT s.emp_no,s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01') AS sCurrentINNER JOIN(SELECT s.emp_no,s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStartON sCurrent.emp_no = sStart.emp_noORDER BY growth
22.统计各个部门对应员工涨幅的次数总和
SELECT dep.dept_no,dep.dept_name,COUNT(dep.dept_no)AS sumFROM departments dep INNER JOIN dept_emp dept ON dep.dept_no=dept.dept_noINNER JOIN salaries s ON s.emp_no=dept.emp_noGROUP BY dep.dept_no
23.对所有员工的薪水按照salary进行按照1-N的排名
没写出来
答案如下
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rankFROM salaries AS s1, salaries AS s2WHERE s1.to_date = '9999-01-01' AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salaryGROUP BY s1.emp_noORDER BY s1.salary DESC, s1.emp_no ASC
24.获取所有非manager员工当前的薪水情况
考虑思路：
①将dept_emp和salaries连接，salaeies中就有所有的员工名单，所以不用employees。
②过滤掉经理
SELECT de.dept_no, s.emp_no, s.salary FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01'WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')
25.获取员工其当前的薪水比其manager当前薪水还高的相关信息
考虑过程：
①提取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_salaryFROM (SELECT de.emp_no,de.dept_no,s.salaryFROM dept_emp de,salaries sWHERE de.emp_no=s.emp_noAND de.to_date='9999-01-01'AND s.to_date='9999-01-01') AS emp_sINNER JOIN (SELECT dm.emp_no,dm.dept_no,s.salaryFROM dept_manager dm,salaries sWHERE dm.emp_no=s.emp_noAND dm.to_date='9999-01-01'AND s.to_date='9999-01-01') AS mar_sON emp_s.dept_no=mar_s.dept_noWHERE emp_s.salary > mar_s.salary
26.汇总各个部门的当前员工title类型的分配数目
解题思路：
①这是一个三表连接加上分组的问题
②先将titles和dept_emp连接，然后将departments 填进去
③讲dept_no和title分组统计
SELECT dept.dept_no, dp.dept_name, t.title, COUNT(t.title) AS countFROM 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_noGROUP BY dept.dept_no, t.title
27.给出每个员工每年薪水涨幅超过5000的员工
参考高赞答案，自己编写的发生了未知错误。
28.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
谁能让我看看数据长啥样啊~~
29.使用join查询方式找出没有分类的电影id以及名称
解题思路：两种方法①第一种，连接表之后用NOT IN②第二种，识别分类id是NULL
SELECT f.film_id,f.titleFROM film f LEFT JOIN film_category fcON f.film_id = fc.film_idWHERE f.film_id NOT IN (SELECT film_id FROM film_category);SELECT f.film_id, f.title FROM film f LEFT JOIN film_category fcON f.film_id = fc.film_id WHERE fc.category_id IS NULL
30.使用子查询的方式找出属于Action分类的所有电影对应的title,description
SELECT f.title, f.descriptionFROM film f INNER JOIN film_category fcON f.film_id = fc.film_idWHERE fc.category_id IN (SELECT category_id FROM categoryWHERE name="Action")
31.获取select * from employees对应的执行计划
EXPLAIN SELECT * FROM employees
可以使用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节。
SQLite Explain
32.将employees表的所有员工的last_name和first_name拼接起来作为Name
SELECT last_name||" "||first_name AS NameFROM employees
注：有些版本还可以用CONCAT
33.创建一个actor表，包含如下信息
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')))
34.批量插入数据
INSERT INTO actor VALUES(1,'PENELOPE','GUINESS','-02-15 12:34:33'),(2, 'NICK', 'WAHLBERG', '-02-15 12:34:33')
35.批量插入数据，不使用replace操作
INSERT OR IGNORE INTO actorVALUES(3,'ED','CHASE','-02-15 12:34:33');
36.创建一个actor_name表
CREATE TABLE actor_name(first_name varchar(45) NOT NULL,last_name varchar(45) NOT NULL);INSERT INTO actor_name (first_name,last_name)SELECT first_name, last_name FROM actor;
37.对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);
注：
①加UNIQUE是创建唯一索引。
②索引好像得一条一条的加，也可能是牛客网这个编辑器的问题。
38.针对actor表创建视图actor_name_view
CREATE VIEW actor_name_viewASSELECT first_name AS first_name_v,last_name AS last_name_vFROM actor
39.针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引
SELECT * FROM salariesINDEXED BY idx_emp_noWHERE emp_no='10005';
注：
①sqlLite 使用 indexed by 进行强制索引 SQLite Indexed By
②mysql 使用 force index 进行强制索引
40.在lastupdate后面增加一列名字为create_date
ALTER TABLE actorADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
注：MySQL中有AFTER参数可以指定放在哪一列后边
41.构造一个触发器audit_log
CREATE TRIGGER audit_log AFTER INSERTON employees_test FOR EACH ROWBEGININSERT INTO audit VALUES(NEW.ID,NEW.NAME);END;
42.删除emp_no重复的记录，只保留最小的id对应的记录
DELETE FROM titles_testWHERE id NOT IN (SELECT MIN(id) FROM titles_test)
43.将所有to_date为9999-01-01的全部更新为NULL
UPDATE titles_testSET 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
UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005)WHERE id = 5
注：
Sqlite数据库字符串处理函数replace
replace(X,Y,Z) ，x:要处理的字符串，y:被替换的字符串，z:替换后的字符串
45.将titles_test表名修改为titles_
ALTER TABLE titles_test RENAME TO titles_
46.在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));
注：牛客网的这套系统不能用MySQL的ALTER TABLE <表名> ADD [CONSTRAINT <约束名>] FOREIGN KEY（外键字段名）REFERENGCES 被参照表（主键字段名）
47.如何获取emp_v和employees有相同的数据no
SELECT ev.*FROM emp_v ev,employees emWHERE ev.emp_no = em.emp_no;
48.将所有获取奖金的员工当前薪水增加10%
UPDATE salaries SET salary = salary*1.1WHERE emp_no IN (SELECT emp_no FROM emp_bonus)
49.针对库中的所有表生成select count(*)对应的SQL语句
SELECT "select count(*) from "||name||";" AS cnts FROM sqlite_masterWHERE type='table'
注：在SQLite系统表sqlite_master中可以获得所有表的索引，其中name表示表的名字，type=‘table’表示当前查找的是表，而type的值永远都是table。
50.将employees表中的所有员工的last_name和first_name通过(')连接起来。
SELECT last_name||"'"||first_name AS nameFROM employees
51.查找字符串'10,A,B'中逗号','出现的次数cnt
题目描述：查找字符串'10,A,B' 中逗号','出现的次数cnt。
SELECT length('10,A,B') - length(replace('10,A,B',",",""))
52.获取Employees中的first_name
SELECT first_nameFROM employeesORDER BY SUBSTR(first_name,length(first_name)-1,2)
注：
解释来自
substr函数的用法，取得字符串中指定起始位置和长度的字符串 ，默认是从起始位置到结束的子串。
substr( string, start_position, [ length ] ) substr('目标字符串',开始位置,长度)
如:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'select substr('Thisisatest', -4, 2) value from dual
53.按照dept_no进行汇总
SELECT dept_no,group_concat(emp_no) AS employeesFROM dept_empGROUP BY dept_no
54.查找排除当前最大、最小salary之后的员工的平均工资avg_salary
SELECT AVG(salary) AS avg_salaryFROM salariesWHERE to_date = '9999-01-01'AND salary NOT IN (SELECT MAX(salary) FROM salaries)AND salary NOT IN (SELECT MIN(salary) FROM salaries)
55.分页查询employees表，每5行一页，返回第2页的数据
SELECT *FROM employeesLIMIT 5,5
56.获取所有员工的emp_no
SELECT de.emp_no, de.dept_no, eb.btype, eb.receviedFROM dept_emp AS de LEFT JOIN emp_bonus AS eb ON de.emp_no = eb.emp_no
注：神题~~没找到emp_bonus表，还是看了讨论才知道的
57.使用含有关键字exists查找未分配具体部门的员工的所有信息
SELECT *FROM employeesWHERE NOT EXISTS(SELECT emp_noFROM dept_empWHERE emp_no = employees.emp_no)
58.获取employees中的行数据，且这些行也存在于emp_v中
SELECT em.*FROM employees AS em, emp_v AS evWHERE em.emp_no = ev.emp_no
59.获取有奖金的员工相关信息
SELECT em.emp_no, em.first_name, em.last_name, eb.btype, sal.salary, (CASE eb.btype WHEN 1 THEN sal.salary * 0.1WHEN 2 THEN sal.salary * 0.2ELSE sal.salary * 0.3 END) AS bonusFROM employees AS em INNER JOIN emp_bonus AS ebON em.emp_no = eb.emp_noINNER JOIN salaries AS salON em.emp_no = sal.emp_noAND sal.to_date = '9999-01-01'
注：最好自己做一个连接图表示表与表之间的联系，这道题很明显没用到dept_emp表。
60.统计salary的累计和running_total
SELECT s1.emp_no, s1.salary, (SELECT SUM(s2.salary)FROM salaries AS s2 WHERE s2.emp_no <= s1.emp_noAND s2.to_date = '9999-01-01') AS running_totalFROM salaries AS s1WHERE s1.to_date = '9999-01-01'ORDER BY s1.emp_no;
61.对于employees表中，给出奇数行first_name
SELECT first_name FROM ( SELECT e2.first_name,(SELECT COUNT(*) FROM employees e1WHERE e1.first_name <= e2.first_name ) AS rownum FROM employees e2 WHERE rownum % 2 =1 );
