Oracle-练习与答案

Oracle_练习与答案1.求部门中薪水最高的人selectename,sal,emp.deptnofromempjoin(selectdeptno,max(sal)max_salfromempgroupbydeptno)ton(emp.deptno=t.deptnoandemp.sal=t.max_sal);2.求部门平均薪水的等级selectdeptno,avg_sal,gradefrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)tjoinsalgradeon(t.avg_salbetweensalgrade.losalandsalgrade.hisal);3.求部门平均的薪水等级selectdeptno,avg(grade)avg_sal_gradefrom(selectdeptno,gradefromempjoinsalgradeonemp.salbetweensalgrade.losalandsalgrade.hisal)groupbydeptno;4.雇员中有哪些人是经理人selectdistincte2.enamemanagerfromempe1joinempe2one1.mgr=e2.empno;selectenamefromempwhereempnoin(selectmgrfromemp);5.不准用组函数，求薪水的最高值selectdistinctsalmax_salfromempwheresalnotin(selecte1.sale1_salfromempe1joinempe2one1.sale2.sal);6.求平均薪水最高的部门的部门编号selectdeptno,avg_salfrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)whereavg_sal=(selectmax(avg_sal)from(selectavg(sal)avg_salfromempgroupbydeptno));组函数嵌套写法(对多可以嵌套一次，groupby只对内层函数有效)selectdeptno,avg_salfrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)whereavg_sal=(selectmax(avg(sal))fromempgroupbydeptno);7.求平均薪水最高的部门的部门名称selectt1.deptno,dname,avg_salfrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t1joindeptont1.deptno=dept.deptnowhereavg_sal=(selectmax(avg_sal)from(selectdeptno,avg(sal)avg_salfromempgroupbydeptno));selectdnamefromdeptwheredeptno=(selectdeptnofrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)whereavg_sal=(selectmax(avg_sal)from(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)));8.求平均薪水的等级最低的部门的部门名称selectdnamefromdeptjoin(selectdeptno,gradefrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)tjoinsalgradeon(t.avg_salbetweensalgrade.losalandsalgrade.hisal))tondept.deptno=t.deptnowheret.grade=(selectmin(grade)from(selectavg(sal)avg_salfromempgroupbydeptno)tjoinsalgradeon(t.avg_salbetweensalgrade.losalandsalgrade.hisal));9.求部门经理人中平均薪水最低的部门名称selectdnamefrom(selectdeptno,avg(sal)avg_salfromempwhereempnoin(selectmgrfromemp)groupbydeptno)tjoindeptont.deptno=dept.deptnowhereavg_sal=(selectmin(avg_sal)from(selectavg(sal)avg_salfromempwhereempnoin(selectmgrfromemp)groupbydeptno)t);10.求比普通员工的最高薪水还要高的经理人名称(notin)selectenamefromempwhereempnoin(selectmgrfromemp)andsal(selectmax(sal)from(selecte2.salfromempe1rightjoinempe2one1.mgr=e2.empnowheree1.enameisnull)t);selectenamefromempwhereempnoin(selectmgrfromemp)andsal(selectmax(sal)fromempwhereempnonotin(selectdistinctmgrfromempwheremgrisnotnull));//NOTIN遇到NULL则返回NULL，必须排除NULL值11.求薪水最高的前5名雇员selectempno,enamefrom(select*fromemporderbysaldesc)whererownum=5;12.求薪水最高的第6到第10名雇员(!important)selectename,salfrom(selectt.*,rownumrfrom(select*fromemporderbysaldesc)t)wherer=6andr=10;13.求最后入职的5名员工selectename,to_char(hiredate,'YYYY年MM月DD日')hiredatefrom(selectt.*,rownumrfrom(select*fromemporderbyhiredatedesc)t)wherer=5;selectename,to_char(hiredate,'YYYY年MM月DD日')hiredatefrom(selectt.*,rownumrfrom(select*fromemporderbyhiredate)t)wherer(selectcount(*)-5fromemp);