模塊一 SQL(DQL)

l 基本SQL 查詢
l 運(yùn)算符與函數(shù)
l 子查詢
l 連接查詢
建表語句emp.sql
Part I(第一天)
01. 查詢員工表所有數(shù)據(jù), 并說明使用*的缺點(diǎn)
答：
select * from emp;
使用*的缺點(diǎn)有
a) 查詢出了不必要的列
b) 效率上不如直接指定列名
02. 查詢職位(JOB)為'PRESIDENT'的員工的工資
答：
select * from emp where job = 'PRESIDENT';
03. 查詢傭金(COMM)為0 或?yàn)镹ULL 的員工信息
答：重點(diǎn)是理解0 與null 的區(qū)別
select * from emp where comm = 0 or comm is null;

04. 查詢?nèi)肼毴掌谠?981-5-1 到1981-12-31 之間的所有員工信息
答：通過此題掌握常用日期函數(shù)
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 號部門的所有經(jīng)理('MANAGER')和20 號部門的所有職員('CLERK')的詳細(xì)信息
答：
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job ='CLERK';
07. 顯示姓名中沒有'L'字的員工的詳細(xì)信息或含有'SM'字的員工信息
答：考察知識點(diǎn)模糊查詢
select * from emp where ename not like '%L%' or ename like '%SM%';
08. 顯示各個(gè)部門經(jīng)理('MANAGER')的工資
答：
select sal from emp where job = 'MANAGER';
09. 顯示傭金(COMM)收入比工資(SAL)高的員工的詳細(xì)信息
答：
select * from emp where comm > sal;
10. 把hiredate 列看做是員工的生日,求本月過生日的員工(考察知識點(diǎn):單行函數(shù))
答：
select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm');

11. 把hiredate 列看做是員工的生日,求下月過生日的員工(考察知識點(diǎn):單行函數(shù))
答：
select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm');
12. 求1982 年入職的員工(考察知識點(diǎn):單行函數(shù))
答：
select * from emp where to_char(hiredate,'yyyy') = '1982';
13. 求1981 年下半年入職的員工(考察知識點(diǎn):單行函數(shù))
答：
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 年各個(gè)月入職的的員工個(gè)數(shù)(考察知識點(diǎn):組函數(shù))
答：
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. 查詢各個(gè)部門的平均工資
答：考察知識點(diǎn)：分組
select deptno,avg(sal) from emp group by deptno;
02. 顯示各種職位的最低工資
答：考察知識點(diǎn)：分組
select job,min(sal) from emp group by job;

03. 按照入職日期由新到舊排列員工信息
答：考察知識點(diǎn)：排序
select * from emp order by hiredate desc;
04. 查詢員工的基本信息，附加其上級的姓名
答：考察知識點(diǎn)：自連接
select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;
05. 顯示工資比'ALLEN'高的所有員工的姓名和工資
答：考察知識點(diǎn)：子查詢
select * from emp where sal > (select sal from emp where ename='ALLEN');
分析：當(dāng)查詢結(jié)果是一行一列時(shí)，可以將此結(jié)果看做一個(gè)值，參與條件比較。
06. 顯示與'SCOTT'從事相同工作的員工的詳細(xì)信息
答：考察知識點(diǎn)：子查詢
select * from emp where job = (select * from emp where ename='SCOTT');
分析：同第5 題
07. 顯示銷售部('SALES')員工的姓名
答：考察知識點(diǎn)：連接查詢
select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';
08. 顯示與30 號部門'MARTIN'員工工資相同的員工的姓名和工資
答：考察知識點(diǎn)：子查詢
select ename, sal from emp
where sal = (select sal from emp where deptno=30 and ename='MARTIN');
分析：同第5 題

