# oracle面试题整理二（10级学员 乔宇整理）

2455人阅读 评论(0)

oracle面试题整理二（10级学员 乔宇整理）

1.查询工资最高的3 名员工信息

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

2. 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第

select rank() over(order by sal) from emp;

3. 求入职日期相同的(年月日相同)的员工

select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1;

4. 查询每个部门的最高工资

select deptno, max(sal) maxsal from emp group by deptno order by deptno;

5. 查询每个部门,每种职位的最高工资

select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;

select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;

7. 查询工资最高的第6-10 名员工

select * from (

select e.*,rownum rn from

(select * from emp order by sal desc) e

where rownum <=10)

where rn > 5;

8. 查询各部门工资最高的员工信息

select * from emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno));

9. 查询每个部门工资最高的前2 名员工

select * from (

select rank() over (partition by deptno order by sal desc) rank, e.* from emp e

) where rank < 3;

10. 查询出有3 个以上下属的员工信息

select * from emp e where

(select count(*) from emp where e.empno = mgr) > 2;

11. 查询所有大于本部门平均工资的员工信息()

select * from emp e where sal >

(select avg(sal) from emp where (deptno = e.deptno))

order by deptno;

12. 查询平均工资最高的部门信息

select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se

where avgsal = (select max(avg(sal)) from emp group by deptno) and d.deptno = se.deptno;

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

select max(avg(sal)) from emp group by deptno;

select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se

where avgsal = (select max(avg(sal)) from emp group by deptno) and d.deptno = se.deptno;

13. 查询大于各部门总工资的平均值的部门信息

select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se

where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = d.deptno;

select sum(sal) sumsal, deptno from emp group by deptno;

select avg(sum(sal)) from emp group by deptno;

select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se

where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = d.deptno;

14. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接

select e.*,sumsal from emp e, (select sum(sal) sumsal, deptno from emp group by deptno) se

where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = e.deptno;

15. 查询没有员工的部门信息

select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null;

0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：1893156次
• 积分：16644
• 等级：
• 排名：第604名
• 原创：209篇
• 转载：76篇
• 译文：0篇
• 评论：703条
阅读排行
评论排行
最新评论