Oracle面试题及答案

本文格式为Word版，下载可任意编辑——Oracle面试题及答案模块一SQL(DQL)

l基本SQL查询l运算符与函数l子查询l连接查询

建表语句emp.sqlPartI(第一天)

01.查询员工表所有数据,并说明使用*的缺点答：

select*fromemp;使用*的缺点有

a)查询出了不必要的列

b)效率上不如直接指定列名

02.查询职位(JOB)为'PRESIDENT'的员工的工资答：

select*fromempwherejob='PRESIDENT';03.查询佣金(COMM)为0或为NULL的员工信息答：重点是理解0与null的区别

select*fromempwherecomm=0orcommisnull;

04.查询入职日期在1981-5-1到1981-12-31之间的所有员工信息答：通过此题把握常用日期函数select*fromempwherehiredate

betweento_date('1981-5-1','yyyy-mm-dd')andto_date('1981-12-31','yyyy-mm-dd');05.查询所有名字长度为4的员工的员工编号,姓名答：

select*fromempwherelength(ename)=4;

06.显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息答：

select*fromempwheredeptno=10andjob='MANAGER'ordeptno=20andjob='CLERK';07.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息答：考察知识点模糊查询

select*fromempwhereenamenotlike'%L%'orenamelike'%SM%';08.显示各个部门经理('MANAGER')的工资答：

selectsalfromempwherejob='MANAGER';

09.显示佣金(COMM)收入比工资(SAL)高的员工的详细信息答：

select*fromempwherecomm>sal;

10.把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)答：

select*fromempwhereto_char(hiredate,'mm')=to_char(sysdate,'mm');

11.把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)答：

select*fromempwhereto_char(hiredate,'mm')=to_char(add_months(sysdate,1),'mm');12.求1982年入职的员工(考察知识点:单行函数)答：

select*fromempwhereto_char(hiredate,'yyyy')='1982';

13.求1981年下半年入职的员工(考察知识点:单行函数)答：

select*fromempwherehiredate

betweento_date('1981-7-1','yyyy-mm-dd')andto_date('1982-1-1','yyyy-mm-dd')-1;14.求1981年各个月入职的的员工个数(考察知识点:组函数)答：

selectcount(*),to_char(trunc(hiredate,'month'),'yyyy-mm')fromempwhereto_char(hiredate,'yyyy')='1981'groupbytrunc(hiredate,'month')orderbytrunc(hiredate,'month');

PartII(其次天)

01.查询各个部门的平均工资答：考察知识点：分组

selectdeptno,avg(sal)fromempgroupbydeptno;02.显示各种职位的最低工资答：考察知识点：分组

selectjob,min(sal)fromempgroupbyjob;03.依照入职日期由新到旧排列员工信息答：考察知识点：排序

select*fromemporderbyhiredatedesc;

04.查询员工的基本信息，附加其上级的姓名答：考察知识点：自连接

selecte.*,e2.enamefromempe,empe2wheree.mgr=e2.empno;05.显示工资比'ALLEN'高的所有员工的姓名和工资答：考察知识点：子查询

select*fromempwheresal>(selectsalfromempwhereename='ALLEN');

分析：当查询结果是一行一列时，可以将此结果看做一个值，参与条件比较。06.显示与'SCOTT'从事一致工作的员工的详细信息答：考察知识点：子查询

select*fromempwherejob=(select*fromempwhereename='SCOTT');分析：同第5题

07.显示销售部('SALES')员工的姓名答：考察知识点：连接查询

selectenamefromempe,deptdwheree.deptno=d.deptnoandd.dname='SALES';08.显示与30号部门'MARTIN'员工工资一致的员工的姓名和工资答：考察知识点：子查询selectename,salfromemp

wheresal=(selectsalfromempwheredeptno=30andename='MARTIN');分析：同第5题

09.查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')

答：考察知识点：子查询

select*fromempwherejob='SALESMAN'andsal>(selectavg(sal)fromemp);10.显示所有职员的姓名及其所在部门的名称和工资答：考察知识点：表连接

selectename,job,dnamefromempe,deptdwheree.deptno=d.deptno;11.查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地答：考察知识点：表连接

selectempno,ename,dname,locfromempe,deptdwheree.deptno=d.deptnoanddanme='RESEARCH';

12.查询各个部门的名称和员工人数答：考察知识点：子查询，表连接

select*from(selectcount(*)c,deptnofromempgroupbydeptno)einnerjoindeptdone.deptno=d.deptno;

分析：主要思路是要将子查询结果看做一个临时表，此临时表又可以与其他表做表连接13.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位答：考察知识点：子查询

selectjob,count(*)fromempwheresal>(selectavg(sal)fromemp)groupbyjob;分析：查询结果是一行一列，可以将查询结果看做一个值，进行条件比较14.查询工资一致的员工的工资和姓名答：考察知识点：子查询

select*fromempewhere(selectcount(*)fromempwheresal=e.salgroupbysal)>1;分析：此题目类似于17题，见17题分析。15.查询工资最高的3名员工信息答：考察知识点：子查询，rownum

select*from(select*fromemporderbysaldesc)whererownume.sal)+1rankfromempeorderbyrank;分析：

此题的要点在于理解selectcount(*)fromempwheresal>e.sal+1的含义，e.sal代表当前员工，该子查询的含义就是求比当前员工工资高的人数个数：比此员工工资高的人数个数假使为0，表示此人排名第一，比此员工工资高的人数个数假使为1，表示此人排名其次…所以该子查询结果就表示排名。

17.求入职日期一致的(年月日一致)的员工答：考察知识点：子查询

select*fromempewhere(selectcount(*)fromempwheree.hiredate=hiredate)>1;分析：常见的一个误会就是把此题当做自连接做：

select*fromempe1,empe2wheree1.hiredate=e2.hiredateande1.empnoe2.empno;这样做的结果中对于只有两个日期相等的没有错误，查询结果有2条，但如果有三个日期相

等的查询结果就是6条，其中3条是重复的。

要点也是理解子查询的含义selectcount(*)fromempwheree.hiredate=hiredate，代表取得与当

前员工入职日期相等的人数个数，如果个数大于1表示此日期有相等的。18.查询每个部门的最高工资答：考察知识点：分组

selectdeptno,max(sal)maxsalfromempgroupbydeptnoorderbydeptno;19.查询每个部门,每种职位的最高工资答：考察知识点：分组

selectdeptno,job,max(sal)fromempgroupbydeptno,joborderbydeptno,job;

分析：要点是理解多列分组：部门与职位都相同的分为一组，求每组的最高工资，其实就是表示每个部门,每种职位的最高工资

20.查询每个员工的信息及工资级别(用到表Salgrade)答：考察知识点：不等值连接select*fromsalgrade;

selecte.*,sg.gradefromempe,salgradesgwheresalbetweenlosalandhisal;21.查询工资最高的第6-10名员工答：考察知识点：子查询,rownumselect*from(

selecte.*,rownumrnfrom

(select*fromemporderbysaldesc)ewhererownum5;

分析：

要点一是rownum不能直接和orderby连用，由于rownum先产生，orderby后执行，因此需要将

select*fromemporderbysaldesc

先排序之后的结果看做一个临时表，再对此临时表产生rownum编号。要点二是rownum不能用作>或>=的比较条件，因此不能够直接这样写selecte.*from

(select*fromemporderbysaldesc)ewhererownum>5andrownum或>=比较条件：

select*from(

selecte.*,rownumrnfrom

(select*fromemporderbysaldesc)ewhererownum5;

两次查询例如图如下：第一次取前10条，其次次排除前5条

22.查询各部门工资最高的员工信息答：考察知识点：子查询

select*fromempewheree.sal=(selectmax(sal)fromempwhere(deptno=e.deptno));

分析：要点同样是理解子查询selectmax(sal)fromempwhere(deptno=e.deptno)获取当前部门（e.deptno）的最高工资，再将此最高值与当前工资（e.sal）进行比较。思路2：

selecte.*from(selectmax(sal)maxsal,deptnofromempgroupbydeptno)b,empewheree.deptno=b.deptnoandb.maxsal=e.sal;

将子查询看做一个临时表，临时表中有最高工资列maxsal，以及deptno列，此临时表与真实表emp做表连接，连接条件为emp表中的工资要等于临时表的最高工资并且两表的部门

编号要相等。

23.查询每个部门工资最高的前2名员工答：考察知识点:子查询select*fromempewhere

(selectcount(*)fromempwheresal>e.salande.deptno=deptno)e.salande.deptno=

deptno的含义：求工资大于当前员工工资（e.sal）并且部门编号等于当前员工部门编号（e.deptno）的员工的个数，此个数+1表示排名，=t2.minrankandt1.rn=t2.minrankandt1.rn24.查询出有3个以上下属的员工信息答：考察知识点：自连接,子查询select*fromempewhere

(selectcount(*)fromempwheree.empno=mgr)>2;

分析：关键是理解连接条件e.empno=mgr是表示连接当前员工（e.empno）和他的下属（mgr）25.查询所有大于本部门平均工资的员工信息()答：考察知识点：子查询select*fromempewheresal>

(selectavg(sal)fromempwhere(deptno=e.deptno))orderbydeptno;

分析：思路与22题一致。

26.查询平均工资最高的部门信息

答：考察知识点：子查询，组函数，连接查询

selectd.*,avgsalfromdeptd,(selectavg(sal)avgsal,deptnofromempgroupbydeptno)sewhereavgsal=(selectmax(avg(sal))fromempgroupbydeptno)andd.deptno=se.deptno;分析：

步骤1：求每个部门的平均工资：

selectavg(sal)avgsal,deptnofromempgroupbydeptno;步骤2：求最高的平均工资：

selectmax(avg(sal))fromempgroupbydeptno;

步骤3：求平均工资最高的部门信息，连接步骤1产生的临时表与真实表dept：

selectd.*,avgsalfromdeptd,(selectavg(sal)avgsal,deptnofromempgroupbydeptno)sewhereavgsal=(selectmax(avg(sal))fromempgroupbydeptno)andd.deptno=se.deptno;27.查询大于各部门总工资的平均值的部门信息答：考察知识点：子查询，组函数，连接查询

selectd.*,sumsalfromdeptd,(selectsum(sal)sumsal,deptnofromempgroupbydeptno)sewheresumsal>(selectavg(sum(sal))fromempgroupbydeptno)andse.deptno=d.deptno;分析：

步骤1：求每个部门总工资

selectsum(sal)sumsal,deptnofromempgroupbydeptno;步骤2：求每总工资平均值

selectavg(sum(sal))fromempgroupbydeptno;

步骤3：求大于总工资平均值的部门信息，连接步骤1产生的临时表与真实表dept：selectd.*,sumsalfromdeptd,(selectsum(sal)sumsal,deptnofromempgroupbydeptno)sewheresumsal>(selectavg(sum(sal))fromempgroupbydeptno)andse.deptno=d.deptno;28.查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接查询)

答：考察知识点：子查询，组函数，连接查询

selecte.*,sumsalfromempe,(selectsum(sal)sumsal,deptnofromempgroupbydeptno)sewheresumsal>(selectavg(sum(sal))fromempgroupbydeptno)andse.deptno=e.deptno;分析：类似于26题，27题29.查询没有员工的部门信息答：考察知识点：表连接

selectd.*fromdeptdleftjoinempeon(e.deptno=d.deptno)whereempnoisnull;

分析：利用了左外连接的特点，部门连接员工时，没有匹配记录的部门对应的员工编号列肯定为null

30.查询用户（users表）huxz所下所有订单编号，下单日期，总价格(orders表)，并包括订

单中的商品数量（orderitem表），名称（product表），价格（product表）答：考察知识点：多表连接

selectu.username,o.orderid,o.orderdate,o.totalprice,ductname,p.price,i.qtyfromusersuinnerjoinordersoon(u.username=o.username)innerjoinorderitemion(o.orderid=i.orderid)innerjoinproductpon(ductid=ductid)whereu.username='huxz';

31.查询100001号商品被哪些顾客（users表）购买过，下单日期(orders表)，每人购买的数

量（orderitem表），购买时的价格（product表）答：考察知识点：多表连接

selectu.username,o.orderdate,ductname,p.price,i.qty

fromproductpinnerjoinorderitemion(ductid=ductid)innerjoinordersoon(o.orderid=i.orderid)innerjoinusersuon(u.username=o.username)whereductid=100001;

32.查询出哪些商品从未被订购过答：考察知识点：连接查询

selectp.*fromproductpleftjoinorderitemion(ductid=ductid)wherei.orderitemidisnull;

分析：同29题

33.查询出被订购过2次以上的商品信息答：考察知识点：连接查询，子查询selectp.*fromproductp

where(selectcount(*)fromorderitemwhereproductid=ductid)>=2;

分析：子查询selectcount(*)fromorderitemwhereproductid=ductid的含义是：当前商

品在orderitem表中出现的次数，即被订购的次数PartIII(面试题目)

01.tmp表中有如下记录（建表SQL见emp.sql）要求结果格式为：

答：考察知识点：case…when…count函数selectrq,

count(casewhenshengfu='WIN'then1elsenullend)WIN,

count(casewhenshengfu='LOSE'then1elsenullend)LOSEfromtmpgroupbyrq;分析：

要点：在计数时，协同case…when…语句只统计取值为WIN或LOSE的个数。case语句返回不为null的值即会参与count计数，返回null则不会参与count计数。02.查询当前月有多少天

答：考察知识点日期函数的灵活运用

selecttrunc(add_months(sysdate,1),'month')-trunc(sysdate,'month')fromdual;

03.pages表有四个字段，id,url,title,body。如图：

现要求将url匹配的排在最前，title匹配的其次，body匹配最终，没有任何字段匹配的，不

返回。现要求查询所有匹配baidu的记录，最终查询结果如图：建表语句在emp.sql中答：考察知识点：unionselectid,contentfrom(

selectid,3mark,urlcontentfrompageswhereurllike'oidu%'union

selectid,2,titlefrompageswheretitlelike'oidu%'union

selectid,1,bodyfrompageswherebodylike'oidu%')orderbymarkdesc;

要点：union可以用来合并屡屡查询结果。这里需要注意屡屡查询的结果列的个数和类型必须一致，合并后的结果集也可以看做一张表，表的列的类型和名称由union的第一条查询结

果来决定。

这里用到一个技巧：手工指定一个优先级mark列，最终根据mark列排序。

04.现有STUDENT(学生),COURSE(课程),SC(成绩)表,完成以下需求(建表语句在emp.sql中,综合考察)

a)查询选修课程为web的学员学号和姓名答：

selects.sid,fromstudents

innerjoinscon(s.sid=sc.sid)innerjoincoursecon(c.cid=sc.cid)where='web';

分析：课程与学生表没有直接联系，必需通过中间成绩表做2次表连接b)查询课程编号为2的学员姓名和单位答：

select,s.deptfromstudentsinnerjoinscon(s.sid=sc.sid)wheresc.cid=2;

c)查询不选修4号课程的学员姓名和单位答：

selectname,deptfromstudentwheresidnotin

(selects.sidfromstudentsleftjoinscons.sid=sc.sidwherecid=4);

或：

selectname,deptfromstudent

wherenotexists(selectsidfromscwheresc.sid=s.sidandcid=4);

分析：要点是先要查询出选修了4号课程的学员id，再从所有学员中排除这些id的学员。方法2效率较高。

d)查询选修全部课程的学员姓名和单位答：

select,s.deptfromstudentswheresidin

(selectsidfromscgroupbysidhaving(count(*)=(selectcount(*)fromcourse)));

分析：

步骤1：查询出所有课程的数目selectcount(*)fromcourse;

步骤2：在成绩(sc)表，按学员id分组，看每组的个数，该个数等于步骤1课程总数的sid即为选修了所有课程的学员id

selectsidfromscgroupbysidhaving(count(*)=(selectcount(*)fromcourse));步骤3：再根据该sid查询学员的详细信息select,s.deptfromstudentswheresidin

