• 极客文库-知识库上线!
  • 极客文库小编@勤劳的小蚂蚁,为您推荐每日资讯,欢迎关注!
  • 每日更新优质编程文章!
  • 更多功能模块开发中。。。

牛客网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’
 


丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:牛客网 SQL 练习题总结(一)
喜欢 (0)
[247507792@qq.com]
分享 (0)

欢迎 注册账号 登录 发表评论!

  • 精品技术教程
  • 编程资源分享
  • 问答交流社区
  • 极客文库知识库

客服QQ

247507792

工作时间:09:00-23:00

新浪微博:点我访问

个人博客:点我访问