当前位置： 首页 > article >正文

MySQL-DQL练习题

article 2024/10/27 15:03:42

文章目录

    • 简介
    • 初始化表
    • 练习题

简介

本节简介:
主要是一些给出一些习题, 关于DQL查询相关的, DQL查询语句是最重要的SQL语句, 功能性最复杂, 功能也最强, 所以本节建议适合以及有了DQL查询基础的食用, 另外注意我们使用的是Navicat, SQL编辑的格式规范也是Navicat指定的默认格式, 不是dos命令行窗口…

初始化表

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT(DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
);
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) 
);

CREATE TABLE SALGRADE( GRADE INT,
    LOSAL INT,
    HISAL INT
);

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

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

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;

上面就是本节所需的SQL脚本文件, 在联系下面的习题之前需要执行一下上面的SQL
简单解释一下这三张表

-- emp员工表, 存储的员工的一些信息数据, 分别是
-- 员工编号, 员工姓名, 工作, 上级领导编号, 入职日期, 薪水, 津贴, 部门编号
 desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

-- dept部门表, 存储的是部门的一些信息
-- 部门编号, 部门名称, 地址
 desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

-- salgrade薪资水平表, 存储的每个级别的薪资对应的薪资范围
-- 等级, 最低薪资(该等级), 最高薪资(该等级)
desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int  | YES  |     | NULL    |       |
| LOSAL | int  | YES  |     | NULL    |       |
| HISAL | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

练习题

第一题 : 取得每个部门最高薪水的人员名称

-- 取得每个部门最高薪水的人员名称
-- step1 : 按照部门编号分组求出来每个部门的最高薪水
SELECT
	deptno,
	max( sal ) AS max_sal 
FROM
	emp 
GROUP BY
	deptno;-- step2 : 因为要查询人员的信息, 所以把上面step1中查询的结果当作临时表t链接emp查询
SELECT
	e.ename,
	e.sal,
	e.deptno,
	t.max_sal 
FROM
	( SELECT deptno, max( sal ) AS max_sal FROM emp GROUP BY deptno ) t
	JOIN emp e ON t.max_sal = e.sal 
	AND t.deptno = e.deptno;

执行结果
在这里插入图片描述
第二题 : 那些人的薪水在部门的平均薪水之上

-- 第二题 : 那些人的薪水在部门的平均薪水之上
-- step1 : 按照部门的编号进行分组然后求出来部门的平均薪水
SELECT
	deptno,
	avg( sal ) AS avg_sal 
FROM
	emp 
GROUP BY
	deptno;
-- step2 : 把上面的查询结果当作临时表, 与emp员工表进行链接
SELECT
	e.ename,
	e.sal,
	e.deptno,
	t.avg_sal 
FROM
	( SELECT deptno, avg( sal ) AS avg_sal FROM emp GROUP BY deptno ) t
	JOIN emp e ON e.sal > t.avg_sal 
	AND e.deptno = t.deptno;

执行结果
在这里插入图片描述

第三题 : 取得每个部门平均薪水的等级

-- 第三题 : 取得每个部门平均薪水的等级
-- step1 : 首先按照部门分组然后找到每一个部门的平均薪资
SELECT
	deptno,
	avg( sal ) AS avg_sal 
FROM
	emp 
GROUP BY
	deptno;
-- step2 : 把上面的表看作是一个临时表t然后与薪资等级表salgrade进行链接
SELECT
	t.deptno,
	t.avg_sal,
	s.grade 
FROM
	( SELECT deptno, avg( sal ) AS avg_sal FROM emp GROUP BY deptno ) t
	JOIN salgrade s ON t.avg_sal BETWEEN s.losal 
	AND s.hisal;

执行结果
在这里插入图片描述
第四题 : 取得部门中（所有人的）平均的薪水等级
在这个题之前我们澄清一下小小的误区就是, 在表进行链接之后, 其实就相当于一张表了, 所以可以对链接的表的字段进行分组, 并且分组函数也可以操作链接的表的字段

