【牛客网】数据库SQL实战（题解）

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

【题解】

hire_date可能存在重复值，所以需要找到hire_date的最大值，然后再筛选，才能hire_date最晚的记录都筛选出来。

 

【代码】

SELECT * FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees)

 

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

【题解】

还是hire_date可能存在重复值问题，所以需要先找到第三晚的hire_date（此处排序记得用distinct去重），然后再进行筛选。

 

【代码】

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

 

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

【题解】

这题好坑哦，因为题目中说的是“薪水详情以及其对应部门编号dept_no”，所以salaries表是主表，要写在前面？（我对这个思路并不是很赞同）

但其实我觉得是后台没有排序问题，只要把emp_no排个序也能过。

 

【代码】

SELECT s.*, d.dept_no
FROM salaries s, dept_manager d
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' AND d.emp_no = s.emp_no

 

SELECT s.*, d.dept_no
FROM dept_manager d, salaries s
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' AND d.emp_no = s.emp_no
ORDER BY s.emp_no

 

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

【题解】

也就是说有的员工不一定被分配了部门，那么只要将部门表左连接到员工表，即部门表上的信息都会有，但部门表上没有员工表上有的信息就不会被筛出，符合题目所求。

 

【代码】

SELECT e.last_name, e.first_name, d.dept_no
FROM dept_emp d LEFT JOIN employees 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 ON e.emp_no = d.emp_no

 

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

【题解】

这里需要注意一下，因为是给出每个员工入职时的薪资，所以还需要将加上这个条件e.hire_date = s.from_date。

 

【代码】

SELECT e.emp_no, s.salary
FROM employees e, salaries 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分类后，COUNT记录每个emp_no的薪水涨幅次数，最后选出大于15的即可。

COUNT语句后面要跟HAVING哦。

如果对GROUP BY和聚合函数的使用不是很了解的话可以戳这里，我感觉讲的蛮好的。

 

【代码】

SELECT emp_no, COUNT(to_date) AS t
FROM salaries GROUP BY emp_no HAVING t > 15

 

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

【题解】

DISTINCT：如果作用于某一列，同一列的相同值只会出现一次，如果作用于所有列，那么所有列的相同值都相同才相同（可用于整张表去重）。

ORDER BY：ORDER BY col DESC 按照col列降序排，ASC为升序。

 

【代码】

SELECT DISTINCT salary 
FROM salaries WHERE to_date = '9999-01-01'
ORDER BY salary DESC

 

9、获取所有部门当前manager的当前薪水情况

【题解】

类似第3题

 

【代码】

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

【题解】

使用NOT IN选出在employees但不在dept_manager中的emp_no记录，NOT IN的话就是顾名思义啦，“不在”的意思。

 

【代码】

SELECT e.emp_no FROM employees e
WHERE emp_no NOT IN(SELECT d.emp_no FROM dept_manager d)

 

11、获取所有员工当前的manager

【题解】

题意中明确说明如果是manager自己的话不用显示，所以需要加上这一句e.emp_no != m.emp_no，然后按要求连接两张表查询就可以啦。

 

【代码】

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

 

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

【题解】

可以通过两步来理解。

第一步：通过emp_no将两个表连接，并挑选出所有部门当前员工工薪，当前是9999-01-01，题里没说，是个小bug。

第二步：因为需要给出所有部门中工薪最高的信息，所以我们按照部门分组，将最高的工薪选出来。

 

【代码】

SELECT d.dept_no, d.emp_no, 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 HAVING MAX(s.salary)

 

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

【题解】

第一步：根据title将表进行分组

第二步：分组后将具有相同title的记录计数，返回>=2的即可。

 

【代码】

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

 

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

【题解】

这题首先要理解清楚题目的意思，题意是想我们找到按照title分组后，每组个数大于等于2（并且其中不能包含重复的emp_no）

比如title emp_no

       1      1

       1       1

这样的COUNT只能算1个哦。所以还是分2步走。

第一步：按照title分组

第二步：利用DISTINCT去掉重复emp_no，然后计数。

【代码】

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

 

15、查找employees表

【题解】

这题比较简单，直接按题意做就可以啦。

 

