数据库-SQL语句练习【已完成26题，还剩35题】
练习题链接：https://www.nowcoder.com/ta/sql?page=0
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 1+1 | 1+1 | 1-1 | 1-1 | 1 | 1 | ||||||||||
| 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 |
| 1 | 1 | 4 | 1 | 0.5 | 1 | ||||||||||
| 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 |
| 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | |||
练习数据库内容：
创建salaries表格
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`));
插入salaries内容
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26'); INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25'); INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25'); INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25'); INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25'); INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24'); INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24'); INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24'); INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24'); INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23'); INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23'); INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23'); INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23'); INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22'); INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22'); INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22'); INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03'); INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03'); INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02'); INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02'); INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02'); INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02'); INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02'); INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02'); INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01'); INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01'); INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01'); INSERT INTO salaries VALUES(10004,40054,'1986-12-01','1987-12-01'); INSERT INTO salaries VALUES(10004,42283,'1987-12-01','1988-11-30'); INSERT INTO salaries VALUES(10004,42542,'1988-11-30','1989-11-30'); INSERT INTO salaries VALUES(10004,46065,'1989-11-30','1990-11-30'); INSERT INTO salaries VALUES(10004,48271,'1990-11-30','1991-11-30'); INSERT INTO salaries VALUES(10004,50594,'1991-11-30','1992-11-29'); INSERT INTO salaries VALUES(10004,52119,'1992-11-29','1993-11-29'); INSERT INTO salaries VALUES(10004,54693,'1993-11-29','1994-11-29'); INSERT INTO salaries VALUES(10004,58326,'1994-11-29','1995-11-29'); INSERT INTO salaries VALUES(10004,60770,'1995-11-29','1996-11-28'); INSERT INTO salaries VALUES(10004,62566,'1996-11-28','1997-11-28'); INSERT INTO salaries VALUES(10004,64340,'1997-11-28','1998-11-28'); INSERT INTO salaries VALUES(10004,67096,'1998-11-28','1999-11-28'); INSERT INTO salaries VALUES(10004,69722,'1999-11-28','2000-11-27'); INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27'); INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01'); INSERT INTO salaries VALUES(10005,78228,'1989-09-12','1990-09-12'); INSERT INTO salaries VALUES(10005,82621,'1990-09-12','1991-09-12'); INSERT INTO salaries VALUES(10005,83735,'1991-09-12','1992-09-11'); INSERT INTO salaries VALUES(10005,85572,'1992-09-11','1993-09-11'); INSERT INTO salaries VALUES(10005,85076,'1993-09-11','1994-09-11'); INSERT INTO salaries VALUES(10005,86050,'1994-09-11','1995-09-11'); INSERT INTO salaries VALUES(10005,88448,'1995-09-11','1996-09-10'); INSERT INTO salaries VALUES(10005,88063,'1996-09-10','1997-09-10'); INSERT INTO salaries VALUES(10005,89724,'1997-09-10','1998-09-10'); INSERT INTO salaries VALUES(10005,90392,'1998-09-10','1999-09-10'); INSERT INTO salaries VALUES(10005,90531,'1999-09-10','2000-09-09'); INSERT INTO salaries VALUES(10005,91453,'2000-09-09','2001-09-09'); INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01'); INSERT INTO salaries VALUES(10006,43311,'1990-08-05','1991-08-05'); INSERT INTO salaries VALUES(10006,43311,'1991-08-05','1992-08-04'); INSERT INTO salaries VALUES(10006,43311,'1992-08-04','1993-08-04'); INSERT INTO salaries VALUES(10006,43311,'1993-08-04','1994-08-04'); INSERT INTO salaries VALUES(10006,43311,'1994-08-04','1995-08-04'); INSERT INTO salaries VALUES(10006,43311,'1995-08-04','1996-08-03'); INSERT INTO salaries VALUES(10006,43311,'1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10006,43311,'1997-08-03','1998-08-03'); INSERT INTO salaries VALUES(10006,43311,'1998-08-03','1999-08-03'); INSERT INTO salaries VALUES(10006,43311,'1999-08-03','2000-08-02'); INSERT INTO salaries VALUES(10006,43311,'2000-08-02','2001-08-02'); INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10007,56724,'1989-02-10','1990-02-10'); INSERT INTO salaries VALUES(10007,60740,'1990-02-10','1991-02-10'); INSERT INTO salaries VALUES(10007,62745,'1991-02-10','1992-02-10'); INSERT INTO salaries VALUES(10007,63475,'1992-02-10','1993-02-09'); INSERT INTO salaries VALUES(10007,63208,'1993-02-09','1994-02-09'); INSERT INTO salaries VALUES(10007,64563,'1994-02-09','1995-02-09'); INSERT INTO salaries VALUES(10007,68833,'1995-02-09','1996-02-09'); INSERT INTO salaries VALUES(10007,70220,'1996-02-09','1997-02-08'); INSERT INTO salaries VALUES(10007,73362,'1997-02-08','1998-02-08'); INSERT INTO salaries VALUES(10007,75582,'1998-02-08','1999-02-08'); INSERT INTO salaries VALUES(10007,79513,'1999-02-08','2000-02-08'); INSERT INTO salaries VALUES(10007,80083,'2000-02-08','2001-02-07'); INSERT INTO salaries VALUES(10007,84456,'2001-02-07','2002-02-07'); INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01'); INSERT INTO salaries VALUES(10008,46671,'1998-03-11','1999-03-11'); INSERT INTO salaries VALUES(10008,48584,'1999-03-11','2000-03-10'); INSERT INTO salaries VALUES(10008,52668,'2000-03-10','2000-07-31'); INSERT INTO salaries VALUES(10009,60929,'1985-02-18','1986-02-18'); INSERT INTO salaries VALUES(10009,64604,'1986-02-18','1987-02-18'); INSERT INTO salaries VALUES(10009,64780,'1987-02-18','1988-02-18'); INSERT INTO salaries VALUES(10009,66302,'1988-02-18','1989-02-17'); INSERT INTO salaries VALUES(10009,69042,'1989-02-17','1990-02-17'); INSERT INTO salaries VALUES(10009,70889,'1990-02-17','1991-02-17'); INSERT INTO salaries VALUES(10009,71434,'1991-02-17','1992-02-17'); INSERT INTO salaries VALUES(10009,74612,'1992-02-17','1993-02-16'); INSERT INTO salaries VALUES(10009,76518,'1993-02-16','1994-02-16'); INSERT INTO salaries VALUES(10009,78335,'1994-02-16','1995-02-16'); INSERT INTO salaries VALUES(10009,80944,'1995-02-16','1996-02-16'); INSERT INTO salaries VALUES(10009,82507,'1996-02-16','1997-02-15'); INSERT INTO salaries VALUES(10009,85875,'1997-02-15','1998-02-15'); INSERT INTO salaries VALUES(10009,89324,'1998-02-15','1999-02-15'); INSERT INTO salaries VALUES(10009,90668,'1999-02-15','2000-02-15'); INSERT INTO salaries VALUES(10009,93507,'2000-02-15','2001-02-14'); INSERT INTO salaries VALUES(10009,94443,'2001-02-14','2002-02-14');
创建dept_emp表格
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`));
插入dept_emp内容
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01'); INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01'); INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01'); INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01'); INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01'); INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01'); INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01'); INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31'); INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01'); INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26'); INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');
创建titles表格
CREATE TABLE `titles` ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL);
插入titles内容
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01'); INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01'); INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01'); INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01'); INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01'); INSERT INTO titles VALUES(10005,'Senior Staff','1996-09-12','9999-01-01'); INSERT INTO titles VALUES(10005,'Staff','1989-09-12','1996-09-12'); INSERT INTO titles VALUES(10006,'Senior Engineer','1990-08-05','9999-01-01'); INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01'); INSERT INTO titles VALUES(10007,'Staff','1989-02-10','1996-02-11'); INSERT INTO titles VALUES(10008,'Assistant Engineer','1998-03-11','2000-07-31'); INSERT INTO titles VALUES(10009,'Assistant Engineer','1985-02-18','1990-02-18'); INSERT INTO titles VALUES(10009,'Engineer','1990-02-18','1995-02-18'); INSERT INTO titles VALUES(10009,'Senior Engineer','1995-02-18','9999-01-01'); INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01'); INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');
创建 departments表格
CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`));
插入departments内容
INSERT INTO departments VALUES('d001','Marketing'); INSERT INTO departments VALUES('d002','Finance'); INSERT INTO departments VALUES('d003','Human Resources'); INSERT INTO departments VALUES('d004','Production'); INSERT INTO departments VALUES('d005','Development'); INSERT INTO departments VALUES('d006','Quality Management');
创建employees表格
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`));
插入employees内容
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'); INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'); INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'); INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'); INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'); INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'); INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'); INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'); INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'); INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'); INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
1，查找最晚入职员工的所有信息
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;
2，查找入职员工时间排名倒数第三的员工所有信息
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;
3，查找当前薪水详情以及部门编号dept_no
查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
错误尝试第二次【原因：①表的先后出错；②to_date两次限定条件】
SELECT d.emp_no,s.salary,s.from_date,s.to_date,d.dept_no FROM dept_manager AS d INNER JOIN salaries AS s ON d.emp_no=s.emp_no WHERE d.to_date='9999-01-01';
正确语句：
SELECT s.*,d.dept_no FROM salaries AS s INNER JOIN dept_manager AS d ON d.emp_no=s.emp_no WHERE d.to_date='9999-01-01' AND s.to_date='9999-01-01';
关于为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？
-----因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前，而dept_manager是因为有领导会离职，to_date时间不一定是9999-01-01，所以要过滤过离职的领导
4，查找所有已经分配部门的员工的last_name和first_name
错误尝试第二次【原因查找到：，的中文标点导致的】
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;
正确的语句：
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【LEFT ,RIGHT ,INNER JOIN】
SELECT e.last_name,e.first_name,d.dept_no FROM employees AS e LEFT JOIN dept_emp AS d ON e.emp_no=d.emp_no;
6，查找所有员工入职时候的薪水情况
第二次错误尝试【错误原因：内连接条件与WHERE条件混淆】
SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no=s.emp_no WHERE e.hire_date=s.to_date ORDER BY emp_no DESC;
正确语句1：【内连接完成】
SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no=s.emp_no AND e.hire_date=s.from_date ORDER BY e.emp_no DESC;
正确语句2：【，号并列连接完成】
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
解析：
内连接是取左右两张表的交集形成一个新表，用FROM并列两张表后仍然还是两张表。
如果还要对新表进行操作则要用内连接。从效率上看应该FROM并列查询比较快，因为不用形成新表。本题从效果上看两个方法没区别。
7，查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
第二次尝试错误【错误原因：把问题想复杂了】
SELECT s.emp_no,COUNT(s.emp) t FROM salaries s INNER JOIN salaries s1 ON s.from_date=s1.to_date GROUP BY emp_no HAVING t>14;
正确语句：
SELECT s.emp_no,COUNT(s.emp_no) t FROM salaries s GROUP BY s.emp_no HAVING t>15;
注意：
group by与order by有什么区别，order by就是排序。而group by就是分组，举个例子好说点，group by 单位名称
这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。
这样可以更好的分下类，更好看一些。
还有就是为什么没有用where而是用的having，记住下面的两句话就好了。
WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。
8，找出所有员工当前薪水salary情况
找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
SELECT DISTINCT s.salary FROM salaries s WHERE s.to_date='9999-01-01' ORDER BY s.salary DESC;
更优化的语句：
SELECT s.salary FROM salaries s WHERE s.to_date='9999-01-01' GROUP BY s.salary ORDER BY s.salary DESC;
注意：
大部分人都是用distinct，但是大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。
简单一点理解，在单表的时候使用distinct，多表的时候使用group by，虽然一般使用group by ，但还是要知道distinct的用法
9，获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'
两表联查
SELECT d.dept_no,s.emp_no,s.salary FROM dept_manager d,salaries s WHERE d.emp_no=s.emp_no AND s.to_date='9999-01-01' AND d.to_date='9999-01-01';
内连接
SELECT d.dept_no,s.emp_no,s.salary FROM dept_manager d INNER JOIN salaries s ON d.emp_no=s.emp_no WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01';
10，获取所有非manager的员工emp_no
NOT IN条件限制
SELECT e.emp_no FROM employees e WHERE e.emp_no NOT IN( SELECT d.emp_no FROM dept_manager d);
左连接尝试错误【①限定条件问题，应该是所有的dept_no；②应该显示连接后新表的所有内容】
SELECT e.emp_no FROM employees e LEFT JOIN dept_manager d ON e.emp_no=d.emp_no WHERE e.dept_no IS NULL;
SELECT emp_no FROM (SELECT e.* FROM employees e LEFT JOIN dept_manager d ON e.emp_no=d.emp_no) WHERE dept_no IS NULL;
左连接正确语句：
SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager ON employees.emp_no=dept_manager.emp_no) WHERE dept_no IS NULL;
SQL是支持集合运算的：
SELECT employees.emp_no FROM employees EXCEPT SELECT dept_manager.emp_no FROM dept_manager;
-- EXPECT 集合差运算【集合A-集合B】-- UNION 集合并运算【集合A，集合B所有元素组成】-- INTERSECT 集合交运算【集合A、集合B共有的部分】
11，获取所有员工当前的manager
获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
两表联查尝试：【不等号<>更加通用；!=在sql2000中用到，则是语法错误，不兼容的】
SELECT de.emp_no,dm.emp_no AS manager_no FROM dept_emp de ,dept_manager dm WHERE de.to_date='9999-01-01' AND dm.to_date='9999-01-01' AND dm.dept_no=de.dept_no AND de.emp_no<>dm.emp_no;
内连接尝试：
SELECT de.emp_no,dm.emp_no AS manager_no FROM dept_emp de INNER JOIN dept_manager dm ON dm.dept_no=de.dept_no WHERE de.to_date='9999-01-01' AND dm.to_date='9999-01-01' AND de.emp_no != dm.emp_no;
12，获取所有部门中当前员工薪水最高的相关信息
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
内连接尝试错误：【GROUP BY 这条语句需要自己好好探究一下】
SELECT d.dept_no,d.emp_no,MAX(s.salary) salary FROM dept_emp d INNER JOIN salaries s ON d.emp_no=s.emp_no WHERE d.to_date='9999-01-01' AND s.to_date='9999-01-01';
正确语法：
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;
两表联查：
select d.dept_no, d.emp_no, max(s.salary) from salaries s , dept_emp d 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;
我在自己的阿里云主机上的MySQL还遇到了【ONLY_FULL_GROUP_BY问题】
这个主要是自己MySQL服务器设置中的sql_mode中设置了ONLY_FULL_GROUP_BY导致
解决方法见链接：https://www.cnblogs.com/xzjf/p/8466858.html
用连接和用from table1, table2并列查询，有时候都可以解题。
只是前者将几张表连接成一张大表，后者是并列查询若干表，两种方法在表中数据庞大的情况下肯定有效率的差异，至于哪个好自己百度一下吧，有实验环境也可以自己测试一下。
13，从titles表获取按照title进行分组
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
SELECT title,COUNT(title) t FROM titles GROUP BY title HAVING t>1;
为什么不用考虑 to_date='9999-01-01' 呢？【关键字：当前没有出现】
以正常逻辑来说要加，否则没有意义，但题目没要求，而且加了 to_date='9999-01-01'就通不过了，可以从测试用例看出题目算的是当前及以前titles的数目总数
14，从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的title进行忽略。
自己的错误尝试：
SELECT DISTINCT title,COUNT(*) t FROM titles GROUP BY title HAVING t>1;
正确的语句：
SELECT title,COUNT(DISTINCT emp_no) t FROM titles GROUP BY title HAVING t>1;
15，查找employees表
查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
SELECT * FROM employees WHERE emp_no%2=1 AND last_name<>'Mary' ORDER BY hire_date DESC;
16，统计出当前各个title类型对应的员工当前薪水对应的平均工资
统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。
两表联立查询：
SELECT t.title,AVG(s.salary) avg FROM salaries s,titles t WHERE s.emp_no=t.emp_no AND s.to_date='9999-01-01' AND t.to_date='9999-01-01' GROUP BY title;
内连接查询：
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 to_date='9999-01-01' ORDER BY salary DESC LIMIT 1,1;
避免了2个问题：
(1) 首先这样可以解决多个人工资相同的问题；
(2) 另外，筛选出第二多的工资时要注意distinct 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 );
大数据量的情况下，避免使用DISTINCT
SELECT emp_no, salary FROM salaries WHERE to_date = '9999-01-01' AND salary = ( SELECT salary from salaries GROUP BY salary ORDER BY salary DESC LIMIT 1,1 );
18，获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by
自己尝试的：
SELECT s.emp_no,MAX(s.salary),e.last_name,e.first_name FROM employees AS e ,salaries AS s WHERE s.emp_no=e.emp_no AND s.to_date='9999-01-01' AND e.to_date='9999-01-01' AND s.salary NOT IN(SELECT MAX(salary) FROM salaries WHERE to_date='9999-01-01');
改正代码【两表联立】：
SELECT s.emp_no,MAX(s.salary),e.last_name,e.first_name FROM employees AS e ,salaries AS s WHERE s.emp_no=e.emp_no AND s.to_date='9999-01-01' AND s.salary NOT IN(SELECT MAX(salary) FROM salaries WHERE to_date='9999-01-01');
正确语句【内连接】：
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
查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工【LEFT,RIGHT JOIN眉头一紧发现关键所在】
自己尝试
SELECT t.last_name,t.first_name,dm.dept_name FROM departments dm ,( SELECT e.last_name,e.first_name,de.* FROM employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no) t WHERE t.dept_no=dm.dept_no;
正确的语句：
SELECT e.last_name,e.first_name,dm.dept_name FROM (employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no) LEFT JOIN departments dm ON de.dept_no=dm.dept_no;
20，查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
分别查找两个to_date的数据相减
SELECT ((SELECT salary FROM salaries WHERE emp_no=10001 AND to_date='9999-01-01')- (SELECT salary FROM salaries WHERE emp_no=10001 ORDER BY to_date LIMIT 0,1)) AS growth;
21，查找所有员工自入职以来的薪水涨幅情况【所有员工】【联查与连接如何区分使用？？？】
尝试使用上面的方法进行解决问题【ERROR 1242 (21000): Subquery returns more than 1 row】
SELECT ((SELECT salary FROM salaries WHERE to_date='9999-01-01')- (SELECT salary FROM salaries ORDER BY to_date LIMIT 0,1)) AS growth;
正确语句：
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS sCurrent, (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart WHERE sCurrent.emp_no = sStart.emp_no ORDER BY growth
22，统计各个部门对应员工涨幅的次数总和
统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
自己手撕出来的
SELECT dm.dept_no,dm.dept_name,dc.num FROM (SELECT de.dept_no,SUM(sc.sSum) AS num FROM dept_emp de INNER JOIN (SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no) sc ON de.emp_no=sc.emp_no GROUP BY de.dept_no) dc,departments dm WHERE dc.dept_no=dm.dept_no;
分级进行处理：
①统计各个员工涨薪次数 COUNT
SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no;
②统计各个部门的涨薪次数 SUM
SELECT de.dept_no,SUM(sc.sSum) AS num FROM dept_emp de INNER JOIN (SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no) sc ON de.emp_no=sc.emp_no GROUP BY de.dept_no;
③将departments联查显示部门名称
SELECT dm.dept_no,dm.dept_name,dc.num FROM (SELECT de.dept_no,SUM(sc.sSum) AS num FROM dept_emp de INNER JOIN (SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no) sc ON de.emp_no=sc.emp_no GROUP BY de.dept_no) dc,departments dm WHERE dc.dept_no=dm.dept_no;
23，对所有员工的薪水按照salary进行按照1-N的排名【两表联查】
对所有员工的当前(to_date='9999-01-01')薪水按照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员工当前的薪水情况
获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
SELECT de.dept_no,de.emp_no,es.salary FROM dept_emp de , (SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date='9999-01-01' AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager)) es WHERE de.emp_no=es.emp_no;
分级来进行处理：
①内连接salaries与employees，找出当前员工的工号与工资
SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date='9999-01-01'；
②在上面的基础上加上一个语句，去除管理岗的工号
SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date='9999-01-01' AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager);
③将新表与dept_emp进行联查处理
SELECT de.dept_no,de.emp_no,es.salary FROM dept_emp de , (SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date='9999-01-01' AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager)) es WHERE de.emp_no=es.emp_no;
正确语句2：本质上是一样的
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 WHERE to_date = '9999-01-01')
25，获取员工其当前的薪水比其manager当前薪水还高的相关信息【两表联查】
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.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 sem, (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 sdm WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary
分级来进行处理：
①先内连接salaries与dept_emp，获取当前工资、工号、部门号；
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')
②先内连接salaries与dept_manager，获取当前工资、工号、部门号；
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')
③将两个表联查限制条件是：同一个部门，非管理比管理工资高；
SELECT sem.emp_no,sdm.emp_no manager_no,sem.salary emp_salary,sdm.salary emp_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') sem, (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') sdm WHERE sem.dept_no=sdm.dept_no AND sem.salary > sdm.salary;
26，汇总各个部门当前员工的title类型的分配数目
汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
错误语句：
SELECT dm.dept_no,dm.dept_name,ec.title,COUNT(ec.title) 'count' FROM departments dm,(SELECT DISTINCT de.emp_no,de.dept_no,t.title FROM dept_emp de INNER JOIN titles t ON de.emp_no=t.emp_no WHERE de.to_date='9999-01-01' AND t.to_date='9999-01-01') ec WHERE dm.dept_no=ec.dept_no GROUP BY dm.dept_no;
正确语句：
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【新知识点】
给出每个员工每年薪水涨幅超过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 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
总结
常用语句：SELECT,FROM,WHERE【基础】
关键知识点：
疑难知识点：
- INNER,LEFT,RIGHT JOIN
- ORDER BY
- DISTICT 大数据时，该语句效率很低，建议使用GROUP BY 去重
- GROUP BY,HAVING
- COUNT
- MAX,AVG
- EXCEPT,UNION,INTERSECT
- 在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
探究未知是最大乐趣
