SQL複雜查詢（一）

1.列出至少有一個員工的所有部門編号、名稱，并統計出這些部門的平均工資、最低工資、最高工資。

确定所需要的資料表

emp表：員工的部門編号，平均工資，最低工資，最高工資

dept表：部門名稱

确定已知的關聯字段

emp.deptno=dept.deptno

1.1 先得到員工數大于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.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)>;
           

2.列出薪金比“SMITH”或“ALLEN”多的所有員工的編号、姓名、部門名稱、其上司姓名。

确定所需要的資料表

emp表：列出“SMITH”或“ALLEN”的薪金

emp表：員工的編号，姓名

dept表：部門名稱

确定已知的關聯字段

emp.deptno=dept.deptno;

emp1.mgr = emp2.empno （自關聯查詢）

2.1 先查詢出“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,))* ,) 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;