Oracle复杂查询案例

 

复杂查询案例：
1、列出工资高于30部门工作的
     所有员工的工资的员工姓名和工资、部门名称，部门人数。
     --已知条件：
     数据表：emp和dept
     关联字段：emp.deptno=dept.deptno
                 从 emp查询：姓名、工资，人数
     从dept查询：部门名称
    

SELECT e.ename,e.sal, d.dname, temp.count
         FROM emp e, dept d,
    (SELECT deptno,COUNT(deptno) count FROM emp GROUP BY deptno) temp
         WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30)
         AND e.deptno=d.deptno
    AND temp.deptno=d.deptno ;

2、列出与“SCOTT”从事相同工作的
     所有员工编号、姓名、部门名称、部门人数、领导姓名。
     --已知条件：
     数据表：emp和dept
     关联字段：emp.deptno=dept.deptno（雇员与部门）、emp.mgr=emp.empno（雇员与领导）
                 从 emp查询：员工编号、姓名、部门人数、领导姓名
     从dept查询：部门名称

 SELECT e.empno,e.ename,e.job,d.dname,temp.count,m.ename
    FROM emp e,dept d,
    (SELECT deptno dno,count(deptno) count FROM emp GROUP BY deptno) temp,emp m
                WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT')
    AND e.ename<>'SCOTT'
    AND e.deptno=d.deptno
    AND temp.dno=d.deptno
    AND e.mgr=m.empno;

  3、列出工资比“SMITH”或“ALLEN”多的
     所有员工的编号、姓名、部门名称、领导姓名、部门人数、平均工资、最高和最低工资。
    --已知条件：
     数据表：emp和dept
     关联字段：emp.deptno=dept.deptno（雇员与部门）、emp.mgr=emp.empno（雇员与领导）
                 从 emp查询：员工编号、姓名、部门人数、领导姓名、平均工资、最高和最低工资
     从dept查询：部门名称

 SELECT e.empno,e.ename,d.dname,temp.count,temp.avg,temp.max,temp.min,m.ename
    FROM emp e,emp m,dept d,
    (SELECT deptno dno,COUNT(deptno) count,AVG(sal) avg,MAX(sal) max,MIN(sal) min FROM emp GROUP BY deptno) temp
    WHERE e.sal>ANY(SELECT sal FROM emp WHERE ename in('SMITH','ALLEN'))
    AND e.ename NOT IN('SMITH','ALLEN')
    AND e.deptno=d.deptno
    AND d.deptno=temp.dno
    AND e.mgr=m.empno;

 

4、列出受雇日期早于其直接上级的
    所有员工的编号、姓名、部门名称、部门位置、部门人数。
 --已知条件：
     数据表：emp和dept
     关联字段：emp.deptno=dept.deptno（雇员与部门）、emp.mgr=emp.empno（雇员与领导）
                 从 emp查询：员工的编号、姓名、部门位置、部门人数
     从dept查询：部门名称

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

 

5、列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数、工资等级。

 --已知条件：
     数据表：emp和dept
     关联字段：emp.deptno=dept.deptno（雇员与部门）
                 从 emp查询：员工姓名、部门人数
     从dept查询：部门名称
     

SELECT e.ename,d.dname,temp.count,s.grade
    FROM emp e,dept d,salgrade s,
    (SELECT deptno dno,COUNT(deptno) count FROM emp GROUP BY deptno) temp
    WHERE e.deptno=d.deptno
    AND temp.dno=e.deptno
    AND job='CLERK'
    AND e.sal BETWEEN s.losal AND s.hisal;