牛客网SQL刷题——SQL1-SQL20

于 2020-04-10 23:16:38 发布
阅读量1.1k 收藏 7
点赞数 1
分类专栏： SQL 文章标签： 牛客网 SQL 刷题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36384657/article/details/105444512
版权

记录一下刷题时候的解题思路和方法

目录

1.查找最晚入职员工的所有信息

2.查找入职员工时间排名倒数第三的员工所有信息

3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)

7.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

9.获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

10.获取所有非manager的员工emp_no

11.获取所有员工当前的(dept_manager.to_date='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

12.获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

 14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

 15.查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

16.统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。

 17.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

18.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

 19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

1.查找最晚入职员工的所有信息

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`));

 思路：1.找到入职hire_date最晚的日期2.找到最晚日期对应的所有职工

select * from employees
where hire_date=(select max(hire_date) from employees)

2.查找入职员工时间排名倒数第三的员工所有信息

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`));

思路：1.对入职日期按降序排列

2.查询结果限制从第2个元素开始输出（编号从0开始），输出一个元素

select *from employees 
order by hire_date desc limit 2,1

3.查找各个部门当前(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`));

select salaries.*, dept_manager.dept_no 
from salaries ,  dept_manager 
where salaries.to_date='9999-01-01'
and dept_manager.to_date='9999-01-01'
and salaries.emp_no=dept_manager.emp_no;

 思路：查询的内容在两张表里面，所以要用到多表连接，这里使用内连接即可，因为两表均有emp_no字段，所以where连接判断时取两表相等时判断。此外还要考虑题目要求

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

select employees.last_name,employees.first_name,dept_emp.dept_no
from  employees,dept_emp
where employees.emp_no=dept_emp.emp_no

5.查找所有员工的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`));

select e.last_name,e.first_name,d.dept_no
from employees as e left join dept_emp  as d
on d.emp_no=e.emp_no

 考察“左外连接”，在查询时，左边的表全部保留，如果左边表没有右边表中的元素，则为空

6.查找所有员工入职时候的薪水情况，给出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`));

select employees.emp_no,salaries.salary 
from employees,salaries
where employees.emp_no=salaries.emp_no and employees.hire_date=salaries.from_date
order by employees.emp_no desc

这个题目的陷阱就是，你需要设置条件，查询所有人的入职时候的薪水，也即将薪水表的from_date日期设置为员工表的hire_date入职日期

7.查找薪水变动超过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字段分成若干组，每组对应了相同的emp_no，每组的长度就是不同的salary长度（这里不考虑salary字段是否有相等值，只考虑记录数）。分组的化就用到了group by关键字， 可参考中https://www.runoob.com/sql/sql-groupby.html对group by的详细描述。分组后用having 关键字筛选数据。

这里as关键字的作用是：当我们想认为指定一个不存在的列或修改原有的列名，可以通过as关键字设置。

select emp_no,count(emp_no) as t
from salaries
group by emp_no
having t>15

8.找出所有员工当前(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 salary
from salaries
where to_date='9999-01-01'
order by salary desc

 考点：distinct关键字，在表中，一个列可能会包含多个重复值，仅仅列出不同的值时，用distinct区分。

9.获取所有部门当前(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`));

select dept_manager.dept_no,dept_manager.emp_no,salaries.salary
from dept_manager,salaries
where dept_manager.emp_no=salaries.emp_no 
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'

 这题简单，考察and多条件连接

10.获取所有非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`));

select  emp_no
from employees
where emp_no not in (select emp_no from dept_manager)

考察in关键字，in关键字用在where里作为筛选条件之一。

A in B 表示 前者在后者中，A not in B表示A不在B中

（select emp_no from dept_manager)表示的先从dept_manager表中将经理的员工号选出来。

用not in 排除到经理

11.获取所有员工当前的(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`));