09. 查詢所有工資高于平均工資(平均工資包括所有員工)的銷售人員('SALESMAN')
答：考察知識點(diǎn)：子查詢
select * from emp where job='SALESMAN' and sal > (select avg(sal) from emp);
10. 顯示所有職員的姓名及其所在部門的名稱和工資
答：考察知識點(diǎn)：表連接
select ename, job, dname from emp e, dept d where e.deptno = d.deptno;
11. 查詢在研發(fā)部('RESEARCH')工作員工的編號，姓名，工作部門，工作所在地
答：考察知識點(diǎn)：表連接
select empno,ename,dname,loc from emp e, dept d
where e.deptno = d.deptno and danme='RESEARCH';
12. 查詢各個(gè)部門的名稱和員工人數(shù)
答：考察知識點(diǎn)：子查詢，表連接
select * from (select count(*) c, deptno from emp group by deptno) e
inner join dept d on e.deptno = d.deptno;
分析：主要思路是要將子查詢結(jié)果看做一個(gè)臨時(shí)表，此臨時(shí)表又可以與其他表做表連接
13. 查詢各個(gè)職位員工工資大于平均工資(平均工資包括所有員工)的人數(shù)和員工職位
答：考察知識點(diǎn)：子查詢
select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;
分析：查詢結(jié)果是一行一列，可以將查詢結(jié)果看做一個(gè)值，進(jìn)行條件比較
14. 查詢工資相同的員工的工資和姓名
答：考察知識點(diǎn)：子查詢

select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;
分析：此題目類似于17 題，見17 題分析。
15. 查詢工資最高的3 名員工信息
答：考察知識點(diǎn)：子查詢，rownum
select * from (select * from emp order by sal desc) where rownum <= 3;
分析：見21 題要點(diǎn)一
16. 按工資進(jìn)行排名,排名從1 開始,工資相同排名相同(如果兩人并列第1 則沒有第2 名,從第
三名繼續(xù)排)
答：考察知識點(diǎn)：子查詢
select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;
分析：
此題的要點(diǎn)在于理解select count(*) from emp where sal > e.sal+1 的含義，e.sal 代表當(dāng)前員工，
該子查詢的含義就是求比當(dāng)前員工工資高的人數(shù)個(gè)數(shù)：比此員工工資高的人數(shù)個(gè)數(shù)如果為
0，表示此人排名第一，比此員工工資高的人數(shù)個(gè)數(shù)如果為1，表示此人排名第二… 所以該
子查詢結(jié)果就表示排名。
17. 求入職日期相同的(年月日相同)的員工
答：考察知識點(diǎn)：子查詢
select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1;
分析：常見的一個(gè)誤解就是把此題當(dāng)做自連接做：
select * from emp e1, emp e2 where e1.hiredate = e2.hiredate and e1.empno <> e2.empno;
這樣做的結(jié)果中對于只有兩個(gè)日期相等的沒有錯(cuò)誤，查詢結(jié)果有2 條，但如果有三個(gè)日期相

等的查詢結(jié)果就是6 條，其中3 條是重復(fù)的。
要點(diǎn)也是理解子查詢的含義select count(*) from emp where e.hiredate=hiredate，代表取得與當(dāng)
前員工入職日期相等的人數(shù)個(gè)數(shù)，如果個(gè)數(shù)大于1 表示此日期有相等的。
18. 查詢每個(gè)部門的最高工資
答：考察知識點(diǎn)：分組
select deptno, max(sal) maxsal from emp group by deptno order by deptno;
19. 查詢每個(gè)部門,每種職位的最高工資
答：考察知識點(diǎn)：分組
select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;
分析：要點(diǎn)是理解多列分組：部門與職位都相同的分為一組，求每組的最高工資，其實(shí)就是
表示每個(gè)部門,每種職位的最高工資
20. 查詢每個(gè)員工的信息及工資級別(用到表Salgrade)
答：考察知識點(diǎn)：不等值連接
select * from salgrade;
select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;
21. 查詢工資最高的第6-10 名員工
答：考察知識點(diǎn)：子查詢, rownum
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10)
where rn > 5;
分析：

