牛客网SQL练习题总结(一)

题目描述

查找最晚入职员工的所有信息
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`));

输入描述:

输出描述:

emp_no
birth_date
first_name
last_name
gender
hire_date
10008
1958-02-19
Saniya
Kalloufi
M
1994-09-15
select * from employees
where hire_date =
(select max(hire_date) from employees)
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;  # 不推荐
top和limit方法有牵强之处,与给定数据集有关
最晚入职的当天未必就一个人,也许有多人,使用排序并限制得只能取得指定数量的结果

题目描述

查找入职员工时间排名倒数第三的员工所有信息
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`));

输入描述:

输出描述:

emp_no
birth_date
first_name
last_name
gender
hire_date
10005
1955-01-21
Kyoichi
Maliniak
M
1989-09-12
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;
LIMIT m,n : 表示从第m+1条开始,取n条数据;
LIMIT n : 表示从第0条开始,取n条数据,是limit(0,n)的缩写。
本题limit 2,1 表示从第(2+1)条数据开始,取一条数据,即入职员工时间排名倒数第三的员工。

题目描述

查找各个部门当前(to_date=’9999-01-01′)领导当前薪水详情以及其对应部门编号dept_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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`));

输入描述:

输出描述:

emp_no
salary
from_date
to_date
dept_no
10002
72527
2001-08-02
9999-01-01
d001
10004
74057
2001-11-27
9999-01-01
d004
10005
94692
2001-09-09
9999-01-01
d003
10006
43311
2001-08-02
9999-01-01
d002
10010
94409
2001-11-23
9999-01-01
d006
select s.*,d.dept_no
from salaries s, dept_manager d
where s.to_date = ‘9999-01-01’
and d.to_date = ‘9999-01-01’
and s.emp_no = d.emp_no;
这里的坑主要在于两个表的逻辑关系,题目要求是薪水情况以及部门编号,再结合输出情况dept_no 被放到了最后一列,可见是主表是“salaries”。这里顺序错了就会提示:您的代码无法通过所有用例。。。

题目描述

查找所有已经分配部门的员工的last_name和first_name
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`));
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`));

输入描述:

输出描述:

last_name
first_name
dept_no
Facello
Georgi
d001
省略
省略
省略
Piveteau
Duangkaew
d006
SELECT e.last_name, e.first_name, d.dept_no
FROM dept_emp d NATURAL JOIN employees e;
只有一列是公有的,用自然连接呀亲们。

题目描述

查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
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`));
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`));

输入描述:

输出描述:

last_name
first_name
dept_no
Facello
Georgi
d001
省略
省略
省略
Sluis
Mary
NULL(在sqlite中此处为空,MySQL为NULL)
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 会读取右边数据表的全部数据,即便左边表无对应数据。
注意
on与where有什么区别,两个表连接时用on,在使用left  jion时,on和where条件的区别如下:
1、  on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left  join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

题目描述

查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
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`));
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`));

输入描述:

输出描述:

emp_no
salary
10011
25828
省略
省略
10001
60117
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;
SELECT e.emp_no, s.salary FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC
此题应注意以下四个知识点:
1、由于测试数据中,salaries.emp_no不唯一(因为号码为emp_no的员工会有多次涨薪的可能,所以在salaries中对应的记录不止一条),employees.emp_no唯一,即salaries的数据会多于employees,因此需先找到employees.emp_no在salaries表中对应的记录salaries.emp_no,则有限制条件e.emp_no = s.emp_no
2、根据题意注意到salaries.from_date 和employees.hire_date的值应该要相等,因此有限制条件 e.hire_date = s.from_date
3、根据题意要按照emp_no值逆序排列,因此最后要加上 ORDER BY e.emp_no DESC
4、为了代码良好的可读性,运用了Alias别名语句,将employees简化为e,salaries简化为s,即 employees AS e与salaries AS s,其中AS可以省略

题目描述

查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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`));

输入描述:

输出描述:

emp_no
t
10001
17
10004
16
10009
18
select emp_no,count(emp_no) as t
from salaries
group by emp_no
having t > 15;
此题应注意以下四点:
1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意,输出的涨幅次数为t,故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件
4、最后存在一个理解误区,涨幅超过15次,salaries中相应的记录数应该超过16(从第2条记录开始算作第1次涨幅),不过题目为了简单起见,将第1条记录当作第1次涨幅,所以令t>15即可
/**  注意: 严格来说,下一条salary高于本条才算涨幅,但本题只要出现了一条记录就算一次涨幅,salary相同可以理解为涨幅为0,salary变少理解为涨幅为负 **/

题目描述

