1、查找最晚入职员工的所有信息
【题解】
hire_date可能存在重复值，所以需要找到hire_date的最大值，然后再筛选，才能hire_date最晚的记录都筛选出来。
【代码】
1 SELECT * FROM employees
2 WHERE hire_date = (SELECT MAX(hire_date) FROM employees) View Code
2、查找入职员工时间排名倒数第三的员工所有信息
【题解】
还是hire_date可能存在重复值问题，所以需要先找到第三晚的hire_date（此处排序记得用distinct去重），然后再进行筛选。
【代码】
1 SELECT * FROM employees
2 WHERE hire_date = (SELECT DISTINCT hire_date FROM employees
3 ORDER BY hire_date DESC LIMIT 2, 1) View Code
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 View Code
1 SELECT s.*, d.dept_no
2 FROM dept_manager d, salaries s
3 WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' AND d.emp_no = s.emp_no
4 ORDER BY s.emp_no View Code
4、查找所有已经分配部门的员工的last_name和first_name
【题解】
也就是说有的员工不一定被分配了部门，那么只要将部门表左连接到员工表，即部门表上的信息都会有，但部门表上没有员工表上有的信息就不会被筛出，符合题目所求。
【代码】
1 SELECT e.last_name, e.first_name, d.dept_no
2 FROM dept_emp d LEFT JOIN employees e ON e.emp_no = d.emp_no View Code
5、查找所有员工的last_name和first_name以及对应部门编号dept_no
【题解】
跟上一题刚好反一下，这里是要把员工表左连接到部门表，这样不管员工是否有对应的部门，都能被显示出来。
【代码】
1 SELECT e.last_name, e.first_name, d.dept_no
2 FROM employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no View Code
6、查找所有员工入职时候的薪水情况
【题解】
这里需要注意一下，因为是给出每个员工入职时的薪资，所以还需要将加上这个条件e.hire_date = s.from_date。
【代码】
1 SELECT e.emp_no, s.salary
2 FROM employees e, salaries s
3 WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
4 ORDER BY e.emp_no DESC View Code
7、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
【题解】
GROUP BY配合聚合函数使用，按照emp_no分类后，COUNT记录每个emp_no的薪水涨幅次数，最后选出大于15的即可。
COUNT语句后面要跟HAVING哦。
如果对GROUP BY和聚合函数的使用不是很了解的话可以戳这里，我感觉讲的蛮好的。
【代码】
1 SELECT emp_no, COUNT(to_date) AS t
2 FROM salaries GROUP BY emp_no HAVING t > 15 View Code
8、找出所有员工当前薪水salary情况
【题解】
DISTINCT：如果作用于某一列，同一列的相同值只会出现一次，如果作用于所有列，那么所有列的相同值都相同才相同（可用于整张表去重）。
ORDER BY：ORDER BY col DESC 按照col列降序排，ASC为升序。
【代码】
1 SELECT DISTINCT salary
2 FROM salaries WHERE to_date = '9999-01-01'
3 ORDER BY salary DESC View Code
9、获取所有部门当前manager的当前薪水情况
【题解】
类似第3题
【代码】
1 SELECT d.dept_no, d.emp_no, s.salary
2 FROM dept_manager d, salaries s
3 WHERE d.emp_no = s.emp_no AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' View Code
10、获取所有非manager的员工emp_no
【题解】
使用NOT IN选出在employees但不在dept_manager中的emp_no记录，NOT IN的话就是顾名思义啦，“不在”的意思。
【代码】
1 SELECT e.emp_no FROM employees e
2 WHERE emp_no NOT IN(SELECT d.emp_no FROM dept_manager d) View Code
11、获取所有员工当前的manager
【题解】
题意中明确说明如果是manager自己的话不用显示，所以需要加上这一句e.emp_no != m.emp_no，然后按要求连接两张表查询就可以啦。
【代码】
1 SELECT e.emp_no, m.emp_no AS manager_no
2 FROM dept_emp e, dept_manager m
3 WHERE e.dept_no = m.dept_no AND e.to_date = '9999-01-01'
4 AND m.to_date = '9999-01-01' AND e.emp_no != m.emp_no View Code
12、获取所有部门中当前员工薪水最高的相关信息
【题解】
可以通过两步来理解。
第一步：通过emp_no将两个表连接，并挑选出所有部门当前员工工薪，当前是9999-01-01，题里没说，是个小bug。
第二步：因为需要给出所有部门中工薪最高的信息，所以我们按照部门分组，将最高的工薪选出来。
【代码】
1 SELECT d.dept_no, d.emp_no, s.salary
2 FROM dept_emp d, salaries s
3 WHERE d.emp_no = s.emp_no AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
4 GROUP BY d.dept_no HAVING MAX(s.salary) View Code
13、从titles表获取按照title进行分组
【题解】
第一步：根据title将表进行分组
第二步：分组后将具有相同title的记录计数，返回>=2的即可。
【代码】
1 SELECT title, COUNT(title) AS t
2 FROM titles
3 GROUP BY title HAVING t >= 2 View Code
14、从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。
【题解】
这题首先要理解清楚题目的意思，题意是想我们找到按照title分组后，每组个数大于等于2（并且其中不能包含重复的emp_no）
比如title emp_no
1 1
1 1
这样的COUNT只能算1个哦。所以还是分2步走。
第一步：按照title分组
第二步：利用DISTINCT去掉重复emp_no，然后计数。
【代码】
1 SELECT title, COUNT(DISTINCT emp_no) AS t
2 FROM titles
3 GROUP BY title HAVING t >= 2 View Code
15、查找employees表
【题解】
这题比较简单，直接按题意做就可以啦。
【代码】
1 SELECT * FROM employees
2 WHERE emp_no % 2 = 1 AND last_name != 'Mary'
3 ORDER BY hire_date DESC View Code
16、统计出当前各个title类型对应的员工当前薪水对应的平均工资
【题解】
还是分2步来理解。
第一步：先通过emp_no将两个表连接
第二步：按照title分组，并对同属一个title的salay进行avg运算。
【代码】
1 SELECT t.title, AVG(s.salary) AS avg
2 FROM titles t, salaries s
3 WHERE t.emp_no = s.emp_no AND t.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
4 GROUP BY t.title View Code
17、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
【题解】
类似第二题
【代码】
1 SELECT emp_no, salary FROM salaries
2 WHERE to_date = '9999-01-01' AND salary = (
3 SELECT DISTINCT salary FROM salaries
4 ORDER BY salary DESC LIMIT 1, 1
5 ) View Code
18、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by
【题解】
仍然2步走。
第一步：先把两个表通过emp_no连接起来
第二步：因为这里不能用ORDER BY，所以不能再用上面的方法了，我们可以这么想，我先找出一个最大值salary，然后再找比这个最大值小的最大值，那不就是次大值了吗？
然后这里要注意题意是说当前薪水第二多的员工，所以只需s.to_date = '9999-01-01'即可。
【代码】
1 SELECT e.emp_no, MAX(s.salary), e.last_name, e.first_name
2 FROM employees e, salaries s
3 WHERE s.salary < (SELECT MAX(salary) FROM salaries
4 WHERE to_date = '9999-01-01')
5 AND e.emp_no = s.emp_no AND s.to_date = '9999-01-01' View Code
19、查找所有员工的last_name和first_name以及对应的dept_name
【题解】
第一步：将employees表和dept_emp表通过emp_no进行左外连接，这样不管有没有分配部门的员工号对应的部门号就找出来了。
第二步：用同样的方法就讲dept_emp和departments左外连接，就能得到答案啦。
【代码】
1 SELECT e.last_name, e.first_name, dp.dept_name
2 FROM (employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no)
3 LEFT JOIN departments dp ON d.dept_no = dp.dept_no View Code
20、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
【题解】
涨幅值即薪水的最大值 - 最小值，知道这个就可以做啦。
【代码】
1 SELECT (MAX(salary) - MIN(salary)) AS growth
2 FROM salaries WHERE emp_no = '10001' View Code
21、查找所有员工自入职以来的薪水涨幅情况
【题解】
做到这题的时候会发现，这次要求的时候所有盐工自入职以来的薪水涨幅情况，所以我们可以新建两张表。
一张是当前员工的薪水，一张是员工入职时的薪水，再将两张表连接一下排序就可以了。
【代码】
1 SELECT now.emp_no, (now.salary - pre.salary) AS growth
2 FROM (SELECT e.emp_no, s.salary FROM employees e, salaries s
3 WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS now,
4 (SELECT e.emp_no, s.salary FROM employees e, salaries s
5 WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date) AS pre
6 WHERE now.emp_no = pre.emp_no
7 ORDER BY growth ASC View Code
22、统计各个部门对应员工涨幅的次数总和
【题解】
将三个表连接后，按照dept_no分组，计算有几条salary记录即可。
【代码】
1 SELECT dp.dept_no, dp.dept_name, COUNT(s.salary) AS sum
2 FROM departments dp, dept_emp d, salaries s
3 WHERE dp.dept_no = d.dept_no AND d.emp_no = s.emp_no
4 GROUP BY dp.dept_no View Code
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的排名啦。
【代码】
1 SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
2 FROM salaries s1, salaries s2
3 WHERE s1.to_date = '9999-01-01' AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
4 GROUP BY s1.emp_no
5 ORDER BY s1.salary DESC, s1.emp_no ASC View Code
24、获取所有非manager员工当前的薪水情况
【题解】
好像斌不需要用employees表？
因为我只需从salaries表中找出不属于dept_manager表的emp_no，然后让salaries表和dept_emp连接就可以得到非manager员工当前的薪水情况啦。
【代码】
1 SELECT d.dept_no, s.emp_no, s.salary
2 FROM dept_emp d, salaries s
3 WHERE s.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND
4 s.emp_no = d.emp_no AND s.to_date = '9999-01-01' View Code
25、获取员工其当前的薪水比其manager当前薪水还高的相关信息
【题解】
首先将manager表和salaries表连接成一个新表ms，emp表和salaries表也连接成一个新表es。
然后就可以根据题意操作啦，只要es.salary > ms.salary就可以了。
【代码】
1 SELECT es.emp_no, ms.emp_no AS manager_no, es.salary AS emp_salary, ms.salary AS manager_salary
2 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,
3 (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
4 WHERE es.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND
5 es.dept_no = ms.dept_no AND es.salary > ms.salary View Code
26、汇总各个部门当前员工的title类型的分配数目
【题解】
这里有几个注意点。
（1）这里统计的是当前员工的当前头衔，所以t.to_date = '9999-01-01'，d.to_date = '9999-01-01'
（2）分组也有两个参数，因为首先需要根据部门分组，再在部门里面根据不同的title进行分组统计。
【代码】
1 SELECT d.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
2 FROM departments dp, dept_emp d, titles t
3 WHERE dp.dept_no = d.dept_no AND d.emp_no = t.emp_no AND t.to_date = '9999-01-01'
4 AND d.to_date = '9999-01-01'
5 GROUP BY d.dept_no, t.title View Code
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年。
【代码】
1 SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
2 FROM salaries s1, salaries s2
3 WHERE s1.emp_no = s2.emp_no
4 AND salary_growth > 5000
5 AND (strftime("%Y", s2.to_date) - strftime("%Y", s1.to_date) = 1
6 OR strftime("%Y", s2.from_date) - strftime("%Y", s1.from_date) = 1 )
7 ORDER BY salary_growth DESC View Code
28、查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量
【题解】
这题我们可以把每个类别电影数目>=5的类别先找出来建立个虚表cc。
然后经过这几个表的连接就可以得到答案啦。
【代码】
1 SELECT c.name, COUNT(fc.film_id) AS num
2 FROM (SELECT category_id FROM film_category
3 GROUP BY category_id HAVING COUNT(film_id) >=5) AS cc,
4 film f, category c, film_category fc
5 WHERE f.description LIKE '%robot%' AND f.film_id = fc.film_id
6 AND fc.category_id = cc.category_id AND fc.category_id = c.category_id View Code
29、使用join查询方式找出没有分类的电影id以及名称
【题解】
这题还是蛮简单的，只需要通过左连接找到有分类的电影id，然后加个NOT IN就可以了。
【代码】
1 SELECT film_id, title
2 FROM film
3 WHERE film_id NOT IN(
4 SELECT f.film_id FROM film_category AS fc LEFT JOIN film AS f ON fc.film_id = f.film_id) View Code
30、用子查询的方式找出属于Action分类的所有电影对应的title,description
【题解】
简单题，通过WHERE...IN...将3张表连接即可。
【代码】
1 SELECT title, description FROM film
2 WHERE film_id IN (SELECT film_id FROM film_category WHERE category_id IN
3 (SELECT category_id FROM category WHERE name = 'Action')) View Code
31、获取select * from employees对应的执行计划
【题解】
explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
【代码】
1 EXPLAIN SELECT * FROM employees View Code
32、将employees表的所有员工的last_name和first_name拼接起来作为Name
【题解】
MySQL、SQL Server、Oracle等数据库支持CONCAT方法，
而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串
【代码】
mysql写法：
1 SELECT CONCAT(last_name, " ", first_name) AS Name FROM employees View Code
SQLite写法：
1 SELECT last_name ||" "|| first_name AS Name FROM employees View Code
33、创建一个actor表，包含如下列信息
【题解】
获取系统默认时间是datetime('now','localtime')
【代码】
1 CREATE TABLE actor
2 (
3 actor_id smallint(5) NOT NULL PRIMARY KEY,
4 first_name varchar(45) NOT NULL,
5 last_name varchar(45) NOT NULL,
6 last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
7 ) View Code
34、批量插入数据
【题解】
只需要知道INSERT INTO 表名 VALUES的用法就可以啦
【代码】
1 INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33') ,
2 (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33') View Code
35、批量插入数据，不使用replace操作
【题解】
需要知道INSERT OR IGNORE INTO 表名 VALUES...
即如果数据存在就忽略不插入
【代码】
SQLite写法：
1 INSERT OR IGNORE INTO actor VALUES
2 (3, 'ED', 'CHASE', '2006-02-15 12:34:33') View Code
Mysql写法：
1 INSERT IGNORE INTO actor VALUES
2 (3, 'ED', 'CHASE', '2006-02-15 12:34:33') View Code
36、创建一个actor_name表
【题解】
把查询到的内容建表！get√
【代码】
SQLite写法：
1 CREATE TABLE actor_name AS
2 SELECT first_name, last_name FROM actor View Code
MySQL写法：
1 CREATE TABLE actor_name
2 SELECT first_name, last_name FROM actor View Code
37、对first_name创建唯一索引uniq_idx_firstname
【题解】
添加索引的两种方式：（不知道为啥这题第二种过不了）
①修改表：ALTER TABLE 表名 ADD (UNIQUE) INDEX ON 索引名（属性名）
②创建索引：CREATE (UNIQUE) INDEX 索引名 ON 表名(属性名)
【代码】
1 CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
2 CREATE INDEX idx_lastname ON actor(last_name);
3
4 //
5 ALTER TABLE actor ADD UNIQUE INDEX uniq_idx_firstname(first_name);
6 ALTER TABLE actor ADD INDEX idx_lastname(last_name); View Code
38、针对actor表创建视图actor_name_view
【题解】
创建视图：CREATE VIEW 视图名 AS ...
【代码】
1 CREATE VIEW actor_name_view AS
2 SELECT first_name AS first_name_v, last_name AS last_name_v
3 FROM actor View Code
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
【代码】
1 SELECT * FROM salaries
2 INDEXED BY idx_emp_no WHERE emp_no = '10005' View Code
40、在last_update后面新增加一列名字为create_date
【题解】
修改表结构：ARTER TABLE 表名
添加列：ADD COLUMN 列名 ...
删除列：DROP COLUMN 列名
修改列：CHANGE COLUMN 列名 新列名...
【代码】
1 ALTER TABLE actor
2 ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00' View Code
转载于:https://www.cnblogs.com/z1014601153/p/11310968.html