Oracle面试题汇总--3

<span style="font-size:18px;">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; </span>