经典五十道SQL题练习 持续更新

前言:这些是网上广为流传的经典50道SQL题合集 最近正好想提升一下自己的SQL能力,所以将这些SQL题开个博客,慢慢更新,当作一个记录帖吧,记录一下自己的成长

建表语句

为了不影响文章的排版和整体美观，我将建表语句单独抽出来了，可以访问： 经典五十题SQL建表语句 获取建表语句

题目

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

3. 查询在 SC 表存在成绩的学生信息

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

5. 查询「李」姓老师的数量

6. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

7. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩和没成绩的)

8. 查询学过「张三」老师授课的同学的信息

9. 查询没有学全所有课程的同学的信息

10. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

11. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

12. 查询没学过"张三"老师讲授的任一门课程的学生姓名

13. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

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

15. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

16. 查询各科成绩最高分、最低分和平均分：

    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

17. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

18. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

19. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

20. 查询各科成绩前三名的记录

21. 查询每门课程被选修的学生数

22. 查询出只选修两门课程的学生学号和姓名

23. 查询男生、女生人数

24. 查询名字中含有「风」字的学生信息

25. 查询同名同性学生名单，并统计同名人数

26. 查询 1990 年出生的学生名单

27. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

28. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

29. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

30. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

31. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

32. 查询不及格的课程

33. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

34. 求每门课程的学生人数

35. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

36. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

37. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

38. 查询每门功成绩最好的前两名

39. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

40. 检索至少选修两门课程的学生学号

41. 查询选修了全部课程的学生信息

42. 查询各学生的年龄，只按年份来算

43. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

44. 查询本周过生日的学生

45. 查询下周过生日的学生

46. 查询本月过生日的学生

48. 查询下月过生日的学生

答案

1. 查询" 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
   

2. 查询平均成绩大于等于 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
   

3. 查询在 成绩表存在成绩的学生信息

   SELECT stu.* FROM student as stu ,score as sco   WHERE stu.s_id = sco.s_id  GROUP BY sco.s_id
   

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 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 
   

5. 查询「李」姓老师的数量

   SELECT  COUNT(t_id) AS 数量  FROM teacher WHERE t_name like "%李%"
   

6. 查询学过「张三」老师授课的同学的信息

   # 思路 先查出名字为张三老师的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 = "张三"
   

7. 查询没有学全所有课程的同学的信息

   #思路:先查询出课程的数量 再与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
   
   

8. 查询至少有一门课与学号为" 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
   

9. 查询和" 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'
   

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

    #查出所有的学过张三的课程的学生，再进行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 = "张三"
    )
    
    

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

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

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

14. 查询各科成绩最高分、最低分和平均分：

    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

18. 查询各科成绩前三名的记录

19. 查询每门课程被选修的学生数

20. 查询出只选修两门课程的学生学号和姓名

21. 查询男生、女生人数

22. 查询名字中含有「风」字的学生信息

23. 查询同名同性学生名单，并统计同名人数

24. 查询 1990 年出生的学生名单

25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

30. 查询不及格的课程

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

32. 求每门课程的学生人数

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

36. 查询每门功成绩最好的前两名

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

38. 检索至少选修两门课程的学生学号

39. 查询选修了全部课程的学生信息

40. 查询各学生的年龄，只按年份来算

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

42. 查询本周过生日的学生

43. 查询下周过生日的学生

44. 查询本月过生日的学生

46. 查询下月过生日的学生