SQL常见面试问题

最新推荐文章于 2024-09-23 09:43:13 发布

转载最新推荐文章于 2024-09-23 09:43:13 发布 · 143 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/java001/archive/2009/08/25/1553445.html

文章标签：

#面试 #java #数据库

SQL常见面试问题

原贴地址：http://bbs.tarena.com.cn/topic.cgi?forum=5&topic=846&show=0

最近有许多学员问了一些面试中的问题，请数据库教师总结了一下：总结起来看：}+b?_I
©达内科技论坛 -- 达内科技论坛　　$
一是关于怎样找出和去除重复数据，这在另一个帖子利已有详细介绍。j
二是关于找出某一列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据。针对这种情况，再此做一个介绍。O
©达内科技论坛 -- 达内科技论坛　　z
1：找出公司里收入最高的前三名员工：OJ~r"
©达内科技论坛 -- 达内科技论坛　　&.]FQ
SQL> select rownum, last_name, salaryE
2 from (select last_name, salarykRfk
3 from s_empo
4 order by salary desc)XQ(n?~
5 where rownum<=3;$/:* c
©达内科技论坛 -- 达内科技论坛　　]
ROWNUM LAST_NAME SALARYb
---------- ------------------------- ----------?gZ0
1 Velasquez 4750b}kbD<
2 Ropeburn 2945@H#h
3 Nguyen 2897.5_tygGe
©达内科技论坛 -- 达内科技论坛　　[K8/
©达内科技论坛 -- 达内科技论坛　　s}!m
注意：请大家分析一下一下语句为什么不对：N)wL
©达内科技论坛 -- 达内科技论坛　　ZQq%
SQL> select rownum, last_name, salary)f
2 from s_emprY|
3 where rownum<=3}!t0
4 order by salary desc;Ix&?9
©达内科技论坛 -- 达内科技论坛　　*n#
ROWNUM LAST_NAME SALARY2(
---------- ------------------------- ----------/*
1 Velasquez 4750}B]@g-
3 Nagayama 2660}u
2 Ngao 2000U`F
©达内科技论坛 -- 达内科技论坛　　^PNk<
©达内科技论坛 -- 达内科技论坛　　FD<
2：找出表中的某一行或某几行的数据：:y"H-
©达内科技论坛 -- 达内科技论坛　　.
（1）：找出表中第三行数据："Ik2C
用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。O7Y~;
©达内科技论坛 -- 达内科技论坛　　{
SQL> select * from s_emp X]
2 where rownum=3;8s=3F
©达内科技论坛 -- 达内科技论坛　　j@M
no rows selectedE1pUJp
©达内科技论坛 -- 达内科技论坛　　>H9
SQL> select * from s_empsDt
2 where rownum between 3 and 5;K
©达内科技论坛 -- 达内科技论坛　　sbVGI
no rows selected+xK_
©达内科技论坛 -- 达内科技论坛　　/&
正确的方法如下：A
©达内科技论坛 -- 达内科技论坛　　F~`Ioh
SQL> lS'm
1 select last_name, salary;yfk
2 from (select rownum a, b.*2?[CQ
3 from s_emp b)1%h{
4* where a=3r"s.~*
SQL> /90
©达内科技论坛 -- 达内科技论坛　　aib
LAST_NAME SALARYE
------------------------- ----------,
Nagayama 2660&p
©达内科技论坛 -- 达内科技论坛　　K*
（2）：找出第三行到第五行之间的数据：w%@
SQL> lY#
1 select last_name, salaryjR)Z
2 from (select rownum a, b.*#Wq8y5
3 from s_emp b)>z:P-o
4* where a between 3 and 5gv9UR
SQL> /}Q&c
©达内科技论坛 -- 达内科技论坛　　(
LAST_NAME SALARYM8H
------------------------- ----------{qh0T
Nagayama 2660 D
Quick-To-See 2755W
Ropeburn 2945J?VGs!
©达内科技论坛 -- 达内科技论坛　　M
3：找出那些工资高于他们所在部门的平均工资的员工。1
©达内科技论坛 -- 达内科技论坛　　r{.U
（1）：第一种方法：6";)x
SQL> select last_name, dept_id, salaryu
2 from s_emp aUAj~
3 where salary>(select avg(salary)wW8H8
4 from s_emp?v~T0
5 where dept_id=a.dept_id);`/"d"
©达内科技论坛 -- 达内科技论坛　　tZe".I
LAST_NAME DEPT_ID SALARY6
------------------------- ---------- ----------=
Velasquez 50 4750?
Urguhart 41 2280s9VlSm
Menchu 42 2375@eMx
Biri 43 20904[xqL
Catchpole 44 2470S
Havel 45 2483.3/-%
Nguyen 34 2897.5n
Maduro 41 2660Z:
Nozaki 42 2280]u:%
Schwartz 45 2090mxm
©达内科技论坛 -- 达内科技论坛　　*g
10 rows selected.o@ :
©达内科技论坛 -- 达内科技论坛　　P,a;V2
（2）：第二种方法：)(,
SQL> l6
1 select a.last_name, a.salary, a.dept_id, b.avgsal5Cy
2 from s_emp a, (select dept_id, avg(salary) avgsale `9
3 from s_emp0
4 group by dept_id) brH;9P8
5 where a.dept_id=b.dept_ide
6* and a.salary>b.avgsalS?^i
SQL> /Qf
©达内科技论坛 -- 达内科技论坛　　hYpd h
LAST_NAME SALARY DEPT_ID AVGSALfd
------------------------- ---------- ---------- ----------ePdJjr
Velasquez 4750 50 3847.5lclRJ
Urguhart 2280 41 2181.5(haO
Menchu 2375 42 2055.16667R~Km
Biri 2090 43 17101
Catchpole 2470 44 1995gV
Havel 2483.3 45 2069.1h-)
Nguyen 2897.5 34 2204jA
Maduro 2660 41 2181.5g6"
Nozaki 2280 42 2055.16667v:|=%
Schwartz 2090 45 2069.1yQs~(y
©达内科技论坛 -- 达内科技论坛　　f'#>:Z
10 rows selected.':Id
©达内科技论坛 -- 达内科技论坛　　 RT27Q
4：找出那些工资高于他们所在部门的manager的工资的员工。DZfE?O
©达内科技论坛 -- 达内科技论坛　　z[n{L
SQL> l*]
1 select id, last_name, salary, manager_id4wjWM
2 from s_emp aW
3 where salary>(select salary]
4 from s_empmWb
5* where id=a.manager_id)I0$@
SQL> /xSQA
©达内科技论坛 -- 达内科技论坛　　6TD
ID LAST_NAME SALARY MANAGER_ID"Gr
---------- ------------------------- ---------- ----------L'&{'
6 Urguhart 2280 2"1ku1
7 Menchu 2375 2i`-z*a
8 Biri 2090 2k|0M
9 Catchpole 2470 2"+*C?D
10 Havel 2483.3 2@v
12 Giljum 2831 3V*{,
13 Sedeghi 2878.5 36*qk
14 Nguyen 2897.5 3_
15 Dumas 2755 3BZ
16 Maduro 2660 6x)
©达内科技论坛 -- 达内科技论坛　　C:1qL
10 rows selected./eRQ/H
©达内科技论坛 -- 达内科技论坛　　V~e
©达内科技论坛 -- 达内科技论坛　　[4FGd
找出部门工资排名第二，三的员工D
©达内科技论坛 -- 达内科技论坛　　afNZF
1 select name,salary,deptno from (VV
2 select concat(last_name,first_name) name,salary,department_id deptno,Hcgo
3 rank() over (partition by department_id order by salary desc) rnkMU%(
4* from employees) where rnk=2 or rnk=38>Z3"
SQL> /ft
©达内科技论坛 -- 达内科技论坛　　`R"
NAME©达内科技论坛 -- 达内科技论坛　　*dR`FI
--------------------------------------------------------------------------------y;
SALARY DEPTNOL'@vg=
---------- ----------@Zl%
FayPat&
6000 20@?
©达内科技论坛 -- 达内科技论坛　　mkE1
KhooAlexanderkMEf)I
3100 30Ay:ka
©达内科技论坛 -- 达内科技论坛　　n0=$
BaidaShelliGv%!Qc
2900 30lE%u6#
©达内科技论坛 -- 达内科技论坛　　"@1V
©达内科技论坛 -- 达内科技论坛　　l7
NAME©达内科技论坛 -- 达内科技论坛　　e!q9
--------------------------------------------------------------------------------.&R&J
SALARY DEPTNOypv
---------- ----------R2u
WeissMatthewAY_lF+
8000 50Jn%
©达内科技论坛 -- 达内科技论坛　　VGE
KauflingPayams
7900 50K>L
©达内科技论坛 -- 达内科技论坛　　%<
ErnstBruce'lg
6000 60E]Z3ME
©达内科技论坛 -- 达内科技论坛　　Q%
©达内科技论坛 -- 达内科技论坛　　||
NAME©达内科技论坛 -- 达内科技论坛　　O4mH,
--------------------------------------------------------------------------------(u:'A
SALARY DEPTNOMLp1
---------- ----------d$;Q
AustinDavid+.D
4800 60vHyP
©达内科技论坛 -- 达内科技论坛　　/E
PataballaValli}
4800 60Y0q_}
©达内科技论坛 -- 达内科技论坛　　94R
PartnersKaren&!=+
13500 80[sFQ{
©达内科技论坛 -- 达内科技论坛　　
©达内科技论坛 -- 达内科技论坛　　o"}6CV
NAME©达内科技论坛 -- 达内科技论坛　　"/8A5e
--------------------------------------------------------------------------------5(I=4
SALARY DEPTNOD@J{
---------- ----------M9
ErrazurizAlbertoU&09U
12000 80.6y_GW
©达内科技论坛 -- 达内科技论坛　　vH@{;
KochharNeenaO>^
17000 90'k
©达内科技论坛 -- 达内科技论坛　　!6wm
De HaanLexItgv>
17000 906_Z
©达内科技论坛 -- 达内科技论坛　　Y
©达内科技论坛 -- 达内科技论坛　　IT
NAME©达内科技论坛 -- 达内科技论坛　　?Kj
-------------------------------------------------------------------------------- CS
SALARY DEPTNOEwd!}
---------- ----------C
FavietDanielpd
9000 100HG|xx
©达内科技论坛 -- 达内科技论坛　　-vq
ChenJohn+
8200 1004
©达内科技论坛 -- 达内科技论坛　　wEd
GietzWilliaml%/
8300 1103upZ
©达内科技论坛 -- 达内科技论坛　　5
©达内科技论坛 -- 达内科技论坛　　]
15 rows selected.Pc!LL
©达内科技论坛 -- 达内科技论坛　　KDlz
SQL>ltb6o
©达内科技论坛 -- 达内科技论坛　　qz0|
找出部门工资排名第二，三的员工Hvg#
©达内科技论坛 -- 达内科技论坛　　!
1 select name,salary,deptno from (9T
2 select concat(last_name,first_name) name,salary,department_id deptno,T;z>h
3 rank() over (partition by department_id order by salary desc) rnkX^&
4* from employees) where rnk=2 or rnk=3]{}
SQL> /%-+
©达内科技论坛 -- 达内科技论坛　　j.S
NAME©达内科技论坛 -- 达内科技论坛　　Ye?`:
--------------------------------------------------------------------------------B
SALARY DEPTNOA9^
---------- ----------D@];l)
FayPatxEA>
6000 20@PyB!`
©达内科技论坛 -- 达内科技论坛　　QF
KhooAlexanderBp
3100 30M]
©达内科技论坛 -- 达内科技论坛　　<"?
BaidaShelliA
2900 30+;UZ
©达内科技论坛 -- 达内科技论坛　　 C}$W"
©达内科技论坛 -- 达内科技论坛　　z2^
NAME©达内科技论坛 -- 达内科技论坛　　LFOZ3`
--------------------------------------------------------------------------------&
SALARY DEPTNOo
---------- ----------fwo
WeissMatthew@9bs
8000 50?u
©达内科技论坛 -- 达内科技论坛　　Ny
KauflingPayam+]j
7900 50U,Py
©达内科技论坛 -- 达内科技论坛　　t
ErnstBruce*e
6000 60iOCb{
©达内科技论坛 -- 达内科技论坛　　_ZVNA
©达内科技论坛 -- 达内科技论坛　　U1U
NAME©达内科技论坛 -- 达内科技论坛　　k
--------------------------------------------------------------------------------]0
SALARY DEPTNOf4%G1>
---------- ----------
AustinDavidb^
4800 606#`*a
©达内科技论坛 -- 达内科技论坛　　D
PataballaValliX
4800 60xH
©达内科技论坛 -- 达内科技论坛　　zQO
PartnersKarenm?Ne
13500 809*
©达内科技论坛 -- 达内科技论坛　　4
©达内科技论坛 -- 达内科技论坛　　.OT(%%
NAME©达内科技论坛 -- 达内科技论坛　　5Pj<+
--------------------------------------------------------------------------------d
SALARY DEPTNO]
---------- ----------SSz
ErrazurizAlberto6X=
12000 80;
©达内科技论坛 -- 达内科技论坛　　~m2@/
KochharNeenaB95uv1
17000 90I*`LI
©达内科技论坛 -- 达内科技论坛　　!Fh
De HaanLexX
17000 909%
©达内科技论坛 -- 达内科技论坛　　ib
©达内科技论坛 -- 达内科技论坛　　wQ[a
NAME©达内科技论坛 -- 达内科技论坛　　c$)S
--------------------------------------------------------------------------------pDv
SALARY DEPTNOhh0Y#k
---------- ----------%>
FavietDanielDP
9000 100O
©达内科技论坛 -- 达内科技论坛　　l-P
ChenJohniS#p
8200 1005
©达内科技论坛 -- 达内科技论坛　　][yAA@
GietzWilliam?Zkt(
8300 110/4<
©达内科技论坛 -- 达内科技论坛　　"XB"`
©达内科技论坛 -- 达内科技论坛　　M+8=1
15 rows selected.P*6o
©达内科技论坛 -- 达内科技论坛　　!UN
SQL>w'P
©达内科技论坛 -- 达内科技论坛　　.0*Cf
又是一道面试题:u|Uc, SPAN>
©达内科技论坛 -- 达内科技论坛　　^2;,sN
原表:©达内科技论坛 -- 达内科技论坛　　1(YJv
©达内科技论坛 -- 达内科技论坛　　aH@:
id proid pronameEuSgO
1 1 M©达内科技论坛 -- 达内科技论坛　　8Jn
1 2 F©达内科技论坛 -- 达内科技论坛　　wM
2 1 N©达内科技论坛 -- 达内科技论坛　　;u
2 2 G©达内科技论坛 -- 达内科技论坛　　y
3 1 B©达内科技论坛 -- 达内科技论坛　　xC$ ow
3 2 A©达内科技论坛 -- 达内科技论坛　　0O
查询后的表:!y(
©达内科技论坛 -- 达内科技论坛　　p
id pro1 pro2cF0
1 M F©达内科技论坛 -- 达内科技论坛　　:
2 N G©达内科技论坛 -- 达内科技论坛　　Wo
3 B A©达内科技论坛 -- 达内科技论坛　　ZPnMDT
写出查询语句 -)|Q
©达内科技论坛 -- 达内科技论坛　　TCNk/
又是一道面试题:D
©达内科技论坛 -- 达内科技论坛　　/,#0Yz
原表:©达内科技论坛 -- 达内科技论坛　　m'
©达内科技论坛 -- 达内科技论坛　　D5$F[
id proid pronameTG/
1 1 M©达内科技论坛 -- 达内科技论坛　　R-1G
1 2 F©达内科技论坛 -- 达内科技论坛　　p7
2 1 N©达内科技论坛 -- 达内科技论坛　　*whR
2 2 G©达内科技论坛 -- 达内科技论坛　　+S8
3 1 B©达内科技论坛 -- 达内科技论坛　　8b.R
3 2 A©达内科技论坛 -- 达内科技论坛　　 i[
查询后的表:lK-}h
©达内科技论坛 -- 达内科技论坛　　AkPm%f
id pro1 pro2Cv_
1 M F©达内科技论坛 -- 达内科技论坛　　wf
2 N G©达内科技论坛 -- 达内科技论坛　　#`R0^L
3 B A©达内科技论坛 -- 达内科技论坛　　pD
写出查询语句 {fPQ#
©达内科技论坛 -- 达内科技论坛　　jy~o$P
又是一道面试题:A7
©达内科技论坛 -- 达内科技论坛　　'
原表:©达内科技论坛 -- 达内科技论坛　　vj~O7W
©达内科技论坛 -- 达内科技论坛　　G
id proid pronameq
1 1 M©达内科技论坛 -- 达内科技论坛　　6
1 2 F©达内科技论坛 -- 达内科技论坛　　aMVy:
2 1 N©达内科技论坛 -- 达内科技论坛　　l1$
2 2 G©达内科技论坛 -- 达内科技论坛　　n@$['
3 1 B©达内科技论坛 -- 达内科技论坛　　~x1%hQ
3 2 A©达内科技论坛 -- 达内科技论坛　　FP+
查询后的表:(?P
©达内科技论坛 -- 达内科技论坛　　k
id pro1 pro2Cq<=`
1 M F©达内科技论坛 -- 达内科技论坛　　M|L~!
2 N G©达内科技论坛 -- 达内科技论坛　　sPrrS=
3 B A©达内科技论坛 -- 达内科技论坛　　Gv&:Go
写出查询语句 i
©达内科技论坛 -- 达内科技论坛　　r5o/Y
又是一道面试题: o-Xcas
©达内科技论坛 -- 达内科技论坛　　2
原表: A7D.n
©达内科技论坛 -- 达内科技论坛　　""3#+
id proid proname ]
1 1 M C9$
1 2 F <
2 1 N wwln/
2 2 G }
3 1 B >0{
3 2 A g0X?D8
查询后的表: "O#D
©达内科技论坛 -- 达内科技论坛　　{ Hin)
id pro1 pro2 ]4s
1 M F X
2 N G "w^8"
3 B A ^6F
写出查询语句 OP
©达内科技论坛 -- 达内科技论坛　　+2U
解决方案可有以下三种作参考：mkT
©达内科技论坛 -- 达内科技论坛　　>
©达内科技论坛 -- 达内科技论坛　　eJN
1：使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。 `i+L
下面是一个例子 "
create or replace type strings_table is table of varchar2(20);,
/©达内科技论坛 -- 达内科技论坛　　m
create or replace function merge (pv in strings_table) return varchar2FuzD
is©达内科技论坛 -- 达内科技论坛　　RP
ls varchar2(4000);^
begin©达内科技论坛 -- 达内科技论坛　　`Sm|
for i in 1..pv.count loopf">
ls := ls || pv(i);~~Dco
end loop;5}+|R
return ls;p}c
end;Q
/©达内科技论坛 -- 达内科技论坛　　4LtI]!
create table t (id number,name varchar2(10));@3;~P"
insert into t values(1,'Joan');>aiUq&
insert into t values(1,'Jack');nyq
insert into t values(1,'Tom');Z>0VK
insert into t values(2,'Rose');
insert into t values(2,'Jenny');'Gyf]
©达内科技论坛 -- 达内科技论坛　　?
column names format a80;"'F0
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names %
from (select distinct id from t) t0;c~
©达内科技论坛 -- 达内科技论坛　　[?T
drop type strings_table;"Rlp
drop function merge;9prmX
drop table t;9Zi)~;
©达内科技论坛 -- 达内科技论坛　　H
©达内科技论坛 -- 达内科技论坛　　^#p{
2：用sql： LJV
©达内科技论坛 -- 达内科技论坛　　#t&3_;
Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky. bPHX+n
©达内科技论坛 -- 达内科技论坛　　cy
This example uses a max of 6, and would need more cut n pasting to do more than that. 634b
©达内科技论坛 -- 达内科技论坛　　w[
©达内科技论坛 -- 达内科技论坛　　~y#d-
SQL> select deptno, dname, emps Y5jqb
2 from ( ("iv
3 select d.deptno, d.dname, rtrim(e.ename ||', '|| 91l3
4 lead(e.ename,1) over (partition by d.deptno Jg."
5 order by e.ename) ||', '|| ,_yc
6 lead(e.ename,2) over (partition by d.deptno V$}
7 order by e.ename) ||', '|| '<#U
8 lead(e.ename,3) over (partition by d.deptno D=4#
9 order by e.ename) ||', '|| v}*%p
10 lead(e.ename,4) over (partition by d.deptno ea
11 order by e.ename) ||', '|| #8Bb
12 lead(e.ename,5) over (partition by d.deptno i9@
13 order by e.ename),', ') emps, wQsJ$%
14 row_number () over (partition by d.deptno ZY;D]f
15 order by e.ename) x '-u6K
16 from emp e, dept d L .v
17 where d.deptno = e.deptno W{
18 ) ©达内科技论坛 -- 达内科技论坛　　Y1{7^d
19 where x = 1
20 / ©达内科技论坛 -- 达内科技论坛　　kTW
©达内科技论坛 -- 达内科技论坛　　5e7:t
DEPTNO DNAME EMPS s
------- ----------- ------------------------------------------ Tk"`
10 ACCOUNTING CLARK, KING, MILLER CyX'
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH b
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARDSH
©达内科技论坛 -- 达内科技论坛　　,
©达内科技论坛 -- 达内科技论坛　　_ITzze
3：先用pl/sql创建一个函数（create function get_a2）; C|b
create or replace function get_a2( tmp_a1 number) Vr2dM
return varchar2 s
is ©达内科技论坛 -- 达内科技论坛　　2
Col_a2 varchar2(4000); ?(
begin 24
Col_a2:=''; ,QaQ
for cur in (select a2 from unite_a where a1=tmp_a1) @_zk
loop ©达内科技论坛 -- 达内科技论坛　　,8
Col_a2=Col_a2||cur.a2; (SI
end loop; JpY
return Col_a2; ?e%w+t
end get_a2; +`q@a6
©达内科技论坛 -- 达内科技论坛　　+'
select distinct a1 ,get_a2(a1) from unite_a ok
1 ABC Q5n
2 EFG PJ}RGf
3 KMN©达内科技论坛 -- 达内科技论坛　　N04t
原表: GtdD
courseid coursename score uFLKY:
------------------------------------- &YzMF'
1 java 70 UC
2 oracle 90 2Fat
3 xml 40 sO
4 jsp 30 ye-"
5 servlet 80 Dg
------------------------------------- )in>n$
为了便于阅读,查询此表后的结果显式如下(及格分数为60): pk8
courseid coursename score mark 3
--------------------------------------------------- u
1 java 70 pass 5"
2 oracle 90 pass "LC#
3 xml 40 fail &#+{?
4 jsp 30 fail Hb
5 servlet 80 pass ey
--------------------------------------------------- vS3g
写出此查询语句 Eh
©达内科技论坛 -- 达内科技论坛　　ZE^@
©达内科技论坛 -- 达内科技论坛　　BBF<[
解决办法如下，要用到decode和sign两个函数： ?tM$B
©达内科技论坛 -- 达内科技论坛　　Dg:4-
SQL> select courseid,coursename ,score ,decode(sign(score-61),-1,'fail','pass') as mark hQ68u
from course_v;@Kp!C
©达内科技论坛 -- 达内科技论坛　　]"|w
COURSEID COURSENAME SCORE MARKpu"GS.
---------- ---------- ---------- ----U]
1 java 70 passLB
2 oracle 90 passa42|
3 xml 40 failzvIh4
4 jsp 30 faile8#
5 servlet 80 pass `[