Oracle面試題匯總–3

SQL> --09.查詢所有工資高於平均工資（平均工資包括所有員工）的銷售人員（‘SLESMAN’）<br />SQL> select * from emp where job='SLESMAN' and sal > (select avg(sal) from emp);<br />SQL> --10.顯示所有職員的姓名及其所在的名稱和工資<br />SQL> select ename, dname,sal from emp e,dept d where e.deptno=d.deptno;<br />SQL> --11.查詢在研究部（‘RESARCH’）工作員工的編號，姓名，工作部門，工作所在地<br />SQL> select empno ,ename ,dname,loc from emp e,dept d where e.deptno=d.deptno<br /> and dname='RESEARCH';<br />SQL> --12.查詢各個部門的名稱和人數<br />SQL> select * from (select deptno ,count(*) from emp group by deptno) e inner join dept d on e.deptno=d.deptno; </p><p>SQL> --13.查詢各個職位員工工資大於平均工資（平均工資包括所有員工）的人數和員工職位<br />SQL> select count(*),job from emp where sal>(select avg(sal) from emp) group by job; </p><p>SQL> --14.查詢工資相同的員工的工資和姓名<br />SQL> select * from emp e where(select count(*) from emp where sal=e.sal group by sal)>1;<br />SQL> --15.查詢工資最高的3名員工資訊<br />SQL> select * from (select * from emp order by sal desc) where rownum<=3;<br />SQL> --16.按工資進行排名，排名從1開始，工資相同排名相同(如果兩人並例第一名則沒有第二名，從第三名開始排)<br />SQL> select e.* ,(select count(*) from emp where sal>e.sal)+1 rank from emp e order by rank;</p><p>SQL> --17.求入職日期相同的（年月日相同）的員工<br />SQL> select * from emp e where (select count(*) from emp where e.hiredate=hiredate) > 1;</p><p>SQL> --18.查詢每個部門的最高工資</p><p>SQL> select deptno ,max(sal) maxsal from emp group by deptno order by deptno;</p><p>SQL> --19.查詢每個部門，每種職位的最高薪資<br />SQL> select deptno ,job ,max(sal) from emp group by deptno,job order by deptno ,job;</p><p>SQL> --20.查詢每個員工的資訊級工資及層級<br />SQL> select * from (select e.*,rownum m from (select * from emp order by sal desc)e where rownum <=10) where </p><p>m>5;</p><p>SQL> --22.查詢各部門工資最高的員工資訊</p><p>SQL> select * from emp e where e.sal =(select max(sal) from emp where (deptno=e.deptno));</p><p>SQL> --23.查詢每個部門工資最高的前兩名員工<br />SQL> select * from emp e where (select count(*) from emp where sal>e.sal and e.deptno=deptno)<2<br /> order by deptno ,sal desc;</p><p>SQL> --24.查詢出有3個以上下屬的員工資訊<br />SQL> select * from emp e where (select count(*) from emp where e.empno=mgr)>2;</p><p>SQL> --25。查詢所有大於本部們平均工資的員工資訊<br />SQL> select * from emp e where sal>(select avg(sal) from emp where (deptno=e.deptno)) order by deptno;<br />SQL> --26.查詢平均工資最高的部門資訊<br />SQL> select avg(sal) avgsal ,deptno from emp group by deptno;</p><p>SQL> --27.查詢大於各部門總工資的平均值的部門資訊<br />SQL> --1、求每個部門總工資<br />SQL> select sum(sal) sumsal ,deptno from emp group by deptno;<br />SQL> --2、每個部門的平均值<br />SQL> select avg(sum(sal)) from emp group by deptno;<br />SQL> --3、求大於總工資平均值的部門資訊，串連步驟<br />SQL>select d.* ,sumsal from dept d,(select sum(sal) sumsal ,deptno from emp group by deptno ) se where sumsal > </p><p>(select avg(sum(sal)) from emp group by deptno) and se.deptno=d.deptno;</p><p>SQL> --29.查詢沒員工的部門的資訊<br />SQL> select d.* from dept d left join emp e on(e.deptno=d.deptno) where empno is null;<br />