数据库–牛客数据库SQL实战(前20道题中等)汇总

数据库–牛客数据库SQL实战(前20道题中等)汇总

具体目录:

牛客上的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)    
)