SQL面试50题-白红宇的个人博客

SQL面试50题

发布日期：2021-05-07 06:24:43 浏览次数：19 分类：精选文章

本文共 6292 字，大约阅读时间需要 20 分钟。

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)

SELECT a.s_id,a.s_score FROM(SELECT * FROM score WHERE c_id='01') as aINNER JOIN(SELECT * FROM score WHERE c_id='02') as bon a.s_id=b.s_idWHERE a.s_score>b.s_score;

View Code

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(重点)

SELECT a.s_id,a.s_name,avg(s_score) FROM student as aINNER JOIN score as bON a.s_id=b.s_idWHERE a.s_id IN(SELECT s_id FROM scoreWHERE s_score<60 GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2  )GROUP BY a.s_id,s_name

View Code

16、检索"01"课程分数小于60，按分数降序排列的学生信息

# 方法一：个人SELECT * FROM studentINNER JOIN(SELECT s_id,s_score FROM score WHERE c_id=01 AND s_score<60) as ton student.s_id=t.s_idORDER BY s_score DESC;# 方法二：个人SELECT * FROM studentINNER JOIN score ON student.s_id=score.s_idWHERE s_score<60 AND c_id=01ORDER BY s_score DESC;# 方法三：SELECT * FROM student as tINNER JOIN score as s on t.s_id=s.s_idWHERE s.c_id=01 AND s.s_score<60ORDER BY s.s_score DESC ;

View Code

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

SELECT s_id "学号",max(CASE WHEN c_id=01 THEN s_score ELSE NULL END ) "语文",max(CASE WHEN c_id=02 THEN s_score ELSE NULL END ) "数学",max(CASE WHEN c_id=03 THEN s_score ELSE NULL END ) "英语",avg(s_score) "平均成绩" FROM scoreGROUP BY s_idORDER BY avg(s_score) DESC

View Code

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

SELECT s.c_id "课程ID",c.c_name "课程名字",max(s.s_score) "最高分",min(s.s_score)"最低分",avg(s.s_score)"平均分",sum(CASE WHEN s.s_score>=60 THEN 1 ELSE 0 END )/count(s_id) "及格",sum(CASE WHEN s.s_score>=70 AND s.s_score<80 THEN 1 ELSE 0 END )/count(s_id) "中等",sum(CASE WHEN s.s_score>=80 AND s.s_score<90 THEN 1 ELSE 0 END )/count(s_id)"优良",sum(CASE WHEN s.s_score>=90 THEN 1 ELSE 0 END )/count(s_id) "优秀"FROM course AS cINNER JOINscore as s ON c.c_id=s.c_idGROUP BY c.c_id

View Code

SELECT c_id,       avg(CASE WHEN s_score >= 0 AND s_score < 60 THEN 1.0 ELSE 0.0 END)   "及格率",       avg(CASE WHEN s_score >= 60 AND s_score < 70 THEN 1.0 ELSE 0.0 END)  "中等率",       avg(CASE WHEN s_score >= 70 AND s_score < 85 THEN 1.0 ELSE 0.0 END)  "良好率",       avg(CASE WHEN s_score >= 85 AND s_score < 100 THEN 1.0 ELSE 0.0 END) "优秀率"FROM scoreGROUP BY c_id;

View Code

19、按各科成绩进行排序，并显示排名(重点row_number)

窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。

窗口函数大体可以分为以下两种：

1.能够作为窗口函数的聚合函数(sum，avg，count，max，min)

2.rank，dense_rank，row_number等专用窗口函数。

语法的基本使用方法：使用rank函数

rank函数是用来计算记录排序的函数

https://blog.csdn.net/qq_41805514/article/details/81772182

专用函数的种类：1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

3.row_number函数：赋予唯一的连续位次。

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

SELECT c.c_id "课程ID",c_name "课程名字",sum(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE 0 END ) AS "[100,85]",sum(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE 0 END ) "[85,70]",sum(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE 0 END )"[70,60]",sum(CASE WHEN s_score<=60 THEN 1 ELSE 0 END )"<60"FROM score as sINNER JOIN course as c on s.c_id=c.c_idGROUP BY c.c_id,c.c_name;

View Code

SELECT c.c_id "课程ID",c_name "课程名字",count(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE NULL END ) AS "[100,85]",count(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE NULL END ) "[85,70]",count(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE NULL END )"[70,60]",count(CASE WHEN s_score<=60 THEN 1 ELSE NULL END )"<60"FROM score as sINNER JOIN course as c on s.c_id=c.c_idGROUP BY c.c_id,c.c_name;

View Code

24、查询学生平均成绩及其名次(同19题，重点)