-- 第四题 : 取得部门中（所有人的）平均的薪水等级
-- step1 : 通过表的链接直接找到所有人的薪水等级
SELECT
	s.grade,
	e.deptno 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal 
	AND s.hisal;
-- step2 : 在上面的表的基础上直接进行deptno分组然后分组函数直接作用于grade字段
SELECT
	avg( s.grade ),
	e.deptno 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal 
	AND s.hisal 
GROUP BY
	e.deptno;

执行结果
在这里插入图片描述
第五题 : 不准用组函数（Max），取得最高薪水（给出两种解决方案）
方案一 : limit方案

-- 方法1 : Limit方案
SELECT
	sal 
FROM
	emp 
ORDER BY
	sal DESC 
	LIMIT 1;

方法2 : 自连接方案

-- 方案2 : 子链接方案, 首先用自链接查出来除了最大薪资的所以薪资然后用not in判断
-- step1 : 首先去除最大薪资的集合
SELECT DISTINCT
	a.sal 
FROM
	emp a
	JOIN emp b ON a.sal < b.sal;
-- step2 : 用not in进行过滤
SELECT
	sal 
FROM
	emp 
WHERE
	sal NOT IN ( SELECT DISTINCT a.sal FROM emp a JOIN emp b ON a.sal < b.sal );

执行结果均为下图
在这里插入图片描述
第六题 : 取得平均薪水最高的部门的部门编号

-- 第六题 : 取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
-- step1 : 取到每一个部门的平均薪水
SELECT
	avg( sal ) AS avg_sal 
FROM
	emp 
GROUP BY
	deptno;
-- step2 : 把上面的表当作是临时表然后用max函数查到最大的平均薪水
SELECT
	max( t.avg_sal ) 
FROM
	( SELECT avg( sal ) AS avg_sal FROM emp GROUP BY deptno ) t;
-- step3 : 把上面的表再次当作临时表然后找到step1中的平均信息进行筛选
SELECT
	tt.deptno 
FROM
	( SELECT deptno, avg( sal ) AS avg_sal FROM emp GROUP BY deptno ) tt 
WHERE
	tt.avg_sal IN ( SELECT max( t.avg_sal ) FROM ( SELECT avg( sal ) AS avg_sal FROM emp GROUP BY deptno ) t );

本题贴士 : limti不可以在子查询当中使用
执行结果为下图
在这里插入图片描述

第七题 : 取得平均薪水最高的部门的部门名称

-- 第七题 : 取得平均薪水最高的部门的部门名称
-- 这道题跟上一题的区别就是我们进行表连接之后进行分组
-- step1 : 进行连接之后按照部门名称分组
SELECT
	d.dname,
	avg( sal ) 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
GROUP BY
	d.dname;
-- step2 : 把上面的表当作临时表然后用分组函数找到最大值
SELECT
	max( avg_sal ) 
FROM
	(
	SELECT
		d.dname,
		avg( sal ) AS avg_sal 
	FROM
		emp e
		JOIN dept d ON e.deptno = d.deptno 
	GROUP BY
		d.dname 
	) t;
-- step3 : 根据step1中的表和step2的最大值进行筛选
SELECT
	tt.dname 
FROM
	(
	SELECT
		d.dname,
		avg( sal ) AS avgSal 
	FROM
		emp e
		JOIN dept d ON e.deptno = d.deptno 
	GROUP BY
		d.dname 
	) tt 
WHERE
	tt.avgSal IN (
	SELECT
		max( avg_sal ) 
	FROM
		(
		SELECT
			d.dname,
			avg( sal ) AS avg_sal 
		FROM
			emp e
			JOIN dept d ON e.deptno = d.deptno 
		GROUP BY
			d.dname 
		) t 
	);

本题贴士 :

  1. 不允许出现tt.t.name(也就是临时表名字嵌套临时表名字的写法)
  2. 在进行了表连接之后, 其实就相当于一张表, 所以可以对连接表的字段进行分组, 同时可以运用分组函数

