sql查询练习题含答案

--（1）查询20号部门的所有员工信息。select*fromempewheree.deptno=20;--（2）查询奖金（COMM）高于工资（SAL）的员工信息。select*fromempwherecommsal;--（3）查询奖金高于工资的20%的员工信息。select*fromempwherecommsal*0.2;--（4）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。select*fromempewhere(e.deptno=10ande.job='MANAGER')or(e.deptno=20ande.job='CLERK')--（5）查询所有工种不是MANAGER和CLERK，--且工资大于或等于2000的员工的详细信息。select*fromempwherejobnotin('MANAGER','CLERK')andsal=2000;--（6）查询有奖金的员工的不同工种。select*fromempwherecommisnotnull;--（7）查询所有员工工资和奖金的和。select(e.sal+nvl(e.comm,0))fromempe;--（8）查询没有奖金或奖金低于100的员工信息。select*fromempwherecommisnullorcomm100;--（9）查询员工工龄大于或等于10年的员工信息。select*fromempwhere(sysdate-hiredate)/365=10;--（10）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。selectinitcap(ename)fromemp;selectupper(substr(ename,1,1))||lower(substr(ename,2))fromemp;--（11）显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，--若月份相同则按入职的年份排序。selectename,to_char(hiredate,'yyyy')year,to_char(hiredate,'MM')monthfromemporderbymonth,year;--（12）查询在2月份入职的所有员工信息。select*fromempwhereto_char(hiredate,'MM')='02'--（13）查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。selecte.ename,floor((sysdate-e.hiredate)/365)||'年'||floor(mod((sysdate-e.hiredate),365)/30)||'月'||floor(mod(mod((sysdate-e.hiredate),365),30))||'日'fromempe;--（14）查询从事同一种工作但不属于同一部门的员工信息。selecta.ename,a.job,a.deptno,b.ename,b.job,b.deptnofromempa,empbwherea.job=b.jobanda.deptnob.deptno;--（15）查询各个部门的详细信息以及部门人数、部门平均工资。selectd.deptno,count(e.empno),avg(e.sal),d.dname,d.locfromempe,deptdwheree.deptno=d.deptnogroupbyd.deptno,d.dname,d.loc--（16）查询10号部门员工以及领导的信息。select*fromempwhereempnoin(selectmgrfromempwheredeptno=10)ordeptno=10;--（17）查询工资为某个部门平均工资的员工信息。select*fromempwheresalin(selectavg(sal)fromempgroupbydeptno);--（18）查询工资高于本部门平均工资的员工的信息。select*fromempe1wheresal(selectavg(sal)fromempe2wheree2.deptno=e1.deptno);--（19）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。selecte.*,a.avgsalfromempe,(selectdeptno,avg(sal)asavgsalfromempgroupbydeptno)awherea.deptno=e.deptnoande.sala.avgsal;--（20）统计各个工种的人数与平均工资。selectcount(*),e.job,avg(e.sal)fromempegroupbye.job--（21）统计每个部门中各个工种的人数与平均工资。selectdeptno,job,count(empno),avg(sal)fromempegroupbye.deptno,e.job--（22）查询所有员工工资都大于1000的部门的信息。select*fromdeptwheredeptnoin(selectdeptnofromempwheredeptnonotin(selectdistinctdeptnofromempwheresal1000));--（23）查询所有员工工资都大于1000的部门的信息及其员工信息。select*fromempejoindeptdond.deptnoin(selectdeptnofromempwheredeptnonotin(selectdistinctdeptnofromempwheresal1000))andd.deptno=e.deptno;--（24）查询所有员工工资都在900~3000之间的部门的信息。select*fromdeptwheredeptnonotin(selectdeptnofromempwheresalnotbetween900and3000);--（25）查询所有工资都在900~3000之间的员工所在部门的员工信息。select*fromempawherea.deptnoin(selectdistincte.deptnofromempewheree.salbetween900and3000);--（26）查询每个员工的领导所在部门的信息。selectd.*fromdeptdwhered.deptnoin(selectdistincte2.deptnofromempe1,empe2wheree1.empno=e2.mgr);--（27）查询人数最多的部门信息。select*fromdeptwheredeptnoin(selectdeptnofrom(selectcount(*)count,deptnofromempgroupbydeptno)wherecountin(selectmax(count)from(selectcount(*)count,deptnofromempgroupbydeptno)));--（28）查询30号部门中工资排序前3名的员工信息。select*from(selectsalfromempwheredeptno=30orderbysaldesc)ewhererownum4--（29）查询'JONES'员工及所有其直接、间接下属员工的信息。selecte.*fromempestartwithename='JONES'connectbypriorempno=mgr;---（30）查询SCOTT员工及其直接、间接上级员工的信息。selecte.*fromempestartwithename='SCOTT'connectbypriormgr=empno;--（31）以树状结构查询所有员工与领导之间的层次关系。selectsubstr(sys_connect_by_path(ename,'-'),3),levelfromempstartwithmgrisnullconnectbypriorempno=mgr;--（32）向emp表中插入一条记录，员工号为1357，员工名字为oracle，--工资为2050元，部门号为20，入职日期为2002年5月10日。--（33）将各部门员工的工资修改为该员工所在部门平均工资加1000。updateempesetsal=1000+(selectavg(sal)fromempwheredeptno=e.deptno);--(34)查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、--姓名和工资。selecte.ename,e.empno,e.salfromempe,salgrades,deptdwhere(e.salbetweens.losalands.hisal)and(s.grade=2)andto_char(e.hiredate,'yyyy')1985ande.deptno=d.deptnoandd.loc='DALLAS';--35.部门平均薪水最高的部门编号select*from(selectavg(sal)avgsal,deptnofromempgroupbydeptnoorderbyavgsaldesc)whererownum=1;selectdeptno,avg(sal)fromempgroupbydeptnohavingavg(sal)=(selectmax(avg(sal))avgsalfromempgroupbydeptno)--36,部门平均薪水最高的部门名称selectd.*fromdeptdwheredeptnoin(selectdeptnofromempgroupbydeptnohavingavg(sal)=(selectmax(avg(sal))avgsalfromempgroupbydeptno))--37.平均薪水最低的部门的部门名称selectd.*fromdeptdwheredeptnoin(selectdeptnofromempgroupbydeptnohavingavg(sal)=(selectmin(avg(sal))avgsalfromempgroupbydeptno))--38.平均薪水等级最低的部门的部门名称selectd.dnamefromdeptdwhered.deptnoin(selecta.deptnofrom(selecte.deptnofromempe,salgradeswhere(e.salbetweens.losalands.hisal)groupbye.deptnoorderbyavg(s.grade))awhererownum=1);--39.部门经理人中，薪水最低的部门名称selectdnamefromdeptwheredeptno=(selectdeptnofrom(selectdeptnofromempwherejob='MANAGER'groupbydeptnoorderbymin(sal))whererownum=1)--40.比普通员工的最高薪水还要高的经理人名称selectenamefromempwheresal(selectmax(sal)fromempwherejobnotin('MANAGER','PRESIDENT'))andjob='MANAGER'orjob='PRESIDENT'--41.删除重复部门，但是留下一项insertintodeptvalues(70,'RESEARCH','DALLAS')selectdeptno,dname,rowidfromdeptdeletefromdeptdwhererowid(selectmin(rowid)fromdeptwheredname=d.dnameandd.loc=loc)--42.更新员工工资为他的主管的工资,奖金updateempesetsal=(selectsalfromempwhereempno=e.mgr),comm=(selectcommfro