常见数据库面试（精编版）.docx

最近有许多学员问了一些面试中的问题，请数据库教师总结了一下： 总结起来看：一是关于怎样找出和去除重复数据，这在另一个帖子利已有详细介绍二是关于找出某一列里最大或最小的前几个， 或是大于或小于某一个值（最大值或平均值）的数据针对这种情况，再此做一个介绍1：找出公司里收入最高的前三名员工：SQL> select rownum, last_name, salary2 from (select last_name, salary3 from s_emp4 order by salary desc)5 where rownum<=3;ROWNUM LAST_NAME SALARY---------- ------------------------- ----------1Velasquez 47502Ropeburn 29453 Nguyen注意：请大家分析一下一下语句为什么不对：SQL> select rownum, last_name, salary2 from s_emp3 where rownum<=34 order by salary desc;ROWNUM LAST_NAME SALARY---------- ------------------------- ----------1Velasquez 47503Nagayama 26602Ngao 20002： 找出表中的某一行或某几行的数据：（1） ）：找出表中第三行数据：用以下方法是不行的，因为 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） ）：找出第三行到第五行之间的数据： SQL> l1 select last_name, salary2 from (select rownum a, b.*3 from s_emp b) 4* where a between 3 and 5 SQL> /LAST_NAMESALARY------------------------- ----------Nagayama2660Quick-To-See2755Ropeburn29453：找出那些工资高于他们所在部门的平均工资的员工。

1） ）：第一种方法：SQL> select last_name, dept_id, salary2 from s_emp a3 where salary>(select avg(salary)4 from s_emp5 where dept_id=;LAST_NAMEDEPT_IDSALARY------------------------- ---------- ----------Velasquez504750Urguhart412280Menchu422375Biri 432090Catchpole470442Havel45Nguyen34Maduro412660Nozaki422280Schwartz45209010 rows selected.（2） ）：第二种方法： SQL> l1 select , , ,2 from s_emp a, (select dept_id, avg(salary) avgsal3from s_emp4group by dept_id) b5where =6* and > SQL> /LAST_NAME SALARY DEPT_ID AVGSAL------------------------- ---------- ---------- ----------Velasquez 475050Urguhart228041Menchu237542Biri4317102090Catchpole2470441995Havel45Nguyen 342204Maduro 266041Nozaki228042Schwartz45209010 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=SQL> /ID LAST_NAME SALARYMANAGER_ID---------- ------------------------- ---------- ----------6Urguhart 228027Menchu 237528Biri 209029Catchpole 2470210Havel 212Giljum 2831313Sedeghi 314Nguyen 315Dumas 2755316Maduro 2660610 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 salarydesc) rnk4* from employees) where rnk=2 or rnk=3 SQL> /NAME--------------------------------------------------------------------------------SALARY DEPTNO---------- ----------FayPat6000 20KhooAlexander3100 30BaidaShelli2900 30NAME--------------------------------------------------------------------------------SALARY DEPTNO---------- ----------WeissMatthew8000 50KauflingPayam7900 50ErnstBruce6000 60NAME--------------------------------------------------------------------------------SALARY DEPTNO---------- ----------AustinDavid4800 60PataballaValli4800 60PartnersKaren13500 80NAME--------------------------------------------------------------------------------SALARY DEPTNO---------- ----------ErrazurizAlberto12000 80KochharNeena17000 90De HaanLex17000 90NAME--------------------------------------------------------------------------------SALARY DEPTNO---------- ----------FavietDaniel9000 100ChenJohn8200 100GietzWilliam8300 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 salarydesc) rnk4* from employees) where rnk=2 or rnk=3 SQL> /NAME--------------------------------------------------------------------------------SALARY DEPTNO---------- ----------Fay。