MySQL课后习题

电科张国荣

已于 2022-03-30 15:59:21 修改

阅读量1.9k

点赞数 5

分类专栏： 2022秋招突击内容文章标签： java

于 2022-03-27 20:41:17 首次发布

本文链接：https://blog.youkuaiyun.com/qq_37901308/article/details/123774643

版权

2022秋招突击内容专栏收录该内容

21 篇文章

订阅专栏

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

第一步：按部门分组,查询每个部门的最高薪水

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

第二步：将上述查询结果作为一张临时表t,与emp表进行表连接,连接条件: e.sal = t.maxsal and e.deptno = t.deptno;

select e.ename,t.maxsal as sal,t.deptno from emp e join (select max(sal) as maxsal,deptno from emp group by deptno) t on e.sal = t.maxsal and e.deptno = t.deptno;
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

本题的难点在于将查询结果作为一张临时表，与另一张表进行表连接；

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

第一步：按照部门分组,求每个部门的平均薪水;
select avg(sal) as sal,deptno from emp group by deptno;
+-------------+--------+
| sal         | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+

第二步: 将上述查询结果作为临时表t,与emp e表进行表连接;
        要查询的字段为e.ename,e.sal;
        连接条件: e.sal > t.sal and e.deptno = t.deptno
select e.ename,e.sal from emp e join (select avg(sal) as sal,deptno from emp group by deptno) t on e.sal > t.sal and e.deptno = t.deptno;
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

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

第一步：求出所有人的薪资等级  emp表与salgrade表进行表连接
       查询字段,e.ename,e.sal,e.deptno,s.grade
       连接条件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 |
+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| JONES  | 2975.00 |     20 |     4 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| CLARK  | 2450.00 |     10 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| KING   | 5000.00 |     10 |     5 |
| TURNER | 1500.00 |     30 |     3 |
| ADAMS  | 1100.00 |     20 |     1 |
| JAMES  |  950.00 |     30 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+

第二步：将上述查询结果作为临时表t,按deptno分组后,求avg(grade)
select t.deptno,avg(t.grade) from (select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal)t group by t.deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

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

方案一：降序排序+limit 1

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

方案二：表的自连接

// 分析
select sal from emp;
+---------+
| sal     |
+---------+
| 3000.00 |
| 3000.00 |
|  800.00 |
|  950.00 |
| 1100.00 |
| 1300.00 |
| 2450.00 |
| 2850.00 |
| 2975.00 |
| 5000.00 |
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
+---------+ // 做a表,b表
查询所有满足a.sal < b.sal的a.sal, 只有最高值查不出来,其他都能查出来;
利用distinct去重
select distinct a.sal from (select sal from emp) a join (select sal from emp) b on a.sal < b.sal;
+---------+
| sal     |
+---------+
|  800.00 |
|  950.00 |
| 1100.00 |
| 1300.00 |
| 2450.00 |
| 2850.00 |
| 2975.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 3000.00 |
+---------+
// 所以只要查询不在该表中的sal即可

