牛客网获取输入rowinput_练习SQL利器，牛客网SQL实战题库

牛客网SQL实战网址：https://www.nowcoder.com/ta/sql
持续更新——记录自己在牛客网SQL的做题过程

更新进度：61题，2019-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_date
FROM employees
WHERE hire_date=(
                  SELECT DISTINCT hire_date
                  FROM employees
                  ORDER BY hire_date DESC
                  LIMIT 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 d
WHERE 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 emp
WHERE 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 d
ON 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 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

应该考虑的问题：
①需要利用分组函数GROUP BY对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
或
SELECT DISTINCT salary FROM salaries 
WHERE to_date='9999-01-01'
GROUP BY salary
ORDER 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.salary
FROM dept_manager d,salaries 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

SELECT e.emp_no
FROM employees e
WHERE 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_no
FROM dept_emp e,dept_manager m
WHERE e.dept_no = m.dept_no
AND 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 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

注：考虑的时候应该先考虑分组然后再考虑每个取值的函数，所以想着最好是倒着去想，想完了再正着去写。

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

需要考虑的问题：WHERE后边不可跟COUNT()函数

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

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

需要考虑的问题：一个title内可能存在三个emp_no,其中两个有可能是一样的，所以就需要去重统计。

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

15.查找employees表

SELECT *
FROM employees
WHERE emp_no % 2 = 1
AND last_name <> 'Mary'
ORDER BY hire_date DESC;

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

SELECT t.title,AVG(s.salary) AS avg
FROM titles t,salaries 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

SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
LIMIT 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_no
WHERE 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_name
FROM employees AS emp LEFT JOIN dept_emp AS dept ON emp.emp_no = dept.emp_no
LEFT JOIN departments AS dep ON dept.dept_no = dep.dept_no

注：多表连接（超过两个表）

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

SELECT MAX(salary)-MIN(salary) AS growth
FROM salaries
WHERE emp_no="10001"

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

思考过程：
①创建第一个表，存储现在的薪水和员工号
②创建第二个表，存储入职的薪水和员工号
③将两个表连接
④计算涨薪

#wasrehpic答案，自己没写出来
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (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 sCurrent
INNER 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 sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth

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

SELECT dep.dept_no,dep.dept_name,COUNT(dep.dept_no)AS sum
FROM departments dep INNER JOIN dept_emp dept ON dep.dept_no=dept.dept_no
INNER JOIN salaries s ON s.emp_no=dept.emp_no
GROUP BY dep.dept_no

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

没写出来
答案如下

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_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_salary
FROM (SELECT de.emp_no,de.dept_no,s.salary
     FROM dept_emp de,salaries s
     WHERE de.emp_no=s.emp_no
     AND de.to_date='9999-01-01'
     AND s.to_date='9999-01-01') AS emp_s
INNER JOIN (SELECT dm.emp_no,dm.dept_no,s.salary
           FROM dept_manager dm,salaries s
           WHERE dm.emp_no=s.emp_no
           AND dm.to_date='9999-01-01'
           AND s.to_date='9999-01-01') AS mar_s
ON emp_s.dept_no=mar_s.dept_no
WHERE 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 count
FROM 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_no
GROUP BY dept.dept_no, t.title

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

参考高赞答案，自己编写的发生了未知错误。

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

谁能让我看看数据长啥样啊~~

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

解题思路：两种方法 ①第一种，连接表之后用NOT IN ②第二种，识别分类id是NULL

SELECT f.film_id,f.title
FROM film f LEFT JOIN film_category fc
ON f.film_id = fc.film_id
WHERE f.film_id NOT IN (SELECT film_id FROM film_category);



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

SELECT f.title, f.description
FROM film f INNER JOIN film_category fc
ON f.film_id = fc.film_id
WHERE fc.category_id IN (SELECT category_id FROM category
                        WHERE 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 Name
FROM 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','2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

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

INSERT OR IGNORE INTO actor
VALUES(3,'ED','CHASE','2006-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_view
AS
SELECT first_name AS first_name_v,last_name AS last_name_v
FROM actor

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

SELECT * FROM salaries
INDEXED BY idx_emp_no
WHERE emp_no='10005';

注：

①sqlLite 使用 indexed by 进行强制索引 SQLite Indexed By

②mysql 使用 force index 进行强制索引

40.在lastupdate后面增加一列名字为create_date

ALTER TABLE actor
ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

注：MySQL中有AFTER参数可以指定放在哪一列后边

41.构造一个触发器audit_log

CREATE TRIGGER audit_log AFTER INSERT
ON employees_test FOR EACH ROW
BEGIN
    INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END;

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

DELETE FROM titles_test
WHERE id NOT IN (SELECT MIN(id) FROM titles_test)

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

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

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_2017

ALTER TABLE titles_test RENAME TO titles_2017

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 em
WHERE ev.emp_no = em.emp_no;

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

UPDATE salaries SET salary = salary*1.1
WHERE emp_no IN (SELECT emp_no FROM emp_bonus)

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

SELECT "select count(*) from "||name||";" AS cnts  FROM sqlite_master
WHERE type='table'

注：在SQLite系统表sqlite_master中可以获得所有表的索引，其中name表示表的名字，type=‘table’表示当前查找的是表，而type的值永远都是table。

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

SELECT last_name||"'"||first_name AS name
FROM 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_name
FROM employees
ORDER 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 employees
FROM dept_emp
GROUP BY dept_no

54.查找排除当前最大、最小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)
AND salary NOT IN (SELECT MIN(salary) FROM salaries)

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

SELECT *
FROM employees
LIMIT 5,5

56.获取所有员工的emp_no

SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied
FROM dept_emp AS de LEFT JOIN emp_bonus AS eb 
ON de.emp_no = eb.emp_no

注：神题~~没找到emp_bonus表，还是看了讨论才知道的

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

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

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

SELECT em.*
FROM employees AS em, emp_v AS ev
WHERE 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.1
 WHEN 2 THEN sal.salary * 0.2
 ELSE sal.salary * 0.3 END) AS bonus
FROM employees AS em INNER JOIN emp_bonus AS eb
ON em.emp_no = eb.emp_no
INNER JOIN salaries AS sal
ON em.emp_no = sal.emp_no
AND 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_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE 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 e1
     WHERE e1.first_name <= e2.first_name ) AS rownum 
    FROM employees e2 
    WHERE rownum % 2 =1 
);