1 Oracle子查询精选面试题

有职员表emp，表结构表－1所示：

职员表emp的示例数据如图-1所示。

有部门表dept，表结构如表－2所示：

部门表dept的示例数据如图-2所示。

请根据上述表结构和示例数据，完成如下查询。

1. SALES部门有哪些职位？

2. 哪些人不是别人的经理？

3. 谁的薪水比FORD高？如果有多个同名，比任何一个叫FORD的人高就行。

4.谁和FORD同部门？列出除了FORD之外的员工名字。

5. 哪个部门的人数比部门20的人数多？

6. 列出员工名字和职位 , 查询员工所在的部门平均薪水大于2000元的员工信息。

参考答案

各练习的参考答案如下：

1.

select distinct job from emp
where deptno = ( select deptno
from dept
where dname = ‘SALES’) ;

2.

关联子查询方式实现的SQL语句:

select ename from emp a
where not exists (select 1 from emp
where mgr = a.empno) ;

普通子查询方式实现的SQL语句:

select ename from emp
where empno not in ( select distinct mgr 
from emp
where mgr is not null) ;

3.

select ename from emp
where sal > ANY( select sal from emp
where ename = 'FORD' ) ;

4.

select ename
from emp
where deptno = (select deptno from emp
where ename = 'FORD')
and ename <> 'FORD' ;

5.

select deptno , count(*)
from emp
group by deptno
having count(*)  > ( select count(*) from emp
where deptno = 20 ) ;

6.

select ename, job
from emp
where deptno in (select deptno
from emp
group by deptno
having avg( nvl(sal,0)) > 2000 ) ;

2 简述where子句中为什么不能写rownum>…

参考答案

rownum是伪列，随着结果集生成，返回的第一行分配的是1，第二行是2等等，生成的结果是依次递加的，没有1就不会有2，不返回的就不算，而且一旦生成，就不会变化。第一条返回的结果的rownum为1，不支持where做大于查询，只能做<和<=，但并不会报错，只是返回的数据为空，这是因为根本不能满足这样的where条件。 如where rownum>2,取回第1条数据的rownum为1，不满足，就舍弃这条记录。再看下一条，然后取第2条数据的rownum还是为1，还是不满足，再舍弃。以此类推，最终舍弃了所有的数据，这就是所谓不支持的原因。

3 Oracle分页查询面试题

news表的表结构如图-3所示：

写SQL语句，按新闻时间倒序排列，即最新的排在最前面，每页输出5条，查询出第二页。

参考答案

SQL语句如下所示：

SELECT * FROM 
( 
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM news order by occurtime desc) A 
WHERE ROWNUM <= 10 )
WHERE RN >= 6;

4 下列关于decode函数，说法正确的是

A. decode函数用来实现IF-ELSE的逻辑功能

B. decode函数建议设置默认值，如果未能与任一搜索条件匹配则函数返回默认值

C. decode函数只能处理字符串数据

D. decode函数的功能可以用case语句替代实现

参考答案

本题的正确答案为ABD。

DECODE函数基本语法如下：

DECODE (expr, search1, result1[, search2, result2…][, default])

DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果；可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值；default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。与DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。

5 下面SQL语句的输出结果是

SELECT ename, job, 
DECODE(job,'PRESIDENT','A',
'MANAGER','B',
'ANALYST','C',
'SALESMAN','D',
'CLERK','E'
) AS "Grade"
FROM EMP;

参考答案

上述SQL语句的输出结果如图-4所示。

6 Oracle高级查询经典面试题

1.有学员表student，表结构如表－3所示：

学员表student的示例数据如图-5所示。

2.在上题背景下，学生表中按照所在专业分组，同专业内按成绩倒序排序，成绩相同则按学号正序排序，并给予组内等级，用Rank_ID表示。

3.有专业表class，表结构如表－4所示：

专业表的示例数据如图-6所示。

学员表的class_id数据参照class表cid列的数据。关联学员表student和class表，按照class_id分组，每组内按照学生成绩倒序排序，相同成绩按照学号正序排列，列出学生所在的专业名字、学生姓名、成绩及等级drank。

4. 创建mygroup，该表存储了每组员工的工资情况，创建表的SQL语句如下所示：

create table mygroup (
group_id number(4),
job varchar2(10), 
name varchar2(10), 
salary number(10,2)
);

向mygroup表中插入测试数据，SQL语句如下所示：

insert into mygroup values (10,'Coding',    'Bruce',1000);
insert into mygroup values (10,'Programmer','Clair',1000);
insert into mygroup values (10,'Architect', 'Gideon',1000);
insert into mygroup values (10,'Director',  'Hill',1000);

insert into mygroup values (20,'Coding',    'Jason',2000);
insert into mygroup values (20,'Programmer','Joey',2000);
insert into mygroup values (20,'Architect', 'Martin',2000);
insert into mygroup values (20,'Director',  'Michael',2000);

insert into mygroup values (30,'Coding',    'Rebecca',3000);
insert into mygroup values (30,'Programmer','Rex',3000);
insert into mygroup values (30,'Architect', 'Richard',3000);
insert into mygroup values (30,'Director',  'Sabrina',3000);

insert into mygroup values (40,'Coding',    'Samuel',4000);
insert into mygroup values (40,'Programmer','Susy',4000);
insert into mygroup values (40,'Architect', 'Tina',4000);
insert into mygroup values (40,'Director',  'Wendy',4000);

commit;

请统计各组工资的和以及工资总和，要求使用rollup函数完成。

5.分别按（group_id,job）、（group_id）、（job）以及全表统计工资和，要求使用CUBE函数来完成。

6.分别按（group_id）、（job）统计工资和，要求使用GROUPING SETS函数来完成。

参考答案

各练习的参考答案如下所示：

1.

SELECT sid, sname,score,class_id,
ROW_NUMBER()
   OVER (PARTITION BY class_id ORDER BY score desc)     AS order_id 
   FROM student;

2.

SELECT sid, sname,score,class_id,
RANK() OVER (PARTITION BY class_id 
   ORDER BY score DESC,sid)  as "Rank_ID"
   FROM student;

3.

SELECT c.cname,s.sname,s.score, 
            DENSE_RANK()
      OVER (PARTITION BY s.class_id ORDER By s.score desc, sid)     
            AS drank
   FROM student s join class c
on s.class_id  =c.cid;

4.

select group_id,sum(salary) from mygroup group by rollup(group_id);

5.

SELECT group_id, job ,
SUM(salary) AS total_salary 
FROM   mygroup 
GROUP BY CUBE (group_id,job)
ORDER BY group_id,job;

6.

SELECT group_id, job, SUM(salary)  
FROM  mygroup
GROUP BY GROUPING SETS (  (group_id), (job))
order by 1, 2;