Oralce经典练习题—子查询

基础知识

当完成查询时，需要经过多个步骤才能完成，并且一个查询结果是另外一个查询结果条件时，可以想起子查询：

如，列出高于平均工资的所有员工。要完成此功能，需要求出平均工资和列出所有员工，同时求平均工资是所有员工的条件。

select * from emp where sal>(select avg(sal)from emp);

练习

 

--1.列出员工表中每个部门的员工数，和部门 no

select deptno,count(*)from emp group by deptno;

--2.列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称

select (select dname from dept where deptno=e.deptno),count(*)from emp e group by e.deptno having count(*)>3;

select d.dname,de.cou from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>3) de where d.deptno=de.deptno;

--3.找出工资比 JONES 多的员工

select * from emp where sal>(select sal from emp where ename='JONES');

--4.列出所有员工的姓名和其上级的姓名

select e1.ename as lower,e2.ename as upper from emp e1,emp e2 where e1.mgr=e2.empno(+);

select e1.ename as lower,e2.ename as upper from emp e1 left join emp e2 on e1.mgr=e2.empno;

--5.以职位分组，找出平均工资最高的两种职位

select * from(select job,avg(sal) from emp group by job order by avg(sal) desc)where rownum<3;

--6.查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称

select e.ename,d.dname from emp e,dept d where e.deptno!=20 and e.sal>(select max(sal) from emp where deptno=20) and e.deptno=d.deptno;

--7.得到平均工资大于 2000 的工作职种

select job from emp group by job having avg(sal)>2000;

--8.分部门得到工资大于 2000 的所有员工的平均工资，并且平均工资还要大于 2500

select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500;

--9.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置

select * from dept where deptno=(select * from (select deptno from emp group by deptno order by sum(sal)) where rownum=1);

--10.分部门得到平均工资等级为 2 级（等级表）的部门编号

select de.deptno from salgrade s,(select deptno,avg(sal) avgsal from emp group by deptno) de where s.grade=2 and de.avgsal between s.losal and s.hisal;

--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 级的员工的员工名字，部门名字，部门位置

select e.ename,d.dname,d.loc from emp e,dept d,salgrade sg where e.sal between sg.losal and sg.hisal and sg.grade!=4 and e.deptno=d.deptno;

select e.ename,d.dname,d.loc from emp e,dept d,(select * from salgrade where grade=4) s where e.deptno=d.deptno and (e.sal<s.losal or e.sal>s.hisal);

--13.查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资

select avg(sal) from emp where job in(select job from emp where ename in ('MARTIN','SMITH'));

--14.查找出不属于任何部门的员工

select * from emp where deptno is null or deptno not in (select deptno from dept);

--15.按部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）

select d.dname,d.loc from dept d,(select rownum r,deptno from (select deptno from emp group by deptno order by count(*) desc)) de where de.r between 2 and 5 and d.deptno=de.deptno;

--16.查询出 king 所在部门的工作年限最大的员工名字

select ename from emp where deptno=(select deptno from emp where ename='KING') and hiredate=(select min(hiredate) from emp where deptno= (select deptno from emp where ename='KING'));

select * from (select ename from emp where deptno=(select deptno from emp where ename='KING') order by hiredate)where rownum=1;