练习20-25：多表关联查询

表结构回顾

21. 列出在部门“SALES”<销售部>工作的员工的姓名

SELECT
	e.eName
FROM emp e
JOIN dept d
ON e.deptNo = d.deptNo
WHERE d.dName = 'SALES';
1
2
3
4
5
6

22. 列出薪资高于公司平均薪资的所有员工及其所在部门名称、上级领导名称、雇员的工资等级

第一步：首先考虑将结果需要的字段放到同一条记录中，员工表关联部门表获得部门名称，但有员工可能不属于任何部门，所以需要左连接， 同时自关联，获得领导的名称,继续关联薪资等级表，获得薪资等级

SELECT
	e1.empNo,
	d.dName AS deptName,
	e2.eName AS mgrName,
	s.grade
FROM emp e1
LEFT JOIN dept d
ON e1.deptNo = d.deptNo
LEFT JOIN emp e2
ON e1.mgr = e2.empNo
JOIN salgrade s
ON e1.sal BETWEEN s.loSal AND s.hiSal;
1
2
3
4
5
6
7
8
9
10
11
12

第二步：获得公司的平均薪资

SELECT AVG(sal) avg_sal FROM emp;
1

第三步：结合第一步第二步，筛出员工薪资大于公司平均薪资的那些记录

SELECT
	e1.empNo,
	d.dName AS deptName,
	e2.eName AS mgrName,
	s.grade
FROM emp e1
LEFT JOIN dept d
ON e1.deptNo = d.deptNo
LEFT JOIN emp e2
ON e1.mgr = e2.empNo
JOIN salgrade s
ON e1.sal BETWEEN s.loSal AND s.hiSal
WHERE e1.sal > (SELECT AVG(sal) avg_sal FROM emp);
1
2
3
4
5
6
7
8
9
10
11
12
13

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

第一步：求得SCOTT从事的工作

SELECT job FROM emp WHERE eName = 'SCOTT';
1

第二步：员工表关联部门表，可以获得员工所属部门名称，然后筛选出和SCOTT从事相同工作的记录即可

SELECT
	e.empNo,
	e.eName,
	d.dName
FROM emp e
JOIN dept d
ON e.deptNo = d.deptNo
WHERE e.job = (SELECT job FROM emp WHERE eName = 'SCOTT');
1
2
3
4
5
6
7
8

24. 列出薪资等于部门编号为30中员工的薪资的其他员工的姓名和薪资

第一步：首先看部门编号30的所有员工获取过哪些薪资

SELECT DISTINCT sal FROM emp WHERE deptNo = 30;
1

第二步：筛选出除了部门30外，其他部门有哪些员工的薪水在第一步的结果中

SELECT
	eName,
	sal
FROM emp
WHERE sal IN(SELECT DISTINCT sal FROM emp WHERE deptNo = 30)
AND deptNo != 30;
1
2
3
4
5
6

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

第一步：首先观察最终结果需要哪些字段，看到需要部门名称，所有先让员工表关联部门表， 获得员工的部门名称

SELECT
	eName,
	sal,
	dName
FROM emp e
LEFT JOIN dept d
ON e.deptNo = d.deptNo;
1
2
3
4
5
6
7

第二步：取得部门30中所有员工薪资的最大值

SELECT MAX(sal) FROM emp WHERE deptNo = 30;
1

第三步：结合一二步，筛出最终结果

SELECT
	eName,
	sal,
	dName
FROM emp e
LEFT JOIN dept d
ON e.deptNo = d.deptNo
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptNo = 30);
1
2
3
4
5
6
7
8