数据库-SQL语句练习【已完成26题,还剩35题】 - 万雨

时间:2024-03-12 15:52:49

数据库-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`));
View Code

插入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\');
View Code

 创建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`));
View Code

插入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\');
View Code

 创建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);
View Code

插入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\');
View Code

创建 departments表格

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
View Code

插入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\');
View Code

创建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`));
View Code

插入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\');
View Code

 

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【基础】

关键知识点:

疑难知识点:

  1. INNER,LEFT,RIGHT JOIN 
  2. ORDER BY
  3. DISTICT 大数据时,该语句效率很低,建议使用GROUP BY 去重
  4. GROUP BY,HAVING
  5. COUNT
  6. MAX,AVG
  7. EXCEPT,UNION,INTERSECT
  8. 在sqlite中获取datetime时间对应的年份函数为strftime(\'%Y\', to_date)