"17年底数据库面试合集，低积分，福利版"

OURSE_ID, MAX(S.SCORE) AS HIGHEST_SCORE, MIN(S.SCORE) AS LOWEST_SCORE FROM SCORE S JOIN COURSE C ON S.COURSE_ID = C.COURSE_ID GROUP BY S.COURSE_ID; 19、查询每门课程的选修人数：结合学生表和成绩表，以如下形式显示：课程名称，选修人数     SELECT C.COURSE_NAME, COUNT(DISTINCT S.STUDENT_ID) AS STUDENT_COUNT FROM COURSE C JOIN SCORE S ON S.COURSE_ID = C.COURSE_ID GROUP BY C.COURSE_NAME; 20、查询出只有同学选修的所有课程的同学ID、课程ID和成绩     SELECT S.STUDENT_ID, S.COURSE_ID, S.SCORE FROM SCORE S WHERE S.COURSE_ID IN (SELECT COURSE_ID FROM SCORE GROUP BY COURSE_ID HAVING COUNT(DISTINCT STUDENT_ID) = 1); 21、查询成绩前三名的学生及其课程 成绩：以如下形式显示：课程ID，学生ID，成绩     SELECT COURSE_ID, STUDENT_ID, SCORE FROM (SELECT COURSE_ID, STUDENT_ID, SCORE, ROW_NUMBER() OVER (PARTITION BY COURSE_ID ORDER BY SCORE DESC) AS RN FROM SCORE) T WHERE T.RN <= 3; 22、查询每门课程被选修的学生中，成绩为最高的学生的学号和成绩：以如下形式显示：课程ID，学生ID，成绩     SELECT S.COURSE_ID, S.STUDENT_ID, S.SCORE FROM SCORE S JOIN (SELECT COURSE_ID, MAX(SCORE) AS MAX_SCORE FROM SCORE GROUP BY COURSE_ID) T ON S.COURSE_ID = T.COURSE_ID AND S.SCORE = T.MAX_SCORE; 23、就按给定的编号（n）找出学生的成绩以及其名次：以如下形式显示：学生ID，课程ID，成绩，名次     SELECT STUDENT_ID, COURSE_ID, SCORE, RANK() OVER (PARTITION BY COURSE_ID ORDER BY SCORE DESC) AS RANK FROM SCORE WHERE STUDENT_ID = n; 24、查询课程平均成绩大于等于60分的课程号和学生ID：显示：课程号，学生ID     SELECT S.COURSE_ID, S.STUDENT_ID FROM SCORE S JOIN (SELECT COURSE_ID, AVG(SCORE) AS AVG_SCORE FROM SCORE GROUP BY COURSE_ID HAVING AVG(SCORE) >= 60) T ON S.COURSE_ID = T.COURSE_ID; 25、查询所有课程的成绩第2名到第3名的学生学号、课程号、成绩：显示：学生ID，课程ID，成绩     SELECT STUDENT_ID, COURSE_ID, SCORE FROM (SELECT STUDENT_ID, COURSE_ID, SCORE, ROW_NUMBER() OVER (PARTITION BY COURSE_ID ORDER BY SCORE DESC) AS RN FROM SCORE) T WHERE T.RN BETWEEN 2 AND 3; 26、查询如下课程 ID 课程名称 老师 ：SELECT C. COURSE_ID , C. COURSE_NAME , T. TEACHER_NAME FROM COURSE C LEFT JOIN TEACHER T ON C. TEACHER_ID = T. TEACHER_ID ORDER BY C. COURSE_ID ; 以上是17年底数据库面试题，供备战面试参考。