19.多表查询练习答案

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
   列：d.deptno, d.dname, d.loc, 部门人数
   表：dept d, emp e
   条件：e.deptno=d.deptno
   分析：

   • d.deptno, d.dname, d.loc 为dept表所有列

     SELECT * FROM dept
     

   • 部门人数为emp表分组统计出来

     SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno;
     

   • 将2个结果集合并起来并去除笛卡尔积，注意给结果集和count列取别名

       SELECT dept.*, tb2.num
       FROM dept, (SELECT deptno, COUNT(empno) num FROM emp GROUP BY deptno) tb2
       WHERE dept.deptno = tb2.deptno;
     

2. 列出所有员工的姓名及其直接上级的姓名
   列：员工姓名、上级姓名
   表：emp e, emp m
   条件：员工的mgr = 上级的empno

SELECT e.ename, IFNULL(m.ename, 'BOSS') 领导
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno

3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
   列：e.empno, e.ename, d.dname
   表：emp e, emp m, dept d
   条件：e.hiredate < m.hiredate
   思路：

• 先不查部门名称，只查部门编号!
  列：e.empno, e.ename, e.deptno
  表：emp e, emp m
  条件：e.mgr=m.empno, e.hiredate<m.hireadate

SELECT e.empno, e.ename
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.hiredate < m.hiredate;

• 加入dept表

SELECT tb1.*, d.dname
FROM (SELECT e.empno, e.ename '本人', m.ename '上级', e.hiredate '本人入职', m.hiredate '上级入职' , e.deptno
FROM emp e, emp m 
WHERE e.mgr = m.empno AND e.hiredate < m.hiredate) tb1 LEFT JOIN dept d
ON tb1.deptno = d.deptno;

思路2：先将emp和dept表 左连接， 将结果作为一张表，然后在这张表里面查出入职时间<上级入职时间的员工

SELECT tb1.empno, tb1.ename, tb1.hiredate, tb1.dname
FROM (SELECT  emp.*, dept.dname
FROM emp LEFT JOIN dept
ON emp.deptno = dept.deptno) tb1, emp
WHERE tb1.hiredate < emp.hiredate AND tb1.mgr = emp.empno;

4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;

6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

SELECT e.ename
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');

SELECT e.ename
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = '销售部'

7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

SELECT e.*, d.dname, m.ename, s.grade
FROM emp e 
  NATURAL LEFT JOIN dept d
  LEFT JOIN emp m ON m.empno=e.mgr
  LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp);

8.列出与庞统从事相同工作的所有员工及部门名称。

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');

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

SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)

SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

10.列出在每个部门工作的员工数量、平均工资。

SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;