2020.4.22

一、复杂查询练习
1、 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
1、确定所需要的数据表：

• emp表：可以查询出员工的数量；
• dept表：部门名称；
• emp表：统计信息；

2、确定已知的关联字段：

• emp.deptno=dept.deptno；　　

第一步：找出至少有一个员工的部门编号
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno)>1;
第二步：找到部门名称，肯定使用部门表，因为现在的数据量较小，所以可以将之前的emp表和dept表两个进行连接，统一采用多字段分组的方式查询；
SELECT d.deptno,d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)
GROUP BY d.deptno,d.dname
HAVING COUNT(e.empno)>1;
第三步：依然需要继续统计
SELECT d.deptno,d.dname,COUNT(e.empno),AVG(sal),MIN(sal),MAX(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)
GROUP BY d.deptno,d.dname
HAVING COUNT(e.empno)>1;
2、 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
1、确定所需要的数据表：

• emp表：查询出“SMITH”或“ALLEN”工资；
• emp表：最终的显示需要编号、姓名；
• emp表：领导的姓名，自身关联；
• dept表：部门名称；

2、确定已知的关联字段：

• 雇员和领导：emp.mgr=memp.empno；
• 雇员和部门：emp.deptno=dept.deptno；

第一步：找出“SMITH”或“ALLEN”的工资
SELECT sal FROM emp WHERE ename IN(‘SMITH’,’ALLEN’);
第二步：以上的查询返回的多行单列的记录，按照子查询的要求在WHERE子句中写合适，所以这个时候将上面的查询作为一个子查询出现，继续查询符合此要求的员工的编号、姓名。

SELECT e.empno,e.ename
FROM emp e
WHERE e.sal>ALL(
SELECT sal
FROM emp
WHERE ename IN(‘SMITH’,’ALLEN’));

第三步：查询出部门的名称，引入部门表，同时增加消除笛卡尔积的条件

SELECT e.empno,e.ename,d.dname
FROM emp e,dept d
WHERE e.sal>ALL(
SELECT sal
FROM emp
WHERE ename IN(‘SMITH’,’ALLEN’))
AND e.deptno=d.deptno;

第四步：领导的信息需要emp表自身关联

SELECT e.empno,e.ename,d.dname,m.ename
FROM emp e,dept d,emp m
WHERE e.sal>ALL(
SELECT sal
FROM emp
WHERE ename IN(‘SMITH’,’ALLEN’))
AND e.deptno=d.deptno
AND e.mgr=m.empno(+);

3、 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
1、确定所需要的数据表：

• emp表：员工的编号、姓名；
• emp表：领导的编号、姓名、计算年薪；

2、确定已知的关联字段：emp.mgr=memp.empno；
SELECT e.empno,e.ename,m.empno,m.ename,(m.sal+NVL(m.comm,0))*12 income
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
ORDER BY income DESC;
4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
1、确定所需要的数据表：

• emp表：雇员的编号、姓名；
• emp表：求出领导的工作日期；
• dept表：部门名称、位置；
• emp表：统计部门人数；

2、确定已知的关联字段：

• 雇员和部门：emp.deptno=dept.deptno；
• 雇员和领导：emp.mgr=memp.empno；

第一步：列出受雇日期早于其直接上级的所有员工的编号、姓名 —— 自身关联emp表。
SELECT e.empno,e.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+) AND e.hiredate第二步：加入部门信息，继续引入部门表
SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno(+) AND e.hiredateAND e.deptno=d.deptno;
额外提问，此时的笛卡尔积：emp表的14条 emp表的14条 dept表的4条；
第三步：统计部门人数，此时由于要使用统计函数，而且以上的查询也无法再直接出现统计函数，所以使用子查询完成

SELECT e.empno,e.ename,d.dname,d.loc,temp.count
FROM emp e,emp m,dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno) temp
WHERE e.mgr=m.empno(+) AND e.hiredate AND e.deptno=d.deptno
AND e.deptno=temp.dno;

当查询显示的时候需要统计信息，但是又不能直接使用统计函数查询的话，通过子查询在FROM子句之后进行统计。
5、列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
1、确定所需要的数据表：

• dept表：部门的信息；
• emp表：求出所有的统计信息；

2、确定已知的关联字段：emp.deptno=dept.deptno；
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc;