数据库--牛客数据库SQL实战(前20道题中等)汇总
数据库--牛客数据库SQL实战(前20道题中等)汇总
具体目录:
- 数据库--牛客数据库SQL实战(前20道题中等)汇总
- 具体目录:
- 01. 查找最晚入职员工的所有信息
- 02. 查找入职员工时间排名倒数第三的员工所有信息
- 03. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
- 04. 查找所有已经分配部门的员工的last_name和first_name以及dept_no
- 05. 查找所有员工的last_name和first_name以及对应部门编号dept_n
- 06. 查找所有员工入职时候的薪水情况
- 07. 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
- 08.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况
- 09.获取所有部门当前manager的当前薪水情况
- 10.获取所有非manager的员工emp_no
- 11.获取所有员工当前的manager，如果员工是manager的话不显示
- 12.获取所有部门中当前员工当前薪水最高的相关信息
- 13.从titles表获取按照title进行分组(1)
- 14.从titles表获取按照title进行分组(2)
- 15 查找employees表所有emp_no为奇数
- 16. 统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')薪水对应的平均工资
- 17. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
- 18.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号
- 19.查找所有员工的last_name和first_name以及对应的dept_name
- 20查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)
牛客上的SQL实战题解,一共有60道题,我准备一个星期内解决完成,通过实践来巩固基础.会在每道题里写出自己的理解.与解题思路.会在每道题中写出对应与数据库关联的知识.
01. 查找最晚入职员工的所有信息
题目描述
查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
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`));
解题步骤
题目大概:查询入职最晚员工的所有信息
目的:该员工的所有信息
筛选条件:最晚入职(hire_date)
具体代码
select *
from employees
where hire_date=(
select MAX(hire_date)
from employees
);
02. 查找入职员工时间排名倒数第三的员工所有信息
题目描述
查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
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`));
解题步骤
题目大概:查询入职时间
目的:该员工的所有信息排名第三的员工所有信息
筛选条件:最晚入职(hire_date)排名第三
具体代码
select *
from employees
order by hire_date DESC -- 入门时间最小,就是时间最大的应该使用降序排列,descend
limit 2,1;
03. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
题目描述
查找各个部门当前(dept_manager.to_date='9999-01-01')领导当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询，输出结果以salaries.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`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
解题步骤
查询详情:两张表里dept_manager和salaries表里面,
入职时间为to_date='9999-01-01'的领导薪水详情,部门编号,
输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列
思路:两张表,需要联结, salaries inner jion dept_manager on
左联到部门表，薪水表人全，所以做主表比较好，不会出现关联出空的情况
具体代码
select s.*,
d.dept_no
from salaries as s left join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date=d.to_date
and s.to_date='9999-01-01'
04. 查找所有已经分配部门的员工的last_name和first_name以及dept_no
查找所有已经分配部门的员工的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
目的:找出员工中以及分配好部门的信息
筛选条件:员工表和部门表,以及有部门的
具体代码
select e.last_name,e.first_name,d.dept_no
from employees as e inner join dept_emp as d
on d.emp_no=e.emp_no
05. 查找所有员工的last_name和first_name以及对应部门编号dept_n
题目描述
查找所有员工的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,包括为分配工作的
目的:找出员工中以及分配好部门的信息,也包括暂时没有分配具体部门的员工
筛选条件:无,只是在两个表里用了左联结,不用再使用筛选条件
具体代码
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
-- 还要加上一个防止关联为空
06. 查找所有员工入职时候的薪水情况
查找所有员工入职时候的薪水情况，给出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`));
解题步骤
题目大概:查找所有已经分配部门的员工的last_name和first_name,包括为分配工作的
目的:找出员工中以及分配好部门的信息,也包括暂时没有分配具体部门的员工
筛选条件:无,只是在两个表里用了左联结,不用再使用筛选条件
具体代码
-- 题目中所给的所有员工入职时候的薪水情况,要特别注意
select e.emp_no,s.salary
from employees as e left join salaries as s
on e.emp_no=s.emp_no
where s.from_date=e.hire_date
order by e.emp_no DESC
07. 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
题目描述
查找薪水变动超过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`));
解题步骤
题目大概:查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
目的:使用聚合函数在一张表里找出符合条件的分组
筛选条件:变动次数大于15
具体代码
/*
是不是看看一个emp_no有多少个工资salary
应该是聚合函数
分组不能用where的原因,where只能过滤的是行而不是分组
*/
select emp_no,count(salary) as t
from salaries
group by emp_no
having t>15
08.找出所有员工当前(to_date='9999-01-01')具体的薪水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`));
解题步骤:
主要是对薪水去除,逆序
具体代码:
/*简直和答案一模一样*/
select distinct s.salary
from salaries as s
where
s.to_date='9999-01-01'
order by s.salary DESC
09.获取所有部门当前manager的当前薪水情况
题目描述
获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)
//部门表
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_manager部门表,和salaries工资表
*/
select d.dept_no,d.emp_no,s.salary
from dept_manager as d left join salaries as s
on d.emp_no=s.emp_no
where d.to_date=s.to_date
and d.to_date='9999-01-01'
10.获取所有非manager的员工emp_no
题目描述
获取所有非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`));
解题步骤:
/*
给了两张表
employees信息比较齐全
dept_manager职位表,不是每个员工都有职位表
*/
具体代码:
select e.emp_no
from employees as e left join dept_manager as d
on e.emp_no=d.emp_no
where d.emp_no is null
11.获取所有员工当前的manager，如果员工是manager的话不显示
题目描述
获取所有员工当前的(dept_manager.to_date='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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`));
解题步骤:
两个表,一个dept_emp,一个dept_manager
根据部门编号dept_no,对两个表进行左联结
找出来该员工所在部门的经理,员工编号里面应该包含领导编号
所以该员工的编号不能是自己部门的领导
具体代码:
SELECT
de.emp_no,
dm.emp_no AS manager_no
FROM
dept_emp de
LEFT JOIN dept_manager dm ON de.dept_no = dm.dept_no
WHERE
de.emp_no != dm.emp_no
AND de.TO_DATE = '9999-01-01'
AND dm.TO_DATE = '9999-01-01'
12.获取所有部门中当前员工当前薪水最高的相关信息
题目描述
获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出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
*/
实现:本题中使用一个分组,按照部门进行分组(dept_no),工资最高的,所以使用了MAX函数.MAX(s.salary)
具体代码:
select d.dept_no,d.emp_no,MAX(s.salary)
from dept_emp as d left join salaries as s 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
13.从titles表获取按照title进行分组(1)
题目描述
从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表进行分组查询
还要给出title对应数目
*/
具体代码:
select T.title,count(*) as t
from titles as T
group by T.title
having t>=2
14.从titles表获取按照title进行分组(2)
题目描述
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，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);
解题步骤:
/*
题意:单表分组查询
要求:对于重复的emp_no进行忽略,即emp_no重复的title不计算，title对应的数目t不增加
思路:加上一个where过滤,先过滤掉emp_no重复的title
where过滤没有成功,到是使用了count(distinct xx)
*/
具体代码:
select T.title,count(distinct T.emp_no) as t
from titles as T
group by T.title
having t>=2
15 查找employees表所有emp_no为奇数
题目描述:
查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)
查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)
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`));
解题步骤:
/*
不能使用mod函数,求模
为奇数
last_name不为Mary
逆序输出hire_date
老是遗忘的一个点是decsend 降序,逆序,从大到小Z-A
AESC,aescend升序,从小到大A-Z
*/
具体代码:
select *
from employees as e
where e.last_name!='Mary'
and e.emp_no%2=1
order by e.hire_date desc
16. 统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')薪水对应的平均工资
题目描述:
统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')薪水对应的平均工资。结果给出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);
解题步骤:
使用左联结,联结两张表,给出titles.title的分组,先进行求工资平均数,再进行分组
具体代码:
select t.title,AVG(s.salary) as avg
from titles as t left join salaries as s
on t.emp_no=s.emp_no
where t.to_date=s.to_date
and t.to_date='9999-01-01'
group by t.title
17. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
题目描述:
获取当前（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`));
解题步骤:
倒序,分页
具体代码:
select s.emp_no,s.salary
from salaries as s
where s.to_date='9999-01-01'
order by s.salary DESC
limit 1,1
18.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号
题目描述:
查找当前薪水(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`));
解题步骤:
/*
两张表employees,和salaries,
不使用order by,找出排名第二的emp_no
能不能找出它的最大值,并找出来第一个小于最大值的值
傻了,不知道怎么用inner join了
*/
具体代码:
SELECT e.emp_no, MAX(s.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'
)
/*s.salary<(
select max(salary) from salaries
)
and
s.to_date='9999-01-01'
*/
19.查找所有员工的last_name和first_name以及对应的dept_name
题目描述:
查找所有员工的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`));
解题步骤:
/*
三张表departments,dept_emp,employees
也包括暂时没有分配部门的员工
这里要包括没有分配部门的员工，因此将员工表放在左侧，使用left join
*/
具体代码:
select e.last_name,e.first_name,ds.dept_name
from employees as e
left join dept_emp as dp on e.emp_no=dp.emp_no
left join departments as ds on dp.dept_no=ds.dept_no
20查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)
题目描述:
查找员工编号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`));
解题步骤:
/*
题型难度逐渐增加
思路:从入职以来,时间上来说
最大值减去最小值那
*/
具体代码:
select (
(select max(s.salary)from salaries as s where s.emp_no=10001)
-
(select min(s.salary)from salaries as s where s.emp_no=10001)
)