要點(diǎn)一是rownum 不能直接和order by 連用，因?yàn)閞ownum 先產(chǎn)生，order by 后執(zhí)行，因此
需要將
select * from emp order by sal desc
先排序之后的結(jié)果看做一個(gè)臨時(shí)表，再對此臨時(shí)表產(chǎn)生rownum 編號。
要點(diǎn)二是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
查詢結(jié)果看做一個(gè)臨時(shí)表，這個(gè)臨時(shí)表除了有表e 中的所有列之外，多添加一個(gè)rownum 列
并取別名為rn，這時(shí)rn 已經(jīng)作為臨時(shí)表中一個(gè)真實(shí)的列存在了，因此可以使用>或>=比較
條件：
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10)
where rn > 5;
兩次查詢示例圖如下：第一次取前10 條，第二次排除前5 條

22. 查詢各部門工資最高的員工信息
答：考察知識點(diǎn)：子查詢
select * from emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno));
分析：要點(diǎn)同樣是理解子查詢select max(sal) from emp where (deptno = e.deptno)獲取當(dāng)前部
門（e.deptno）的最高工資，再將此最高值與當(dāng)前工資（e.sal）進(jìn)行比較。
思路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;
將子查詢看做一個(gè)臨時(shí)表，臨時(shí)表中有最高工資列maxsal，以及deptno 列，此臨時(shí)表與真
實(shí)表emp 做表連接，連接條件為emp 表中的工資要等于臨時(shí)表的最高工資并且兩表的部門
編號要相等。
23. 查詢每個(gè)部門工資最高的前2 名員工
答：考察知識點(diǎn):子查詢
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 的含義：求工資大于當(dāng)前員工工資（e.sal）并且部門編號等于當(dāng)前員工部門編號
（e.deptno）的員工的個(gè)數(shù)，此個(gè)數(shù)+1 表示排名，< 2 表示取前兩名。
思路2：
使用oracle 提供的分析函數(shù)rank：
select * from (
select rank() over (partition by deptno order by sal desc) rank, e.* from emp e
) where rank < 3;

rank 函數(shù)的作用是產(chǎn)生排名，與普通函數(shù)不同，高亮部分都是函數(shù)語法部分，其中over 是
關(guān)鍵字，總體意思是指按部門編號分組（partition by deptno），按工資降序（order by sal desc）
排名。
思路3：
步驟1：按照部門，工資降序排列，并產(chǎn)生編號
select e.*,rownum rn from (select * from emp order by deptno,sal desc) e;
步驟2：在此基礎(chǔ)上再按照部門編號分組，求每組的編號的最小值
select min(rn) minrank,deptno from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno;
步驟3：將兩步產(chǎn)生的結(jié)果看做是臨時(shí)表分別稱為t1，t2，做連接，連接條件時(shí)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 個(gè)以上下屬的員工信息

