数据库面试宝典

1、数据库面试宝典一是关于怎样找出和去除重复数据，这在另一个帖子利已有详细介绍。二是关于找出某一列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据。针对这种情况，再此做一个介绍。1：找出公司里收入最高的前三名员工：SQL> select rownum, last_name, salary2  from (select last_name, salary3        from s_emp4        order by salary desc)5  where rown

2、um<=3;  ROWNUM LAST_NAME                     SALARY- - -       1 Velasquez                       4750       2 Ropeburn        

3、               2945       3 Nguyen                        2897.5注意：请大家分析一下一下语句为什么不对：SQL> select rownum, last_name, salary2  from s_emp3  where rownum<=34  orde

4、r by salary desc;  ROWNUM LAST_NAME                     SALARY- - -       1 Velasquez                       4750       3 Nagayama      

5、                  2660       2 Ngao                            20002： 找出表中的某一行或某几行的数据：（1）：找出表中第三行数据：用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。SQL> s

6、elect * from s_emp2  where rownum=3;no rows selectedSQL> select * from s_emp2  where rownum between 3 and 5;no rows selected正确的方法如下：SQL> l1  select last_name, salary2  from (select rownum a, b.*3        from s_emp b)4* where a=3SQL> /LAST_NAME  

7、                  SALARY- -Nagayama                        2660（2）：找出第三行到第五行之间的数据：SQL> l1  select last_name, salary2  from (select rownum a, b.*3        from s_em

8、p b)4* where a between 3 and 5SQL> /LAST_NAME                     SALARY- -Nagayama                        2660Quick-To-See                  

9、 2755Ropeburn                        29453：找出那些工资高于他们所在部门的平均工资的员工。（1）：第一种方法：SQL> select last_name, dept_id, salary2  from s_emp a3  where salary>(select avg(salary)4               

10、0;from s_emp5                where dept_id=a.dept_id);LAST_NAME                    DEPT_ID     SALARY- - -Velasquez                        

11、50       4750Urguhart                          41       2280Menchu                            42       2375Biri   &

12、#160;                          43       2090Catchpole                         44       2470Havel              

13、               45     2483.3Nguyen                            34     2897.5Maduro                           &#

14、160;41       2660Nozaki                            42       2280Schwartz                          45       209010 rows s

15、elected.（2）：第二种方法：SQL> l1  select a.last_name, a.salary, a.dept_id, b.avgsal2  from s_emp a, (select dept_id, avg(salary) avgsal3               from s_emp4               group by dept_id) b5  where a.dept_id=b.de

16、pt_id6* and a.salary>b.avgsalSQL> /LAST_NAME                     SALARY    DEPT_ID     AVGSAL- - - -Velasquez                       4750         50 &

17、#160;   3847.5Urguhart                        2280         41     2181.5Menchu                          2375         42

18、2055.16667Biri                            2090         43       1710Catchpole                       2470         44 

19、0;     1995Havel                         2483.3         45     2069.1Nguyen                        2897.5         34

20、60;     2204Maduro                          2660         41     2181.5Nozaki                          2280       &#

21、160; 42 2055.16667Schwartz                        2090         45     2069.110 rows selected.4：找出那些工资高于他们所在部门的manager的工资的员工。SQL> l1  select id, last_name, salary, manager_id2  from s_emp a3  w

22、here salary>(select salary4                from s_emp5*               where id=a.manager_id)SQL> /      ID LAST_NAME                     SALARY MANAGER_ID

23、- - - -       6 Urguhart                        2280          2       7 Menchu                          2375 &

24、#160;        2       8 Biri                            2090          2       9 Catchpole                

25、      2470          2      10 Havel                         2483.3          2      12 Giljum              

26、60;          2831          3      13 Sedeghi                       2878.5          3      14 Nguyen            

27、;            2897.5          3      15 Dumas                           2755          3      16 Maduro       &#

28、160;                  2660          610 rows selected.找出部门工资排名第二，三的员工1  select name,salary,deptno from (2  select concat(last_name,first_name) name,salary,department_id deptno,3  rank() over (partition by department

29、_id order by salary desc) rnk4*  from employees) where rnk=2 or rnk=3SQL> /NAME-  SALARY     DEPTNO- -FayPat    6000         20KhooAlexander    3100         30BaidaShelli    2900        

