常見數(shù)據(jù)庫面試.doc

最近有許多學員問了一些面試中的問題，請數(shù)據(jù)庫教師總結了一下：總結起來看：一是關于怎樣找出和去除重復數(shù)據(jù)，這在另一個帖子利已有詳細介紹。二是關于找出某一列里最大或最小的前幾個，或是大于或小于某一個值（最大值或平均值）的數(shù)據(jù)。針對這種情況，再此做一個介紹。1：找出公司里收入最高的前三名員工：SQL select rownum, last_name, salary2 from (select last_name, salary3 from s_emp4 order by salary desc)5 where rownum select rownum, last_name, salary2 from s_emp3 where rownum=34 order by salary desc; ROWNUM LAST_NAME SALARY- - - 1 Velasquez 4750 3 Nagayama 2660 2 Ngao 20002： 找出表中的某一行或某幾行的數(shù)據(jù)：（1）：找出表中第三行數(shù)據(jù)：用以下方法是不行的，因為rownum后面至可以用或號和其它的比較符號。SQL select * 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 SALARY- -Nagayama 2660（2）：找出第三行到第五行之間的數(shù)據(jù)：SQL l1 select last_name, salary2 from (select rownum a, b.*3 from s_emp b)4* where a between 3 and 5SQL /LAST_NAME SALARY- -Nagayama 2660Quick-To-See 2755Ropeburn 29453：找出那些工資高于他們所在部門的平均工資的員工。（1）：第一種方法：SQL select last_name, dept_id, salary2 from s_emp a3 where salary(select avg(salary)4 from s_emp5 where dept_id=a.dept_id);LAST_NAME DEPT_ID SALARY- - -Velasquez 50 4750Urguhart 41 2280Menchu 42 2375Biri 43 2090Catchpole 44 2470Havel 45 2483.3Nguyen 34 2897.5Maduro 41 2660Nozaki 42 2280Schwartz 45 209010 rows selected.（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.dept_id6* and a.salaryb.avgsalSQL /LAST_NAME SALARY DEPT_ID AVGSAL- - - -Velasquez 4750 50 3847.5Urguhart 2280 41 2181.5Menchu 2375 42 2055.16667Biri 2090 43 1710Catchpole 2470 44 1995Havel 2483.3 45 2069.1Nguyen 2897.5 34 2204Maduro 2660 41 2181.5Nozaki 2280 42 2055.16667Schwartz 2090 45 2069.110 rows selected.4：找出那些工資高于他們所在部門的manager的工資的員工。SQL l1 select id, last_name, salary, manager_id2 from s_emp a3 where salary(select salary4 from s_emp5* where id=a.manager_id)SQL / ID LAST_NAME SALARY MANAGER_ID- - - - 6 Urguhart 2280 2 7 Menchu 2375 2 8 Biri 2090 2 9 Catchpole 2470 2 10 Havel 2483.3 2 12 Giljum 2831 3 13 Sedeghi 2878.5 3 14 Nguyen 2897.5 3 15 Dumas 2755 3 16 Maduro 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_id order by salary desc) rnk4* from employees) where rnk=2 or rnk=3SQL /NAME- SALARY DEPTNO- -FayPat 6000 20KhooAlexander 3100 30BaidaShelli 2900 30NAME- SALARY DEPTNO- -WeissMatthew 8000 50KauflingPayam 7900 50ErnstBruce 6000 60NAME- SALARY DEPTNO- -AustinDavid 4800 60PataballaValli 4800 60PartnersKaren 13500 80NAME- SALARY DEPTNO- -ErrazurizAlberto 12000 80KochharNeena 17000 90De HaanLex 17000 90NAME- SALARY DEPTNO- -FavietDaniel 9000 100ChenJohn 8200 100GietzWilliam 8300 11015 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- SALARY DEPTNO- -FayPat 6000 20KhooAlexander 3100 30BaidaShelli 2900 30NAME- SALARY DEPTNO- -WeissMatthew 8000 50KauflingPayam 7900 50ErnstBruce 6000 60NAME- SALARY DEPTNO- -AustinDavid 4800 60PataballaValli 4800 60PartnersKaren 13500 80NAME- SALARY DEPTNO- -ErrazurizAlberto 12000 80KochharNeena 17000 90De HaanLex 17000 90NAME- SALARY 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 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 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 pro2 1 M F 2 N G 3 B A 寫出查詢語句 解決方案可有以下三種作參考：1：使用pl/sql代碼實現(xiàn)，但要求你組合后的長度不能超出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 | 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 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 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. 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 select 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 lead(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 = 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創(chuàng)建一個函數(shù)（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 (select a2 from unite_a where a1=tmp_a1) loop Col_a2=Col_a2|cur.a2; end loop; return Col_a2; end get_a2; select distinct a1 ,get_a2(a1) from unite_a 1 ABC 2 EFG 3 KMN原表: courseid coursename score - 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 - 為了便于閱讀,查詢此表后的結果顯式如下(及格分數(shù)為60): courseid coursename score mark - 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass - 寫出此查詢語句 解決辦法如下， 要用到decode和sign兩個函數(shù)： SQL select courseid,coursename ,score ,decode(sign(score-61),-1,fail,pass) as mark from course_v;COURSEID COURSENAME SCORE MARK- - - -1 java 70 pass2 oracle 90 pass3 xml 40 fail4 jsp 30 fail5 servlet 80 pass 員工表emp（empno員工號，empname姓名，age年齡，deptn