答：考察知識點(diǎn)：自連接,子查詢
select * from emp e where
(select count(*) from emp where e.empno = mgr) > 2;
分析：關(guān)鍵是理解連接條件e.empno = mgr 是表示連接當(dāng)前員工（e.empno）和他的下屬（mgr）
25. 查詢所有大于本部門平均工資的員工信息()
答：考察知識點(diǎn)：子查詢
select * from emp e where sal >
(select avg(sal) from emp where (deptno = e.deptno))
order by deptno;
分析：思路與22 題相同。
26. 查詢平均工資最高的部門信息
答：考察知識點(diǎn)：子查詢，組函數(shù)，連接查詢
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：求每個(gè)部門的平均工資：
select avg(sal) avgsal, deptno from emp group by deptno;
步驟2：求最高的平均工資：
select max(avg(sal)) from emp group by deptno;
步驟3：求平均工資最高的部門信息，連接步驟1 產(chǎn)生的臨時(shí)表與真實(shí)表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. 查詢大于各部門總工資的平均值的部門信息
答：考察知識點(diǎn)：子查詢，組函數(shù)，連接查詢
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：求每個(gè)部門總工資
select sum(sal) sumsal, deptno from emp group by deptno;
步驟2：求每總工資平均值
select avg(sum(sal)) from emp group by deptno;
步驟3：求大于總工資平均值的部門信息，連接步驟1 產(chǎn)生的臨時(shí)表與真實(shí)表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. 查詢大于各部門總工資的平均值的部門下的員工信息(考察知識點(diǎn):子查詢,組函數(shù),連接
查詢)
答：考察知識點(diǎn)：子查詢，組函數(shù)，連接查詢
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. 查詢沒有員工的部門信息
答：考察知識點(diǎn)：表連接
select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null;
分析：利用了左外連接的特點(diǎn)，部門連接員工時(shí)，沒有匹配記錄的部門對應(yīng)的員工編號列肯
定為null
30. 查詢用戶（users 表）huxz 所下所有訂單編號，下單日期，總價(jià)格(orders 表)，并包括訂
單中的商品數(shù)量（orderitem 表），名稱（product 表），價(jià)格（product 表）
答：考察知識點(diǎn)：多表連接

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 表)，每人購買的數(shù)
量（orderitem 表），購買時(shí)的價(jià)格（product 表）
答：考察知識點(diǎn)：多表連接
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. 查詢出哪些商品從未被訂購過
答：考察知識點(diǎn)：連接查詢
select p.* from product p left join orderitem i on(i. productid = p. productid)
where i.orderitemid is null;
分析：同29 題
33. 查詢出被訂購過2 次以上的商品信息
答：考察知識點(diǎn)：連接查詢，子查詢
select p.* from product p
where (select count(*) from orderitem where productid = p. productid) >= 2;
分析：子查詢select count(*) from orderitem where productid = p. productid 的含義是：當(dāng)前商
品在orderitem 表中出現(xiàn)的次數(shù)，即被訂購的次數(shù)
Part III(面試題目)
01. tmp 表中有如下記錄（建表SQL 見emp.sql）

要求結(jié)果格式為：
答：考察知識點(diǎn)：case… when… count 函數(shù)
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 ;
分析：
要點(diǎn)：在計(jì)數(shù)時(shí)，配合case…when…語句只統(tǒng)計(jì)取值為WIN 或LOSE 的個(gè)數(shù)。case 語句返
回不為null 的值即會(huì)加入count 計(jì)數(shù)，返回null 則不會(huì)加入count 計(jì)數(shù)。
02. 查詢當(dāng)前月有多少天
答：考察知識點(diǎn) 日期函數(shù)的靈活運(yùn)用
select trunc(add_months(sysdate,1),'month') - trunc(sysdate,'month') from dual;
03. pages 表有四個(gè)字段，id, url,title,body。如圖：
現(xiàn)要求將url 匹配的排在最前，title 匹配的其次，body 匹配最后，沒有任何字段匹配的，不
返回。現(xiàn)要求查詢所有匹配baidu 的記錄，最終查詢結(jié)果如圖：

建表語句在emp.sql 中
答：考察知識點(diǎn)：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;
要點(diǎn)：union 可以用來合并多次查詢結(jié)果。這里需要注意多次查詢的結(jié)果列的個(gè)數(shù)和類型必
須相同，合并后的結(jié)果集也可以看做一張表，表的列的類型和名稱由union 的第一條查詢結(jié)
果來決定。
這里用到一個(gè)技巧：手工指定一個(gè)優(yōu)先級mark 列，最后根據(jù)mark 列排序。
04. 現(xiàn)有STUDENT(學(xué)生), COURSE(課程), SC(成績)表,完成以下需求(建表語句在emp.sql
中,綜合考察)
a) 查詢選修課程為web 的學(xué)員學(xué)號和姓名
答：
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';
分析：課程與學(xué)生表沒有直接聯(lián)系，必須通過中間成績表做2 次表連接
b) 查詢課程編號為2 的學(xué)員姓名和單位
答：
select s.name,s.dept from student s inner join sc on (s.sid=sc.sid)
where sc.cid = 2;