// 利用表的自连接查询最高薪水
select sal from emp where sal not in(select distinct a.sal from (select sal from emp) a join (select sal from emp) b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

5. 取得平均薪水最高的部门的部门编号(至少给出两种方案)

方案一：

第一步：按部门编号分组，查询每个部门的平均薪水；
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：上述查询结果作为临时表t,按avgsal降序排序，取第一条
select deptno from (select deptno,avg(sal) as avgsal from emp group by deptno) t order by avgsal desc limit 1;
+--------+
| deptno |
+--------+
|     10 |
+--------+

方案二：

第一步：按部门编号分组，查询每个部门的平均薪水；
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：使用分组函数max
select deptno from (select deptno,avg(sal) as avgsal from emp group by deptno) t where avgsal = (select max(avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t);
+--------+
| deptno |
+--------+
|     10 |
+--------+

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

第一步：按部门编号分组，求每个部门的平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步: 将上述查询结果降序排序,取第一条记录
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

第三步：将上述查询结果作为临时表t，与dept表进行表连接
       查询字段：d.dname
       连接条件：t.deptno = d.deptno
select d.dname from dept d join (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) t where d.deptno = t.deptno;
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+

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

第一步：按部门分组，求每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：上述查询结果作为t表与薪资等级表salgrade进行表连接
        查询字段：t.deptno,s.grade
        连接条件: t.avgsal between s.losal and s.hisal
select t.deptno,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     30 |     3 |
|     10 |     4 |
|     20 |     4 |
+--------+-------+

第三步：在第二步基础上对grade进行升序排序，只显示第一条数据
select t.deptno,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal limit 1;
+--------+-------+
| deptno | grade |
+--------+-------+
|     30 |     3 |
+--------+-------+

第四步：将第三步的查询结果作为一张临时表t1,与dept表进行表连接
       查询字段:d.dname
        连接条件 t1.deptno = d.deptno
select d.dname from dept d join (select t.deptno,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal limit 1) t1 on t1.deptno = d.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+

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

第一步：取得普通员工的最高薪水
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
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 e 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;

第二步: 显示前五名
select ename,sal from emp order by sal desc limit 0,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 |
+--------+---------+

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

第一步：查询ename,hiredate字段,按hiredate降序排序
select ename,hiredate from emp order by hiredate desc;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD   | 1981-12-03 |
| JAMES  | 1981-12-03 |
| KING   | 1981-11-17 |
| MARTIN | 1981-09-28 |
| TURNER | 1981-09-08 |
| CLARK  | 1981-06-09 |
| BLAKE  | 1981-05-01 |
| JONES  | 1981-04-02 |
| WARD   | 1981-02-22 |
| ALLEN  | 1981-02-20 |
| SMITH  | 1980-12-17 |
+--------+------------+

第二步: 显示前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. 取得每个薪水等级有多少员工

第一步：将员工的薪水分等级
即查询e.ename,e.sal,s.grade
连接条件：e.sal between s.losal and s.hisal;
select 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 |
+--------+---------+-------+

第二步:将上述查询结果作为t表按grade分组,利用count(*)取得每个薪水等级有多少员工
select t.grade,count(*) from (select ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13.

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

// emp 可以看成员工表b，也可以看成领导表a

// a表与b表连接，查询字段:b.ename,a.ename 连接条件 b.mgr = a.empno
select b.ename,ifnull(a.ename,'没有上级') from emp b left join emp a on b.mgr = a.empno;
+--------+----------------------------+
| ename  | ifnull(a.ename,'没有上级')       |
+--------+----------------------------+
| SMITH  | FORD                       |
| ALLEN  | BLAKE                      |
| WARD   | BLAKE                      |
| JONES  | KING                       |
| MARTIN | BLAKE                      |
| BLAKE  | KING                       |
| CLARK  | KING                       |
| SCOTT  | JONES                      |
| KING   | 没有上级                         |
| TURNER | BLAKE                      |
| ADAMS  | SCOTT                      |
| JAMES  | BLAKE                      |
| FORD   | JONES                      |
| MILLER | CLARK                      |
+--------+----------------------------+

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

emp a 员工表
emp b 领导表
dept d 部门表
查询字段:a.empno,a.ename,d.dname
a表与b表连接条件:a.mgr = b.empno
a表与d表连接条件:a.deptno = d.deptno
筛选条件:a.hiredate < b.hiredate;

select a.empno,a.ename,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;
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7782 | CLARK | ACCOUNTING |
|  7369 | SMITH | RESEARCH   |
|  7566 | JONES | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7698 | BLAKE | SALES      |
+-------+-------+------------+

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

分析：
1. 待查询字段d.dname, emp表的所有字段 (涉及表连接)
2. 连接条件：e.deptno = d.deptno
3. 要求列出没有员工的部门, 即所有部门都要显示,即将部门表作为主表

select d.dname,e.* from dept d left join emp e on e.deptno = d.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  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 |
| RESEARCH   |  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 |
| SALES      |  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 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

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

分析: 
1. 按部门分组
2. 查询部门名,count计算员工的数量
3. where条件: 员工数量>=5

select deptno,count(*) as num from emp group by deptno;
+--------+-----+
| deptno | num |
+--------+-----+
|     10 |   3 |
|     20 |   5 |
|     30 |   6 |
+--------+-----+
// 将上述查询结果作为临时表t,与dept表进行表连接,查询字段:d.dname,t.num,连接条件:t.deptno = d.deptno
// 再利用where进行筛选
select d.dname,t.num from dept d join (select deptno,count(*) as num from emp group by deptno) t on d.deptno= t.deptno where t.num >= 5;
+----------+-----+
| dname    | num |
+----------+-----+
| RESEARCH |   5 |
| SALES    |   6 |
+----------+-----+

18. 列出薪金比'SMITH'多的所有员工信息

1. 查询SMITH的sal
select sal from emp where ename = 'SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

2. 查询员工的信息,条件是sal>800
select * from emp where sal > (select sal from emp where ename = 'SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

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

分析:
1. 查询job = 'CLERK'的员工姓名、部门编号、部门名称(表连接dept表)
select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK';
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| MILLER |     10 | ACCOUNTING |
| SMITH  |     20 | RESEARCH   |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
+--------+--------+------------+

2. 统计每个部门的人数
select deptno,count(*) as cc from emp group by deptno;
+--------+----+
| deptno | cc |
+--------+----+
|     10 |  3 |
|     20 |  5 |
|     30 |  6 |
+--------+----+

3. 1表与2表进行表连接
select a.ename,a.dname,b.cc from (select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK') a join (select deptno,count(*) as cc from emp group by deptno) b on a.deptno = b.deptno;
+--------+------------+----+
| ename  | dname      | cc |
+--------+------------+----+
| MILLER | ACCOUNTING |  3 |
| SMITH  | RESEARCH   |  5 |
| ADAMS  | RESEARCH   |  5 |
| JAMES  | SALES      |  6 |
+--------+------------+----+