004 oracle经典20题参考答案

1、oracle经典20题(参考答案) Oracle 1列出至少有一个员工的所有部门。select dept.deptno,dept.dnamefrom dept,empwhere dept.deptno=emp.deptno2列出薪金比“SMITH”多的所有员工。select * from empwhere sal >(select sal from emp where ename=SMITH)3列出所有员工的姓名及其直接上级的姓名。select yg.ename,sj.enamefrom emp yg,emp sjwhere yg.mgr=sj.empno 4列出受雇日期早于其直接上级的

2、所有员工。select yg.ename,sj.enamefrom emp yg join emp sj on yg.mgr=sj.empnowhere yg.hiredate<sj.hiredate5列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。select dept.deptno,dept.dname,emp.empno,emp.enamefrom dept left join empon dept.deptno=emp.deptno6列出所有“CLERK”（办事员）的姓名及其部门名称。select * from dept,empwhere dept.dep

3、tno=emp.deptnoand job=CLERK7列出最低薪金大于1500的各种工作。select job from empgroup by jobhaving min(sal)>15008列出在部门名称为“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。方法一：select emp.enamefrom dept,empwhere dept.deptno=emp.deptnoand dname=SALES方法二：select * from empwhere deptno=(select deptno from dept where dname=SALES)9列出

4、薪金高于公司平均薪金的所有员工。select * from empwhere sal> (select avg(sal) from emp)对比：列出薪金高于本部门平均薪金的所有员工。方法一：select * from emp awhere a.sal>(select avg(sal) from emp b where b.deptno=a.deptno)缺点：相关子查询，效率低。方法二：select empno,ename,sal fromemp a,(select avg(sal) avg_sal from emp b where b.deptno=a.deptno) bwhe

5、re a.deptno=b.deptnoand a.sal>b.avg_sal10列出及“SCOTT”从事相同工作的所有员工。select * from empwhere job=(select job from emp where ename=SCOTT)11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。select ename,sal from empwhere sal=any(select sal from emp where deptno=30 )12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。方法一：select ename,sal from empw

6、here sal>all(select sal from emp where deptno=30 )方法二：select ename,sal from empwhere sal>(select max(sal) from emp where deptno=30 )13列出在每个部门工作的员工数量、平均工资和平均服务期限。select deptno,avg(trunc(sysdate-hiredate)/365) as yearfrom empgroup by deptno参考：截断，取整函数select trunc(99.9) from dual;返回9914列出所有员工的姓名、部

7、门名称和工资。select d.dname,e.ename,e.salfrom dept d,emp ewhere d.deptno=e.deptno说明：每个字段都加表前缀，效率要高些 15列出所有部门的详细信息和部门人数。select dept.deptno,dept.dname,count(*)from dept,empwhere dept.deptno=emp.deptnogroup by dept.deptno,dept.dname16列出各种工作的最低工资。select job,min(sal) from emp group by job 17列出各个部门的MA

8、NAGER（经理）的最低薪金。select deptno,min(sal) from emp where job=MANAGER group by deptno18列出所有员工的年工资,按年薪从低到高排序。select empno,ename,sal*12 as 年薪 from emporder by sal19. 求各种工作工资最低的员工。方法一：select * from emp awhere sal=(select min(sal) from emp b where b.job=a.job)方法二：select emp.*from emp a,( select job,min(sal) min_sal from emp group by job) bwhere a.job=b.job and a.sal=b.min_sal20. 列出各种工作工资前3名的员工select * from (select empno,ename,sal,job,dense_rank() over(partition by jo