执行结果如下
在这里插入图片描述
第九题 : 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

-- 第九题 : 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
-- step1 : 找到mgr中出现的所有员工编号(都是当过领导人的)
SELECT DISTINCT
	mgr 
FROM
	emp 
WHERE
	mgr IS NOT NULL;
-- step2 : 用step1中得到的姓名编号再次进行筛选
SELECT
	empno 
FROM
	emp 
WHERE
	empno NOT IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL );
-- step3 : 找到这些员工的最高的薪资
SELECT
	max( sal ) 
FROM
	emp 
WHERE
	empno IN (
	SELECT
		empno 
	FROM
		emp 
	WHERE
	empno NOT IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ));
-- step4 : 找到step2中找到的领导编号的信息
SELECT
	ename 
FROM
	emp 
WHERE
	empno IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ) 
	AND sal > (
	SELECT
		max( sal ) 
	FROM
		emp 
	WHERE
		empno IN (
		SELECT
			empno 
		FROM
			emp 
		WHERE
		empno NOT IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL )));

执行结果如下
在这里插入图片描述

第十题 : 取得薪水最高的前五名员工


-- 第十题 : 取得薪水最高(带补贴)的前五名员工
SELECT
	empno,
	ename,
	sal + ifnull( comm, 0 ) AS all_sal 
FROM
	emp 
ORDER BY
	all_sal DESC 
	LIMIT 5;

执行结果如下
在这里插入图片描述
第十一题 : 取得薪水最高的第六到第十名员工

	
-- 第十一题 : 取得薪水最高的第六到第十名员工
SELECT
	empno,
	ename,
	sal 
FROM
	emp 
ORDER BY
	sal DESC 
	LIMIT 5,
	5;

执行结果如下
在这里插入图片描述
第十二题 : 取得最后入职的5名员工

-- 第十二题 : 取得最后入职的5名员工
SELECT
	empno,
	ename,
	hiredate 
FROM
	emp 
ORDER BY
	hiredate DESC 
	LIMIT 5;

执行结果如下
在这里插入图片描述
第十三题 : 取得每个薪水等级有多少员工

-- 第十三题 : 取得每个薪水等级有多少员工
-- step1 : 表连接找到每个员工的薪水等级
SELECT
	e.ename,
	e.sal,
	s.grade 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal 
	AND s.hisal;
-- step2 : 在step1的表的基础上直接对grade进行分组并计数
SELECT
	s.grade,
	count(*) 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal 
	AND s.hisal 
GROUP BY
	s.grade;

执行结果如下
在这里插入图片描述
第十四题 : 列出所有员工及领导的姓名

-- 第十四题 : 列出所有员工及领导的姓名
-- step1 : 其实就是一个子链接 + 左连接
SELECT
	e.ename,
	l.ename 
FROM
	emp e
	LEFT JOIN emp l ON e.mgr = l.empno;

执行结果如下
在这里插入图片描述
第十五题 : 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

-- 第十五题 : 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
-- step1 : 首先把员工表进行子链接(内连接, 此时不是外连接)
SELECT
	* 
FROM
	emp e
	JOIN emp l ON e.mgr = l.empno;
-- step2 : 把step1中的信息与部门名称继续连接, 并添加where条件过滤
SELECT
	e.empno,
	e.ename,
	d.dname 
FROM
	emp e
	JOIN emp l ON e.mgr = l.empno
	JOIN dept d ON d.deptno = e.deptno 
WHERE
	e.hiredate < l.hiredate;

执行结果如下
在这里插入图片描述
第十六题 : 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

-- 第十六题 : 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
-- step1 : 把emp表跟dept表进行外连即可
SELECT
	d.dname,
	e.* 
FROM
	emp e
	RIGHT JOIN dept d ON e.deptno = d.deptno;

