# 【MySQL】试题 — 31道巩固 SQL 语句的练习题

1.取得每个部门最高薪水的人员名称

[En]

First take out the maximum salary of each department, and then connect it with (the name table of the personnel corresponding to the highest salary) as a temporary table.

select
e.ename,t.*
from
emp e
join
(select deptno,max(sal) as maxsal from emp group by deptno)t
on
t.deptno = e.deptno and t.maxsal = e.sal

1. 哪些人的薪水在部门的平均薪水之上
先拿出每个部门的平均工资，然后连接到(部门平均工资上的部门名称，薪级)作为临时表。
[En]

First take out the average salary of each department, and then connect it to (the name of the department on the average salary of the department, salary scale) as a temporary table.

select
t.*,e.ename,e.sal
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno)t
on
e,deptno = t,deptno and e.sal > t.avgsal;

3.取得部门中所有人的平均薪水等级

select
from
emp e
join
on
e.sal between s.losal and s.hisal;

select
from
emp e
join
on
e.sal between s.losal and s.hisal
group by
e.deptno;

4.不准用组函数（Max），取得最高薪水（给出两种解决方案）

select ename,sal from emp order by sal desc limit 1;

select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal

5.取出平均薪水最高的两个部门编号（至少给出两种解决方案）

select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
having
avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

6.求平均薪水的等级最低的部门的部门名称

[En]

Find out the grade corresponding to the minimum average salary

select
from
where
(select avg(sal) as avgsal,deptno from emp order by avgsal asc limit 1) between losal and hisal;

[En]

Find out the department name, average salary, grade (corresponding to the minimum average salary)

select
from
(select d.dname,avg(sal) as avgsal from emp e join dept on e.deptno = d.deptno group by d.dname) t
join
on
t.avgsal between s.losal and s.hisal;
where
s.grade = (select grade from salgrade where (select avg(sal) as avgsal,deptno from emp order by avgsal asc limit 1)
between losal and hisal);

7.取出比普通员工（员工代码没有在mgr字段出现的）的最高薪水还要高的领导人姓名

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

[En]

Find employees who are higher than (the highest salary of the average employee).

select
ename,sal
from
emp
where
sal > (select max(sal)
from
emp
where
empno not in(select distinct mgr from emp where mgr is not null));

8.取出薪水最高的前五名
select ename,sal from emp order by sal desc limit 5;

9.取出薪水最高的第六到第十的员工
select ename,sal from emp order by sal desc limit 5,5;

10.取出最后入职的5名员工;
select ename,hiredate from emp order by hiredate desc limit 5;

11.取出每个薪水等级有多少个员工
select
from
emp e
join
on
e.sal between s.losal and s.hisal
group by

12.列出所有员工及领导的名字
select
a.ename ‘员工’,b.ename ‘领导’
from
emp a
left join
emp b
on
a.mgr = b.empno;

13.列出受雇日期早于直接上级的所有员工的姓名，受雇日期，直接上级的姓名，受雇日期,部门名称
select
a.empno ‘员工’,a.hiredate,b.ename ‘领导’,b.hire date,d.dname
from
emp a
join
emp b
on
a.mgr = b.empno
join
dept d
on
a.deptno = d.deptno
where
a.hiredate < b.hiredate;

14.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select
e.*,d.dname
from

emp e
right join
dept d
on
e.deptno = d.deptno;

15.列出至少有5个员工的所有部门
select
deptno
from
emp
group by
deptno
having
count(*) >= 5;

16.列出薪水比”SMITH”多的所有员工
select
ename,sal
from
emp
where
sal > (select sal from emp where ename = ‘SMITH’);

17.列出最低薪水大于1500的各种工作及其从事此工作的全部雇员人数
select
job,count(*)
from
emp
group by
job
having
min(sal) > 1500;

18.列出在部门”SALES”
select
ename
from
emp
where
deptno = (select deptno from dept where dname = ‘SALES’);

19.列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级
select
from
emp e
join
dept d
on
e.empno = d.deptno
left join
emp l
on
e.mgr = l.empno
join
on
e.sal between s.losal and s.hisal
where
e.sal > (select avg(sal) from emp);

20.列出与”SCOTT”从事相同工作的所有员工及其部门名称
select
e.ename,e.job,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = (select job from emp where ename = ‘SCOTT’)
and e.name <> ‘SCOTT’;

21.列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
select
ename,sal
from
emp
where
sal in(select distinct sal from emp where deptno = 30)
and
deptno <> 30;

22.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水，部门名称
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d,deptno
where
e.sal > (select max(sal) from emp where deptno = 30) ;

23.列出在每个部门工作的员工数量，平均工资和平均服务期限
select
d.deptno,
count(e.ename) as ecount,
ifnull(avg(e.sal),0) as avgsal,
ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno;

timestampdiff(间隔类型,前一个日期,后一个日期)

FRAC_SECOND 表示间隔是毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年

24.列出所有员工的姓名，部门名称，和薪水
select
e.ename,d.dname,e.sal
from
emp e
join
dept d
on
e.deptno = d.deptno;

25.列出所有部门的详细信息和人数
select
d.deptno,d.dname,d.loc,count(e.ename)
from
emp e
join
dept d
on
e.deptno = d.deptno
group by
d.deptno,d.name,d.loc;

26.列出各种工作的最低工资及从事此工作的雇员姓名
select
e.ename,t.*
from
emp e
join
(select job,min(sal) as minsal from emp group by job)t
on
e.job = t.job and e.sal = t.,minsal;