c) 查詢不選修4 號課程的學(xué)員姓名和單位
答：
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);
分析：要點(diǎn)是先要查詢出選修了4 號課程的學(xué)員id，再從所有學(xué)員中排除這些id 的學(xué)
員。方法2 效率較高。
d) 查詢選修全部課程的學(xué)員姓名和單位
答：
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：查詢出所有課程的數(shù)目
select count(*) from course;
步驟2：在成績(sc)表，按學(xué)員id 分組，看每組的個(gè)數(shù)，該個(gè)數(shù)等于步驟1 課程總數(shù)的
sid 即為選修了所有課程的學(xué)員id
select sid from sc group by sid having (count(*) = (select count(*) from course));
步驟3：再根據(jù)該sid 查詢學(xué)員的詳細(xì)信息
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 門的學(xué)員姓名和單位
答：
select s.name,s.dept from student s

where sid in (select sid from sc group by sid having (count(*) > 3));
f) 找出沒有選修過Teacher LI 講授課程的所有學(xué)生姓名
答：
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) 列出有二門以上(含兩門)不及格課程的學(xué)生姓名及其平均成績
答：
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：查詢所有兩門以上不及格的學(xué)員id
select sid from sc where score < 60 group by sid having(count(*) >=2);
步驟2：步驟1 結(jié)果與真實(shí)表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 結(jié)果與真實(shí)表student 做連接，查學(xué)員姓名
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 結(jié)果與真實(shí)表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：可以發(fā)現(xiàn)，該結(jié)果中sid, name, dept, age 都是取值都相同，按照這些列直接進(jìn)
行分組即可：
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) 列出既學(xué)過1 號課程，又學(xué)過2 號課程的所有學(xué)生姓名
答：
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);
分析：要點(diǎn)是不僅要學(xué)過1，2 號課程in (1,2)，并且要求同時(shí)學(xué)過此兩門課count(*) = 2
i) 列出1 號課成績比2 號課成績高的所有學(xué)生的學(xué)號，姓名和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;
分析：要點(diǎn)在于自連接，把成績表拆成兩張表來看，sc1 中只考慮1 號課，sc2 中只考
慮2 號課且sc1.score > sc2.score；最后再考慮將結(jié)果與student 表連接查詢姓名。
05. 現(xiàn)有test 表，表中數(shù)據(jù)如圖所示：

要求按照格式如下輸出結(jié)果：
a) 連續(xù)的編號要求如下格式
b) 不連續(xù)的編號要求如下格式
答：
a) 求連續(xù)的，考察知識點(diǎn)：rownum，子查詢
分析：查看連續(xù)id 與rownum 之間的關(guān)系，運(yùn)行
select id, rownum, id-rownum from test;
參考下圖看出規(guī)律：

可以發(fā)現(xiàn)，id-rownum 取值相同的，就是那些id 編號連續(xù)的。按照id-rownum 分組并求
每組的最大，最小值即可。
select a.* from
(select min(id) begin, max(id) end from test group by (id - rownum) order by id - rownum) a;
b) 不連續(xù)的，考察知識點(diǎn)，rownum，子查詢
分析：
步驟一：
查詢有上一條記錄的個(gè)數(shù)
select id, (select count(*) from test where id+1 = t1.id) from test t1;
查詢有下一條記錄的個(gè)數(shù)
select id, (select count(*) from test where id-1 = t1.id) from test t1;
分析結(jié)果可知，個(gè)數(shù)為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;
分析結(jié)果可知，要求如上圖格式的數(shù)據(jù)將查詢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. 根據(jù)EMP 表數(shù)據(jù)產(chǎn)生如下格式的報(bào)表（統(tǒng)計(jì)各部門，各職位的人數(shù)）
答：方法1 考察知識點(diǎn)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 考察知識點(diǎn)：自連接
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');
結(jié)果如下：

