SQL数据库综合练习题及答案

写再前面的话

1，本文为Java学习五部分第二部分所有项目回顾（数据库练习系列-对应Java体系第二部分第一阶段内容）这些再前面的博客里有，再写会重复的。但是一想到我设计的第五部分第一阶段本来就要把那些值得记录的项目练习单独整理一遍。所以，理应如此，我会写的更好的。
2，整体项目系列博客（包括整体快递e站系列，数据库练习系列，前端练习系列，Javaweb系列，SSM系列，微服务系列，实战系列，等）
3，完整的Java体系链接（比你想的更多的Java内容），感谢一下开课吧。

需求介绍

自己没事的时候练习的。

问题：

#34道作业题
#三张练习表单：
#| dept -部门表 | emp -员工表 | salgrade -薪资表

1、取得每个部门最高薪水的人员名称
2、哪些人的薪水在部门的平均薪水之上
3、取得部门中（所有人的）平均的薪水等级，如下：
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
6、取得平均薪水最高的部门的部门名称
7、求平均薪水的等级最低的部门的部门名称
8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
9、取得薪水最高的前五名员工
10、取得薪水最高的第六到第十名员工
11、取得最后入职的5名员工
12、取得每个薪水等级有多少员工
13、面试题
14、列出所有员工及领导的姓名
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
17、列出至少有5个员工的所有部门
18、列出薪金比"SMITH"多的所有员工信息.
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
24、列出薪金高于部门30中员工的薪金的其他员工的姓名和薪金.
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
27、列出所有员工的姓名、部门名称和工资。
28、列出所有部门的详细信息和人数
29、列出各种工作的最低工资及从事此工作的雇员姓名
30、列出各个部门的MANAGER(领导)的最低薪金
31、列出所有员工的年工资,按年薪从低到高排序
32、求出员工领导的薪水超过3000的员工名称与领导名称
33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.
34、给任职日期超过30年的员工加薪10%.

答案

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

#我的思路：
#Select e.ENAME ,t.DEPION ,MAX(e.SAL) FRom emp e,dept t;
#答案思路
#第一步，取得每个部门的最高薪水
SELECT deptno ,MAX(sal) AS maxsal FROM emp GROUP BY deptno;
#第二步，将以上查询条件做一张临时表t,t与emp表连接，t.deptno = e.deptno and t.maxsal = e.sal
SELECT e.ename AS 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;

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

#我的思路：第一步，找部门的平均薪水。第二步，找人
SELECT AVG(e.sal) AS salavg,t.deptno FROM emp e,dept t WHERE t.deptno = e.deptno GROUP BY deptno;
SELECT e.ename, s.* FROM emp e JOIN
(SELECT AVG(e.sal) AS salavg,t.deptno FROM emp e,dept t WHERE t.deptno = e.deptno GROUP BY deptno) s
ON t.deptno = s.deptno AND e.sal > ;
SELECT e.ename,d.dname,s.grade FROM emp e JOIN dept d ON e.deptno = d.deptno JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
#答案思路
#第一步，取得每个部门的平均薪水
SELECT deptno,AVG(sal)FROM emp GROUP BY deptno;
#第二步，将以上查询条件做一张临时表t,t与emp表连接，t.deptno = e.deptno and e.sal > t.avgsal
SELECT e.ename,e.SAL,t.deptno FROM emp e
JOIN (SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno) t
ON t.deptno = e.deptno AND e.sal > t.avgsal;

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

#我的思路.1,找出每个人的薪水等级。（完成）同一
#2，将以上查询条件做一张临时表t,t与emp表连接，t.deptno = e.deptno and (不知道)t.avggrade = e.-思路错误
#答案思路
#第一步，找出每个人的薪水等级
SELECT e.ename,e.sal,e.deptno,s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
#第二步，基于以上的结果，继续按照deptno分组，求薪水等级的平均值
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），取得最高薪水（给出两种解决方案）