27.列出各个部门MANAGER（领导）的最低薪水
select
deptno,min(sal)
from
emp
where
job = ‘MANAGER’
group by
deptno;

28.列出所有员工的年工资，按年薪从低到高排序
select
ename,(sal + ifnull(comm,0))*12 as yearsal
from
emp
order by
yearsal asc;

29.求出员工领导薪水超过3000的员工名称和领导
select
a.ename ‘员工’,b.ename ‘领导’
from
emp a
join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;

30.求出部门名称带’S’的部门员工的工资合计，部门人数
select
d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like ‘%S%’
group by
d.deptno,d.name,d.loc;

31.给任职日期超过30年的员工加薪 10%
update
emp
set
sal= sal*1.1 where timestampdiff(YEAR,hiredate,now())>30;

ps：博主少写了3道哟！ ( •̀ ω •́ )✧

Original: https://www.cnblogs.com/Burning-youth/p/15685482.html
Author: 猿头猿脑的王狗蛋
Title: 【MySQL】试题 — 31道巩固 SQL 语句的练习题

(0)

### 大家都在看

• #### centos7 打包chrome离线安装包流程

前提，centos可以连接外网 1、在目录 /etc/yum.repos.d/ 下新建文件 google-chrome.repo 文件中添加以下内容： 2、安装 yum-utils…

数据库 2023年6月11日
0142
• #### 二叉树遍历的常用方法

概述 二叉树的遍历可以说是解决二叉树问题的基础。我们常用的遍历方式无外乎就四种 &#x524D;&#x5E8F;&#x904D;&#x5386;、 …

数据库 2023年6月11日
0133
• #### 入住感言

希望我的博客真的可以帮助到别人，也希望自己能够坚持下去。这些年回想起来，我经常做的事就是感动自己。我并没有自己想象的这么努力，虚有其表罢了！几年后的今天希望自己不会在这么想，唯有时…

数据库 2023年6月6日
0170
• #### MySQL 数据备份与恢复

数据备份 使用 mysqldump 命令可以将数据库中的数据备份成一个文本文件，表的结构和数据以 SQL 的形式将存储生成的文本文件 mysqldump -u username -…

数据库 2023年5月24日
0214
• #### 二进制方式部署K8S（kubernetes)集群（测试、学习环境）-单主双从

1. 二进制方式部署（一主多从） 1.1 环境准备 角色 IP 组件 master 10.27.134.250 kube-apiserver、kube-controller-man…

数据库 2023年6月9日
0155
• #### lvs负载均衡

Lvs 一.Lvs简介 二. 体系结构 三. Lvs管理工具 1. ipvs 2. ipvsadm 四.lvs工作模式及原理 1.NAT模式 2. DR模式 3.TUN模式（隧道模…

数据库 2023年6月14日
0171
• #### MySQL 中 bigint、int、mediumint、smallint、tinyint 有符号和无符号的取值范围

想要了解取值范围首先需要知道的是 bit 和 Byte 的概念 bit ：位 二进制数系统中，位通常简写为 “b”，也称为比特，每个二进制数字 0 或 1 …

数据库 2023年5月24日
0162
• #### MySQL Server可执行注释

MySQL Server当前支持如下3种注释风格： 以’# ‘开头的单行注释 以’– ‘开头的单行注释 C语言风格的单行…

数据库 2023年5月24日
0172
• #### MySQL启动过程详解二：核心模块启动 init_server_components()

mysqld_main() 函数中，init_server_components() 函数负责MySQL核心模块的启动，包括mdl系统，Innodb存储引擎的启动等等： mdl子系…

数据库 2023年6月9日
0152
• #### 深入浅出分析 PriorityQueue

数据库 2023年6月14日
0168
• #### 微服务架构设计模式

内容简介 成功地开发基于微服务架构的应用软件，需要掌握一系列全新的架构思想和实践。在这本书籍中解释了 44 个架构设计模式，这些模式用来解决诸如服务拆分、事务管理、查询和跨服务通信…

数据库 2023年6月6日
0207
• #### innobackupex备份源码解析

目前MySQL的物理备份大多数采用xtrabackupex进行，其备份过程如下图所示，这里通过解析 xtrabackup 的源码来详细看看其是如何进行备份的，xtrabackup …

数据库 2023年6月9日
0170
• #### 关于.netcore即时生成缩略图踩的坑

最近在开发一套系统，很多地方用到了缩略图，然而不同的地方用到的尺寸又不一样，上传的时候生成缩略图就没有意义了，因为你不知道会使用到哪些尺寸，于是想到即时生成的办法，前端判断图片是否…

数据库 2023年6月9日
0151
• #### mybatis缓存

加上flushCache=”true”后,再次运行结果如下 2.二级缓存 mybatis的二级缓存默认开启，但真正使用需要在mapper文件中添加相应的缓存…

数据库 2023年6月16日
0145
• #### 安全生产 系统稳定性建设

前言 安全是产品的底座，是体验的基础，也是企业的一项核心竞争力。安全生产是一项系统性的工作，同时也是一件比较琐碎的事，需要做方方面面的考虑尽一切可能保障系统安全稳定运行。个人之前一…

数据库 2023年6月14日
0139
• #### Nginx 反向代理、Rewrite

Rewrite功能配置 Rewrite是Nginx服务器提供的一个重要基本功能，是Web服务器产品中几乎必备的功能。主要的作用是用来实现URL的重写。www.jd.com注意:Ng…

数据库 2023年6月6日
0181