数据库面试宝典.docx
《数据库面试宝典.docx》由会员分享，可在线阅读，更多相关《数据库面试宝典.docx（15页珍藏版）》请在冰豆网上搜索。
数据库面试宝典
数据库面试宝典
一是关于怎样找出和去除重复数据，这在另一个帖子利已有详细介绍。
二是关于找出某一列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据。
针对这种情况，再此做一个介绍。
1：
找出公司里收入最高的前三名员工：
SQL>selectrownum,last_name,salary
2 from（selectlast_name,salary
3 froms_emp
4 orderbysalarydesc）
5 whererownum<=3;
ROWNUMLAST_NAME SALARY
---------------------------------------------
1Velasquez 4750
2Ropeburn 2945
3Nguyen 2897.5
注意：
请大家分析一下一下语句为什么不对：
SQL>selectrownum,last_name,salary
2 froms_emp
3 whererownum<=3
4 orderbysalarydesc;
ROWNUMLAST_NAME SALARY
---------------------------------------------
1Velasquez 4750
3Nagayama 2660
2Ngao 2000
2：
找出表中的某一行或某几行的数据：
（1）：
找出表中第三行数据：
用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。
SQL>select*froms_emp
2 whererownum=3;
norowsselected
SQL>select*froms_emp
2 whererownumbetween3and5;
norowsselected
正确的方法如下：
SQL>l
1 selectlast_name,salary
2 from（selectrownuma,b.*
3 froms_empb）
4*wherea=3
SQL>/
LAST_NAME SALARY
-----------------------------------
Nagayama 2660
（2）：
找出第三行到第五行之间的数据：
SQL>l
1 selectlast_name,salary
2 from（selectrownuma,b.*
3 froms_empb）
4*whereabetween3and5
SQL>/
LAST_NAME SALARY
-----------------------------------
Nagayama 2660
Quick-To-See 2755
Ropeburn 2945
3：
找出那些工资高于他们所在部门的平均工资的员工。
（1）：
第一种方法：
SQL>selectlast_name,dept_id,salary
2 froms_empa
3 wheresalary>（selectavg（salary）
4 froms_emp
5 wheredept_id=a.dept_id）;
LAST_NAME DEPT_ID SALARY
---------------------------------------------
Velasquez 50 4750
Urguhart 41 2280
Menchu 42 2375
Biri 43 2090
Catchpole 44 2470
Havel 45 2483.3
Nguyen 34 2897.5
Maduro 41 2660
Nozaki 42 2280
Schwartz 45 2090
10rowsselected.
（2）：
第二种方法：
SQL>l
1 selecta.last_name,a.salary,a.dept_id,b.avgsal
2 froms_empa,（selectdept_id,avg（salary）avgsal
3 froms_emp
4 groupbydept_id）b
5 wherea.dept_id=b.dept_id
6*anda.salary>b.avgsal
SQL>/
LAST_NAME SALARY DEPT_ID AVGSAL
-------------------------------------------------------
Velasquez 4750 50 3847.5
Urguhart 2280 41 2181.5
Menchu 2375 422055.16667
Biri 2090 43 1710
Catchpole 2470 44 1995
Havel 2483.3 45 2069.1
Nguyen 2897.5 34 2204
Maduro 2660 41 2181.5
Nozaki 2280 422055.16667
Schwartz 2090 45 2069.1
10rowsselected.
4：
找出那些工资高于他们所在部门的manager的工资的员工。
SQL>l
1 selectid,last_name,salary,manager_id
2 froms_empa
3 wheresalary>（selectsalary
4 froms_emp
5* whereid=a.manager_id）
SQL>/
IDLAST_NAME SALARYMANAGER_ID
-------------------------------------------------------
6Urguhart 2280 2
7Menchu 2375 2
8Biri 2090 2
9Catchpole 2470 2
10Havel 2483.3 2
12Giljum 2831 3
13Sedeghi 2878.5 3
14Nguyen 2897.5 3
15Dumas 2755 3
16Maduro 2660 6
10rowsselected.
找出部门工资排名第二，三的员工
1 selectname,salary,deptnofrom（
2 selectconcat（last_name,first_name）name,salary,department_iddeptno,
3 rank（）over（partitionbydepartment_idorderbysalarydesc）rnk
4* fromemployees）wherernk=2orrnk=3
SQL>/
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
FayPat
6000 20
KhooAlexander
3100 30
BaidaShelli
2900 30
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
WeissMatthew
8000 50
KauflingPayam
7900 50
ErnstBruce
6000 60
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
AustinDavid
4800 60
PataballaValli
4800 60
PartnersKaren
13500 80
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
ErrazurizAlberto
12000 80
KochharNeena
17000 90
DeHaanLex
17000 90
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
FavietDaniel
9000 100
ChenJohn
8200 100
GietzWilliam
8300 110
15rowsselected.
SQL>
找出部门工资排名第二，三的员工
1 selectname,salary,deptnofrom（
2 selectconcat（last_name,first_name）name,salary,department_iddeptno,
3 rank（）over（partitionbydepartment_idorderbysalarydesc）rnk
4* fromemployees）wherernk=2orrnk=3
SQL>/
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
FayPat
6000 20
KhooAlexander
3100 30
BaidaShelli
2900 30
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
WeissMatthew
8000 50
KauflingPayam
7900 50
ErnstBruce
6000 60
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
AustinDavid
4800 60
PataballaValli
4800 60
PartnersKaren
13500 80
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
ErrazurizAlberto
12000 80
KochharNeena
17000 90
DeHaanLex
17000 90
NAME
--------------------------------------------------------------------------------
SALARY DEPTNO
--------------------
FavietDaniel
9000 100
ChenJohn
8200 100
GietzWilliam
8300 110
15rowsselected.
SQL>
又是一道面试题:
原表:
idproidproname
11M
12F
21N
22G
31B
32A
查询后的表:
idpro1pro2
1MF
2NG
3BA
写出查询语句
又是一道面试题:
原表:
idproidproname
11M
12F
21N
22G
31B
32A
查询后的表:
idpro1pro2
1MF
2NG
3BA
写出查询语句
又是一道面试题:
原表:
idproidproname
11M
12F
21N
22G
31B
32A
查询后的表:
idpro1pro2
1MF
2NG
3BA
写出查询语句
又是一道面试题:
原表:
idproidproname
11M
12F
21N
22G
31B
32A
查询后的表:
idpro1pro2
1MF
2NG
3BA
写出查询语句
解决方案可有以下三种作参考：
1：
使用pl/sql代码实现，但要求你组合后的长度不能超出oraclevarchar2长度的限制。
下面是一个例子
createorreplacetypestrings_tableistableofvarchar2（20）;
/
createorreplacefunctionmerge（pvinstrings_table）returnvarchar2
is
lsvarchar2（4000）;
begin
foriin1..pv.countloop
ls:
=ls||pv（i）;
endloop;
returnls;
end;
/
createtablet（idnumber,namevarchar2（10））;
insertintotvalues（1,'Joan'）;
insertintotvalues（1,'Jack'）;
insertintotvalues（1,'Tom'）;
insertintotvalues（2,'Rose'）;
insertintotvalues（2,'Jenny'）;
columnnamesformata80;
selectt0.id,merge（cast（multiset（selectnamefromtwheret.id=t0.id）asstrings_table））names
from（selectdistinctidfromt）t0;
droptypestrings_table;
dropfunctionmerge;
droptablet;
2：
用sql：
Wellifyouhaveathoreticalmaximum,whichIwouldassumeyouwouldgiventhelegibilityoflistinghundredsofemployeesinthewayyoudescribethenyes.ButtheSQLneedstousetheLAGfunctionforeachemployee,henceahundredempsahundredLAGs,sokindofbulky.
Thisexampleusesamaxof6,andwouldneedmorecutnpastingtodomorethanthat.
SQL>selectdeptno,dname,emps
2from（
3selectd.deptno,d.dname,rtrim（e.ename||','||
4lead（e.ename,1）over（partitionbyd.deptno
5orderbye.ename）||','||
6lead（e.ename,2）over（partitionbyd.deptno
7orderbye.ename）||','||
8lead（e.ename,3）over（partitionbyd.deptno
9orderbye.ename）||','||
10lead（e.ename,4）over（partitionbyd.deptno
11orderbye.ename）||','||
12lead（e.ename,5）over（partitionbyd.deptno
13orderbye.ename）,','）emps,
14row_number（）over（partitionbyd.deptno
15orderbye.ename）x
16fromempe,deptd
17whered.deptno=e.deptno
18）
19wherex=1
20/
DEPTNODNAMEEMPS
------------------------------------------------------------
10ACCOUNTINGCLARK,KING,MILLER
20RESEARCHADAMS,FO
升级会员 