004 oracle经典20题(参考详解).docVIP

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．列出受雇日期早于其直接上级的所有员工。select yg.ename,sj.enamefrom emp yg join emp sj on?yg.mgr=sj.empnowhere yg.hiredatesj.hiredate5．列出部门名称与这些部门的员工信息，同时列出那些没有员工的部门。select dept.deptno,dept.dname,emp.empno,emp.enamefrom dept left join empon dept.deptno=emp.deptno6．列出所有“CLERK”（办事员）的姓名及其部门名称。select * from dept,empwhere dept.deptno=emp.deptnoand job=’CLERK’7．列出最低薪金大于1500的各种工作。select job from empgroup by jobhaving min(sal)1500 8．列出在部门名称为“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。方法一：select emp.enamefrom dept,empwhere dept.deptno=emp.deptnoand dname=’SALES’方法二：select * from empwhere deptno=(select deptno from dept where dname=’SALES’)9．列出薪金高于公司平均薪金的所有员工。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) bwhere a.deptno=b.deptnoand a.salb.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 empwhere salall(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．列出所有员工的姓名、部门名称与工资。select d.dname,e.ename,e.salfrom dept d,emp ewhere d.deptno=e.deptno说明：每个字段都加表前缀，效率要高些?15．列出所有部门的详细信息与部门人数。select dept.dept