当前位置：首页 > 2014年信息技术考核数据库试题
= (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) \else null end) \
job order by job;
思路：同第06 题，注意列别名如果为数字开头必须使用双引号。 方法2 考察知识点：自连接
select e.job, count(distinct d10.empno) \ d30.empno) \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 = 表名;
本文作者：...共分享92篇相关文档
