Oracle常见面试题

模块一??????????

Key Point

l基本SQL查询

l运算符与函数

l子查询

l连接查询

建表语句emp.sql

Part I(第一天)

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

select * from emp;

使用*的缺点有

a)查询出了不必要的列

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

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

select * from emp where job = 'PRESIDENT';

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

select * from emp where comm = 0 or comm is null;

04.查询入职日期在1981-5-1到1981-12-31之间的所有员工信息

答：通过此题掌握常用日期函数

select * from emp where hiredate

between to_date('1981-5-1','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');

05.查询所有名字长度为4的员工的员工编号,姓名

答：

select * from emp where length(ename) = 4;

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

select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job ='CLERK';

07.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息

答：考察知识点模糊查询

select * from emp where ename not like '%L%' or ename like '%SM%';

08.显示各个部门经理('MANAGER')的工资

答：

select sal from emp where job = 'MANAGER';

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

答：

select * from emp where comm > sal;

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

答：

select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm');

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

select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm');

12.求1982年入职的员工(考察知识点:单行函数)

答：

select * from emp where to_char(hiredate,'yyyy') = '1982';

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

答：

select * from emp where hiredate

between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1;

14.求1981年各个月入职的的员工个数(考察知识点:组函数)

答：

select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm')

from emp where to_char(hiredate,'yyyy')='1981'

group by trunc(hiredate,'month')

order by trunc(hiredate,'month');

Part II(第二天)

01.查询各个部门的平均工资

答：考察知识点：分组

select deptno,avg(sal) from emp group by deptno;

02.显示各种职位的最低工资

答：考察知识点：分组

select job,min(sal) from emp group by job;

03.按照入职日期由新到旧排列员工信息

答：考察知识点：排序

select * from emp order by hiredate desc;

04.查询员工的基本信息，附加其上级的姓名

答：考察知识点：自连接

select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;

05.显示工资比'ALLEN'高的所有员工的姓名和工资

答：考察知识点：子查询

select * from emp where sal > (select sal from emp where ename='ALLEN');

分析：当查询结果是一行一列时，可以将此结果看做一个值，参与条件比较。

06.显示与'SCOTT'从事相同工作的员工的详细信息

答：考察知识点：子查询

select * from emp where job = (select * from emp where ename='SCOTT');

分析：同第5题

07.显示销售部('SALES')员工的姓名

答：考察知识点：连接查询

select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';

08.显示与30号部门'MARTIN'员工工资相同的员工的姓名和工资

答：考察知识点：子查询

select ename, sal from emp

where sal = (select sal from emp where deptno=30 and ename='MARTIN');

分析：同第5题

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

答：考察知识点：子查询

select * from emp where job='SALESMAN' and sal > (select avg(sal) from emp);

10.显示所有职员的姓名及其所在部门的名称和工资

答：考察知识点：表连接

select ename, job, dname from emp e, dept d where e.deptno = d.deptno;

11.查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地

答：考察知识点：表连接

select empno,ename,dname,loc from emp e, dept d

where e.deptno = d.deptno and danme='RESEARCH';

12.查询各个部门的名称和员工人数

答：考察知识点：子查询，表连接

select * from (select count(*) c, deptno from emp group by deptno) e

inner join dept d on e.deptno = d.deptno;

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

答：考察知识点：子查询

select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;

分析：查询结果是一行一列，可以将查询结果看做一个值，进行条件比较

14.查询工资相同的员工的工资和姓名

答：考察知识点：子查询

select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;

分析：此题目类似于17题，见17题分析。

15.查询工资最高的3名员工信息

答：考察知识点：子查询，rownum

select * from (select * from emp order by sal desc) where rownum <= 3;

分析：见21题要点一

16.按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第

三名继续排)

答：考察知识点：子查询

select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;

分析：

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

17.求入职日期相同的(年月日相同)的员工

答：考察知识点：子查询

select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1;

分析：常见的一个误解就是把此题当做自连接做：

select * from emp e1, emp e2 where e1.hiredate = e2.hiredate and e1.empno <> e2.empno;

这样做的结果中对于只有两个日期相等的没有错误，查询结果有2条，但如果有三个日期相

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

要点也是理解子查询的含义select count(*) from emp where e.hiredate=hiredate ，代表取得与当前员工入职日期相等的人数个数，如果个数大于1表示此日期有相等的。

