Oracle基本查询 二

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 的记录，最终查询结果如图： 

建表语句在emp.sql 中 
答：考察知识点：union 
select id,content from ( 
select id, 3 mark, url content from pages where url like '%baidu%' 
union 
select id, 2, title from pages where title like '%baidu%' 
union 
select id, 1, body from pages where body like '%baidu%' 
) order by mark desc; 
要点：union 可以用来合并多次查询结果。这里需要注意多次查询的结果列的个数和类型必
须相同，合并后的结果集也可以看做一张表，表的列的类型和名称由union 的第一条查询结
果来决定。 
这里用到一个技巧：手工指定一个优先级mark 列，最后根据mark 列排序。 
04. 现有STUDENT(学生), COURSE(课程), SC(成绩)表,完成以下需求(建表语句在emp.sql 
中,综合考察) 
a) 查询选修课程为web 的学员学号和姓名 
答： 
select s.sid,s.name from student s 
inner join sc on(s.sid=sc.sid) inner join course c on (c.cid=sc.cid) 
where c.name = 'web'; 
分析：课程与学生表没有直接联系，必须通过中间成绩表做2 次表连接 
b) 查询课程编号为2 的学员姓名和单位 
答： 
select s.name,s.dept from student s inner join sc on (s.sid=sc.sid) 
where sc.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 s.name,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 s.name,s.dept from student s where sid in 
(select sid from sc group by sid having (count(*) = (select count(*) from course))); 
e) 查询选修课程超过3 门的学员姓名和单位 
答： 
select s.name,s.dept from student s 

where sid in (select sid from sc group by sid having (count(*) > 3)); 
f) 找出没有选修过Teacher LI 讲授课程的所有学生姓名 
答： 
select s.name 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, s.name, s.dept, s.age; 
h) 列出既学过1 号课程，又学过2 号课程的所有学生姓名 
答： 
select s.name 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, s.name, 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 

where r1-1=r2; 
06. 根据EMP 表数据产生如下格式的报表（统计各部门，各职位的人数） 
答：方法1 考察知识点case 
select deptno, 
count(case when job = 'PRESIDENT' then 1 else null end) PRESIDENT, 
count(case when job = 'MANAGER' then 1 else null end) MANAGER, 
count(case when job = 'CLERK' then 1 else null end) CLERK, 
count(case when job = 'SALESMAN' then 1 else null end) SALESMAN, 
count(case when job = 'ANALYST' then 1 else null end) ANALYST 
from emp group by deptno order by deptno; 
方法2 考察知识点：自连接 
select d.deptno, 
count(distinct PRESIDENT.empno) PRESIDENT, 
count(distinct MANAGER.empno) MANAGER, 
count(distinct CLERK.empno) CLERK, 
count(distinct SALESMAN.empno) SALESMAN, 
count(distinct ANALYST.empno) ANALYST from dept d 
left join emp PRESIDENT 
on (d.deptno=PRESIDENT.deptno and PRESIDENT.job='PRESIDENT') 
left join emp MANAGER 
on (d.deptno=MANAGER.deptno and MANAGER.job='MANAGER') 
left join emp CLERK 
on (d.deptno=CLERK.deptno and CLERK.job='CLERK') 
left join emp SALESMAN 
on (d.deptno=SALESMAN.deptno and SALESMAN.job='SALESMAN') 
left join emp ANALYST 
on (d.deptno=ANALYST.deptno and ANALYST.job='ANALYST') 
group by d.deptno order by d.deptno; 
分析：通过dept 表多次左外连接emp 表，比如说 
select d.deptno, d.dname, e.empno, e.job from dept d 
left join emp e on (d.deptno = e.deptno and e.job='CLERK'); 
结果如下： 

可以看出这是求出每个部门职位为CLERK 的员工，将此结果按deptno 分组求个数： 
select d.deptno, count(empno) CLERK from dept d 
left join emp e on (d.deptno = e.deptno and e.job='CLERK') 
group by d.deptno order by d.deptno; 
其中CLERK 列即为最终结果所需列。 
如此类推，连接一次，求出一列，但需要注意，多表连接后，最后结果中会有重复记录，因
此使用count(distinct empno)排除重复记录后再计算个数才为正确结果。 
07. 根据EMP 表数据产生如下格式的报表(统计各职位,各部门的人数)(06 题的变体) 
答：方法1 考察知识点case 
select job, 
count(case when deptno = 10 then 1 else null end) "10", 
count(case when deptno = 20 then 1 else null end) "20", 
count(case when deptno = 30 then 1 else null end) "30" 
from emp group by job order by job; 
思路：同第06 题，注意列别名如果为数字开头必须使用双引号。 
方法2 考察知识点：自连接 