#用函数的方法：SELECT max(e.sal)FROM emp e;
#我的思路 1，薪水降序排序显示第一个
SELECT e.sal FROM emp e ORDER BY sal DESC LIMIT 0,1;
#2，没想到，答案思路：自连接
#查询所有a表工资比b表工资低的值，把这些值去掉
SELECT DISTINCT a.sal FROM emp a JOIN emp b ON a.sal < b.sal;
SELECT sal FROM emp WHERE sal NOT IN(SELECT DISTINCT a.sal FROM emp a JOIN emp b ON a.sal < b.sal);

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

#我的思路-1，先求每个部门的平均工资。2，然后降序取第一个值 思路2，同1，2取最大值
#1，第一步
SELECT e.deptno,AVG(e.sal) FROM emp e GROUP BY e.deptno;
#1，第二步
SELECT e.deptno,AVG(e.sal) AS avgsal FROM emp e GROUP BY e.deptno ORDER BY avgsal DESC LIMIT 0,1;
#2,嵌套-不会过
#SELECT e.deptno,max(t.avgsal) FROM emp e,(SELECT e.deptno,AVG(e.sal) AS avgsal FROM emp e GROUP BY e.deptno) t;

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

#我的思路-1.先求每个部门的平均工资。2.然后降序取第一个值(有问题：不排除有多个最高部门的情况)/嵌套取最大值(不会)
SELECT e.deptno,AVG(e.sal) FROM emp e GROUP BY e.deptno;
#第一种
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 0,1;
#第二种:不大懂
SELECT d.dname,MAX(t.avgsal) FROM dept d JOIN
(SELECT e.deptno,AVG(e.sal) AS avgsal FROM emp e GROUP BY e.deptno) t
ON t.deptno = d.deptno GROUP BY d.dname;

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

#我的思路：1，取得每个部门的平均薪水等级。2，找出平均薪水等级最低的部门名称-不会
SELECT deptno,AVG(sal)FROM emp GROUP BY deptno;
SELECT e.deptno,s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
JOIN dept t ON t.deptno=e.deptno
GROUP BY t.deptno;
#答案思路-不会
#1，找出每个部门的平均薪水
SELECT e.deptno,AVG(e.sal)FROM emp e GROUP BY deptno;
#2，找出每个部门的平均薪水等级
SELECT t.* ,s.grade
FROM （SELECT e.deptno,AVG(e.sal) AS avgsal FROM emp e GROUP BY deptno）t
JOIN salgrade s ON t.avgsal BETWEEN s.losal AND s.hisal;

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

#我的思路：1，求普通员工最高工资。2，求比这个工资高的领导人姓名
#领导人有那些？
SELECT DISTINCT mgr FROM emp;
#普通员工的最高薪水
SELECT MAX(sal) FROM emp WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);
#找出大于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));

#9、取得薪水最高的前五名员工
SELECT e.ename,e.sal FROM emp e WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
ORDER BY e.sal DESC LIMIT 0,5;

#10、取得薪水最高的第六到第十名员工
SELECT e.ename,e.sal FROM emp e WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
ORDER BY e.sal DESC LIMIT 5,5;

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

#日期降序排取员工名
SELECT ename,hiredate FROM emp ORDER BY hiredate DESC LIMIT 5;

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

SELECT e.ename,s.grade,

SELECT COUNT(e.ename),s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade;

13、面试题

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

#我的思路：自连接
SELECT a.ename ‘员工’,b.ename '领导’FROM emp a LEFT JOIN emp b
ON a.mgr = b.EMPNO;

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

SELECT a.MGR,a.ename ‘员工’,c.dname ‘部门名称’
FROM emp a LEFT JOIN emp b ON a.mgr = b.EMPNO
JOIN dept c ON a.deptno = c.deptno
WHERE a.hiredate < b.hiredate;

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

SELECT b.* , a.dname FROM emp b RIGHT JOIN dept a
ON a.deptno = b.deptno ;

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

#我的思路
SELECT COUNT(a.ename),b.dname FROM emp a JOIN dept b
WHERE COUNT(a.ename) > 5
GROUP BY b.dname;
#答案思路
SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(*)>=5;