可以看出這是求出每個(gè)部門職位為CLERK 的員工，將此結(jié)果按deptno 分組求個(gè)數(shù)：
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 列即為最終結(jié)果所需列。
如此類推，連接一次，求出一列，但需要注意，多表連接后，最后結(jié)果中會(huì)有重復(fù)記錄，因
此使用count(distinct empno)排除重復(fù)記錄后再計(jì)算個(gè)數(shù)才為正確結(jié)果。
07. 根據(jù)EMP 表數(shù)據(jù)產(chǎn)生如下格式的報(bào)表(統(tǒng)計(jì)各職位,各部門的人數(shù))(06 題的變體)
答：方法1 考察知識點(diǎn)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 題，注意列別名如果為數(shù)字開頭必須使用雙引號。
方法2 考察知識點(diǎn)：自連接

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 號員工的信息
答：考察知識點(diǎn) 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 可以將多次查詢結(jié)果連接起來，要注意，每條查詢的列的個(gè)數(shù)和數(shù)據(jù)類
型必須一致。因此在查詢時(shí)都使用了to_char 函數(shù)將第二列同一轉(zhuǎn)換為字符型。
Part IV(擴(kuò)展知識點(diǎn))
01. 分級查詢

Oracle 提供其他數(shù)據(jù)庫沒有的分級查詢操作：
例如：希望通過一次查詢以樹狀結(jié)構(gòu)顯示EMP 表中的所有上下級關(guān)系
select level, lpad(' ',level-1) || empno empno, ename, mgr,deptno from emp start with
empno=7839 connect by prior empno = mgr;
level 是ORACLE 關(guān)鍵字表示分級級別，其中l(wèi)pad(' ',level-1) 函數(shù)是根據(jù)level 的值生成
level-1 個(gè)空格
又如：希望通過某個(gè)員工回溯它的所有上級，包括上級的上級
select level, lpad(' ',level-1) || empno, ename, mgr, deptno from emp start with empno=7369
connect by empno = prior mgr;
02. CUBE（立方查詢）
如果想統(tǒng)計(jì)EMP 表中的所有職員數(shù)，每個(gè)部門的職員數(shù)，每種職位的職員數(shù)，每個(gè)部
門每種職位的職員數(shù)，使用普通分組查詢需要查詢4 次。但利用ORACLE 的增強(qiáng)語法，
可以非常方便的完成此類查詢（經(jīng)常用于生成報(bào)表）
例如：

select count(*),deptno,job,grouping_id(deptno,job) from emp group by cube (deptno,job)
order by grouping_id(deptno,job) ;
可以生成如下形式的報(bào)表：
03. 如何考察查詢效率
1) 在SQL-PLUS 中執(zhí)行 set autotrace on explain
2) 執(zhí)行查詢
返回結(jié)果中cost（成本）與bytes（字節(jié)數(shù)）都是越低越好。
04. 閃回查詢
ORACLE 的特點(diǎn)還有能夠更快速的恢復(fù)之前誤操作的數(shù)據(jù)，這是通過閃回日志完成的
例如：查詢20 分鐘之前的emp 表
select * from emp as of timestamp sysdate - interval '20' minute;
再如：恢復(fù)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;
甚至可以查詢及恢復(fù)被刪除的表
select * from user_recyclebin;
flashback table 表 to before drop;
05. 正則表達(dá)式
ORACLE 在建表或查詢時(shí)提供正則表達(dá)式支持：
例如：查詢名字以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. 如何加注釋
建表時(shí)給表和列加注釋是一個(gè)比較好的數(shù)據(jù)庫編程習(xí)慣
例如：表加注釋
comment on table 表 is '表注釋';
例如：列加注釋
comment on column 表.列 is '列注釋';
例如：查表注釋

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

?

Oracle面試題及答案