(selectsidfromscgroupbysidhaving(count(*)=(selectcount(*)fromcourse)));e)查询选修课程超过3门的学员姓名和单位答：

select,s.deptfromstudents

wheresidin(selectsidfromscgroupbysidhaving(count(*)>3));

f)找出没有选修过TeacherLI讲授课程的所有学生姓名答：

selectfromstudentswheresidnotin

(selectsidfromcoursecleftjoinscon(c.cid=sc.cid)wherec.teacher='TeacherLI');g)列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩答：

selects.*,b.avgsalfromstudents,

(selectsc.sid,avg(score)avgscorefromsc,

(selectsidfromscwherescore=2))awheresc.sid=a.sidgroupbysc.sid)bwheres.sid=b.sid;

分析：

步骤1：查询所有两门以上不及格的学员id

selectsidfromscwherescore=2);

步骤2：步骤1结果与真实表sc做连接，算平均成绩selectsc.sid,avg(score)avgscorefromsc,

(selectsidfromscwherescore=2))awheresc.sid=a.sidgroupbysc.sid;

步骤3：步骤2结果与真实表student做连接，查学员姓名selects.*,b.avgscorefromstudents,(selectsc.sid,avg(score)avgscorefromsc,

(selectsidfromscwherescore=2))awheresc.sid=a.sidgroupbysc.sid)bwheres.sid=b.sid;思路2：步骤1：同上

步骤2：步骤1结果与真实表sc，student共3张表做连接selects.*,avg(sc.score)avgscorefromstudents,sc,

(selectsidfromscwherescore=2))awheres.sid=sc.sidands.sid=a.sid;

步骤3：可以发现，该结果中sid,name,dept,age都是取值都一致，依照这些列直接进

行分组即可：

selects.*,avg(sc.score)avgscorefromstudents,sc,

(selectsidfromscwherescore=2))awheres.sid=sc.sidands.sid=a.sidgroupbys.sid,,s.dept,s.age;h)列出既学过1号课程，又学过2号课程的所有学生姓名答：

selectfromstudentsinnerjoin

(selectsc.sidfromscwheresc.cidin(1,2)groupbysidhaving(count(*)=2))aon(s.sid=a.sid);

分析：要点是不仅要学过1，2号课程in(1,2)，并且要求同时学过此两门课count(*)=2i)列出1号课成绩比2号课成绩高的所有学生的学号，姓名和1号课和2号课的成绩答：

selects.sid,,sc1.score,sc2.scorefromscsc1,scsc2,studentswheres.sid=sc1.sidandsc1.sid=sc2.sid

andsc1.cid=1andsc2.cid=2andsc1.score>sc2.score;

分析：要点在于自连接，把成绩表拆成两张表来看，sc1中只考虑1号课，sc2中只考虑2号课且sc1.score>sc2.score；最终再考虑将结果与student表连接查询姓名。05.现有test表，表中数据如下图：要求依照格式如下输出结果：a)连续的编号要求如下格式b)不连续的编号要求如下格式答：

a)求连续的，考察知识点：rownum，子查询分析：查看连续id与rownum之间的关系，运行selectid,rownum,id-rownumfromtest;参考下图看出规律：

可以发现，id-rownum取值一致的，就是那些id编号连续的。依照id-rownum分组并求每组的最大，最小值即可。selecta.*from

(selectmin(id)begin,max(id)endfromtestgroupby(id-rownum)orderbyid-rownum)a;b)不连续的，考察知识点，rownum，子查询分析：步骤一：

查询有上一条记录的个数

selectid,(selectcount(*)fromtestwhereid+1=t1.id)fromtestt1;查询有下一条记录的个数

selectid,(selectcount(*)fromtestwhereid-1=t1.id)fromtestt1;分析结果可知，个数为0的即为我们所需要的。步骤二：

selectid,rownumr1fromtestt1where(selectcount(*)fromtestwhereid+1=t1.id)=0;selectid,rownumr2fromtestt1where(selectcount(*)fromtestwhereid-1=t1.id)=0;分析结果可知，要求如上图格式的数据将查询1中的r1-1=查询2中的r2列即可：selectb.idbegin,a.idendfrom

