oracle复杂查询的练习题
练习一、列出薪金高于在部门30工作的所有员工的薪金的员工的姓名、薪金、部门名称、部门人数
思考步骤：
1、确定需要使用的数据表
一、emp：员工的姓名、薪金
二、dept : 部门表：部门名称
三、emp： 部门人数：需要使用统计字段
2、确定已知的关联字段
关联字段：emp.deptno=dept.deptno
1、查询出部门30工作的所有员工的薪金
select sal from emp where deptno=30; ---查询出来的数据为多行单列数据，需要大于这里面的所有数据，需要 all
2、找出大于以上条件的所有的人员信息
select ename ,sal
from emp
where sal >all(select sal from emp where deptno=30) ---重点：>all where 子句嵌套
3、显示部门名称---需要关联dept表
select e.ename ,e.sal ,d.dname
from emp e,dept d
where e.sal >all(
select sal from emp where deptno=30 )
and e.deptno=d.deptno; -----注意，在from后面的逗号，不能多加逗号
4、统计部门人数：
select e.ename ,e.sal ,d.dname, temp.count
from emp e,dept d,(
select deptno,count(empno)count from emp group by deptno) temp
where e.sal >all(
select sal from emp where deptno=30 )
and e.deptno=d.deptno
and e.deptno=temp.deptno;
----注意两点：如果需要统计人数，则需要进行分组
但是在使用分组的时候，select子句中只能出现分组的字段与统计函数，而不能出现其他我们需要的字段，因此，我们采用from的子句进行操作，现将其分组后作为一个临时表再进行关联操作
注意，有几个关联的动作就需要做几次字段关联来消除笛卡尔积
练习二：列出与‘Scott’从事相同工作的所有员工及部门名称、部门人数、领导姓名。
确定要使用的数据表：
1、emp：确定工作、员工姓名、领导姓名需要根据编码显示出来
2、dept：确定部门的名称
3、部门人数
确定已知的关联字段：
emp.deptno=dept.deptno
二、列出与‘Scott’从事相同工作的所有员工及部门名称、部门人数、领导姓名。
确定要使用的数据表：
1、emp：确定工作、员工姓名、领导姓名需要根据编码显示出来
2、dept：确定部门的名称
3、部门人数
确定已知的关联字段：
emp.deptno=dept.deptno
emp.mgr=emp.empno ---自身关联很重要
1.试下怎样找出该员工的领导，找出scott的领导
select mgr from emp where ename='SCOTT';
SELECT ename FROM EMP where empno=（select mgr from emp where ename='SCOTT'）;
2、找出与scott工作相同的同事
select job from emp where ename ='SCOTT';
SELECT ename FROM EMP WHERE JOB =(select job from emp where ename ='SCOTT');
SELECT ename mgrname FROM EMP where empno=(select MGR from emp where ename ='SCOTT');
3、部门名称，需要关联另一张表
SELECT e.empno, e.ename ,d.dname
FROM EMP e,dept d
WHERE JOB =(select job from emp where ename ='SCOTT')
and e.deptno=d.deptno;
4.部门人数，部门人数需要将数据进行分组，分组后统计
select count(empno) from emp group by deptno; ---将其分组后作为一个新的函数进行关联
SELECT e.empno, e.ename ,d.dname, temp.count ，ename mgrname
FROM EMP e,dept d,(
select deptno,count(empno) count from emp group by deptno) temp
WHERE JOB =(select job from emp where ename ='SCOTT')
and e.deptno=d.deptno
and e.deptno=temp.deptno;
----在select取值的时候，你必须要把你需要使用的字段取出来
5、显示领导，与自身关联
SELECT e.empno, e.ename ,d.dname, temp.count ，c.ename mgrname
FROM EMP e,dept d,(
select deptno,count(empno) count from emp group by deptno) temp,emp c
WHERE e.JOB =(select job from emp where ename ='SCOTT')
and e.deptno=d.deptno
and e.deptno=temp.deptno
and e.mgr=c.empno;
6、去掉重复的信息：
SELECT e.empno, e.ename ,d.dname, temp.count ，c.ename mgrname
FROM EMP e,dept d,(
select deptno,count(empno) count from emp group by deptno) temp,emp c
WHERE e.JOB =(select job from emp where ename ='SCOTT')
and e.deptno=d.deptno
and e.deptno=temp.deptno
and e.mgr=c.empno
and e.ename!='SCOTT';
三、列出薪金比‘SMITH’或者‘ALLEN’多的所有员工的编号、姓名、部门名称、领导名称、部门人数、平均工资、最高工资、最低工资
确定要使用的数据表：
1、emp：确定员工编号、员工姓名、领导姓名需要根据编码显示出来
2、dept：确定部门的名称
3、部门人数，根据分组后计算出来
确定已知的关联字段：
emp.deptno=dept.deptno
emp.mgr=emp.empno ---自身关联很重要，用于显示出领导的名称
count、max、min 、avg ----用于计算最高、最低、平均工资
练习三：
列出薪金比‘SMITH’或者‘ALLEN’多的所有员工的编号、姓名、部门名称、领导名称、部门人数、平均工资、最高工资、最低工资
确定要使用的数据表：
1、emp：确定员工编号、员工姓名、领导姓名需要根据编码显示出来
2、dept：确定部门的名称
3、部门人数，根据分组后计算出来
确定已知的关联字段：
emp.deptno=dept.deptno
emp.mgr=emp.empno ---自身关联很重要，用于显示出领导的名称
count、max、min 、avg ----用于计算最高、最低、平均工资
1、找出薪金比‘SMITH’或者‘ALLEN’多的所有员工的编号、姓名
select sal from emp where ename='SMITH' or ename ='ALLEN' ; ---- >any 大于他们中的最小值就可以了
select empno ,ename ,sal
from emp
where sal >any (select sal from emp where ename='SMITH' or ename ='ALLEN');
2、显示部门名称，需要关联dept表
select e.empno ,e.ename ,e.sal ,d.dname
from emp e, dept d
where sal >any (select sal from emp where ename='SMITH' or ename ='ALLEN')
and e.deptno=d.deptno;
3、计算部门人数，需要按照部门分组,显示计算最高工资、最低工资、平均工资
select deptno, count(empno) from emp group by deptno;
select e.empno ,e.ename ,e.sal ,d.dname ,temp.count, temp.AVG , temp.MAX, temp.MIN
from emp e, dept d ,(
select deptno, count(empno) count ,AVG(SAL) AVG,MAX(SAL) MAX,MIN(SAL) MIN from emp group by deptno) temp
where sal >any (select sal from emp where ename='SMITH' or ename ='ALLEN')
and e.deptno=d.deptno
and e.deptno=temp.deptno;
4.显示领导名称
select e.empno ,e.ename ,e.sal ,d.dname , c.ename mgrname,temp.count, temp.AVG , temp.MAX, temp.MIN
from emp e, dept d ,(
select deptno, count(empno) count ,AVG(SAL) AVG,MAX(SAL) MAX,MIN(SAL) MIN from emp group by deptno) temp ,emp c
where e.sal >any (select sal from emp where ename='SMITH' or ename ='ALLEN')
and e.deptno=d.deptno
and e.deptno=temp.deptno
and e.mgr=c.empno;
5.去掉重复数据并进行检查
select e.empno ,e.ename ,e.sal ,d.dname , c.ename mgrname,temp.count, temp.AVG , temp.MAX, temp.MIN
from emp e, dept d ,(
select deptno, count(empno) count ,AVG(SAL) AVG,MAX(SAL) MAX,MIN(SAL) MIN from emp group by deptno) temp ,emp c ----这边是统计的数据，是不可以直接放在select语句中的，否则会造成其他类型的字段不可以显示
where e.sal >any (select sal from emp where ename='SMITH' or ename ='ALLEN')
and e.deptno=d.deptno
and e.deptno=temp.deptno
and e.mgr=c.empno(+) ---为了将没有领导的员工信息显示出来
and e.ename not in('SMITH','ALLEN'); ---为了排除题中已涉及的数据（去掉重复）
练习四：列出受雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
确定要使用的数据表：
1、emp：确定员工编号、员工姓名、领导姓名需要根据编码显示出来，及雇佣日期
2、dept：确定部门的名称、部门位置
3、部门人数，根据分组后计算出来
确定已知的关联字段：
emp.deptno=dept.deptno
emp.mgr=emp.empno ---自身关联很重要，用于显示出领导的名称
count、max、min 、avg ----用于计算最高、最低、平均工资
练习四：列出受雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
确定要使用的数据表：
1、emp：确定员工编号、员工姓名、领导姓名需要根据编码显示出来，及雇佣日期
2、dept：确定部门的名称、部门位置
3、部门人数，根据分组后计算出来
确定已知的关联字段：
emp.deptno=dept.deptno
emp.mgr=emp.empno ---自身关联很重要，用于显示出领导的名称
count、max、min 、avg ----用于计算最高、最低、平均工资
1、对雇佣日期进行计算 hiredate 关联自己可以直接使用关系： emp.mgr=emp.empno
select e.empno ,e.ename ,e.hiredate 员工时间 ,c.hiredate 上司时间
from emp e,emp c
where e.hiredate<c.hiredate
and e.mgr=c.empno;
2、显示部门名称与部门位置，需要关联dept表
select e.empno ,e.ename ,e.hiredate 员工时间 ,c.hiredate 上司时间 ,d.dname ,d.loc
from emp e,emp c ,dept d
where e.hiredate<c.hiredate
and e.mgr=c.empno
and e.deptno=d.deptno;
3、计算部门的人数，需要用group by 进行分组
select deptno ,count(empno) count
from emp
group by deptno;
select e.empno ,e.ename ,e.hiredate 员工时间 ,c.hiredate 上司时间 ,d.dname ,d.loc ,temp.count
from emp e,emp c ,dept d ,(select deptno ,count(empno) count
from emp
group by deptno
) temp
where e.hiredate<c.hiredate
and e.mgr=c.empno
and e.deptno=d.deptno
and e.deptno=temp.deptno;
练习五：列出所有‘CLERK(办事员)’的姓名及部门名称、部门人数、工资等级
确定要使用的数据表：
1、emp：确定员工姓名及工作
2、dept：确定部门的名称
3、emp：部门人数，根据分组后计算出来
4、salgrade：工资等级 grade
确定已知的关联字段：
emp.deptno=dept.deptno
emp.mgr=emp.empno ---自身关联很重要，用于显示出领导的名称
1、找出所有job为‘CLERK’的员工信息
select ename , sal
from emp
where job='CLERK';
2、显示部门名称，关联dept表
select e.ename , e.sal ,d.dname
from emp e ,dept d
where job='CLERK'
and e.deptno=d.deptno;
3、计算部门人数，使用group by分组
select deptno ,count(empno) count
from emp
group by deptno
select e.ename , e.sal ,d.dname, temp.count
from emp e ,dept d ,(
select deptno ,count(empno) count
from emp
group by deptno) temp
where job='CLERK'
and e.deptno=d.deptno
and e.deptno=temp.deptno;
4、计算薪资等级
查询一下scott的工资在几级？
select s.grade ,e.ename,e.sal
from salgrade s, emp e
where e.sal between s.losal and s.hisal;
select e.ename , e.sal ,d.dname, temp.count, s.grade
from emp e ,dept d ,(
select deptno ,count(empno) count
from emp
group by deptno) temp ,salgrade s
where job='CLERK'
and e.deptno=d.deptno
and e.deptno=temp.deptno
and e.sal between s.losal and s.hisal;