select e.job, count(distinct d10.empno) "10",count(distinct d20.empno) "20", count(distinct 
d30.empno) "30" from 
(select job from emp group by job) e 
left join emp d10 on (e.job=d10.job and d10.deptno = 10) 
left join emp d20 on (e.job=d20.job and d20.deptno = 20) 
left join emp d30 on (e.job=d30.job and d30.deptno = 30) group by e.job order by job; 
08. 按照如下格式显示7369 号员工的信息 
答：考察知识点 UNION 
select empno, 'ENAME' as KEY, ename VALUE from emp where empno = 7369 
union 
select empno, 'JOB', job from emp where empno = 7369 
union 
select empno, 'HIREDATE', to_char(hiredate,'yyyy-mm-dd') a from emp where empno = 7369 
union 
select empno, 'MGR', to_char(mgr) from emp where empno = 7369 
union 
select empno, 'SAL', to_char(sal) from emp where empno = 7369 
union 
select empno, 'COMM', to_char(comm) from emp where empno = 7369 
union 
select empno, 'DEPTNO', to_char(deptno) from emp where empno = 7369; 
分析：使用UNION 可以将多次查询结果连接起来，要注意，每条查询的列的个数和数据类
型必须一致。因此在查询时都使用了to_char 函数将第二列同一转换为字符型。 
Part IV(扩展知识点) 
01. 分级查询 

Oracle 提供其他数据库没有的分级查询操作： 
例如：希望通过一次查询以树状结构显示EMP 表中的所有上下级关系 
select level, lpad(' ',level-1) || empno empno, ename, mgr,deptno from emp start with 
empno=7839 connect by prior empno = mgr; 
level 是ORACLE 关键字表示分级级别，其中lpad(' ',level-1) 函数是根据level 的值生成
level-1 个空格 
又如：希望通过某个员工回溯它的所有上级，包括上级的上级 
select level, lpad(' ',level-1) || empno, ename, mgr, deptno from emp start with empno=7369 
connect by empno = prior mgr; 
02. CUBE（立方查询） 
如果想统计EMP 表中的所有职员数，每个部门的职员数，每种职位的职员数，每个部
门每种职位的职员数，使用普通分组查询需要查询4 次。但利用ORACLE 的增强语法， 
可以非常方便的完成此类查询（经常用于生成报表） 
例如： 

select count(*),deptno,job,grouping_id(deptno,job) from emp group by cube (deptno,job) 
order by grouping_id(deptno,job) ; 
可以生成如下形式的报表： 
03. 如何考察查询效率 
1) 在SQL-PLUS 中执行 set autotrace on explain 
2) 执行查询 
返回结果中cost（成本）与bytes（字节数）都是越低越好。 
04. 闪回查询 
ORACLE 的特点还有能够更快速的恢复之前误操作的数据，这是通过闪回日志完成的 
例如：查询20 分钟之前的emp 表 
select * from emp as of timestamp sysdate - interval '20' minute; 
再如：恢复5 分钟之前的7369 号员工姓名 
update emp e set ename = 
(select ename from emp 

as of timestamp systimestamp - interval '5' minute where empno=e.empno ) 
where empno=7369; 
甚至可以查询及恢复被删除的表 
select * from user_recyclebin; 
flashback table 表 to before drop; 
05. 正则表达式 
ORACLE 在建表或查询时提供正则表达式支持： 
例如：查询名字以S 作为开头字母的员工 
select * from emp where regexp_like(ename ,'^S'); 
例如：替换电话号码显示方式 
select regexp_replace('123.321.1234', '([0-9]{3})\.([0-9]{3})\.([0-9]{4})', '(\1) \2-\3') 
from dual; 
例如：取得email 地址中的用户名 
select regexp_substr( 'yihang@163.com' , '^[^@]+') from dual; 
例如：取得email 地址中的域名 
select regexp_substr( 'yihang@163.com' , '[^@]+$') from dual; 
06. 如何加注释 
建表时给表和列加注释是一个比较好的数据库编程习惯 
例如：表加注释 
comment on table 表 is '表注释'; 
例如：列加注释 
comment on column 表.列 is '列注释'; 
例如：查表注释 

select * from user_tab_comments where table_name = 表名; 
例如：查列注释 
select * from user_col_comments where table_name = 表名;