Oracle常见面试题

模块一SQL(DQL)KeyPointl基本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*fromempwherehiredatebetweento_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*fromempwherecommsal;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*fromempwherehiredatebetweento_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,salfromempwheresal=(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名员工信息答：考察知识点：子查询，rownumselect*from(select*fromemporderbysaldesc)whererownum=3;分析：见21题要点一16.按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第三名继续排)答：考察知识点：子查询selecte.*,(selectcount(*)fromempwheresale.sal)+1rankfromempeorderbyrank;分析：此题的要点在于理解selectcount(*)fromempwheresale.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)ewhererownum=10)wherern5;分析：要点一是rownum不能直接和orderby连用，因为rownum先产生，orderby后执行，因此需要将select*fromemporderbysaldesc先排序之后的结果看做一个临时表，再对此临时表产生rownum编号。要点二是rownum不能用作或=的比较条件，因此不能够直接这样写selecte.*from(select*fromemporderbysaldesc)ewhererownum5andrownum=10;因此需要将selecte.*,rownumrnfrom(select*fromemporderbysaldesc)ewhererownum=10查询结果看做一个临时表，这个临时表除了有表e中的所有列之外，多添加一个rownum列并取别名为rn，这时rn已经作为临时表中一个真实的列存在了，因此可以使用或=比较条件：select*from(selecte.*,rownumrnfrom(select*fromemporderbysaldesc)ewhererownum=10)wherern5;两次查询示例图如下：第一次取前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,deptnofromem