sql 面试问题

Posted on 2006-12-08 15:39 looline 阅读(2385) 评论(1)  编辑  收藏
最近有 一些面试中的问题，总结起来看，一是关于怎样找出和去除重复数据，这在另一个帖子利已有详细介绍。二是关于找出某一列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据。针对这种情况，再此做一个介绍。

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                                 47508 G
        2 Ropeburn                                 2945l6C.G
        3 Nguyen                                      2897.5 QUVoW
© 达内科技论坛 -- 达内科技论坛　　 =9F}j\
© 达内科技论坛 -- 达内科技论坛　　 J'
注意：请大家分析一下一下语句为什么不对： dOb{RO
© 达内科技论坛 -- 达内科技论坛　　 bt7]c0
SQL> select rownum, last_name, salary
 2  from s_emp g:m4>
 3  where rownum<=3 .ny
 4  order by salary desc; tUJ
© 达内科技论坛 -- 达内科技论坛　　 pqGE\[
   ROWNUM LAST_NAME                     SALARY !7.
---------- ------------------------- ---------- Y
        1 Velasquez                       4750 YHJ#V
        3 Nagayama                        2660 %p8~
        2 Ngao                            2000 Bb;
© 达内科技论坛 -- 达内科技论坛　　 @
© 达内科技论坛 -- 达内科技论坛　　 g#|E
2 ： 找出表中的某一行或某几行的数据： k9
© 达内科技论坛 -- 达内科技论坛　　 mM
（ 1 ）：找出表中第三行数据： P
用以下方法是不行的，因为 rownum 后面至可以用 < 或 <= 号 , 不可以用 =,> 号和其它的比较符号。 +h">^
© 达内科技论坛 -- 达内科技论坛　　 Vr$$]h
SQL> select * from s_emp {
 2  where rownum=3; O_{
© 达内科技论坛 -- 达内科技论坛　　 /;B
no rows selected p(
© 达内科技论坛 -- 达内科技论坛　　 1"B
SQL> select * from s_emp ">7
 2  where rownum between 3 and 5; Dky!m
© 达内科技论坛 -- 达内科技论坛　　 qS
no rows selected m^)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=3 DI
SQL> / Cu
© 达内科技论坛 -- 达内科技论坛　　 `t
LAST_NAME                     SALARY d$#>
------------------------- ---------- ,/N?UK
Nagayama                        2660 w
© 达内科技论坛 -- 达内科技论坛　　 .$CX
（ 2 ）：找出第三行到第五行之间的数据： L
SQL> l ]M]~H
 1  select last_name, salary H
 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                     SALARY 5'
------------------------- ----------6yd
Nagayama                        2660 @L
Quick-To-See                    2755 >
Ropeburn                        2945 Ni'=r
© 达内科技论坛 -- 达内科技论坛　　 <Lh_VZ
3 ：找出那些工资高于他们所在部门的平均工资的员工。 I'R*u
© 达内科技论坛 -- 达内科技论坛　　 ;7Z
（ 1 ）：第一种方法： 5acm6S
SQL> select last_name, dept_id, salary (
 2  from s_emp a g1}+7
 3  where salary>(select avg(salary) S-(
 4                from s_emp 2ksQsY
 5                where dept_id=a.dept_id); PZRM
© 达内科技论坛 -- 达内科技论坛　　 WFa=Fa
LAST_NAME                    DEPT_ID     SALARY _f7
------------------------- ---------- ---------- 3x-v0
Velasquez                         50       4750 r6h*8
Urguhart                          41       2280 yECF*
Menchu                            42       2375 TLoS/'
Biri                              43       2090 ^xJ
Catchpole                         44       2470 ZS,
Havel                             45     2483.3 ;R`$_
Nguyen                            34     2897.5 pK\=Q$
Maduro                            41       2660 ?OZ_(:
Nozaki                            42       2280 wR
Schwartz                          45       2090 `
© 达内科技论坛 -- 达内科技论坛　　 AtMr
10 rows selected. loa
© 达内科技论坛 -- 达内科技论坛　　 q8a{
（ 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) avgsal 7P<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.avgsal Z
SQL> / '^NJ
© 达内科技论坛 -- 达内科技论坛　　 7s/
LAST_NAME                     SALARY    DEPT_ID     AVGSAL CX@
------------------------- ---------- ---------- ---------- j
Velasquez                       4750         50     3847.5 }@7
Urguhart                        2280         41     2181.5 _m
Menchu                          2375         42 2055.16667 ks5
Biri                            2090         43       1710 I/Q
Catchpole                       2470         44       1995 k
Havel                         2483.3         45     2069.1 ,H
Nguyen                        2897.5         34       2204 nB
Maduro                          2660         41     2181.5 <&
Nozaki                          2280         42 2055.16667 88W
Schwartz                        2090         45     2069.1 "L$Aht
© 达内科技论坛 -- 达内科技论坛　　 @3`tD~
10 rows selected. p3Q#b
© 达内科技论坛 -- 达内科技论坛　　 pV[@QT
4 ：找出那些工资高于他们所在部门的 manager 的工资的员工。 Y^;'
© 达内科技论坛 -- 达内科技论坛　　 5z
SQL> l g!O`?~
 1  select id, last_name, salary, manager_id a
 2  from s_emp a e8wG
 3  where salary>(select salary b)-r
 4                from s_emp flzfJG
 5*               where id=a.manager_id) |/t)yO
SQL> / {Nwt
© 达内科技论坛 -- 达内科技论坛　　 *^0LJ
       ID LAST_NAME                     SALARY MANAGER_ID CQV
---------- ------------------------- ---------- ---------- =Il
        6 Urguhart                        2280          2 If@
        7 Menchu                          2375          2 j:QMG
        8 Biri                            2090          2 wjkW.!
        9 Catchpole                       2470          2 3]
       10 Havel                         2483.3          2f&&MPS
       12 Giljum                          2831          3 8NT<!
       13 Sedeghi                       2878.5          3 _
       14 Nguyen                        2897.5          3 b@
       15 Dumas                           2755          3 ${
       16 Maduro                          2660          6Kg
 © 达内科技论坛 -- 达内科技论坛　　 Zu
10 rows selected. Uk2fN

第一题：有两个表分别如下： =Z<
表A(varchar(32) NAME,int GRADE) *{
数据： :WyN$
ZHANGSHAN 80 R
LISI      60 N
WANGWU    84 #
© 达内科技论坛 -- 达内科技论坛　　m]P
表B(varchar(32) NAME,int AGE) [!
数据： Hi)Y:7
ZHANGSHAN 26 =(
LISI      24 @
WANGWU    26 1>
WUTIAN    26 ~.yj4
© 达内科技论坛 -- 达内科技论坛　　X@~~O
（1）写SQL语句得到如下查询结果： >g1\oj
NAME      GRADE   AGE     C
ZHANGSHAN 80      26 q5IV
LISI      60      24 sw
WANGWU    84      262c. l
WUTIAN    NULL    26 Z]
疑问：这里的没有成绩的那个人的记录怎么得到呢？ 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_name we#
1000  S1L&@U
1001  S2 .$]K
1002  S3 1?
1003  S4 <q(SlO
1000  S5 p8
1000  S6 gZ~&>=
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 a hCu
 2  (name varchar2(32), t
 3  grade int); fHYm
Table created. j|
SQL> insert into a_t y5.u
 2  values('&a',&b); Vk-~\y
Enter value for a: zhangshan tS
Enter value for b: 80 hYTG&
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: wangwu 9J
Enter value for b: 84 rR`
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_t J
 2  values('&a',&b); UK|'?]
Enter value for a: zhangshan ZA+X:
Enter value for b: 26 :x`.M(
old   2: values('&a',&b) wbi[
new   2: values('zhangshan',26) M=A
© 达内科技论坛 -- 达内科技论坛　　#
1 row created.6f4
 © 达内科技论坛 -- 达内科技论坛　　*h5
SQL> / #u
Enter value for a: lisi 18J/-
Enter value for b: 24 fA*
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: 26 7Max
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: wutian z&a
Enter value for b: 26g
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> l 71srGP
 1  select b.name, a.grade, b.age 5)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         24 W
wangwu                                   84         26 477E
wutian                           null               26 [a?EA
zhangshan                                80         26 h~
© 达内科技论坛 -- 达内科技论坛　　?!;
SQL> -#D
© 达内科技论坛 -- 达内科技论坛　　?kMGSC
2: © 达内科技论坛 -- 达内科技论坛　　45r&'a
SQL> l L
 1  select name, avg(grade), age (sJ7rh
 2  from (select b.name name, a.grade grade, b.age age NUx3>;
 3  from a_t a,b_t b SJx
 4  where a.name(+)=b.name) ,5
 5* group by age, name ZU
SQL> / E
© 达内科技论坛 -- 达内科技论坛　　Ewhj
NAME                             AVG(GRADE)        AGE &H
-------------------------------- ---------- ---------- g]<pc&
lisi                                     60         24 3
wangwu                                   84         26 !ZOnC
wutian                                              26 Nn
zhangshan                                80         26f
 © 达内科技论坛 -- 达内科技论坛　　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'); R23g
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 :!|j8m
------------------ ---- ------- '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 DAu8a
000000D5.0001.0002 1112 1111 sRxcx
000000D5.0002.0002 1113 1111 ;+<J
000000D5.0003.0002 1114 1111 WbQN
© 达内科技论坛 -- 达内科技论坛　　?| -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 1111 q

 

=====================================================

ProductID  SALE_YEAR   SALES    productID   2001  2002     2003L5WI3
001          2001               10           001        10 "
002          2001               15           002        15 <
003          2002               12           003                12         10 AT
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: 001l7g[
Enter value for 2: 2001 (!Zj
Enter value for 3: 10 AXREC
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: 002 w3
Enter value for 2: 2001 Y
Enter value for 3: 15 i
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: 003 e
Enter value for 2: 2002g-P
Enter value for 3: 12G^RV
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: 003 OI
Enter value for 2: 2003 y<
Enter value for 3: 10l )@d
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                 SALES yk
---------- -------------------- ----------
        1 2001                                   10 U
        2 2001                                   15 ;o
        3 2002                                   12 rN\ou
        3 2003                                   10 eI%/
© 达内科技论坛 -- 达内科技论坛　　 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; <rjT
        GROUP BY PRODUCT_ID,SALE_YEA d
                            * Ri
ERROR at line 6: y
ORA-00904: "SALE_YEA": invalid identifier r".
© 达内科技论坛 -- 达内科技论坛　　 !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       2003 1
---------- ---------- ---------- ---------- Tq
        1              10 r)<D
        2              15 8^o
        3                             12         10 NY?


分页模板：

SELECT B.* FROM 78 (SELECT A.*,ROWNUM ROW_NUM FROM j (SELECT * FROM [ 表名 ] WHERE [ 选择条件 ] % ORDER BY [ 排序方式 ] ) A where rownum <= [ 页号 * 每页记录数 ] )B ©

WHERE ROW_NUM >= [( 页号 -1)* 每页记录数 +1]

Feedback

# re: sql 面试问题  回复  更多评论   

2008-04-22 21:36 by 的
烦的

只有注册用户登录后才能发表评论。
网站导航:
 

posts - 3, comments - 1, trackbacks - 0, articles - 1

Copyright © looline