【数据库34道习题（一）】通过34道习题来真正掌握sql语句的运用

最新推荐文章于 2023-01-11 10:30:04 发布

晏楠先生

最新推荐文章于 2023-01-11 10:30:04 发布

阅读量525

点赞数

分类专栏：数据库文章标签： sql 数据库

本文链接：https://blog.csdn.net/maikotom/article/details/123147404

版权

数据库专栏收录该内容

7 篇文章 2 订阅

订阅专栏

习题篇（一）

首要：三表的全部数据查询
1、取得每个部门最高薪水的人员名称
2、哪些人的薪水在部门的平均薪水之上
3、取得部门中（所有人的）平均的薪水等级
4、不准用组函数（Max ），取得最高薪水
5、取得平均薪水最高的部门的部门编号
6、取得平均薪水最高的部门的部门名称
7、求平均薪水的等级最低的部门的部门名称
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
9、取得薪水最高的前五名员工
10、取得薪水最高的第六到第十名员工
11、取得最后入职的 5 名员工
12、取得每个薪水等级有多少员工
13、面试题
14、列出所有员工及领导的姓名
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
16、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
17、列出至少有 5 个员工的所有部门

首要：三表的全部数据查询

emp

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

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

第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+

第二步：将以上的查询结果当做一张临时表t，t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal

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;

+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

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

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：将以上查询结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal

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;
+--------+-------------+-------+---------+
| deptno | avgsal      | ename | sal     |
+--------+-------------+-------+---------+
|     30 | 1566.666667 | ALLEN | 1600.00 |
|     20 | 2175.000000 | JONES | 2975.00 |
|     30 | 1566.666667 | BLAKE | 2850.00 |
|     20 | 2175.000000 | SCOTT | 3000.00 |
|     10 | 2916.666667 | KING  | 5000.00 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+

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

区分：平均的薪水等级，平均薪水的等级

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

第一步：找出每个人的薪水等级 emp e和salgrade s表连接。
连接条件：e.sal between s.losal and s.hisal

select 
		e.ename,e.sal,e.deptno,s.grade
	from
		emp e
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;

	+--------+---------+--------+-------+
	| ename  | sal     | deptno | grade |
	+--------+---------+--------+-------+
	| CLARK  | 2450.00 |     10 |     4 |
	| KING   | 5000.00 |     10 |     5 |
	| MILLER | 1300.00 |     10 |     2 |

	| SMITH  |  800.00 |     20 |     1 |
	| ADAMS  | 1100.00 |     20 |     1 |
	| SCOTT  | 3000.00 |     20 |     4 |
	| FORD   | 3000.00 |     20 |     4 |
	| JONES  | 2975.00 |     20 |     4 |

	| MARTIN | 1250.00 |     30 |     2 |
	| TURNER | 1500.00 |     30 |     3 |
	| BLAKE  | 2850.00 |     30 |     4 |
	| ALLEN  | 1600.00 |     30 |     3 |
	| JAMES  |  950.00 |     30 |     1 |
	| WARD   | 1250.00 |     30 |     2 |
	+--------+---------+--------+-------+

第二步：基于以上的结果继续按照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;

	+--------+--------------+
	| deptno | avg(s.grade) |
	+--------+--------------+
	|     10 |       3.6667 |
	|     20 |       2.8000 |
	|     30 |       2.5000 |
	+--------+--------------+

4、不准用组函数（Max ），取得最高薪水

第一种：sal降序，limit 1

select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

第二种方案：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);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

select 
	distinct a.sal 
from 
	emp a 
join 
	emp b 
on 
	a.sal < b.sal

a表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

b表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

5、取得平均薪水最高的部门的部门编号

方法1：排序用limit找第一个数据

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;
		+--------+-------------+
		| deptno | avgsal      |
		+--------+-------------+
		|     10 | 2916.666667 |
		|     20 | 2175.000000 |
		|     30 | 1566.666667 |
		+--------+-------------+

第二步：降序选第一个

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

法2：找出各部门平均值，然后再找出最大值，然后再选择查询

第一步、找出各部门平均薪水

select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno;

第二步、找出平均薪水的最大值

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

最后综合查询，找出emp中跟平均薪水最大值相等的平均薪水数据

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

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

*分析：avgsal = (select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t); ====》找出与最大值相等的avgsal*

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

select 
	d.dname,avg(e.sal) as avgsal 
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
group by 
	d.dname
order by 
	avgsal desc 
limit 1;

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

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

1.求出平均薪资的等级
2.在排序得到名称

select
	e.deptno,avg(e.sal) as avgsal
from
	emp e
group by
	e.deptno	

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

select
	t.*,s.grade
from
	(select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t
join
	salgrade s
on
	t.avgsal between LOSAL and HISAL;
	
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+

select
	t.*,s.grade
from
	(select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t
join
	salgrade s
on
	t.avgsal between LOSAL and HISAL
order by
	s.grade asc
limit 1;

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+

其实平均薪资最低肯定就是最低的等级
所以可以先找出最低的平均薪资然后找出对应的薪资等级

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

	+-------+
	| grade |
	+-------+
	|     3 |
	+-------+

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

注意：比“普通员工的最高薪水”还要高的一定是领导！

找出在mgr字段上出现的员工，就可以找出不在mgr上的普通员工

select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

普通员工(员工代码没有在 mgr 字段上出现的) === 》代码出现在mgr上面的就是领导 == 》员编号没有在以上范围内的都是普通员工

第一步：找出普通员工的最高薪水
not in在使用的时候，后面小括号中记得排除NULL。

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

第二步：找出高于1600的

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

+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

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

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

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

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

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

+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+

注意一下：limit是从下标0开始的，所以第六名员工下标为5

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

日期也可以排序哦

select ename,hiredate from emp order by hiredate desc limit 5;

	+--------+------------+
	| ename  | hiredate   |
	+--------+------------+
	| ADAMS  | 1987-05-23 |
	| SCOTT  | 1987-04-19 |
	| MILLER | 1982-01-23 |
	| FORD   | 1981-12-03 |
	| JAMES  | 1981-12-03 |
	+--------+------------+

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

使用count关键字

第一步：找出每个员工的薪水等级

select
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

第二步：继续按照grade分组统计数量

select 
	s.grade ,count(*)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	s.grade;

+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13、面试题

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

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

select 
	a.ename '员工', b.ename '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

+--------+-------+
| 员工   | 领导    |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

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

员工与领导则使用自连接

emp a 员工表
emp b 领导表

select
	a.ename '员工', a.hiredate, b.ename '领导', b.hiredate, 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;
	
+-------+------------+-------+------------+------------+
| 员工     | hiredate   | 领导    | hiredate   | dname      |
+-------+------------+-------+------------+------------+
| CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
| SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
| JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
+-------+------------+-------+------------+------------+

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

select 
	e.*,d.dname
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno;

+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

注意注意这里要右外连接不然null会查询不出来

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

按照部门编号分组，计数，筛选出 >= 5

select
	deptno
from
	emp
group by
	deptno
having
	count(*) >= 5;

+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+

晏楠先生

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
【数据库34道习题（一）】通过34道习题来真正掌握sql语句的运用

mysql> select * from emp;+-------+--------+-----------+------+------------+---------+---------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+--------+-----------+------+------------+---------+-------
复制链接

扫一扫