oracle面试题


模块一 SQL(DQL) K P l 基本 SQL 查询 l 运算符与函数 l 子查询 l 连接查询 建表语句 emp.sql P 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'); P 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 u.username, o.orderid, o.orderdate, o.totalprice, p.productname, p.price, i.qty from users u inner join orders o on (u.username = o.username) inner join orderitem i on (o.orderid = i.orderid) inner join product p on (p.productid = i.productid) where u.username = 'huxz'; 31. 查询 100001 号商品被哪些顾客(users 表)购买过,下单日期(orders 表),每人购买的数 量(orderitem 表),购买时的价格(product 表) 答:考察知识点:多表连接 select u.username, 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 (u.username = o.username) 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 表中出现的次数,即被订购的次数 P 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 函数将第二列同一转换为字符型。 P 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 = 表名;
还剩26页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 11 人已下载

下载pdf

pdf贡献者

xinlu

贡献于2012-01-17

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf