oracle高级查询练习题
1、 列出员工表中每个部门的员工数和部门编号
Select deptno,count(*) from emp group by deptno;
补充1：列出员工表中，员工人数大于3的部门编号和员工人数
Select deptno,count(*) from emp group by deptno having count(*)>3;
补充2：列出员工表中，员工人数大于3的部门的部门编号，部门名称和部门位置
答案1：Select d.* from dept d,(select deptno,count(*) from emp group bydeptno having count(*)>3)x where d.deptno=x.deptno;
答案2：select * from dept where deptno in (select deptno from emp group bydeptno having count(*)>3);
补充3：列出员工表中，员工人数大于3的部门的部门编号，名称，位置和员工人数。
Select d.*,x.co from dept d，(selectdeptno,count(*) co from emp group by deptno having count(*)>3)x whered.deptno=x.deptno;(注意看聚合函数的处理方式)
2、 列出员工表中每个部门的员工数（员工数必须大于3），和部门名称
(1).先列出员工数大于3的部门号
Select deptno,count(*) from emp group by deptno having count(*)>3;
（2）列出员工表中每个部门的员工数（员工数必须大于3），和部门名称
Select x.co,d.dname from dept d,( Select deptno,count(*) cofrom emp group by deptno having count(*)>3)x where x.deptno=d.deptno ;
3、 找出工资比JONES多的员工
Select * from emp where sal>(select salfrom emp where ename=’JONES’);Select * from emp where sal>(select sal from emp where ename=’JONES’);
4、 列出所有员工的姓名和其上级的姓名（表的自连接）
Select e1.ename,e2.ename from emp e1,emp e2where e1.mgr=e2.empno;
5、 以职位分组，找出平均工资最高的两种职位
（1）先以职位分组并按平均工资降序排序
Select job,avg(sal)avg_sal from emp group by job order by avg_sal desc;
（2）找到前两位
Select x.* from (Select job,avg(sal) avg_sal from emp group by job order by avg_sal desc)x where rownum <= 2;
补充：查找出不在部门20，且比部门20中任何一个人工资都高的员工的姓名、部门名称
（1）先找出部门20的最高薪水
Select max(sal) from emp where deptno =20;
（2）再找到薪水比（1）高的且不在部门20
Select e.ename,d.dname from emp e,dept dwhere e.deptno=d.deptno and e.sal>( Select max(sal) from emp where deptno =20)and e.deptno!=20;
方法2： select e.ename,e.sal,d.dnamefrom emp e,dept d where e.deptno=d.deptno and e.deptno<>20 ande.sal>all(select sal from emp where deptno=20);
6、 得到平均工资大于2000的工作职种
Select job,avg(sal) from emp group by job havingavg(sal)>2000;
7、 分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500
Select deptno,avg(sal) from emp where sal>2000 group bydeptno having avg(sal)>2500
8、 得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
Select *from dept where deptno in(select x.deptno from (select deptno from emp group bydeptno order by sum(sal))x where rownum=1);
(1) 先按照工资总数升序排序
Select deptno from emp group bydeptno order by sum(sal); --x
(2) 找到第一位（即工资总数最少）
Select x.* from (Select deptnofrom emp group by deptno order by sum(sal))x where rownum=1;
(3) Select * from dept where deptno=( Select x.* from (Selectdeptno from emp group by deptno order by sum(sal))x where rownum=1);
9、 分部门得到平均工资等级为3级（等级表）的部门编号
思路：（1）获取到每个部门的平均工资x
Select deptno,avg(sal) avg_sal from emp group by deptno;
(2)获取到平均工资的等级。需要将x与salgrade做非等值连接。
select x.*,s.* fromx,salgrade s where x.avg_sal between s.losal and s.hisal;
(3) 将第二步中的x用（1）的语句替换 --最终结果
select x.*,s.* from (Selectdeptno,avg(sal) avg_sal from emp group by deptno)x,salgrade s where x.avg_salbetween s.losal and s.hisal and s.grade=3;
10、 查找出部门10和部门20中，工资最高第3名到第5名的员工的员工名字，部门名字，部门位置
(1)部门10和20按工资
Select * from emp where deptno in(10,20) order by sal desc;
（2）排名3-5
Select * from (Select rownum ro ,x.*from (Select * from emp wheredeptno in(10,20) order by sal desc)x )where ro>=3 and ro <=5;
3)员工名字，部门名字，部门
Select e.ename,d.dname,d.loc from (Select * from (Select rownum ro ,x.*from(Select * from emp where deptno in(10,20) order by sal desc)x )where ro>=3and ro <=5)e,dept d where e.deptno=d.deptno;
11、 查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入
Select e1.empno,e1.ename,e1.sal+nvl(e1.comm,0)from emp e1,emp e2 where e1.mgr=e2.empno and e1.sal+nvl(e1.comm,0)>e2.sal+nvl(e2.comm,0);
12、 查找出工资等级不为4级的员工的员工名字，部门名字，部门位置
(1)工资等级
Select e.*,s.grade from emp e,salrade s where e.sal between s.losal ands.hisal;
(2)工资等级不为4
Select e1.ename,d.dname,d.loc,e1.grade from ( Select e.*,s.grade fromemp e,salgrade s where e.sal between s.losal and s.hisal)e1,dept d whered.deptno=e1.deptno and e1.grade != 4;
方法2：三表连接！！！！！！！！
Select e.ename,d.dname,d.loc,s.grade from emp e,dept d,salgrade s wheree.deptno=d.deptno and e.sal between s.losal and s.hisal and s.grade<>4;
13、 查找出职位和‘MARTIN’或者‘SMITH’一样的员工的平均工资
Select avg(sal) from emp where job in(selectjob from emp where ename in(‘MARTIN’,’SMITH’));
14、 查找出不属于任何部门的员工
Select * from emp where deptno is null;
15、 按照部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
(1)统计各部门员工数,并降序排序
Select count(*), deptno from emp group by deptno order by count(*) desc;
(2)2-5名
Select * from (Select rownum ro,x.* from (Select count(*), deptno fromemp group by deptno order by count(*) desc)x) where ro>=2 and ro<=5;
(3)
Select d.*,x1.* from dept d,( Select * from (Select rownum ro,x.* from(Select count(*), deptno from emp group by deptno order by count(*) desc)x) wherero>=2 and ro<=5)x1 where d.deptno=x1.deptno;
16、 查出KING所在部门的部门号、部门名称、部门人数
(1)king所在的部门
Select deptno from emp where ename=’KING’
(2)
Select d.deptno,d.dname,count(*) from empe,dept d where e.deptno=d.deptno and d.deptno=( Select deptno from emp whereename=’KING’) group by d.deptno,d.dname;
17、 查出KING所在部门的工作年限最大的员工名字
Select ename from emp where deptno=(select deptno from empwhere ename=’KING’) and hiredate=(select min(hiredate) from emp where deptno=( selectdeptno from emp where ename=’KING’));
18、 查出工资成本最高的部门的部门号和部门名称
(1)工资成本
Select sum(sal)+sum(nvl(comm,0)) sum_sal,deptno from emp group by deptno order by sum_sal desc;
(2)找到第一位
Select d.*,x.sum_sal from dept d ,( Select sum(sal)+sum(nvl(comm,0))sum_sal,deptno from emp group by deptno orderby sum_sal desc)x where x.deptno=d.deptno and rownum=1;
