(MySQL)SQL语句练习题【老杜34题】

最新推荐文章于 2024-06-02 14:54:06 发布
最新推荐文章于 2024-06-02 14:54:06 发布
阅读量315 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44786702/article/details/134007637
版权

文章目录

一、建表语句

-- 1、部门表
CREATE TABLE DEPT ( 
	DEPTNO INT ( 2 ) NOT NULL, 
	DNAME VARCHAR ( 14 ), 
	LOC VARCHAR ( 13 ), 
	PRIMARY KEY ( DEPTNO ) );
	
-- 2、员工表
CREATE TABLE EMP (
	EMPNO INT ( 4 ) NOT NULL,
	ENAME VARCHAR ( 10 ),
	JOB VARCHAR ( 9 ),
	MGR INT ( 4 ),
	HIREDATE DATE DEFAULT NULL,
	SAL DOUBLE ( 7, 2 ),
	COMM DOUBLE ( 7, 2 ),
	PRIMARY KEY ( EMPNO ),
	DEPTNO INT ( 2 ) 
	
-- 3、薪水等级表
CREATE TABLE SALGRADE ( 
	GRADE INT, 
	LOSAL INT, 
	HISAL INT );
);

二、插入测试数据

-- 1、插入部门表数据
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); 
commit;
 
 -- 2、插入员工表数据
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
 -- 3、插入薪水等级表数据
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); 
commit;

三、练习题

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

SELECT
	ename 
FROM
	emp e
	JOIN ( SELECT deptno, max( sal ) sal FROM emp GROUP BY deptno ) a ON e.deptno = a.deptno 
	AND e.sal = a.sal

2、哪些人的薪水在部门的平均薪水之上(△)

SELECT
	e.ename,
	e.sal,
	a.* 
FROM
	emp e
	JOIN ( SELECT deptno, avg( sal ) avgSal FROM emp GROUP BY deptno ) a ON e.deptno = a.deptno 
	AND e.sal > a.avgSal

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

-- 平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。
-- 平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。
-- 第一步：获取每个人的薪水等级
-- 第二步：基于以上的结果继续按照deptno分组，求grade的平均值
SELECT
	e.deptno,
	avg( s.grade ) 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal 
	AND s.hisal 
GROUP BY
	e.deptno

4、不准用组函数(max)，取得最高薪水(至少两种解决方案)

-- 第一种方案：sal降序,limit 
select sal from emp order by sal desc limit 1
-- 第二种方案：
select max(sal) from emp;
-- 第三种方案：
select sal from emp where sal not in(
select distinct a.sal from emp a 
join emp b on a.sal <b.sal )

5、取得平均薪水最高的部门的部门编号(至少两种解决方案)

第一种方案：降序取第一个
SELECT
	a.deptno 
FROM
	( SELECT deptno, avg( sal ) sal FROM emp GROUP BY deptno ) a 
ORDER BY
	sal DESC 
	LIMIT 1
第二种方案：max
SELECT
	a.deptno,
	max( a.sal ) maxSal 
FROM
	( SELECT deptno, avg( sal ) sal FROM emp GROUP BY deptno ) a

6、取得平均薪水最高的部门的部门名称

SELECT
	d.dname,
	avg( e.sal ) avgSal 
FROM
	emp e
	LEFT JOIN dept d ON e.deptno = d.deptno 
GROUP BY
	e.deptno 
ORDER BY
	avgSal DESC 
	LIMIT 1

7、求平均薪水的等级最低的部门的部门名称(△)

select a.*,s.grade from 
(select d.dname,avg(sal) avgSal from emp e
left join dept d on e.deptno = d.deptno
group by d.dname) a  join salgrade s on a.avgSal between s.losal and s.hisal
where grade =
(select grade from salgrade 
where (select avg(sal) avgSal from emp group by deptno  order by avgSal asc limit 1) between losal and hisal)

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名(△)

-- 比普通员工的最高薪水还要高的一定是领导！√
-- not in在使用的时候,后面小括号中记的排除null
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 ) 
	)