找出所有员工当前(to_date=’9999-01-01′)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
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`));

输入描述:

输出描述:

salary
94692
94409
88958
88070
74057
72527
59755
43311
25828
select salary from salaries  where to_date=’9999-01-01′ group by salary order by salary desc;
SELECT DISTINCT salary FROM salaries WHERE to_date = ‘9999-01-01’ ORDER BY salary DESC

对于distinct,groupby的性能。

数据量非常巨大时候,比如1000万中有300W重复数据,这时候的distinct的效率略好于group by;
对于相对重复量较小的数据量比如1000万中1万的重复量,用groupby的性能会远优于distnct。
简书上的一篇博客说的不错,大家可以穿送过去看一看传送门
重复量小用group by,重复量大用dstinct

题目描述

获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=’9999-01-01′
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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`));

输入描述:

输出描述:

dept_no
emp_no
salary
d001
10002
72527
d004
10004
74057
d003
10005
94692
d002
10006
43311
d006
10010
94409
SELECT d.dept_no, d.emp_no, s.salary 
FROM salaries AS s INNER JOIN dept_manager AS d 
ON d.emp_no = s.emp_no
AND d.to_date = ‘9999-01-01’
AND s.to_date = ‘9999-01-01’
1、先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no,并且将salaries用别名s代替,dept_manager用别名d代替
2、根据题意,要获取当前manager的当前salary情况,再加上限制条件d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’即可(因为同一emp_no在salaries表中对应多条涨薪记录,而当s.to_date = ‘9999-01-01’时是该员工当前的薪水记录)

题目描述

获取所有非manager的员工emp_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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`));

输入描述:

输出描述:

emp_no
10001
10003
10007
10008
10009
10011
方法一:使用NOT IN选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
not in在实际使用中,因为not in会转化成多表连接,而且不使用索引,在这里,觉得还是用left_join代替会好一点
方法二:先使用LEFT JOIN连接两张表,再从此表中选出dept_no值为NULL对应的emp_no记录
SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL
方法三:方法二的简版,使用单层SELECT语句即可
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL

题目描述

获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date=’9999-01-01’。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
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`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

输入描述:

输出描述:

emp_no
manager_no
10001
10002
10003
10004
10009
10010
select de.emp_no,dm.emp_no as manager_no
from dept_emp de,dept_manager dm
where de.dept_no = dm.dept_no
and de.to_date=’9999-01-01′
and dm.to_date=’9999-01-01′
and de.emp_no <> dm.emp_no;
应注意以下三点:
1、用INNER JOIN连接两张表,因为要输出自己的经理,得知自己与经理的部门要相同,故有限制条件 de.dept_no = dm.dept_no
2、再用WHERE限制当前员工与当前经理的条件,即 dm.to_date 等于 ‘9999-01-01’ 、de.to_date 等于 ‘9999-01-01’ 、 de.emp_no 不等于dm.emp_no
3、为了增强代码可读性,将dept_emp用别名de代替,dept_manager用dm代替,最后根据题意将de.emp_no用别名manager_no代替后输出

题目描述

获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
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`));
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`));

输入描述:

输出描述:

dept_no
emp_no
salary
d001
10001
88958
d002
10006
43311
d003
10005
94692
d004
10004
74057
d005
10007
88070
d006
10009
95409
此题思路如下:
1、先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no;
2、选取每个员工当前的工资水平,用d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’作条件限制,因为此表中每条最新记录的 to_date 都用 9999-01-01 表示;
3、用GROUP BY d.dept_no将每个部门分为一组,用MAX()函数选取每组中工资最高者;
4、将salaries用s代替,dept_emp用d代替,最后将MAX(s.salary)用salary代替后输出。
select d.dept_no,d.emp_no,max(s.salary) as salary
from dept_emp d inner join salaries s
on 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;
——————————————分割线:若存在多条最大记录—————————————-
有同学提出疑问,如果存在多条最大记录怎么办?而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录,其余记录均被忽略。此时解法如下:
1、创建两张表,一张为maxsalary,用于存放当前每个部门薪水的最大值;另一张为currentsalary,用于存放当前每个部门所有员工的编号和薪水;
2、限定条件为两张表的 dept_no 和 salary 相等,这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了;
3、最后记得根据 currentsalary.dept_no 升序排列,输出与参考答案相同的记录表。
4、以下代码虽然很长,仔细一看都是基于上面的基础解法变化而来的,中心思想就是绕开 MAX 的特性限制,运用比较的方法选出多个相同的最大值。
SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
FROM
//创建maxsalary表用于存放当前每个部门薪水的最大值
(SELECT d.dept_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) AS maxsalary,
//创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
(SELECT d.dept_no, s.emp_no, s.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’
) AS currentsalary
//限定条件为两表的dept_no和salary均相等
WHERE currentsalary.dept_no = maxsalary.dept_no
AND currentsalary.salary = maxsalary.salary
//最后以currentsalary.dept_no排序输出符合要求的记录表
ORDER BY currentsalary.dept_no

题目描述

