oracle子查询精选面试题,oracle 子查询 经典总结

-----------------------------------------------子查询

--1,单行子查询

--查看工资高于工号为7566员工的所有员工信息

select * from emp

where sal>(select sal from emp where empno=7566);

--2,子查询空值、多值问题

--如果子查询未返回任何行，则主查询也不会返回任何结果

select * from emp where sal>nvl((select sal from emp where empno =8888),0);

select nvl((select sal from emp where empno =8888),0) from dual;

--3,如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

select * from emp where sal>(select sal from emp where empno=7566);

--4,如果子查询返回赴欧行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

--5，多行子查询

select * from emp where sal > any(select avg(sal) from emp group by deptno);

select * from emp where sal > all(select avg(sal) from emp group by deptno);

select * from emp where job in(select job from emp where ename = 'MARTIN' or ename = 'SMITH');

--6,TopN查询

select * from emp where rownum = 1 or rownum = 2;

select * from (select * from emp order by sal desc) where rownum <=5;

--7,分页查询

select * from (select rownum no,e.* from

(select * from emp order by sal desc )e

where rownum<=5) where no>3;

select * from (select rownum no,e.* from

(select * from emp order by sal desc) e )

where  no>3 and no<=5;

--8,exists的执行流程

select * from t1 where t1 where exists (select null from t2 where y=x)

--可以理解成

for  x in(select * from t1)

loop

if(exists(select null from t2 where y=x))

then

output the record

end if

end loop

--对于in和exists的性能区别：

--如果子查询独处的结果集记录较少，主要查询中格的表较大且又有索引时应该用in，

--反之若果外层的猪查询记录较少，子查询种的表大，又有索引时使用exists

--区分in和exists 主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是

--exists,那么以外层表为驱动表，先被访问，如果是in那么先执行子查询，所以我们会以

--驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了另外in是不对null进行处理

--如 select 1 from dual where null in (0,1,1,null);

--练习

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

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

--2,列出员工表中每个部门的员工数(员工数必须大于3)，和部门名称

select d.*,ed.cou from dept d,

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

--3,找出工资比jones高的员工

select * from emp where sal>=(select sal from emp where lower(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,e1.job,e2.ename,e2.job from emp e1,emp e2 where e1.mgr=e2.empno;

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

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

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

select ename,sal from emp join (select min(sal) m from emp where deptno = 20) a on emp.deptno <> 20 and a.m

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

select * from (select job,avg(sal) a from emp group by job) b where b.a>2000 order by b.a desc

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

select deptno,avg(sal),count(*) 部门工资大于2000的人数 from emp where sal >2000 group by deptno

select deptno,avg(sal) from emp group by deptno

select sal from emp where deptno = 30;

--9,得到每个月工资总数最多的那个部门的部门编号,名称,部门所在地,工资总数,部门人数

select a.d from (select deptno d,sum(sal) s,count(*) from emp group by deptno order by s desc)a where rownum<2

select * from DEPT join(select * from (select deptno d,sum(sal) s,count(8) from emp group by deptno order by s desc)a where rownum<2)b on dept.deptno = b.d;

select max(sum(sal)) from emp group by deptno

--10,分部门得到平均工资等级为2级(等级表)的部门编号

select b.dno from salgrade sa,(select deptno as dno,avg(sal) as avgsal from emp

group by deptno) b where sa.grade=4 and b.avgsal between sa.losal and sa.hisal;

--11,查找出部门10和部门20中，工资最高的第3名到工资第5名的员工的员工名字，部门名字，部门所在

select * from (select  rownum no,e.* from

(select empno,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and emp.deptno between 10 and 20 order by emp.sal desc) e)

where no between 3 and 5;

--12,查找出收入(工资+奖金)，下级比自己上级还高的员工编号，姓名，收入

select empno 工资高于上级的员工的编号,ename 工资高于上级的员工的姓名,sal+nvl(emp.comm,0) 工资高于上级的员工的总收入 from emp join (select empno eno,sal+nvl(emp.comm,0) c from emp) a

on emp.mgr=a.eno and emp.sal+nvl(emp.comm,0)>a.c

--13,查出工资等级不为4级的员工的员工名字，部门名字，部门位置

select emp.deptno,emp.ename,emp.sal from emp,salgrade where sal between losal and hisal and grade<>4

select a.en,dept.dname,dept.loc from dept

join(select emp.deptno d,emp.ename en,emp.sal es from emp,salgrade where sal between losal and hisal and grade<>4

) a on dept.deptno = a.d

--14,查找出职位和MARTIN或者SMITH一样的平均员工的平均工资

select avg(a.sal) from

(select * from emp where job =

(select job from emp where ename='MARTIN') or job = (select job from emp where ename='SMITH'))a;

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

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

--15,查找不属于任何部门的员工

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

--16,按部门统计员工数,查出员工数最多的部门中工资第二名到第五名的所有员工信息

select * from(select rownum N,e.* from (select emp.* from emp where deptno =

(select deptno from (select deptno,count(*) c from emp group by deptno order by c desc) where rownum =1)

order by sal desc)e) where N between 2 and 5;

--17,查出king所在部门的部门号\部门名称\部门人数

select a.deptno,dname,a.c from dept join

(select deptno,count(*) c from emp where emp.deptno=(select deptno from emp where ename = 'KING')group by deptno) a on

dept.deptno = a.deptno

--18,查出king所在部门工作年限最大的员工名字

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

--19,查出工资成本最高的部门的部门编号和部门名称

select emp.deptno,dept.dname from emp,dept

where nvl(emp.mgr,0)+sal = (select max(nvl(emp.mgr,0)+sal) from emp)

and emp.deptno = dept.deptno;