19.多表查询练习答案

2018-12-24 本文已影响16人 Liuzhl

查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
列：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;
```
列出所有员工的姓名及其直接上级的姓名
列：员工姓名、上级姓名
表：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

列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列：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;

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

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

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

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

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

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 = '销售部'

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

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;

19.多表查询练习答案

猜你喜欢

热点阅读