SQL面试题

现在有三个表student,score,course
对应的建表语句如下：
CREATE TABLE student(
student_id NUMBER PRIMARY KEY,
student_name,VARCHAR2(30) NOT NULL)

CREATE TABLE score(
score_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
score NUMBER)

CREATE TABLE course(
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(30))

要求用基本SQL实现如下的两条查询要求：
(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名

(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名

Oracle的解法：
表及数据：

Sql代码  

1. create table STUDENT  
2. (  
3.   STUDENT_ID   NUMBER not null,  
4.   STUDENT_NAME VARCHAR2(30) not null  
5. )  
6. ;  
7. alter table STUDENT  
8.   add primary key (STUDENT_ID);  
9.   
10. prompt Loading STUDENT...  
11. insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
12. values (1, '张三');  
13. insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
14. values (2, '李四');  
15. insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
16. values (3, '王五');  
17. insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
18. values (4, '马六');  
19. insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
20. values (5, '孙七');  
21. insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
22. values (6, '王八');  
23. commit;
    

Sql代码  

1. create table COURSE  
2. (  
3.   COURSE_ID   NUMBER not null,  
4.   COURSE_NAME VARCHAR2(30)  
5. )  
6. ;  
7. alter table COURSE  
8.   add primary key (COURSE_ID);  
9.   
10. prompt Loading COURSE...  
11. insert into COURSE (COURSE_ID, COURSE_NAME)  
12. values (1, '语文');  
13. insert into COURSE (COURSE_ID, COURSE_NAME)  
14. values (2, '数学');  
15. insert into COURSE (COURSE_ID, COURSE_NAME)  
16. values (3, '英语');  
17. commit;

 

Sql代码  

1. create table SCORE  
2. (  
3.   SCORE_ID   NUMBER not null,  
4.   STUDENT_ID NUMBER,  
5.   COURSE_ID  NUMBER,  
6.   SCORE      NUMBER  
7. )  
8. ;  
9. alter table SCORE  
10.   add primary key (SCORE_ID);  
11.   
12. prompt Loading SCORE...  
13. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
14. values (1, 1, 1, 99);  
15. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
16. values (2, 1, 2, 98);  
17. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
18. values (3, 1, 3, 97);  
19. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
20. values (4, 2, 1, 99);  
21. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
22. values (5, 2, 2, 97);  
23. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
24. values (6, 2, 3, 98);  
25. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
26. values (7, 3, 1, 96);  
27. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
28. values (8, 3, 2, 95);  
29. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
30. values (9, 3, 3, 94);  
31. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
32. values (10, 4, 1, 93);  
33. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
34. values (11, 4, 2, 92);  
35. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
36. values (12, 4, 3, 91);  
37. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
38. values (13, 5, 1, 90);  
39. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
40. values (14, 5, 2, 89);  
41. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
42. values (15, 5, 3, 88);  
43. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
44. values (16, 6, 1, 87);  
45. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
46. values (17, 6, 2, 86);  
47. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
48. values (18, 6, 3, 85);  
49. commit;</span>  

 

(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名：
根据不同的排名方式有三种不同的sql写法：
1.1成绩相同的人排名相同，且排名是连续的。
Sql如下：

Sql代码  

1. select *  
2.   from (select s.STUDENT_NAME,  
3.                sc.SCORE,  
4.                c.COURSE_NAME,  
5.                dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank  
6.           from student s, course c, score sc  
7.          where s.STUDENT_ID = sc.STUDENT_ID  
8.            and c.COURSE_ID = sc.COURSE_ID) t  
9. where t.drank < 6;

 结果如下：

STUDENT_NAME SCORE COURSE_NAME DRANK 张三 99 语文 1 李四 99 语文 1 王五 96 语文 2 马六 93 语文 3 孙七 90 语文 4 王八 87 语文 5 张三 98 数学 1 李四 97 数学 2 王五 95 数学 3 马六 92 数学 4 孙七 89 数学 5 李四 98 英语 1 张三 97 英语 2 王五 94 英语 3 马六 91 英语 4 孙七 88 英语 5

1.2成绩相同的人排名相同，且排名不是连续的。
Sql如下：

Sql代码  

1. select *  
2.   from (select s.STUDENT_NAME,  
3.                sc.SCORE,  
4.                c.COURSE_NAME,  
5.                rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking  
6.           from student s, course c, score sc  
7.          where s.STUDENT_ID = sc.STUDENT_ID  
8.            and c.COURSE_ID = sc.COURSE_ID) t  
9. where t.ranking < 6;

 结果如下：

STUDENT_NAME SCORE COURSE_NAME RANKING 张三 99 语文 1 李四 99 语文 1 王五 96 语文 3 马六 93 语文 4 孙七 90 语文 5 张三 98 数学 1 李四 97 数学 2 王五 95 数学 3 马六 92 数学 4 孙七 89 数学 5 李四 98 英语 1 张三 97 英语 2 王五 94 英语 3 马六 91 英语 4 孙七 88 英语 5

1.2成绩相同的人根据学号排序，排名是连续的。
Sql如下：

Sql代码  

1. select *  
2.   from (select s.STUDENT_NAME,  
3.                sc.SCORE,  
4.                c.COURSE_NAME,  
5.                row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn  
6.           from student s, course c, score sc  
7.          where s.STUDENT_ID = sc.STUDENT_ID  
8.            and c.COURSE_ID = sc.COURSE_ID) t  
9. where t.rn < 6;

 结果如下：

STUDENT_NAME SCORE COURSE_NAME RN 张三 99 语文 1 李四 99 语文 2 王五 96 语文 3 马六 93 语文 4 孙七 90 语文 5 张三 98 数学 1 李四 97 数学 2 王五 95 数学 3 马六 92 数学 4 孙七 89 数学 5 李四 98 英语 1 张三 97 英语 2 王五 94 英语 3 马六 91 英语 4 孙七 88 英语 5

(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名：
Sql如下：

Sql代码  

1. select *  
2.   from (select s.STUDENT_NAME,  
3.                sc.SCORE,  
4.                c.COURSE_NAME,  
5.                row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn  
6.           from student s, course c, score sc  
7.          where s.STUDENT_ID = sc.STUDENT_ID  
8.            and c.COURSE_ID = sc.COURSE_ID) t  
9. where t.rn = 3;
   

 结果如下：

STUDENT_NAME SCORE COURSE_NAME RN
王五 96 语文 3
王五 95 数学 3
王五 94 英语 3