亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

Oracle面試題及答案

系統(tǒng) 1862 0

模塊一 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面試題及答案


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯(lián)系: 360901061

您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會(huì)非常 感謝您的哦!!!

發(fā)表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 妇女网站爱嘿嘿视频免费观看 | 亚州免费一级毛片 | 毛片免费永久不卡视频观看 | 日本一区二区视频免费播放 | 亚州在线播放 | 天天干夜夜操视频 | 国产精品2020在线看亚瑟 | 西西大胆实体啪啪色哟哟 | 免费国产一区二区在免费观看 | 手机看片日韩欧美 | 国产性生活视频 | jizz免费在线观看 | 久久这里只有精品首页 | 欧美伦理一区 | 国内免费在线视频 | 亚洲国产一区二区三区综合片 | 91青青青国产在观免费影视 | 欧美性色xo影院在线观看 | 深夜成人| 波多野结衣精品中文字幕 | 欧美日韩不卡在线 | 国产品精人成福利视频 | www久| 久久性生大片免费观看性 | 久久久这里只有精品加勒比 | 久久网精品视频 | 国产凹凸在线一区二区色老头 | 欧美日韩在大午夜爽爽影院 | 最新国产福利 | 国产免费一级高清淫曰本片 | 亚洲欧美精品中字久久99 | 国产91福利在线精品剧情尤物 | 成人永久免费视频网站在线观看 | 色91在线 | 国产乱码 | 91久久综合九色综合欧美98 | 国产精品亚洲一区二区三区久久 | 一级色网站 | 午夜亚洲国产精品福利 | 亚洲精品视频免费看 | 天天舔天天射天天干 |