执行结果如下
在这里插入图片描述
第十七题 : 列出至少有5个员工的所有部门

-- 第十七题 : 列出至少有5个员工的所有部门
-- step1 : 首先进行表连接
SELECT
	* 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno;
-- step2 : 在step1的基础上进行分组having过滤
SELECT
	d.dname,
	count(*) 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
GROUP BY
	d.dname 
HAVING
	count(*) >= 5;

执行结果如下
在这里插入图片描述
第十八题 : 列出薪金比"SMITH"多的所有员工信息

-- 第十八题 : 列出薪金比"SMITH"多的所有员工信息
-- step1 : 首先找到'smith'的薪资
SELECT
	sal 
FROM
	emp 
WHERE
	ename = 'smith';
-- step2 : 运用子查询找到薪资比smith高的员工信息
SELECT 
	empno, ename, sal 
FROM 
	emp 
WHERE 
	sal > ( SELECT sal FROM emp WHERE ename = 'smith' );

执行结果如下
在这里插入图片描述
第十九题 : 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

-- 第十九题 : 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
-- step1 : 首先找到每个部门的名称以及部门的人数
SELECT
	d.dname,
	count(*) 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
GROUP BY
	d.dname;
-- step2 : 找到所有job = CLERK的人员的信息
SELECT
	e.ename,
	e.job,
	e.deptno,
	d.dname 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
WHERE
	job = 'CLERK';
-- step3 : 把上面的两张表都看作是临时表进行连接
SELECT
	t2.ename,
	t2.dname,
	t1.cnt 
FROM
	(
	SELECT
		d.dname,
		count(*) AS cnt 
	FROM
		emp e
		JOIN dept d ON e.deptno = d.deptno 
	GROUP BY
		d.dname 
	) t1
	JOIN (
	SELECT
		e.ename,
		e.job,
		e.deptno,
		d.dname 
	FROM
		emp e
		JOIN dept d ON e.deptno = d.deptno 
	WHERE
		job = 'CLERK' 
	) t2 ON t1.dname = t2.dname;

执行结果如下
在这里插入图片描述
第二十题 : 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

-- 第二十题 : 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
-- step1 : 直接按照job进行分组然后having过滤就好了
SELECT
	job,
	min( sal ),
	count(*) 
FROM
	emp 
GROUP BY
	job 
HAVING
	min( sal ) > 1500;

执行结果如下
在这里插入图片描述


http://www.kler.cn/news/367413.html

相关文章：

  • 破解API加密逆向接口分析，看这篇就够了
  • HarmonyOS 相对布局（RelativeContainer）
  • C++——String类讲解
  • 使用 ASP.NET Core 8.0 创建最小 API
  • C# 串口通信教程
  • 用接地气的例子趣谈 WWDC 24 全新的 Swift Testing 入门（一）
  • 2024.10.27 直接插入排序 非递归后序遍历（复杂版）
  • 基于STM32的智能寝室控制系统设计（论文+源码）
  • Spring Boot环境下论坛网站的架构与优化
  • idea 无法输入中文 快速解决
  • 2024 BuildCTF 公开赛|MISC
  • 【好玩的经典游戏】Docker环境下部署网页小游戏网站
  • 媒小象 1.7.2 | 完全免费的内容创作二创工具
  • uniapp 发起post和get请求！uni.request(OBJECT)
  • PouchDB - 免费开源的 JavaScript 数据库，轻量易用，用于离线保存数据的场景
  • 联想笔记本电脑睡眠后打开黑屏解决方法
  • 路由器 相关知识
  • 【Android】浅析OkHttp（1）
  • 【动态规划】力扣 70. 爬楼梯
  • pytorch dataloader学习
  • ros机器人导航以及物体、动作识别
  • SpringBoot多线程
  • MAC终端SSH连接成功但VSCODE连接失败解决方案
  • java实现的音视频格式转化器
  • Java进阶篇设计模式之一 ----- 单例模式
  • 前端学习---(6)js基础--4