select dept_emp.emp_no,dept_manager.emp_no as manager_no
from dept_emp,dept_manager
where dept_emp.dept_no=dept_manager.dept_no
and dept_emp.emp_no not in (select emp_no from dept_manager)
and dept_manager.to_date='9999-01-01'

这是在上题的基础上考虑了多条件查询，增加了一个where判断

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`));

select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary)
from dept_emp inner join salaries on dept_emp.emp_no=salaries.emp_no
and dept_emp.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by dept_no

或者

select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary)
from dept_emp, salaries 
where dept_emp.emp_no=salaries.emp_no
and dept_emp.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by dept_no

 对部门按照部门编号dept_no进行分组，每组中选出工资最大的员工信息。

考察点group by和聚合函数Max的适用

两表可以适用表A inner join 表B实现，或者用where和=连接实现

13.从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);

输出描述：

select title, count(emp_no) as t
from titles
group by title
having t>=2

再次考察group by 函数和having相结合，对数据进行分组。

在SQL中增加having子句的原因是，where关键字无法与聚合函数一起使用，一起判断。having子句可以让我们筛选分组后的各组数据。

 14.从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);

输出描述：

select title, count(distinct emp_no) as t
from titles
group by title
having t>=2

 与上题区别在于 group by +having +distinct去重函数组合使用

 15.查找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`));

select *
from employees
where emp_no%2=1 and last_name is not 'Mary'
order by hire_date desc

 emp_no % 2=1也可以改成MOD(emp_no, 2)=1，但是某些sql版本可能不支持后者(比如题库就不支持)
补充：不相等有三种表示方式：<>、!=、IS NOT
注意：last_name是varchar类型，所以对它的判断需要加上单引号

16.统计出当前(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);

select title,avg(salary) as avg
from salaries,titles
where salaries.emp_no=titles.emp_no
and titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by title

考察了一个avg函数求平均值，各个title类型对应的员工 group by title

 17.获取当前（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 emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary desc limit 1,1

子查询+order by

desc 按照工资下降的顺序排序

limit 1,1表示从序列为1的元素开始输出第一个元素。这里sql是从0开始作为起始下标

18.查找当前薪水(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`));

select s.emp_no,s.salary,e.last_name,e.first_name
from employees e inner join salaries s on e.emp_no=s.emp_no
where s.salary=
(
    select max(salary) from salaries where salary <  
    (
        select max(salary) from salaries where to_date='9999-01-01'
    )and to_date='9999-01-01'
)

 尝试用inner join on  而不是where = 进行两表连接。

这题思路是两次最大salary选择，最里面的第一次max，目的是选择进行过滤，将最大的那个排除。

第二次最大是从过滤后的salaries表中选择最大的。即完成了从原表中选择第二大。

有了这个思路，我们可以将外循环的select max写到最开始的地方，参加下面代码

select s.emp_no, max(salary),e.last_name,e.first_name
from employees e inner join salaries s on e.emp_no=s.emp_no
where salary <  
    (
        select max(salary) from salaries where to_date='9999-01-01'
    )
and to_date='9999-01-01'

 19.查找所有员工的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`));

select e.last_name,e.first_name,d.dept_name
from (employees e left join dept_emp de on de.emp_no=e.emp_no)
left join departments d on d.dept_no=de.dept_no

考察：

1.多表连接：题目给了三个表，查询的内容在departments表和employees表中，但是两表不能直接连接，要借助dept_emp表建立起关系。

2.左外连接：题目要求dept信息可以空着，但是必须有员工信息，因此，建立连接时employees表必须最先考虑。

而dept_emp由于起承接性自然还是放在中间，

20.查找员工编号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`));

思路：考察SQL函数部分的max函数和min函数

查询该员工最高工资和最低工资，两者的差即growth

复杂一点的可用排序得到最大最小。

select max(salary)-min(salary) as growth
from salaries
where emp_no='10001'

 

三月的一天
关注
  • 1
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

三月的一天

你的鼓励将是我前进的动力。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值