18. 查询每个部门的最高工资 答：考察知识点：分组

select deptno, max(sal) maxsal from emp group by deptno order by deptno;

19. 查询每个部门,每种职位的最高工资 答：考察知识点：分组

select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;

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

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

select * from salgrade; select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;

21. 查询工资最高的第6-10名员工 答：考察知识点：子查询, rownum

select * from ( select e.*,rownum rn from (select * from emp order by sal desc) e where rownum <=

10) where rn > 5; 分析：

要点一是rownum 不能直接和order by 连用，因为rownum 先产生，order by 后执行，因此需要将

select * from emp order by sal desc

先排序之后的结果看做一个临时表，再对此临时表产生rownum 编号。

要点二是rownum 不能用作>或>=的比较条件，因此不能够直接这样写

select e.* from (select * from emp order by sal desc) e where rownum > 5 and rownum <=10; 因此需要将

select e.*,rownum rn from

(select * from emp order by sal desc) e where rownum <=10

查询结果看做一个临时表，这个临时表除了有表e 中的所有列之外，多添加一个rownum 列并取别名为rn ，这时rn 已经作为临时表中一个真实的列存在了，因此可以使用>或>=比较条件：

select * from ( select e.*,rownum rn from (select * from emp order by sal desc) e where rownum <=

10)

where rn > 5; 两次查询示例图如下：第一次取前10条，第二次排除前5条

22.查询各部门工资最高的员工信息

答：考察知识点：子查询

select * from emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno));

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

思路2：

select e.* from (select max(sal) maxsal, deptno from emp group by deptno) b, emp e where e.deptno = b.deptno and b.maxsal = e.sal;

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

23.查询每个部门工资最高的前2名员工

答：考察知识点:子查询

select * from emp e where

(select count(*) from emp where sal > e.sal and e.deptno =deptno) < 2

order by deptno, sal desc;

分析：此题类似于第16题，需要理解select count(*) from emp where sal > e.sal and e.deptno = deptno的含义：求工资大于当前员工工资（e.sal）并且部门编号等于当前员工部门编号（e.deptno）的员工的个数，此个数+1表示排名，< 2表示取前两名。

思路2：

使用oracle提供的分析函数rank：

select * from (

select rank() over (partition by deptno order by sal desc) rank, e.* from emp e

) where rank < 3;

rank函数的作用是产生排名，与普通函数不同，高亮部分都是函数语法部分，其中over是关键字，总体意思是指按部门编号分组（partition by deptno），按工资降序（order by sal desc）排名。

思路3：

步骤1：按照部门，工资降序排列，并产生编号

select e.*,rownum rn from (select * from emp order by deptno,sal desc) e;

步骤2：在此基础上再按照部门编号分组，求每组的编号的最小值

select min(rn) minrank,deptno from

(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)

group by deptno;

步骤3：将两步产生的结果看做是临时表分别称为t1，t2，做连接，连接条件时t1表中部门编号等于t2部门编号且t1.rn >= t2.minrank and t1.rn <= t2.minrank+1

select t1.* from

(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e) t1,

(select min(rn) minrank,deptno from

(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)

group by deptno) t2

where t1.deptno = t2.deptno and t1.rn >= t2.minrank and t1.rn <= t2.minrank+1;

24.查询出有3个以上下属的员工信息

答：考察知识点：自连接,子查询

select * from emp e where

(select count(*) from emp where e.empno =mgr) > 2;

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

答：考察知识点：子查询

select * from emp e where sal >

(select avg(sal) from emp where (deptno = e.deptno))

order by deptno;

分析：思路与22题相同。

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

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

select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se where avgsal =(select max(avg(sal)) from emp group by deptno) and d.deptno =se.deptno;

分析：

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

select avg(sal) avgsal, deptno from emp group by deptno;

步骤2：求最高的平均工资：

select max(avg(sal)) from emp group by deptno;

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

select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se where avgsal =(select max(avg(sal)) from emp group by deptno) and d.deptno =se.deptno;

27.查询大于各部门总工资的平均值的部门信息

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

select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se

where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno =d.deptno; 分析：

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

select sum(sal) sumsal, deptno from emp group by deptno;

步骤2：求每总工资平均值

select avg(sum(sal)) from emp group by deptno;

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