9、取得薪水最高的前五名员工

select ename,sal from emp
order by sal desc
limit 5

10、取得薪水最高的第六到第十名员工

select ename,sal from emp
order by sal desc
limit 5,5

11、取得最后入职的 5 名员工日期

日期也可以降序，升序

select hiredate from emp 
order by hiredate desc
limit 5

12、取得每个薪水等级有多少员工

select a.grade,count(a.ename) num from 
(select e.ename,e.sal,s.grade from emp e
left join salgrade s on e.sal between s.losal and s.hisal)a 
group by a.grade

13、面试题略过

14、列出所有员工及领导的姓名

select e.ename,m.ename leader from emp e 
left join emp m on e.mgr = m.empno

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

select e.ename '员工',e.HIREDATE,m.ename '领导',m.hiredate,d.dname from emp e
left join emp m on e.mgr = m.empno
left join dept d on e.deptno = d.deptno 
where e.hiredate < m.HIREDATE

16、 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select d.dname,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm from dept d
left join emp e on d.deptno = e.deptno

17、列出至少有 5 个员工的所有部门

select deptno,count(empno) num from emp
group by deptno 
having num>=5
select deptno num from emp
group by deptno 
having count(*)>=5

18、列出薪金比"SMITH" 多的所有员工信息

select e.ename,e.job,e.mgr,e.hiredate,sal,deptno from emp  e 
where sal> (select sal from emp where ename = 'SMITH')

19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

select a.ename,a.dname,b.num from 
(select e.ename,e.deptno,d.dname from emp e
left join dept d on e.deptno = d.deptno
where e.job = 'CLERK') a left join 
(select deptno,count(ename) num from emp
group by deptno) b on a.deptno = b.deptno

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数，按照工作岗位分组求最小值(△)

select job,count(*) from emp group by job having min(sal) > 1500;

21、列出在部门"SALES"< 销售部> 工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp e
left join dept d on e.deptno = d.deptno
where d.dname ='SALES'

select ename from emp 
where deptno =(select deptno from dept where dname ='SALES')

22、列出薪金高于公司平均薪金的所有员工, 所在部门，上级领导，雇员的工资等级

select e.ename '员工',d.dname, m.ename '领导',s.grade from emp e
left join dept d on e.deptno = d.deptno 
left join emp m on e.mgr = m.empno
left join salgrade s on e.sal BETWEEN s.LOSAL and s.hisal
where e.sal> (select avg(sal) avgSal from emp )

23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称

select e.ename,d.dname
from emp e
left join dept d on e.deptno = d.deptno
where e.job in (select job from emp
where ename = 'SCOTT') and e.ename <> 'SCOTT'

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金

select ename,sal,deptno from emp 
where deptno != 30 
and sal in 
(select distinct sal from emp 
where deptno =30)

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称

select e.ename,e.sal,d.dname from emp e
left join dept d on  e.deptno = d.deptno
where  e.sal >
(select max(sal) from emp where deptno =30)

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限(△)

-- 在mysql当中怎么计算两个日期的"年差",差了多少年:TimeStampDiff(间隔类型,前一个日期,后一个日期)
-- TimeStampDiff(YEAR,hiredate,now())
-- 间隔类型：SECOND 秒,MINUTE 分钟,HOUR 小时,DAY 天,WEEK 星期,MONTH 月,QUARTER 季度,YEAR 年
select d.*,count(e.ename) number,ifnull(avg(sal),0) avgSal,ifnull(avg(TimeStampDiff(YEAR,hiredate,now())),0) avgServiceTime from dept d
left join emp e on d.deptno = e.deptno 
group by d.deptno,d.dname,d.loc

27、列出所有员工的姓名、部门名称和工资

select e.ename,d.dname,e.sal from emp e 
left join dept d on e.deptno = d.deptno

28、列出所有部门的详细信息和人数