从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS “titles” (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

输入描述:

输出描述:

title
t
Assistant Engineer
2
Engineer
4
省略
省略
Staff
3
select title,count(title) as t
from titles
group by title
having t >=2;
此题应注意以下三点:
1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数
2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(title)的值转换为t
3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件

题目描述

从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS “titles” (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

输入描述:

输出描述:

title
t
Assistant Engineer
2
Engineer
3
省略
省略
Staff
3
select title,count(distinct emp_no) as t
from titles
group by title
having t >=2;
此题应注意以下三点:
1、先用GROUP BY title将表格以title分组,再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数
2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(DISTINCT emp_no)的值转换为t
3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件

题目描述

查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
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`));

输入描述:

输出描述:

emp_no
birth_date
first_name
last_name
gender
hire_date
10011
1953-11-07
Mary
Sluis
F
1990-01-22
10005
1955-01-21
Kyoichi
Maliniak
M
1989-09-12
10007
1957-05-23
Tzvetan
Zielinski
F
1989-02-10
10003
1959-12-03
Parto
Bamford
M
1986-08-28
10001
1953-09-02
Georgi
Facello
M
1986-06-26
10009
1952-04-19
Sumant
Peac
F
1985-02-18
select * from employees
where emp_no % 2 =1
and last_name != ‘Mary’
order by hire_date desc;
三点需要注意:
1、员工号为奇数,则emp_no取余应为1
2、last_name不为Mary,用‘!=’表示
3.根据hire_date逆序排列,用desc
 

题目描述

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
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`));
CREATE TABLE IF NOT EXISTS “titles” (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

输入描述:

输出描述:

title
avg
Engineer
94409.0
Senior Engineer
69009.2
Senior Staff
91381.0
Staff
72527.0
SELECT t.title AS title, AVG(s.salary) AS avg
FROM titles AS t
INNER JOIN (SELECT * FROM salaries GROUP BY emp_no HAVING to_date = MAX(to_date)) AS s
ON s.emp_no = t.emp_no AND s.to_date = t.to_date AND s.to_date = ‘9999-01-01’
GROUP BY t.title
1、先算出当前员工的当前工资表,即由每个emp_no的to_date为最大时的记录构成的表
2、再将此表与titles连接,限定条件为emp_no相等且to_date相等,即得当前员工的工资表
3、最后以title分组,利用AVG()函数计算每个title下的平均工资

题目描述

获取当前(to_date=’9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary
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`));

输入描述:

输出描述:

emp_no
salary
10009
94409
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)
避免了2个问题:
(1) 首先这样可以解决多个人工资相同的问题;
(2) 另外,筛选出第二多的工资时要注意distinct salary,否则不能选出第二多的工资。
 

题目描述

查找当前薪水(to_date=’9999-01-01′)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
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`));
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`));

输入描述:

输出描述:

emp_no
salary
last_name
first_name
10009
94409
Peac
Sumant
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’)
 
本题做法很多,主要思想为多层SELECT嵌套与MAX()函数结合
1、先利用MAX()函数找出salaries中当前薪水最高者,即SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’
2、再利用INNER JOIN连接employees与salaries表,限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = ‘9999-01-01’与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’)
3、在以上限制条件下找薪水最高者,即为所有员工薪水的次高者
 

题目描述

查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
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`));
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`));

输入描述:

输出描述:

last_name
first_name
dept_name
Facello
Georgi
Marketing
省略
省略
省略
Sluis
Mary
NULL
SELECT em.last_name, em.first_name, dp.dept_name
FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no)
LEFT JOIN departments AS dp ON de.dept_no = dp.dept_no
 
本题思路为运用两次LEFT JOIN连接嵌套
1、第一次LEFT JOIN连接employees表与dept_emp表,得到所有员工的last_name和first_name以及对应的dept_no,也包括暂时没有分配部门的员工
2、第二次LEFT JOIN连接上表与departments表,即连接dept_no与dept_name,得到所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
 

 

题目描述

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
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`));

输入描述:

输出描述:

growth
28841
本题严谨的思路如下:
1、先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录
2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅,最后用别名growth代替
SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY from_date DESC LIMIT 1) –
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY from_date ASC LIMIT 1)
) AS growth
 
本题的另一种解法也能通过测试,但实际上不严谨,只有在员工最后一条工资记录为最大值时成立,如果最后一次的工资调整为降薪,则此思路通不过。具体思路如下:直接找到emp_no=10001的员工的工资记录,将其最大工资减去最小工资得到涨幅
 
SELECT (MAX(salary)-MIN(salary)) AS growth 
FROM salaries WHERE emp_no = ‘10001’
 

本站所有文章均由网友分享,仅用于参考学习用,请勿直接转载,如有侵权,请联系网站客服删除相关文章。若由于商用引起版权纠纷,一切责任均由使用者承担
极客文库 » 牛客网SQL练习题总结(一)

Leave a Reply

欢迎加入「极客文库」,成为原创作者从这里开始!

立即加入 了解更多