神马文学网sql 面试问题1
来源：百度文库 编辑：神马文学网 时间：2024/04/27 10:23:48
1：找出公司里收入最高的前三名员工：
SQL> select rownum, last_name, salary
2 from (select last_name, salary
3 from s_emp
4 order by salary desc)
5 where rownum<=3;
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez
2 Ropeburn
3 Nguyen 2897.5 QUVoW
©达内科技论坛 -- 达内科技论坛 =
©达内科技论坛 -- 达内科技论坛 J‘
注意：请大家分析一下一下语句为什么不对：dOb{RO
©达内科技论坛 -- 达内科技论坛 bt7]c0
SQL> select rownum, last_name, salary
2 from s_empg:m4>
3 where rownum<=3.ny
4 order by salary desc;tUJ
©达内科技论坛 -- 达内科技论坛 pqGE\[
ROWNUM LAST_NAME SALARY!7.
---------- ------------------------- ----------Y
1 Velasquez 4750YHJ#V
3 Nagayama 2660%p8~
2 Ngao 2000Bb;
©达内科技论坛 -- 达内科技论坛 @
©达内科技论坛 -- 达内科技论坛 g#|E
2： 找出表中的某一行或某几行的数据：k9
©达内科技论坛 -- 达内科技论坛 mM
（1）：找出表中第三行数据：P
用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。+h">^
©达内科技论坛 -- 达内科技论坛 Vr$$]h
SQL> select * from s_emp{
2 where rownum=3;O_{
©达内科技论坛 -- 达内科技论坛 /;B
no rows selectedp(
©达内科技论坛 -- 达内科技论坛 1"B
SQL> select * from s_emp">7
2 where rownum between 3 and 5;Dky!m
©达内科技论坛 -- 达内科技论坛 qS
no rows selectedm^)0
©达内科技论坛 -- 达内科技论坛 H_&
正确的方法如下：,~w[KF
©达内科技论坛 -- 达内科技论坛 z7@>Me
SQL> l/(
1 select last_name, salary(?\
2 from (select rownum a, b.*ZVwI!
3 from s_emp b)N
4* where a=3DI
SQL> /Cu
©达内科技论坛 -- 达内科技论坛 `t
LAST_NAME SALARYd$#>
------------------------- ----------,/N?UK
Nagayama 2660w
©达内科技论坛 -- 达内科技论坛 .$CX
（2）：找出第三行到第五行之间的数据：L
SQL> l]M]~H
1 select last_name, salaryH
2 from (select rownum a, b.*CD5w+
3 from s_emp b)om2
4* where a between 3 and 5,fQ&,=
SQL> /R4SS
©达内科技论坛 -- 达内科技论坛 O?D7)
LAST_NAME SALARY5‘
------------------------- ---------
Nagayama 2660@L
Quick-To-See 2755>
Ropeburn 2945Ni‘=r
©达内科技论坛 -- 达内科技论坛
3：找出那些工资高于他们所在部门的平均工资的员工。I‘R*u
©达内科技论坛 -- 达内科技论坛 ;7Z
（1）：第一种方法：5acm6S
SQL> select last_name, dept_id, salary(
2 from s_emp ag1}+7
3 where salary>(select avg(salary)S-(
4 from s_emp2ksQsY
5 where dept_id=a.dept_id);PZRM
©达内科技论坛 -- 达内科技论坛 WFa=Fa
LAST_NAME DEPT_ID SALARY_f7
------------------------- ---------- ----------3x-v0
Velasquez 50 4750r6h*8
Urguhart 41 2280yECF*
Menchu 42 2375TLoS/‘
Biri 43 2090^xJ
Catchpole 44 2470ZS,
Havel 45 2483.3;R`$_
Nguyen 34 2897.5pK\=Q$
Maduro 41 2660?OZ_(:
Nozaki 42 2280wR
Schwartz 45 2090`
©达内科技论坛 -- 达内科技论坛 AtMr
10 rows selected.loa
©达内科技论坛 -- 达内科技论坛 q
（2）：第二种方法：5AH1
SQL> l=\]n
1 select a.last_name, a.salary, a.dept_id, b.avgsal!NZW2d
2 from s_emp a, (select dept_id, avg(salary) avgsal7P<2H;
3 from s_emp)(o?
4 group by dept_id) b(a5
5 where a.dept_id=b.dept_id-6
6* and a.salary>b.avgsalZ
SQL> /‘^NJ
©达内科技论坛 -- 达内科技论坛 7s/
LAST_NAME SALARY DEPT_ID AVGSALCX@
------------------------- ---------- ---------- ----------j
Velasquez 4750 50 3847.5}@7
Urguhart 2280 41 2181.5_m
Menchu 2375 42 2055.16667ks5
Biri 2090 43 1710I/Q
Catchpole 2470 44 1995k
Havel 2483.3 45 2069.1,H
Nguyen 2897.5 34 2204nB
Maduro 2660 41 2181.5<&
Nozaki 2280 42 2055.1666788W
Schwartz 2090 45 2069.1"L$Aht
©达内科技论坛 -- 达内科技论坛 @3`tD~
10 rows selected.p3Q#b
©达内科技论坛 -- 达内科技论坛 pV[@QT
4：找出那些工资高于他们所在部门的manager的工资的员工。Y^;‘
©达内科技论坛 -- 达内科技论坛 5z
SQL> lg!O`?~
1 select id, last_name, salary, manager_ida
2 from s_emp ae8wG
3 where salary>(select salaryb)-r
4 from s_empflzfJG
5* where id=a.manager_id)|/t)yO
SQL> /{Nwt
©达内科技论坛 -- 达内科技论坛 *^0LJ
ID LAST_NAME SALARY MANAGER_IDCQV
---------- ------------------------- ---------- ----------=Il
6 Urguhart 2280 2If@
7 Menchu 2375 2j:QMG
8 Biri 2090 2wjkW.!
9 Catchpole 2470 23]
10 Havel 2483.3
12 Giljum 2831 38NT
13 Sedeghi 2878.5 3_
14 Nguyen 2897.5 3b@
15 Dumas 2755 3${
16 Maduro 2660
©达内科技论坛 -- 达内科技论坛 Zu
10 rows selected.Uk2fN
第一题：有两个表分别如下：=Z<
表A(varchar(32) NAME,int GRADE)*{
数据：:WyN$
ZHANGSHAN 80 R
LISI 60N
WANGWU 84#
©达内科技论坛 -- 达内科技论坛 m]P
表B(varchar(32) NAME,int AGE)[!
数据：Hi)Y:7
ZHANGSHAN 26 =(
LISI 24@
WANGWU 261>
WUTIAN 26~.yj4
©达内科技论坛 -- 达内科技论坛 X@~~O
（1）写SQL语句得到如下查询结果：>g1\oj
NAME GRADE AGE C
ZHANGSHAN 80 26 q5IV
LISI 60 24sw
WANGWU 84
WUTIAN NULL 26Z]
疑问：这里的没有成绩的那个人的记录怎么得到呢？C#~,Q
©达内科技论坛 -- 达内科技论坛 `
（2）写SQl语句根据名字（NAME）相同按年龄（AGE）分组得到不同年龄的人的平均成绩，并写出结果。f${4\n
疑问：按照名字相同，WUTIAN这个人没有成绩该不该把他统计在内呢？8B<
©达内科技论坛 -- 达内科技论坛 t%#
（3）还有一问具体数据记不清了，比上两问更怪。y#1\^m
[这个贴子最后由SunOne在 2003/12/30 04:11pm 编辑]m
©达内科技论坛 -- 达内科技论坛 ;(
第二题：有一个数据库表dept中有如下数据：3!|-
id_no id_namewe#
1000 S
1001 S2.$]K
1002 S31?
1003 S4
1000 S5p8
1000 S6gZ~&>=
1001 S7/
表中有id_no重复，如id_no为1000的有3条记录，如id_no为1001的有2条记录，z-XS
现在要按照id_no给表建索引，需要删除id_no重复了的那些记录，但不能删掉所有拥有该id_no的记录，必需保留一条拥有该id_no的记录(如id_no为1000的只剩下一条记录)7
©达内科技论坛 -- 达内科技论坛 (
(1)请写出SQl语句（或SQL语句组），查询所有id_no重复的记录。bxt
©达内科技论坛 -- 达内科技论坛 qwZ239
(2)请写出SQl语句实现题目要求的结果。
1:©达内科技论坛 -- 达内科技论坛 SWT
SQL> create table ahCu
2 (name varchar2(32),t
3 grade int);fHYm
Table created.j|
SQL> insert into a_ty5.u
2 values(‘&a‘,&b);Vk-~\y
Enter value for a: zhangshantS
Enter value for b: 80hYTG&
old 2: values(‘&a‘,&b)7]bgW
new 2: values(‘zhangshan‘,80)#sQukk
©达内科技论坛 -- 达内科技论坛 )dD>M:
1 row created.5
©达内科技论坛 -- 达内科技论坛 >ic5
SQL> /Nb>#
Enter value for a: lisi|FS|3
Enter value for b: 60[7s
old 2: values(‘&a‘,&b)6
new 2: values(‘lisi‘,60)^s-a
©达内科技论坛 -- 达内科技论坛 hR-Bni
1 row created.3
©达内科技论坛 -- 达内科技论坛 Cc]
SQL> /iX@
Enter value for a: wangwu9J
Enter value for b: 84rR`
old 2: values(‘&a‘,&b)I z4Bn
new 2: values(‘wangwu‘,84)u
©达内科技论坛 -- 达内科技论坛 Cwdw,M
1 row created.‘&F4H&
©达内科技论坛 -- 达内科技论坛 zU
SQL> commit;pS#~f
©达内科技论坛 -- 达内科技论坛 R"
Commit complete.I@b}
©达内科技论坛 -- 达内科技论坛 X#{! 9
SQL> create table b_t%;R
2 (name varchar2(32),71
3 age int);x;SQ^
©达内科技论坛 -- 达内科技论坛 Q+z
Table created.!M
©达内科技论坛 -- 达内科技论坛 L|
SQL> insert into b_tJ
2 values(‘&a‘,&b);UK|‘?]
Enter value for a: zhangshanZA+X:
Enter value for b: 26:x`.M(
old 2: values(‘&a‘,&b)wbi[
new 2: values(‘zhangshan‘,26)M=A
©达内科技论坛 -- 达内科技论坛 #
1 row created
©达内科技论坛 -- 达内科技论坛 *h5
SQL> /#u
Enter value for a: lisi18J/-
Enter value for b: 24fA*
old 2: values(‘&a‘,&b)8MqSY"
new 2: values(‘lisi‘,24)Myt6W
©达内科技论坛 -- 达内科技论坛 d"
1 row created.;2NtAv
©达内科技论坛 -- 达内科技论坛 UPy:Z;
SQL> /"[u
Enter value for a: wangwu{|Q3
Enter value for b: 267Max
old 2: values(‘&a‘,&b)5+
new 2: values(‘wangwu‘,26)9tn+{
©达内科技论坛 -- 达内科技论坛 1oT;
1 row created.Yg
©达内科技论坛 -- 达内科技论坛 j3{M
SQL> /?G#i
Enter value for a: wutianz&a
Enter value for b:
old 2: values(‘&a‘,&b)3
new 2: values(‘wutian‘,26)lqP
©达内科技论坛 -- 达内科技论坛 H
1 row created.XqM
©达内科技论坛 -- 达内科技论坛 |t
SQL> commit;*x!#v
©达内科技论坛 -- 达内科技论坛 gfh.
Commit complete.m>:QD
©达内科技论坛 -- 达内科技论坛 Gv*h
SQL>}rDu#
SQL> col grade null ‘null‘3"K
SQL> l71srGP
1 select b.name, a.grade, b.age5)F<
2 from a_t a,b_t b-!$YRs
3* where a.name(+)=b.name\H
SQL> /q9[
©达内科技论坛 -- 达内科技论坛 Ia-
NAME GRADE AGE-n&M
-------------------------------- ---------- ----------BL
lisi 60 24W
wangwu 84 26477E
wutian null 26[a?EA
zhangshan 80 26h~
©达内科技论坛 -- 达内科技论坛 ?!;
SQL>-#D
©达内科技论坛 -- 达内科技论坛 ?kMGSC
2:©达内科技论坛 -- 达内科技论坛 45r&‘a
SQL> lL
1 select name, avg(grade), age(sJ7rh
2 from (select b.name name, a.grade grade, b.age ageNUx3>;
3 from a_t a,b_t bSJx
4 where a.name(+)=b.name),5
5* group by age, nameZU
SQL> /E
©达内科技论坛 -- 达内科技论坛 Ewhj
NAME AVG(GRADE) AGE&H
-------------------------------- ---------- ----------g]
wangwu 84 26!ZOnC
wutian 26Nn
zhangshan 80
©达内科技论坛 -- 达内科技论坛 uThcM\
SWUTIAN这个人没有成绩不把他统计在内q)
3:©达内科技论坛 -- 达内科技论坛 i-:
1、当我们想要为一个表创建唯一索引时，如果该表有重复的记录，则无法创建成功。 (QJS
方法原理： )KQ
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的， Gk
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 yhjK
©达内科技论坛 -- 达内科技论坛 `,
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中 V>)P
那些具有最大rowid的就可以了，其余全部删除。 lH
©达内科技论坛 -- 达内科技论坛 k7
3、以下语句用到了3项技巧：rowid、子查询、别名。 XNR*
©达内科技论坛 -- 达内科技论坛 ba
实现方法： /e
SQL> create table a ( 33
2 bm char(4), --编码 <}cU@4
3 mc varchar2(20) --名称 =jY;Z
4 ) ©达内科技论坛 -- 达内科技论坛 g!KM
5 / ©达内科技论坛 -- 达内科技论坛 {:e[
©达内科技论坛 -- 达内科技论坛 e`e
表已建立. 1?,.
©达内科技论坛 -- 达内科技论坛 8=Dl9;
SQL> insert into a values(‘1111‘,‘1111‘); R
SQL> insert into a values(‘1112‘,‘1111‘); #
SQL> insert into a values(‘1113‘,‘1111‘); i|
SQL> insert into a values(‘1114‘,‘1111‘); q>V+L
©达内科技论坛 -- 达内科技论坛 }J
SQL> insert into a select * from a; +i2|@0
©达内科技论坛 -- 达内科技论坛 *T
插入4个记录. lIG\^X
©达内科技论坛 -- 达内科技论坛 T
SQL> commit; EnemB
SQL> select rowid,bm,mc from a; O7
©达内科技论坛 -- 达内科技论坛 e
ROWID BM MC :!|j
------------------ ---- ------- ‘9%EK
000000D5.0000.0002 1111 1111 h9$-)L
000000D5.0001.0002 1112 1111 dd
000000D5.0002.0002 1113 1111 OXC
000000D5.0003.0002 1114 1111 K7K
000000D5.0004.0002 1111 1111 #O
000000D5.0005.0002 1112 1111 Hk
000000D5.0006.0002 1113 1111 <
000000D5.0007.0002 1114 1111 iAs Y
©达内科技论坛 -- 达内科技论坛 u
查询到8记录. {9&
©达内科技论坛 -- 达内科技论坛 ||
©达内科技论坛 -- 达内科技论坛 x
查出重复记录 j-H&D
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); $8& X^
©达内科技论坛 -- 达内科技论坛 V‘
ROWID BM MC w@
------------------ ---- -------------------- Z>
000000D5.0000.0002 1111 1111 DAu
000000D5.0001.0002 1112 1111 sRxcx
000000D5.0002.0002 1113 1111 ;+
©达内科技论坛 -- 达内科技论坛 ?| -5
删除重复记录 $@
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); I‘=!c
©达内科技论坛 -- 达内科技论坛 a(S}C
删除4个记录. 8
©达内科技论坛 -- 达内科技论坛 #2B
SQL> select rowid,bm,mc from a; >i/
©达内科技论坛 -- 达内科技论坛 Wj~cn
ROWID BM MC dij
------------------ ---- -------------------- uWg
000000D5.0004.0002 1111 1111 HO/0hp
000000D5.0005.0002 1112 1111 mfc
000000D5.0006.0002 1113 1111 T$S5aA
000000D5.0007.0002 1114 1111q
=====================================================
ProductID SALE_YEAR SALES productID 2001 2002
001 2001 10 001 10"
002 2001 15 002 15<
003 2002 12 003 12 10AT
003 2003 10`h%@{7
写一个SQL或者PL/SQL完成左边的表变成右边的表。=
SQL> CREATE TABLE TEST(PRODUCT_ID NUMBER ,SALE_YEAR VARCHAR2(20),SALES NUMBER) ;Rxcs
©达内科技论坛 -- 达内科技论坛 R
Table created.HQ/
©达内科技论坛 -- 达内科技论坛 RQ
SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);v
Enter value for 1:
Enter value for 2: 2001(!Zj
Enter value for 3: 10AXREC
old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)S\_
new 1: INSERT INTO TEST valueS(‘001‘,‘2001‘,‘10‘)A{]KYG
©达内科技论坛 -- 达内科技论坛 6,De%
1 row created.73.
©达内科技论坛 -- 达内科技论坛 ]LHQ
SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);d|z&
Enter value for 1: 002w3
Enter value for 2: 2001Y
Enter value for 3: 15i
old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)Fj
new 1: INSERT INTO TEST valueS(‘002‘,‘2001‘,‘15‘)^ZawQJ
©达内科技论坛 -- 达内科技论坛 :
1 row created.:
©达内科技论坛 -- 达内科技论坛 k0=O7
SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);N
Enter value for 1: 003e
Enter value for 2:
Enter value for 3:
old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)jp/I0
new 1: INSERT INTO TEST valueS(‘003‘,‘2002‘,‘12‘)Y
©达内科技论坛 -- 达内科技论坛 )
1 row created.[1UqH
©达内科技论坛 -- 达内科技论坛 lKrk
SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);<(n
Enter value for 1: 003OI
Enter value for 2: 2003y<
Enter value for 3:
old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)S`v>1
new 1: INSERT INTO TEST valueS(‘003‘,‘2003‘,‘10‘)Bjax
©达内科技论坛 -- 达内科技论坛 .dvn
1 row created.kov/,
©达内科技论坛 -- 达内科技论坛 /j
SQL> COMMIT;:[Po1
©达内科技论坛 -- 达内科技论坛 Yor{B
Commit complete.`XH`h0
©达内科技论坛 -- 达内科技论坛 -2#en$
SQL> SELECT * FROM TEST;-C
©达内科技论坛 -- 达内科技论坛 [
PRODUCT_ID SALE_YEAR SALESyk
---------- -------------------- ----------
1 2001 10U
2 2001 15;o
3 2002 12rN\ou
3 2003 10eI%/
©达内科技论坛 -- 达内科技论坛 T0[WI"
SQL> SELECT PRODUCT_ID,:B
2 DECODE(SALE_YEAR,2001,SUM(SALES)) "2001",B\
3 DECODE(SALE_YEAR,2002,SUM(SALES)) "2002",cp79
4 DECODE(SALE_YEAR,2003,SUM(SALES)) "2003"C
5 FROM TEST,‘Yd[Z
6 GROUP BY PRODUCT_ID,SALE_YEA;
GROUP BY PRODUCT_ID,SALE_YEAd
*Ri
ERROR at line 6:y
ORA-00904: "SALE_YEA": invalid identifierr".
©达内科技论坛 -- 达内科技论坛 !0!%
©达内科技论坛 -- 达内科技论坛 ~1
SQL> SELECT PRODUCT_ID,Ss
2 SUM(DECODE(SALE_YEAR,2001,SALES)) "2001",JQ+XNx
3 SUM(DECODE(SALE_YEAR,2002,SALES)) "2002",{Sp‘c
4 SUM(DECODE(SALE_YEAR,2003,SALES)) "2003"C
5 FROM TEST[ 6
6 GROUP BY PRODUCT_ID;2:6R1x
©达内科技论坛 -- 达内科技论坛 `
PRODUCT_ID 2001 2002 20031
---------- ---------- ---------- ----------Tq
1 10r)
2 158^o
3 12 10NY?
分页模板：
SELECT B.* FROM78 (SELECT A.*,ROWNUM ROW_NUM FROMj (SELECT * FROM [表名] WHERE [选择条件]% ORDER BY [排序方式] ) A where rownum <= [页号*每页记录数] )B©
WHERE ROW_NUM >= [(页号-1)*每页记录数+1]