SQL复杂查询（一）

1.列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。

确定所需要的数据表
emp表：员工的部门编号，平均工资，最低工资，最高工资
dept表：部门名称

确定已知的关联字段
emp.deptno=dept.deptno
1.1 先得到员工数大于1的部门

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

1.2 找到部门名称

SELECT 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;

1.3 统计各种信息
SQL分组：http://blog.csdn.net/dingchenxixi/article/details/70193634
select子句之后，只能出现分组的字段和统计函数，其它的字段不能出现
由于AVG(),MAX(),MIN()为统计函数，可以直接使用

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”多的所有员工的编号、姓名、部门名称、其领导姓名。
确定所需要的数据表
emp表：列出“SMITH”或“ALLEN”的薪金
emp表：员工的编号，姓名
dept表：部门名称

确定已知的关联字段
emp.deptno=dept.deptno;
emp1.mgr = emp2.empno （自关联查询）

2.1 先查询出“SMITH”或“ALLEN”的薪金

SELECT sal FROM emp WHERE ename='SMITH' OR ename='ALLEN'

或者

SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN');

2.2 子查询，查询比“SMITH”或“ALLEN”薪金多的员工编号，姓名，部门名称

SELECT e.empno,e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.sal>ANY(SELECT sal FROM emp WHERE ename='SMITH' OR ename='ALLEN') 
      AND e.deptno=d.deptno;

2.3 自身关联查询

SELECT e.empno,e.ename,e.sal,d.dname,m.ename leader FROM  emp e,emp m,dept d 
      WHERE e.sal>ANY(SELECT sal FROM emp WHERE           ename='SMITH' OR ename='ALLEN') 
      AND e.deptno=d.deptno AND e.mgr=m.empno(+);

3.列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列

确定所需要的数据表
emp表1：员工的编号，姓名，上级的编号
emp表2：上级的姓名，领导的年工资

确定已知的关联字段
emp1.mgr=emp2.empno

SELECT e.empno,e.ename,m.ename leader,m.empno leader_no , NVL((m.sal+NVL(m.comm,0))*12 ,0) INCOME FROM emp e,emp m  
       WHERE e.mgr=m.empno(+)  ORDER BY INCOME DESC ;

4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数

确定所需要的数据表：
emp表：员工的编号，姓名
dept表：部门名称，部门位置
emp表：部门人数

确定已知的关联字段：
emp.deptno=dept.deptno 部门人数
emp1.mgr = emp2.empno 上级领导

4.1 受雇日期早于其直接上级的所有员工的编号，姓名

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

4.2 列出满足条件的部门名称、部门位置

 SELECT e.empno,e.ename,d.dname ,d.loc 
       FROM emp e,emp m,dept d 
       WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno;

4.3 统计部门人数，此时由于要使用统计函数，而以上的查询无法再直接出现统计函数，所以使用子查询完成

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

5.列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门

确定所需要的数据表：
dept表：部门名称
emp表：员工平均工资
emp表：统计员工数量

确定已知的关联字段：
emp.deptno=dept.deptno

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