【代码】

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

 

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

【题解】

还是分2步来理解。

第一步：先通过emp_no将两个表连接

第二步：按照title分组，并对同属一个title的salay进行avg运算。

 

【代码】

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' AND salary = (
SELECT DISTINCT salary FROM salaries
    ORDER BY salary DESC LIMIT 1, 1
)

 

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

【题解】

仍然2步走。

第一步：先把两个表通过emp_no连接起来

第二步：因为这里不能用ORDER BY，所以不能再用上面的方法了，我们可以这么想，我先找出一个最大值salary，然后再找比这个最大值小的最大值，那不就是次大值了吗？

然后这里要注意题意是说当前薪水第二多的员工，所以只需s.to_date = '9999-01-01'即可。

 

【代码】

SELECT e.emp_no, MAX(s.salary), e.last_name, e.first_name
FROM employees e, salaries s 
WHERE s.salary < (SELECT MAX(salary) FROM salaries
                 WHERE to_date = '9999-01-01')
AND e.emp_no = s.emp_no AND s.to_date = '9999-01-01' 

 

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

【题解】

第一步：将employees表和dept_emp表通过emp_no进行左外连接，这样不管有没有分配部门的员工号对应的部门号就找出来了。

第二步：用同样的方法就讲dept_emp和departments左外连接，就能得到答案啦。

 

【代码】

SELECT e.last_name, e.first_name, dp.dept_name
FROM (employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no) 
LEFT JOIN departments dp ON d.dept_no = dp.dept_no 

 

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

【题解】

涨幅值即薪水的最大值 - 最小值，知道这个就可以做啦。

 

【代码】

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

 

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

【题解】

做到这题的时候会发现，这次要求的时候所有盐工自入职以来的薪水涨幅情况，所以我们可以新建两张表。

一张是当前员工的薪水，一张是员工入职时的薪水，再将两张表连接一下排序就可以了。

 

【代码】

SELECT now.emp_no, (now.salary - pre.salary) AS growth
FROM (SELECT e.emp_no, s.salary FROM employees e, salaries s
     WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS now,
(SELECT e.emp_no, s.salary FROM employees e, salaries s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date) AS pre
WHERE now.emp_no = pre.emp_no
ORDER BY growth ASC

 

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

【题解】

将三个表连接后，按照dept_no分组，计算有几条salary记录即可。

 

【代码】

SELECT dp.dept_no, dp.dept_name, COUNT(s.salary) AS sum
FROM departments dp, dept_emp d, salaries s
WHERE dp.dept_no = d.dept_no AND d.emp_no = s.emp_no
GROUP BY dp.dept_no

 

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

【题解】

这道题我觉得还是挺巧妙的，有必要收藏一下嘻嘻嘻。

首先要将两张salaries表连接，先挑选出to_date = '9999-01-01'的记录，然后注入灵魂的一句话就是s1.salary <= s2.salary，这句话是什么意思呢？比如我在s1表中找到一个salary是100，在s2中就能找到对应的大于等于100的（100,100,300）这样3条数据，然后用DISTINCT去重之后也就是2条，就是该salary的排名啦。

 

【代码】

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries s1, salaries 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员工当前的薪水情况

【题解】

好像斌不需要用employees表？

因为我只需从salaries表中找出不属于dept_manager表的emp_no，然后让salaries表和dept_emp连接就可以得到非manager员工当前的薪水情况啦。

 

【代码】

SELECT d.dept_no, s.emp_no, s.salary
FROM dept_emp d, salaries s
WHERE s.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND
s.emp_no = d.emp_no AND s.to_date = '9999-01-01'

 

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

【题解】

首先将manager表和salaries表连接成一个新表ms，emp表和salaries表也连接成一个新表es。

然后就可以根据题意操作啦，只要es.salary > ms.salary就可以了。

 

【代码】

SELECT es.emp_no, ms.emp_no AS manager_no, es.salary AS emp_salary, ms.salary AS manager_salary
FROM (dept_emp AS d INNER JOIN salaries AS s ON d.emp_no = s.emp_no AND s.to_date = '9999-01-01')AS es,
(dept_manager AS dm INNER JOIN salaries AS s ON dm.emp_no = s.emp_no AND s.to_date = '9999-01-01')AS ms
WHERE es.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND
es.dept_no = ms.dept_no AND es.salary > ms.salary 

 

 

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

【题解】

这里有几个注意点。

（1）这里统计的是当前员工的当前头衔，所以t.to_date = '9999-01-01'，d.to_date = '9999-01-01'

（2）分组也有两个参数，因为首先需要根据部门分组，再在部门里面根据不同的title进行分组统计。

 

【代码】

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

 

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

【题解】

设s1表为员工涨薪之前的表，s2表为员工涨薪之后的表。那么通过emp_no就可以把他们连接起来。然后这道题其实题意不是很明确，他说是每年，但事实上只要年份差为1即可。

因此只需要两个表的to_date相差1，且满足薪水涨幅超过5000就要把它选出来，另外不能忘记如果两个表的from_date相差1，也是需要被选出来的。比如有两个字段（2000-5-05,2001-6-13）和（2001-7-30,2001-8-12）也可以算是相差1年。

 

【代码】

SELECT s2.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 salary_growth > 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、查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量

【题解】

这题我们可以把每个类别电影数目>=5的类别先找出来建立个虚表cc。

然后经过这几个表的连接就可以得到答案啦。

 

【代码】

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

 

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

【题解】

这题还是蛮简单的，只需要通过左连接找到有分类的电影id，然后加个NOT IN就可以了。

 

【代码】

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

 

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

【题解】

简单题，通过WHERE...IN...将3张表连接即可。

 

【代码】

SELECT title, description FROM film
WHERE film_id IN (SELECT film_id FROM film_category WHERE category_id IN
                 (SELECT category_id FROM category WHERE name = 'Action'))

 

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

【题解】

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。

 

【代码】

EXPLAIN SELECT * FROM employees

 

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

【题解】

MySQL、SQL Server、Oracle等数据库支持CONCAT方法，
而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串

 

【代码】

mysql写法：

SELECT CONCAT(last_name, " ", first_name) AS Name FROM employees

 

SQLite写法：

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

 

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

【题解】

获取系统默认时间是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'))
)

 