select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno =d.deptno;

28.查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接

查询)

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

select e.*,sumsal from emp e, (select sum(sal) sumsal, deptno from emp group by deptno) se where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno =e.deptno;

分析：类似于26题，27题

29.查询没有员工的部门信息

答：考察知识点：表连接

select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null;

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

定为null

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

单中的商品数量（orderitem表），名称（product表），价格（product表）

答：考察知识点：多表连接

select https://www.360docs.net/doc/b714867421.html,ername, o.orderid, o.orderdate, o.totalprice, p.productname, p.price, i.qty

from users u inner join orders o on (https://www.360docs.net/doc/b714867421.html,ername = https://www.360docs.net/doc/b714867421.html,ername)

inner join orderitem i on (o.orderid =i.orderid)

inner join product p on (p.productid =i.productid)

where https://www.360docs.net/doc/b714867421.html,ername = 'huxz';

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

量（orderitem表），购买时的价格（product表）

答：考察知识点：多表连接

select https://www.360docs.net/doc/b714867421.html,ername, o.orderdate, p.productname, p.price, i.qty

from product p inner join orderitem i on (p.productid = i.productid)

inner join orders o on (o.orderid =i.orderid)

inner join users u on (https://www.360docs.net/doc/b714867421.html,ername =https://www.360docs.net/doc/b714867421.html,ername)

where p.productid = 100001;

32.查询出哪些商品从未被订购过

答：考察知识点：连接查询

select p.* from product p left join orderitem i on(i. productid = p. productid)

where i.orderitemid is null;

分析：同29题

33.查询出被订购过2次以上的商品信息

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

select p.* from product p

where (select count(*) from orderitem where productid = p. productid) >= 2;

分析：子查询select count(*) from orderitem where productid = p. productid的含义是：当前商品在orderitem表中出现的次数，即被订购的次数

Part III(面试题目)

01. tmp表中有如下记录（建表SQL见emp.sql）

要求结果格式为：

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

select rq,

count(case when shengfu='WIN' then 1 else null end) WIN,

count(case when shengfu='LOSE' then 1 else null end) LOSE from tmp group by rq ;

分析：

要点：在计数时，配合case…when…语句只统计取值为WIN或LOSE的个数。case语句返回不为null的值即会加入count计数，返回null则不会加入count计数。

02. 查询当前月有多少天

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

select trunc(add_months(sysdate,1),'month') - trunc(sysdate,'month') from dual;

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

现要求将url匹配的排在最前，title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。现要求查询所有匹配baidu的记录，最终查询结果如图：

建表语句在

select

union

union

要点：

须相同，合并后的结果集也可以看做一张表，表的列的类型和名称由

果来决定。

04. 现有

中,综合考察)

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

答：

select s.sid,https://www.360docs.net/doc/b714867421.html, from student s

inner join sc on(s.sid=sc.sid) inner join course c on (c.cid=sc.cid)

where https://www.360docs.net/doc/b714867421.html, 'web';

分析：课程与学生表没有直接联系，必须通过中间成绩表做2次表连接

b)查询课程编号为2的学员姓名和单位

答：

select https://www.360docs.net/doc/b714867421.html,,s.dept from student s inner join sc on (s.sid=sc.sid)

where s c.cid=2;

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

答：

select name,dept from student where sid not in

(select s.sid from student s left join sc on s.sid =sc.sid where cid =4);

或：

select name,dept from student

where not exists(select sid from sc where sc.sid =s.sid and cid =4);

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

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

答：

select https://www.360docs.net/doc/b714867421.html,,s.dept from student s where sid in

(select sid from sc group by sid having (count(*) = (select count(*) from course)));

分析：

步骤1：查询出所有课程的数目

select count(*) from course;

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

select sid from sc group by sid having (count(*) = (select count(*) from course));

步骤3：再根据该sid查询学员的详细信息

select https://www.360docs.net/doc/b714867421.html,,s.dept from student s where sid in

(select sid from sc group by sid having (count(*) = (select count(*) from course)));

e)查询选修课程超过3门的学员姓名和单位

答：

select https://www.360docs.net/doc/b714867421.html,,s.dept from student s

where sid in (select sid from sc group by sid having (count(*) > 3));

f)找出没有选修过Teacher LI讲授课程的所有学生姓名