30、30NAME-  SALARY     DEPTNO- -WeissMatthew    8000         50KauflingPayam    7900         50ErnstBruce    6000         60NAME-  SALARY     DEPTNO- -AustinDavid    4800 &#

31、160;       60PataballaValli    4800         60PartnersKaren   13500         80NAME-  SALARY     DEPTNO- -ErrazurizAlberto   12000         80KochharNeena   17000   &#

32、160;     90De HaanLex   17000         90NAME-  SALARY     DEPTNO- -FavietDaniel    9000        100ChenJohn    8200        100GietzWilliam    8300        11

33、015 rows selected.SQL>找出部门工资排名第二，三的员工1  select name,salary,deptno from (2  select concat(last_name,first_name) name,salary,department_id deptno,3  rank() over (partition by department_id order by salary desc) rnk4*  from employees) where rnk=2 or rnk=3SQL> /NAME-  SALA

34、RY     DEPTNO- -FayPat    6000         20KhooAlexander    3100         30BaidaShelli    2900         30NAME-  SALARY     DEPTNO- -WeissMatthew    8000      

35、60; 50KauflingPayam    7900         50ErnstBruce    6000         60NAME-  SALARY     DEPTNO- -AustinDavid    4800         60PataballaValli    4800         60Partners

36、Karen   13500         80NAME-  SALARY     DEPTNO- -ErrazurizAlberto   12000         80KochharNeena   17000         90De HaanLex   17000         90NAME-  SALARY

37、    DEPTNO- -FavietDaniel    9000        100ChenJohn    8200        100GietzWilliam    8300        11015 rows selected.SQL>又是一道面试题:原表:id proid proname1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro

38、21 M F2 N G3 B A写出查询语句 又是一道面试题:原表:id proid proname1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro21 M F2 N G3 B A写出查询语句 又是一道面试题:原表:id proid proname1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro21 M F2 N G3 B A写出查询语句 又是一道面试题: 原表: id proid proname 1 1 M 1 2 F 2 1 N 2 2 G 3 1 B 3 2 A 查询后的表: id pro1 pr

39、o2 1 M F 2 N G 3 B A 写出查询语句 解决方案可有以下三种作参考：1：使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。 下面是一个例子 create or replace type strings_table is table of varchar2(20);/create or replace function merge (pv in strings_table) return varchar2isls varchar2(4000);beginfor i in 1.pv.count loop  ls := ls |

40、 pv(i);end loop;return ls;end;/create table t (id number,name varchar2(10);insert into t values(1,'Joan');insert into t values(1,'Jack');insert into t values(1,'Tom');insert into t values(2,'Rose');insert into t values(2,'Jenny');column names format a80;select

41、 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;drop type strings_table;drop function merge;drop table t;2：用sql： Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundred

42、s 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. This example uses a max of 6, and would need more cut n pasting to do more than that. SQL> select deptno, dname, emps 2 from ( 3 sel

43、ect d.deptno, d.dname, rtrim(e.ename |', '| 4 lead(e.ename,1) over (partition by d.deptno 5 order by e.ename) |', '| 6 lead(e.ename,2) over (partition by d.deptno 7 order by e.ename) |', '| 8 lead(e.ename,3) over (partition by d.deptno 9 order by e.ename) |', '| 10 le

44、ad(e.ename,4) over (partition by d.deptno 11 order by e.ename) |', '| 12 lead(e.ename,5) over (partition by d.deptno 13 order by e.ename),', ') emps, 14 row_number () over (partition by d.deptno 15 order by e.ename) x 16 from emp e, dept d 17 where d.deptno = e.deptno 18 ) 19 where x

45、 = 1 20 / DEPTNO DNAME EMPS - - - 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD3：先用pl/sql创建一个函数（create function get_a2）; create or replace function get_a2( tmp_a1 number) return varchar2 is Col_a2 varchar2(4000); begin Col_a2:='' for cur in (sele