(selectid,rownumr1fromtestt1where(selectcount(*)fromtestwhereid+1=t1.id)=0)a,(selectid,rownumr2fromtestt1where(selectcount(*)fromtestwhereid-1=t1.id)=0)bwherer1-1=r2;

06.根据EMP表数据产生如下格式的报表（统计各部门，各职位的人数）答：方法1考察知识点caseselectdeptno,

count(casewhenjob='PRESIDENT'then1elsenullend)PRESIDENT,count(casewhenjob='MANAGER'then1elsenullend)MANAGER,count(casewhenjob='CLERK'then1elsenullend)CLERK,

count(casewhenjob='SALESMAN'then1elsenullend)SALESMAN,count(casewhenjob='ANALYST'then1elsenullend)ANALYSTfromempgroupbydeptnoorderbydeptno;

方法2考察知识点：自连接selectd.deptno,

count(distinctPRESIDENT.empno)PRESIDENT,count(distinctMANAGER.empno)MANAGER,count(distinctCLERK.empno)CLERK,

count(distinctSALESMAN.empno)SALESMAN,

count(distinctANALYST.empno)ANALYSTfromdeptdleftjoinempPRESIDENT

on(d.deptno=PRESIDENT.deptnoandPRESIDENT.job='PRESIDENT')leftjoinempMANAGER

on(d.deptno=MANAGER.deptnoandMANAGER.job='MANAGER')leftjoinempCLERK

on(d.deptno=CLERK.deptnoandCLERK.job='CLERK')leftjoinempSALESMAN

on(d.deptno=SALESMAN.deptnoandSALESMAN.job='SALESMAN')leftjoinempANALYST

on(d.deptno=ANALYST.deptnoandANALYST.job='ANALYST')groupbyd.deptnoorderbyd.deptno;

分析：通过dept表屡屡左外连接emp表，譬如说selectd.deptno,d.dname,e.empno,e.jobfromdeptd

leftjoinempeon(d.deptno=e.deptnoande.job='CLERK');结果如下：

可以看出这是求出每个部门职位为CLERK的员工，将此结果按deptno分组求个数：selectd.deptno,count(empno)CLERKfromdeptd

leftjoinempeon(d.deptno=e.deptnoande.job='CLERK')groupbyd.deptnoorderbyd.deptno;其中CLERK列即为最终结果所需列。

如此类推，连接一次，求出一列，但需要注意，多表连接后，最终结果中会有重复记录，因此使用count(distinctempno)排除重复记录后再计算个数才为正确结果。

07.根据EMP表数据产生如下格式的报表(统计各职位,各部门的人数)(06题的变体)答：方法1考察知识点caseselectjob,

count(casewhendeptno=10then1elsenullend)\count(casewhendeptno=20then1elsenullend)\count(casewhendeptno=30then1elsenullend)\fromempgroupbyjoborderbyjob;

思路：同第06题，注意列别名假使为数字开头必需使用双引号。方法2考察知识点：自连接

selecte.job,count(distinctd10.empno)\d30.empno)\

(selectjobfromempgroupbyjob)e

leftjoinempd10on(e.job=d10.jobandd10.deptno=10)leftjoinempd20on(e.job=d20.jobandd20.deptno=20)

leftjoinempd30on(e.job=d30.jobandd30.deptno=30)groupbye.joborderbyjob;08.依照如下格式显示7369号员工的信息答：考察知识点UNION

selectempno,'ENAME'asKEY,enameVALUEfromempwhereempno=7369union

selectempno,'JOB',jobfromempwhereempno=7369union

selectempno,'HIREDATE',to_char(hiredate,'yyyy-mm-dd')afromempwhereempno=7369union

selectempno,'MGR',to_char(mgr)fromempwhereempno=7369union

selectempno,'SAL',to_char(sal)fromempwhereempno=7369union

selectempno,'COMM',to_char(comm)fromempwhereempno=7369union

selectempno,'DEPTNO',to_char(deptno)fromempwhereempno=7369;

分析：使用UNION可以将屡屡查询结果连接起来，要注意，每条查询的列的个数和数据类型必需