select d.deptno,d.dname,count(ename) num from dept d
left join emp e on d.deptno = e.deptno
GROUP BY d.deptno

29、列出各种工作的最低工资及从事此工作的雇员姓名

select e.ename,a.* from emp e
 join (select job,min(sal) lowSal
from emp 
group by job) a on e.job = a.job and e.sal = a.lowSal

30、列出各个部门的MANAGER(领导)的最低薪金

select deptno,min(sal) as minSal from emp
where job ='MANAGER'
GROUP BY deptno

31、列出所有员工的年工资, 按年薪从低到高排序

SELECT
	ename,(
	sal + ifnull( comm, 0 ))* 12 AS yearSal 
FROM
	emp 
ORDER BY
	yearSal

32、求出员工领导的薪水超过3000的员工名称与领导

SELECT
	e.ename,
	m.ename leader_name 
FROM
	emp e
	JOIN emp m ON e.mgr = m.empno 
WHERE
	m.sal > 3000

33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数

SELECT
	d.deptno,
	d.dname,
	ifnull( sum( e.sal ), 0 ) sumSal,
	count( e.ename ) num 
FROM
	emp e
	RIGHT JOIN dept d ON e.deptno = d.deptno 
WHERE
	d.dname LIKE '%S%' 
GROUP BY
	d.deptno,
	d.dname

34、给任职日期超过 30 年的员工加薪 10%

update emp set sal = sal * 1.1 where timeStampDiff(YEAR,hiredate,now()) >30

四、视频链接

https://www.bilibili.com/video/BV1Vy4y1z7EX

