oracle经典20道笔试题

oracle经典20道笔试题发布时间:2013-01-29来源:笔试网1.列出至少有一个员工的所有部门。selectdept.deptno,dept.dnamefromdept,empwheredept.deptno=emp.deptno2.列出薪金比“SMITH”多的所有员工。select*fromempwheresal(selectsalfromempwhereename=’SMITH’)3.列出所有员工的姓名及其直接上级的姓名。selectyg.ename,sj.enamefromempyg,empsjwhereyg.mgr=sj.empno4.列出受雇日期早于其直接上级的所有员工。selectyg.ename,sj.enamefromempygjoinempsjonyg.mgr=sj.empnowhereyg.hiredate5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。selectdept.deptno,dept.dname,emp.empno,emp.enamefromdeptleftjoinempondept.deptno=emp.deptno6.列出所有“CLERK”(办事员)的姓名及其部门名称。select*fromdept,empwheredept.deptno=emp.deptnoandjob=’CLERK’7.列出最低薪金大于1500的各种工作。selectjobfromempgroupbyjobhavingmin(sal)15008.列出在部门名称为“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。方法一：selectemp.enamefromdept,empwheredept.deptno=emp.deptnoanddname=’SALES’方法二：select*fromempwheredeptno=(selectdeptnofromdeptwheredname=’SALES’)9.列出薪金高于公司平均薪金的所有员工。select*fromempwheresal(selectavg(sal)fromemp)对比：列出薪金高于本部门平均薪金的所有员工。方法一：select*fromempawherea.sal(selectavg(sal)fromempbwhereb.deptno=a.deptno)缺点：相关子查询，效率低。方法二：selectempno,ename,salfromempa,(selectavg(sal)avg_salfromempbwhereb.deptno=a.deptno)bwherea.deptno=b.deptnoanda.salb.avg_sal10.列出与“SCOTT”从事相同工作的所有员工。select*fromempwherejob=(selectjobfromempwhereename=’SCOTT’)11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。selectename,salfromempwheresal=any(selectsalfromempwheredeptno=30)12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。方法一：selectename,salfromempwheresalall(selectsalfromempwheredeptno=30)方法二：selectename,salfromempwheresal(selectmax(sal)fromempwheredeptno=30)13.列出在每个部门工作的员工数量、平均工资和平均服务期限。selectdeptno,avg(trunc((sysdate-hiredate)/365))asyearfromempgroupbydeptno–参考：截断，取整函数selecttrunc(99.9)fromdual;返回9914.列出所有员工的姓名、部门名称和工资。selectd.dname,e.ename,e.salfromdeptd,empewhered.deptno=e.deptno说明：每个字段都加表前缀，效率要高些15.列出所有部门的详细信息和部门人数。selectdept.deptno,dept.dname,count(*)fromdept,empwheredept.deptno=emp.deptnogroupbydept.deptno,dept.dname16.列出各种工作的最低工资。selectjob,min(sal)fromempgroupbyjob17.列出各个部门的MANAGER(经理)的最低薪金。selectdeptno,min(sal)fromempwherejob=’MANAGER’groupbydeptno18.列出所有员工的年工资,按年薪从低到高排序。selectempno,ename,sal*12as年薪fromemporderbysal19.求各种工作工资最低的员工。方法一：select*fromempawheresal=(selectmin(sal)fromempbwhereb.job=a.job)方法二：selectemp.*fromempa,(selectjob,min(sal)min_salfromempgroupbyjob)bwherea.job=b.jobanda.sal=b.min_sal20.列出各种工作工资前3名的员工select*from(selectempno,ename,sal,job,dense_rank()over(partitionbyjoborderbysaldesc)as名次fromemp)awherea.名次=2orderbyjob;说明：用到了Oracle强大的“分区排名技术”，其中“dense_rank()”是Oracle的解析函数。