答：

select https://www.360docs.net/doc/b714867421.html, from student s where sid not in

(select sid from course c left join sc on (c.cid = sc.cid) where c.teacher='Teacher LI');

g)列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

答：

select s.*, b.avgsal from student s,

(select sc.sid,avg(score) avgscore from sc ,

(select sid from sc where score < 60 group by sid having(count(*) >=2)) a where sc.sid = a.sid group by sc.sid) b

where s.sid = b.sid;

分析：

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

select sid from sc where score < 60 group by sid having(count(*) >=2);

步骤2：步骤1结果与真实表sc做连接，算平均成绩

select sc.sid,avg(score) avgscore from sc,

(select sid from sc where score < 60 group by sid having(count(*) >=2)) a where sc.sid =a.sid group by sc.sid;

步骤3：步骤2结果与真实表student做连接，查学员姓名

select s.*, b. avgscore from student s,

(select sc.sid,avg(score) avgscore from sc ,

(select sid from sc where score < 60 group by sid having(count(*) >=2)) a where sc.sid = a.sid group by sc.sid) b

where s.sid = b.sid;

思路2：

步骤1：同上

步骤2：步骤1结果与真实表sc，student共3张表做连接

select s.*,avg(sc.score) avgscore from student s, sc,

(select sid from sc where score < 60 group by sid having(count(*) >=2)) a

where s.sid =sc.sid and s.sid =a.sid;

步骤3：可以发现，该结果中sid, name, dept, age都是取值都相同，按照这些列直接进行分组即可：

select s.*,avg(sc.score) avgscore from student s, sc,

(select sid from sc where score < 60 group by sid having(count(*) >=2)) a

where s.sid =sc.sid and s.sid =a.sid group by s.sid, https://www.360docs.net/doc/b714867421.html,, s.dept, s.age;

h)列出既学过1号课程，又学过2号课程的所有学生姓名

答：

select https://www.360docs.net/doc/b714867421.html, from student s inner join

(select sc.sid from sc where sc.cid in (1,2) group by sid having (count(*) =2)) a

on (s.sid a.sid);

分析：要点是不仅要学过1，2号课程in (1,2)，并且要求同时学过此两门课count(*) = 2

i)列出1号课成绩比2号课成绩高的所有学生的学号，姓名和1号课和2号课的成

绩

答：

select s.sid, https://www.360docs.net/doc/b714867421.html,, sc1.score, sc2.score from sc sc1,sc sc2,student s

where s.sid =sc1.sid and sc1.sid =sc2.sid

and sc1.cid =1 and sc2.cid =2 and sc1.score > sc2.score;

分析：要点在于自连接，把成绩表拆成两张表来看，sc1中只考虑1号课，sc2中只考虑2号课且sc1.score > sc2.score；最后再考虑将结果与student表连接查询姓名。

05. 现有test表，表中数据如图所示：

要求按照格式如下输出结果：

a)连续的编号要求如下格式

b)不连续的编号要求如下格式

答：

a)求连续的，考察知识点：rownum，子查询

分析：查看连续id与rownum之间的关系，运行select id, rownum, id-rownum from test;

参考下图看出规律：

可以发现，id-rownum取值相同的，就是那些id编号连续的。按照id-rownum分组并求每组的最大，最小值即可。

select a.* from

(select min(id) begin, max(id) end from test group by (id - rownum) order by id - rownum) a;

b)不连续的，考察知识点，rownum，子查询

分析：

步骤一：

查询有上一条记录的个数

select id, (select count(*) from test where id+1 = t1.id) from test t1;

查询有下一条记录的个数

select id, (select count(*) from test where id-1 = t1.id) from test t1;

分析结果可知，个数为0的即为我们所需要的。

步骤二：

select id, rownum r1 from test t1 where (select count(*) from test where id+1 = t1.id) = 0;

select id, rownum r2 from test t1 where (select count(*) from test where id-1 = t1.id) = 0;

分析结果可知，要求如上图格式的数据将查询1中的r1-1 = 查询2中的r2列即可：

select b.id begin, a.id end from

(select id, rownum r1 from test t1 where (select count(*) from test where id+1 = t1.id) = 0) a, (select id, rownum r2 from test t1 where (select count(*) from test where id-1 = t1.id) = 0) b