经典五十道SQL题练习 持续更新
前言:这些是网上广为流传的经典50道SQL题合集 最近正好想提升一下自己的SQL能力,所以将这些SQL题开个博客,慢慢更新,当作一个记录帖吧,记录一下自己的成长
建表语句
为了不影响文章的排版和整体美观,我将建表语句单独抽出来了,可以访问: 经典五十题SQL建表语句 获取建表语句
题目
-
查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-
查询在 SC 表存在成绩的学生信息
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-
查询「李」姓老师的数量
-
查询"01"课程比"02"课程成绩低的学生的信息及课程分数
-
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩和没成绩的)
-
查询学过「张三」老师授课的同学的信息
-
查询没有学全所有课程的同学的信息
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-
查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
-
检索" 01 "课程分数小于 60,按分数降序排列的学生信息
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-
查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
-
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-
查询各科成绩前三名的记录
-
查询每门课程被选修的学生数
-
查询出只选修两门课程的学生学号和姓名
-
查询男生、女生人数
-
查询名字中含有「风」字的学生信息
-
查询同名同性学生名单,并统计同名人数
-
查询 1990 年出生的学生名单
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-
查询不及格的课程
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-
求每门课程的学生人数
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)。
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
-
查询各学生的年龄,只按年份来算
-
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
-
查询本周过生日的学生
-
查询下周过生日的学生
-
查询本月过生日的学生
-
查询下月过生日的学生
答案
-
查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数\
SELECT stu.*,sco.score_01,score_02 FROM student stu RIGHT JOIN ( SELECT s1.s_score AS score_01,s2.s_score AS score_02, s1.s_id AS studentId FROM ( SELECT * FROM score WHERE c_id = '01' ) AS s1 LEFT JOIN ( SELECT * FROM score WHERE c_id = "02" ) AS s2 ON s1.s_score > s2.s_score WHERE s1.s_id = s2.s_id ) AS sco ON stu.s_id = sco.studentId #或者 SELECT a.*, b.s_score AS score_01, c.s_score AS score_02 FROM student a,score b,score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id = '01' AND c.c_id = '02' AND b.s_score > c.s_score
-
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#方法一 SELECT stu.s_id,stu.s_name,sco.avgScore FROM student AS stu,(SELECT s_id,AVG( s_score ) AS avgScore FROM score GROUP BY s_id HAVING AVG( s_score ) >= 60 ) AS sco WHERE stu.s_id = sco.s_id #方法二 SELECT stu.s_id,stu.s_name,sco.avgScore FROM student AS stu INNER JOIN ( SELECT AVG( s_score ) AS avgScore, s_id FROM score GROUP BY s_id HAVING AVG( s_score ) >= 60 ) AS sco ON sco.s_id = stu.s_id
-
查询在 成绩表存在成绩的学生信息
SELECT stu.* FROM student as stu ,score as sco WHERE stu.s_id = sco.s_id GROUP BY sco.s_id
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT stu.*, sco.sumScore as 总成绩 ,sco.count AS 选课总数 FROM student as stu LEFT JOIN ( SELECT s_id, SUM(s_score) AS sumScore,COUNT(s_id) as count FROM score GROUP BY s_id ) as sco on stu.s_id = sco.s_id
-
查询「李」姓老师的数量
SELECT COUNT(t_id) AS 数量 FROM teacher WHERE t_name like "%李%"
-
查询学过「张三」老师授课的同学的信息
# 思路 先查出名字为张三老师的t_id 然后再查出在课程表中查询该id老师教的课程,用in是因为一个老师可能存在同时教 #多门课的情况,通过拿到课程id去查成绩表,再通过选课得到学生id去查学生信息 #方法一 子查询 SELECT * FROM student WHERE s_id IN ( SELECT s_id FROM score WHERE c_id IN ( SELECT c_id FROM course WHERE t_id IN (select t_id FROM teacher WHERE t_name = "张三") ) ) #方法二 关联查询 SELECT stu.* FROM student as stu inner JOIN score as sco ON stu.s_id = sco.s_id inner JOIN course as cou ON sco.c_id = cou.c_id inner JOIN teacher as t ON t.t_id = cou.t_id AND t.t_name = "张三"
-
查询没有学全所有课程的同学的信息
#思路:先查询出课程的数量 再与score进行关联查询数量小于这些课程量的学生id 然后与学生关联id进行查询学生信息 SELECT stu.* FROM student AS stu right JOIN ( SELECT COUNT(c_id) AS coun,s_id FROM score GROUP BY s_id HAVING coun < (select count(c_id) FROM course ) ) AS a on a.s_id = stu.s_id
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
#先查出学号01同学所学的课程id, 用这个id进行in条件筛选选了这些课的同学的id 再进行去重 SELECT stu.* FROM student as stu RIGHT JOIN ( SELECT DISTINCT s_id FROM score WHERE c_id IN ( select c_id FROM score WHERE s_id = "01" ) ) AS a on a.s_id = stu.s_id
-
查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
#这里用到了一个非常有用的聚合函数 group_concat()这个函数可以把查出来的1 2 3 字段转成1,2,3这样的格式 #可以非常方便的解决一对多的问题 SELECT s.* FROM ( SELECT s_id, group_concat( c_id ) AS cids FROM Score GROUP BY s_id ) b LEFT JOIN Student s ON s.s_id = b.s_id WHERE b.cids = ( SELECT group_concat( c_id ) FROM Score WHERE s_id = '01' ) AND b.s_id != '01'
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
#查出所有的学过张三的课程的学生,再进行not in #方法一 SELECT * FROM student WHERE s_id NOT IN ( SELECT s_id FROM score WHERE c_id IN ( SELECT c_id FROM course WHERE t_id IN (select t_id FROM teacher WHERE t_name = "张三") ) ) #方法二 select * from student WHERE s_id not in ( SELECT stu.s_id FROM student as stu right JOIN score as sco ON stu.s_id = sco.s_id inner JOIN course as cou ON sco.c_id = cou.c_id inner JOIN teacher as t ON t.t_id = cou.t_id AND t.t_name = "张三" )
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
-
检索" 01 "课程分数小于 60,按分数降序排列的学生信息
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-
查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
-
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-
查询各科成绩前三名的记录
-
查询每门课程被选修的学生数
-
查询出只选修两门课程的学生学号和姓名
-
查询男生、女生人数
-
查询名字中含有「风」字的学生信息
-
查询同名同性学生名单,并统计同名人数
-
查询 1990 年出生的学生名单
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-
查询不及格的课程
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-
求每门课程的学生人数
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)。
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
-
查询各学生的年龄,只按年份来算
-
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
-
查询本周过生日的学生
-
查询下周过生日的学生
-
查询本月过生日的学生
-
查询下月过生日的学生