oracle 复杂操作

复杂查询：案例：
------------------------
1.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门编号。部门人数
确定要是使用的数据表：
  ·emp 表 姓名 、薪金
  ·dept 表 部门名称
  ·emp 表 统计出部门的人数
确定已知的管理字段：
   ·雇员和部门 emp.deptno = dept.deptno;
第一步：找出部门30工作的所有雇员你的工资（返回多行单列）
SELECT sal  FROM emp  WHERE deptno = 30;
第二步 找的员工的姓名
SELECT e.ename,e.sal
FROM EMP e
WHERE sal>ALL(SELECT sal  FROM emp  WHERE deptno = 30);
第三步： 查部门信息
SELECT e.ename,e.sal,d.dname
FROM EMP e，dept d
WHERE sal>ALL(SELECT sal  
FROM emp  
WHERE deptno = 30)
AND e.deptno = d.deptno;


第四步：
SELECT deptno dno ,COUNT(empno) count
FROM emp
GROUP BY deptno;
第五步：
SELECT e.ename,e.sal,d.dname,temp.count
FROM EMP e，dept d(
SELECT deptno dno ,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp
WHERE sal>ALL(SELECT sal  
FROM emp  
WHERE deptno = 30)
AND e.deptno = d.deptno
AND temp.dno = d.deptno;

temp ---—-→ 是除去笛卡尔积的
 --------------------------------------------------------------------------
2.列出“SCOTT”从事相同工作的所有员工及部门名称，部门人数，领导姓名。
·确定要使用的数据表
   ·emp 表 员工信息
   ·dept表  部门名称
   ·emp 表 统计出部门人数
   ·emp 表 找到领导信息
·确定已知的关联字段
   ·雇员和部门 emp.deptno = dept.deptno;
   ·雇员和领导：emp.mgr  = memp.empno.
第一步：找到SCOTT 的工作，
SELECT job
FROM emp
WHERE ename= 'SCOTT';
第二步：找的所有的符合此要求的人
SELECT e.ename,e.job,e.sal
FROM emp e
WHERE e.job=(
SELECT job
FROM emp
WHERE ename= 'SCOTT');
第三步：查找的对应的部门的名称信息加入dept表，同时消除笛卡尔积的条件
SELECT e.ename,e.job,e.sal,d.dname
FROM emp e，dept d
WHERE e.job=(
SELECT job
FROM emp
WHERE ename= 'SCOTT')
and e.deptno = d.deptno;


第四步  部门人数，单独统计，然后放在from中
SELECT e.ename,e.job,e.sal,d.dname,temp.count
FROM emp e，dept d,
(
SELECT deptno dno ,COUNT(EMPNO) count
FROM emp
GROUP BY deptno
)temp
WHERE e.job=(
SELECT job
FROM emp
WHERE ename= 'SCOTT')
and e.deptno = d.deptno
- and d.deptno = temp.dno;
第五步：查找出雇员对应的领导信息，使用emp表实现自身关联操作
-------------------------------------------------------------------------------
SELECT e.ename,e.job,e.sal,d.dname,temp.count,m.ename
FROM emp e，dept d,
(
SELECT deptno dno ,COUNT(EMPNO) count
FROM emp
GROUP BY deptno
)temp,emp m
WHERE e.job=(
SELECT job
FROM emp
WHERE ename= 'SCOTT')
and e.deptno = d.deptno
and d.deptno = temp.dno
and e.mgr = m.empno
第六步 消除掉SCOTT的 and e.ename <>'SCOTT';
-----------------------------------------------------------
3.列出薪金比“SMIYH”或"ALLEN"多的所有员工的编号、姓名、部门名称、其领导姓名，平均工资，最多和最低工资
·确定要使用的数据表
   ·emp 表 员工编号，姓名
   ·dept表  部门名称
   ·emp 表 统计出部门人数
   ·emp 表 找到领导信息
·确定已知的关联字段
   ·雇员和部门 emp.deptno = dept.deptno;
   ·雇员和领导：emp.mgr = memp.empno.
第一步：找的“SMIYH”和"ALLEN"的薪金
SELSECT sal
FROM emp
WHERE ename IN(“SMIYH”,"ALLEN");
2.
SELECT e.empno,e.ename,e.sal
FROM emp e
WHERE e.sal>ANY(
SELECT sal
FROM emp
WHERE ename IN('SMIYH','ALLEN')
AND e.ename NOT IN('SMIYH','ALLEN'));
3.找的领导信息


SELECT e.empno,e.ename,e.sal,m.ename
FROM emp e ,emp m
WHERE e.sal>ANY(
SELECT sal
FROM emp
WHERE ename IN('SMIYH','ALLEN')
AND e.ename NOT IN('SMIYH','ALLEN')
AND e.mgr = m.empno(+)
);


4.统计部门的信息在from子句中完成


SELECT e.empno,e.ename,e.sal,m.ename,temp.count,temp.avg,temp.max,temp.min,d.dname
FROM emp e,emp m,(
SELECT deptno dno ,COUNT(empno)
count,AVG(sal)avg,MAX(sal)max,MIN(sal)min
FROM emp
GROUP BY deptno) temp,dept d
WHERE e.sal>ANY(
SELECT sal
FROM emp
WHERE ename IN('SMIYH','ALLEN'))
AND e.ename NOT IN('SMIYH','ALLEN')
AND e.mgr = m.empno(+)
AND temp.dno = d.deptno
AND e.deptno = d.deptno;
---------------------------------------------------------------------------
4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
·确定要使用的数据表
   ·emp 表 员工编号，姓名
   ·dept表  部门名称、部门位置
   ·emp 表 统计出部门人数
   ·emp 表 找到领导雇佣日期
·确定已知的关联字段
   ·雇员和部门 emp.deptno = dept.deptno;
   ·雇员和领导：emp.mgr = memp.empno.


1.实现emp的自身关联
SELECT e.empno,e.ename
FROM emp e ,emp m
WHERE e.mgr = m.empno(+) 
AND e.hiredate<m.hiredate;
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;
3.找的部门人数
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<m.hiredate
AND e.deptno = d.deptno;