翻山越玲
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql语句练习题（带答案版本）
09-14
这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习
MySQL入门学习（超详细+34练习题）
AiPyThOn001的博客
08-23 6069
MySQL入门学习（超详细+34练习题） 学习B站老杜MySQL后总结笔记。收藏、点赞、转发。 1.简介 什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？ 数据库： 英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。 顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。 数据库管理系统： DataBaseManagement，简称DBMS。 数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据
MySQL-老杜学习笔记
weixin_45581692的博客
03-02 2155
MySQL1. 数据库概述1.1 数据库、数据库管理系统、SQL之间的关系1.2 表的理解1.3 SQL语句的分类1.4 导入数据1.5 查看表结构1.6 MySQL常用命令2. 查询语句DQL2.1 简单查询2.2 条件查询2.3 排序2.4 分组查询2.5 总结（单表查询）2.6 查询结果去重3. 函数3.1 单行处理函数3.2 分组函数4. 连接查询笛卡尔积现象内连接外连接三张表，四张表连接子查询union合并查询结果集limit5. DDL语句5.1 建表5.2 删表5.3 快速创建表（表的复制）5
MySQL 练习题
zhu_fangyuan的博客
04-20 882
MySQL 练习题 题目来源“老杜入门MySQL学习：https://www.bilibili.com/video/BV1Vy4y1z7EX?p=132 目录建立三张表1. 取得每个部门最高薪水的人员名称2. 哪些人的薪水在部门的平均薪水之上3. 取得部门中（所有人的）平均的薪水等级（工资由高到低1~5等级：grade字段）4. 不使用组函数（Max）,取得最高薪水（给出两种解决方案）5. 取得平局薪水最高的部门的部门编号6. 取得平均薪水最高的部门的部门名称7. 求平均薪水的等级最低的部门的部门名称8.
MYSQL(老杜数据库笔记)
qq_51923226的博客
05-15 3796
1.卸载MYSQL mysql-5.5.36-win32 (1).msi进去remove 找到C盘下的program files(x86)目录进去把MYSQL目录删除然后把隐藏目录programData下MYSQL删除 2.数据库登录 命令提示符要以管理员身份运行:c:Windows/System32/cmd cd C:\Program Files\MySQL\MySQL Server 5.7\bin bin>.\mysql.exe -hlocalhost -uroot -p20140818
MySQL34道例题
Andy86666的博客
09-16 366
MySQL34道例题使用：1、创建一个text的文本文件，将下面的SQL语句复制到文本文件中2、将后缀改为.sql3、在数据库中创建一个新的数据库（create database 数据库名），再使用数据库（use 数据库名）4、source XX.sql 导入脚本文本文件 1、取得每个部门最高薪水的人员名称 第一步：找到每一个部门的最大值，因此需要根据部门编号进行分组，再筛选出最大值第二步：将上面这个做为一张临时表 2、哪些人的薪水在部门平均薪水之上 思路：第一步：找出每一个部门的平均工资 第二步：将表进行
MySql学习记录（B站老杜）
weixin_45941080的博客
06-17 376
在小破站看老杜的mysql学习数据库过程中做的笔记，包括后面练习题的详解，包含所有需要用到的文件；
2022.2.1-2022.2.3mysql学习笔记
YYYYYY12133463的博客
02-03 827
过年没回老家，在这没事做就开始学习，还是学习效率低，但是我尽力了，今天终于把这个mysql的视频看完，总结一下。 参考： 老杜 - mysql入门基础 + 数据库实战：https://www.bilibili.com/video/BV1Vy4y1z7EX（较新、内容相对精炼，有习题） 视频感受：总体看完，感觉很新手，合适所有没学过数据库的人员学习，而且讲的比较详细，还有习题练习，缺乏的是对索引原理的详细解析，但是对于一个开发人员来说，完全足够 什么是DB?DBMS?SQL?他们的关系 DB：数据库 .
MySQL学习笔记
m0_60520853的博客
12-10 516
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，全部自己手打的，杜老师讲的十分通俗易懂，respect！感兴趣的小伙伴可以去看看哦！
MySQL笔记，老杜，初级MySQL笔记
06-23
MySQL笔记，老杜，初级MySQL笔记
MySQL数据库视频教程老杜最新MySQL教程
06-16
本课程全面讲授了MySQL数据库的相关技术知识，内容全面翔实，是技术开发人员、数据库人员不可多得的视频资料；
MySQL 练习目（含建表语句，解析）
12-24
MySQL 练习目（含建表语句，解析） 题目出自于老杜MySQL，该资源中有题解，若有疑问请留言
34道作业题.txt
12-28
动力节点 sql练习题
动力节点老杜MySQL数据库教程课堂笔记分享
07-24
本套是MySQL数据库视频教程是动力节点教学总监杜老师讲述，其中详细讲解了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性，通过观看本...
视频教程-MySQL数据库视频教程老杜最新MySQL教程-MySQL
weixin_33585818的博客
05-28 363
MySQL数据库视频教程老杜最新MySQL教程 动力节点王勇老师，CCTV《...
mysql支持的存储引擎有哪些
JustinMars的博客
05-28 175
选择存储引擎时应根据具体应用场景和需求来决定。例如： - 如果需要事务和高数据完整性，选择InnoDB。 - 如果读操作频繁且不需要事务，选择MyISAM。 - 如果需要快速的临时数据存储，选择MEMORY。 - 如果需要数据压缩和存档，选择ARCHIVE。
chat4-Server端保存聊天消息到mysql
最新发布
gulanga5的博客
06-02 468
本文档描述了Server端接收到Client的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql服务端为当前客户端创建一个线程，此线程接收当前客户端的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql本文档主要总结了将聊天消息保存到mysql!!!),;import;import;import;/**用druid连接池来连接数据库的工具类*/DBUtil。
【MySQL】索引
cefler的博客
05-31 777
【MySQL】索引
老杜mysql34题
07-29
抱歉，我无法回答关于"老杜mysql34题"的问题。请提供更具体的问题或者相关的引用内容，我将尽力帮助您解答。 #### 引用[.reference_title] - *1* *2* [老杜 | Mysql的34道作业题](https://blog.csdn.net/qq_43167873/article/details/121134891)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] - *3* [老杜 mySql自学笔记34道例题](https://blog.csdn.net/m0_67946241/article/details/126912237)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值