34、批量插入数据

【题解】

只需要知道INSERT INTO 表名 VALUES的用法就可以啦

 

【代码】

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 表名 VALUES...

即如果数据存在就忽略不插入

 

【代码】

SQLite写法：

INSERT OR IGNORE INTO actor VALUES
(3, 'ED', 'CHASE', '2006-02-15 12:34:33')

 

Mysql写法：

INSERT IGNORE INTO actor VALUES
(3, 'ED', 'CHASE', '2006-02-15 12:34:33')

 

36、创建一个actor_name表

【题解】

把查询到的内容建表！get√

 

【代码】

SQLite写法：

CREATE TABLE actor_name AS
SELECT first_name, last_name FROM actor

 

MySQL写法：

CREATE TABLE actor_name 
SELECT first_name, last_name FROM actor

 

37、对first_name创建唯一索引uniq_idx_firstname

【题解】

添加索引的两种方式：（不知道为啥这题第二种过不了）

①修改表：ALTER TABLE 表名 ADD (UNIQUE) INDEX ON 索引名（属性名）

②创建索引：CREATE (UNIQUE) INDEX 索引名 ON 表名(属性名)

 

【代码】

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

//
ALTER TABLE actor ADD UNIQUE INDEX  uniq_idx_firstname(first_name);
ALTER TABLE actor ADD INDEX idx_lastname(last_name);

 

38、针对actor表创建视图actor_name_view

【题解】

创建视图：CREATE VIEW 视图名 AS ...

 

【代码】

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

【题解】

 

SQLite中，使用INDEXED语句进行强制索引查询：

SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

MySQL中，使用 FORCE INDEX 语句进行强制索引查询：

SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

【代码】

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

 

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

【题解】

修改表结构：ARTER TABLE 表名

添加列：ADD COLUMN 列名 ...

删除列：DROP COLUMN 列名

修改列：CHANGE COLUMN 列名 新列名...

 

【代码】

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