SELECT s_id,avg(s_score),       rank() OVER (ORDER BY avg(s_score)) as ranking FROM scoregroup by s_id# 不可加partition by，需使用group by

View Code

25、查询各科成绩前三名的记录(不考虑成绩并列情况)(重点 与22题类似)26、查询每门课程被选修的学生数(不重点)

26、查询每门课程被选修的学生数(不重点)SELECT c_name,COUNT(c.c_id)FROM score as sINNER JOINcourse as c on s.c_id=c.c_idGROUP BY c_name;# 注：一般需要将select后的字段放在group by后面SELECT c.c_id,c.c_name,count(DISTINCT s.s_id) AS "数量"FROM score as sINNER JOINcourse as c on s.c_id=c.c_idGROUP BY c.c_id,c.c_name;

View Code

27、 查询出只有两门课程的全部学生的学号和姓名(不重点)

# 方法一：SELECT s.s_id,s.s_nameFROM student AS sINNER JOIN  score as sc on s.s_id=sc.s_idGROUP BY s.s_id HAVING count(DISTINCT c_id)=2;# 错误SELECT s.s_name,s.s_idFROM student AS sINNER JOIN  score as sc on s.s_id=sc.s_idWHERE count(DISTINCT c_id)=2;# 方法二：SELECT s_id,s_name FROM studentWHERE s_id in(SELECT s_id FROM score GROUP BY s_id  HAVING count(DISTINCT c_id)=2);

View Code

28、查询男生、女生人数(不重点)

# 方法一：SELECT s_sex,count(s_sex) FROM studentGROUP BY s_sex;# 方法二：SELECTsum(CASE WHEN s_sex='男' THEN 1 ELSE 0 END ) "男生人数",sum(CASE WHEN s_sex='女' THEN 1 ELSE 0 END ) "女生人数"FROM student# 方法三：SELECTcount(CASE WHEN s_sex='男' THEN 1 ELSE NULL END ) "男生人数",count(CASE WHEN s_sex='女' THEN 1 ELSE NULL END ) "女生人数"FROM student# 注：null对count来说是不计算个数的，所以后面不能写0，只能用null

View Code

35、查询所有学生的课程及分数情况(重点)

# 不对SELECT s.s_id,s.s_name,c.c_name,sc.s_scoreFROM student as sINNER JOIN score as scON s.s_id=sc.s_idINNER JOIN course as cON c.c_id=sc.c_idGROUP BY s.s_id,s.s_name# 正确做法：(行转列)SELECT s.s_id,s.s_name,max(CASE WHEN c.c_name="语文" THEN s_score ELSE NULL END ) AS "语文",max(CASE WHEN c.c_name="数学" THEN s_score ELSE NULL END ) AS "数学",max(CASE WHEN c.c_name="英语" THEN s_score ELSE NULL END ) AS "英语"FROM student as sLEFT JOIN score as scON s.s_id=sc.s_idLEFT JOIN course as cON c.c_id=sc.c_idGROUP BY s.s_id,s.s_name

View Code

，

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数(重点)

# 错误处理方法：SELECT s.s_name,c.c_name,sc.s_scoreFROM student as sINNER JOIN score as sc on s.s_id=sc.s_idINNER JOIN course AS c ON c.c_id=sc.c_idGROUP BY s.s_id HAVING sc.s_score>70;# 正确处理方法：SELECT s.s_name,c.c_name,sc.s_scoreFROM student as sINNER JOIN score as sc on s.s_id=sc.s_idINNER JOIN course AS c ON c.c_id=sc.c_idWHERE sc.s_score>70;# 注：where 后面要跟的是数据表里的字段，where针对数据库文件的发挥作用，# 而having只是根据前面查询出来的结果集再次进行查询，因此having是针对结果集发挥作用。

View Code

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩(重要top)

SELECT s.s_id,s.s_name,c.c_name,c.c_id,sc.s_scoreFROM student as sINNER JOIN score as scON s.s_id=sc.s_idINNER JOIN course as cON sc.c_id=c.c_idINNER JOIN teacher as tON t.t_id=c.t_idWHERE t.t_name="张三" ORDER BY sc.s_score DESC limit 0,1; # 0表示从0开始取，若为降序排列则0是第一位，1表示取几条# SQL SERVER 中用top/*SELECT top 1 s.s_id,s.s_name,c.c_name,c.c_idFROM student as sINNER JOIN score as scON s.s_id=sc.s_idINNER JOIN course as cON sc.c_id=c.c_idINNER JOIN teacher as tON t.t_id=c.t_idWHERE t.t_name="张三" ORDER BY DESC*/

View Code

上一篇：第3章衡量线性回归的指标：MSE，RMSE，MAE

下一篇：第七章-模型评估

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章