Oracle面试题汇总--3

SQL> --09.查询所有工资高于平均工资（平均工资包括所有员工）的销售人员（‘SLESMAN’）SQL> select * from emp where job='SLESMAN' and sal > (select avg(sal) from emp);                                SQL> --10.显示所有职员的姓名及其所在的名称和工资SQL> select ename, dname,sal from emp e,dept d where e.deptno=d.deptno;SQL> --11.查询在研究部（‘RESARCH’）工作员工的编号，姓名，工作部门，工作所在地SQL> select empno ,ename ,dname,loc from emp e,dept d where e.deptno=d.deptno     and dname='RESEARCH';SQL> --12.查询各个部门的名称和人数SQL> select * from (select deptno ,count(*) from emp group by deptno) e inner join dept d on e.deptno=d.deptno;                     SQL> --13.查询各个职位员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位SQL> select count(*),job from emp where sal>(select avg(sal) from emp) group by job;                                                              SQL> --14.查询工资相同的员工的工资和姓名SQL> select * from emp e where(select count(*) from emp where sal=e.sal group by sal)>1;SQL> --15.查询工资最高的3名员工信息SQL> select * from (select * from emp order by sal desc) where rownum<=3;SQL> --16.按工资进行排名，排名从1开始，工资相同排名相同(如果两人并例第一名则没有第二名，从第三名开始排)SQL> select e.* ,(select count(*) from emp where sal>e.sal)+1 rank from emp e order by rank;SQL> --17.求入职日期相同的（年月日相同）的员工SQL> select * from emp e where (select count(*) from emp where e.hiredate=hiredate) > 1;SQL> --18.查询每个部门的最高工资SQL> select deptno ,max(sal) maxsal from emp group by deptno order by deptno;SQL> --19.查询每个部门，每种职位的最高薪资SQL> select deptno ,job ,max(sal) from emp group by deptno,job order by deptno ,job;SQL> --20.查询每个员工的信息级工资及级别SQL> select * from (select e.*,rownum m from (select * from emp order  by sal desc)e where rownum <=10) where m>5;SQL> --22.查询各部门工资最高的员工信息SQL> select * from emp e where e.sal =(select max(sal) from emp where (deptno=e.deptno));SQL> --23.查询每个部门工资最高的前两名员工SQL> select * from emp e where (select count(*) from emp where sal>e.sal and e.deptno=deptno)<2    order by deptno ,sal desc;SQL> --24.查询出有3个以上下属的员工信息SQL> select * from emp e where (select count(*) from emp where e.empno=mgr)>2;SQL> --25。查询所有大于本部们平均工资的员工信息SQL> select * from emp e where sal>(select avg(sal) from emp where (deptno=e.deptno)) order by deptno;SQL> --26.查询平均工资最高的部门信息SQL> select avg(sal) avgsal ,deptno from emp group by deptno;SQL> --27.查询大于各部门总工资的平均值的部门信息SQL> --1、求每个部门总工资SQL> select sum(sal) sumsal ,deptno from emp group by deptno;SQL> --2、每个部门的平均值SQL> select avg(sum(sal)) from emp group by deptno;SQL> --3、求大于总工资平均值的部门信息，连接步骤SQL>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;                                 SQL> --29.查询没员工的部门的信息SQL>  select d.* from dept d left join emp e on(e.deptno=d.deptno) where empno is null;