50道经典SQL练习题及答案

发表于 | 分类于 |

50道经典SQL练习题

数据表介绍

  1. 学生表
    Student(SId,Sname,Sage,Ssex)
    –SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-12-20' , '男');
    insert into Student values('04' , '李云' , '1990-12-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
    insert into Student values('09' , '张三' , '2017-12-20' , '女');
    insert into Student values('10' , '李四' , '2017-12-25' , '女');
    insert into Student values('11' , '李四' , '2012-06-06' , '女');
    insert into Student values('12' , '赵六' , '2013-06-13' , '女');
    insert into Student values('13' , '孙七' , '2014-06-01' , '女');

  2. 课程表
    Course(CId,Cname,TId)
    –CId 课程编号,Cname 课程名称,TId 教师编号

    1
    2
    3
    4
    create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');

  3. 教师表
    Teacher(TId,Tname)
    –TId 教师编号,Tname 教师姓名

    1
    2
    3
    4
    create table Teacher(TId varchar(10),Tname varchar(10));
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');

  4. 成绩表
    SC(SId,CId,score)
    –SId 学生编号,CId 课程编号,score 分数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
    insert into SC values('01' , '01' , 80);
    insert into SC values('01' , '02' , 90);
    insert into SC values('01' , '03' , 99);
    insert into SC values('02' , '01' , 70);
    insert into SC values('02' , '02' , 60);
    insert into SC values('02' , '03' , 80);
    insert into SC values('03' , '01' , 80);
    insert into SC values('03' , '02' , 80);
    insert into SC values('03' , '03' , 80);
    insert into SC values('04' , '01' , 50);
    insert into SC values('04' , '02' , 30);
    insert into SC values('04' , '03' , 20);
    insert into SC values('05' , '01' , 76);
    insert into SC values('05' , '02' , 87);
    insert into SC values('06' , '01' , 31);
    insert into SC values('06' , '03' , 34);
    insert into SC values('07' , '02' , 89);
    insert into SC values('07' , '03' , 98);

题目

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT tb1.SId AS StudentID, 
           tb3.Sname AS 'name', 
           tb3.Ssex AS Sex, 
           tb1.score AS score1, 
           tb2.score AS score2
    FROM (SELECT SId, score FROM SC WHERE SC.CId = '01') AS tb1
    INNER JOIN (SELECT SId, score FROM SC WHERE SC.CId = '02') AS tb2
    ON tb1.SId = tb2.SId
    INNER JOIN Student AS tb3
    ON tb1.SId = tb3.SId
    WHERE tb1.score > tb2.score;

    1.1 查询同时存在” 01 “课程和” 02 “课程的情况

    1
    2
    3
    SELECT *
    FROM SC AS tb1, SC AS tb2
    WHERE tb1.SId = tb2.SId AND tb1.CId = '01' AND tb2.CId = '02';

    1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

    1
    2
    3
    4
    5
    SELECT tb1.SId AS StudentID, tb1.score AS score1, tb2.score AS score2
    FROM (SELECT * FROM SC WHERE SC.CId = '01') AS tb1
         LEFT OUTER JOIN
         (SELECT * FROM SC WHERE SC.CId = '02') AS tb2
         ON tb1.SId = tb2.SId;

    1.3 查询不存在” 01 “课程但存在” 02 “课程的情况

    1
    2
    3
    4
    SELECT tb1.SId AS StudentID, tb1.CId AS course, tb1.score AS Score
    FROM SC AS tb1
    WHERE tb1.SId NOT IN (SELECT tb2.SId FROM SC AS tb2 WHERE tb2.CId = '01')
          AND tb1.CId <> '01';

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

    1
    2
    3
    4
    5
    6
    7
    SELECT tb1.SId AS StudentID, tb1.Sname AS StudentName, tb2.AverageScore
    FROM Student AS tb1
    INNER JOIN (SELECT SC.SId, AVG(SC.score) AS AverageScore
                FROM SC
                GROUP BY SC.SId
                HAVING AVG(SC.score) >= 60) AS tb2
    ON tb1.SId = tb2.SId;

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

    1
    2
    3
    4
    SELECT tb1.*
    FROM Student AS tb1
    WHERE tb1.SId IN (SELECT DISTINCT tb2.SId 
                      FROM SC AS tb2);

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

    1
    2
    3
    4
    5
    6
    SELECT tb1.SId AS StudentID, tb1.Sname AS StudentName, tb2.CourseNum, tb2.TotalScore
    FROM Student AS tb1 
    LEFT OUTER JOIN (SELECT SId, COUNT(*) AS CourseNum, SUM(score) AS TotalScore
                     FROM SC 
                     GROUP BY SId) AS tb2
    ON tb1.SId = tb2.SId;

    4.1 查有成绩的学生信息

    1
    2
    3
    4
    5
    6
    SELECT tb1.SId AS StudentID, tb1.Sname AS StudentName, tb2.CourseNum, tb2.TotalScore
    FROM Student AS tb1 
    RIGHT OUTER JOIN (SELECT SId, COUNT(*) AS CourseNum, SUM(score) AS TotalScore
                     FROM SC 
                     GROUP BY SId) AS tb2
    ON tb1.SId = tb2.SId;

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

    1
    2
    3
    SELECT COUNT(*) AS TeacherNumber
    FROM Teacher
    WHERE Tname LIKE '李%';

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT tb4.*
    FROM Teacher AS tb1, 
         Course AS tb2,
         SC AS tb3,
         Student AS tb4
    WHERE tb1.Tname = '张三' 
          AND tb1.TId = tb2.TId 
          AND tb2.CId = tb3.CId
          AND tb3.SId = tb4.SId;

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

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT *
    FROM Student AS tb1
    WHERE tb1.SId NOT IN (SELECT SC.SId
                          FROM SC
                          GROUP BY SC.SId
                          HAVING COUNT(*) = (SELECT COUNT(*)
                                             FROM SC
                                             WHERE SC.SId = '01'));

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

    1
    2
    3
    4
    5
    6
    7
    SELECT DISTINCT tb1.*
    FROM Student AS tb1 LEFT OUTER JOIN SC AS tb2
    ON tb1.SId = tb2.SId
    WHERE tb2.CId IN (SELECT tb4.CId
                      FROM SC AS tb4
                      WHERE tb4.SId = '01') 
          AND tb2.SId <> '01';

  9. 查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT *
    FROM Student AS tb1
    WHERE tb1.SId NOT IN (SELECT tb2.SId    -- 学了01所学课程以外的人
                          FROM SC AS tb2
                          WHERE tb2.CId NOT IN (SELECT CId   -- 01学的课程
                                                FROM SC
                                                WHERE SC.SId = '01'))
          AND tb1.SId IN (SELECT tb3.SId
                          FROM SC AS tb3
                          GROUP BY tb3.SId
                          HAVING COUNT(*) = (SELECT COUNT(*)
                                             FROM SC 
                                             WHERE SC.SId = '01'))
          AND tb1.SId <> '01';

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT *
    FROM Student AS tb1
    WHERE tb1.SId NOT IN (SELECT DISTINCT SId
                          FROM SC
                          WHERE CId = (SELECT CId
                                       FROM Course
                                       WHERE TId = (SELECT TId 
                                                    FROM Teacher
                                                    WHERE Tname = '张三')));

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

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT tb1.SId, tb2.Sname, AverageScore
    FROM (SELECT SId, AVG(score) AS AverageScore
          FROM SC
          WHERE score < 60 
          GROUP BY SId
          HAVING COUNT(*) >= 2) AS tb1
    LEFT OUTER JOIN Student AS tb2  
    ON tb1.SId = tb2.SId;

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

    1
    2
    3
    4
    5
    6
    7
    SELECT tb2.*, tb1.score
    FROM (SELECT SId, score
          FROM SC
          WHERE score < 60 AND CId = '01') AS tb1
    LEFT OUTER JOIN Student AS tb2
    ON tb1.SId = tb2.SId
    ORDER BY tb1.score DESC;

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT tb1.SId AS StudentID, tb1.Sname AS 'Name', tb3.CId AS 'Course', tb3.score AS 'Score', AverageScore
    FROM Student AS tb1
    LEFT OUTER JOIN (SELECT SId, AVG(score) AS AverageScore
                     FROM SC
                     GROUP BY SId) AS tb2
    ON tb1.SId = tb2.SId
    LEFT OUTER JOIN SC AS tb3
    ON tb1.SId = tb3.SId
    ORDER BY AverageScore DESC, tb3.CId;

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

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT 
        tb1.CId AS CourseID,
        tb2.Cname AS Course,
        MAX(tb1.score) AS MaxScore,
        MIN(tb1.score) AS MinScore,
        AVG(tb1.score) AS AvgScore,
        COUNT(*) AS StudentNumber,
        SUM(CASE WHEN tb1.score>60 THEN 1 ELSE 0 END)/COUNT(*) AS PassRate
    FROM SC AS tb1
    LEFT OUTER JOIN Course AS tb2 
    ON tb1.CId = tb2.CId
    GROUP BY tb1.CId, tb2.Cname
    ORDER BY StudentNumber DESC, CourseID;

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

    1
    2
    3
    4
    -- MySQL 8.X开始支持窗口函数
    SELECT CId AS Course, score, RANK() OVER (ORDER BY score DESC) AS Ranking
    FROM SC
    ORDER BY Ranking;
    1
    2
    3
    4
    5
    6
    -- 不使用窗口函数
    SELECT tb1.CId AS Course, score, (SELECT COUNT(tb2.Score)
                                          FROM SC AS tb2
                                          WHERE tb2.score > tb1.score) + 1 AS Ranking
    FROM SC AS tb1
    ORDER BY Ranking;

    15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

    1
    2
    3
    SELECT CId AS Course, score, DENSE_RANK() OVER (ORDER BY score DESC) AS Ranking
    FROM SC
    ORDER BY Ranking;
    1
    2
    3
    4
    5
    SELECT tb1.CId AS Course, score, (SELECT COUNT(DISTINCT tb2.Score)
                                          FROM SC AS tb2
                                          WHERE tb2.score > tb1.score) + 1 AS Ranking
    FROM SC AS tb1
    ORDER BY Ranking;

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

    1
    2
    3
    4
    SELECT SId AS StudentID, SUM(score) AS ScoreSum, RANK() OVER (ORDER BY SUM(score) DESC) AS Ranking
    FROM SC
    GROUP BY SId
    ORDER BY ScoreSum DESC;

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

    1
    2
    3
    4
    SELECT SId AS StudentID, SUM(score) AS ScoreSum, DENSE_RANK() OVER (ORDER BY SUM(score) DESC) AS Ranking
    FROM SC
    GROUP BY SId
    ORDER BY ScoreSum DESC;

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT tb1.CId AS CourseID, 
           tb2.Cname AS Course,
           SUM(CASE WHEN tb1.score > 85 AND tb1.score <= 100 THEN 1 ELSE 0 END)/COUNT(*) AS '[85-100]',
           SUM(CASE WHEN tb1.score > 70 AND tb1.score <= 85 THEN 1 ELSE 0 END)/COUNT(*) AS '[70-85]',
           SUM(CASE WHEN tb1.score > 60 AND tb1.score <= 70 THEN 1 ELSE 0 END)/COUNT(*) AS '[60-70]',
           SUM(CASE WHEN tb1.score > 0 AND tb1.score <= 60 THEN 1 ELSE 0 END)/COUNT(*) AS '[0-60]'
    FROM SC AS tb1
    LEFT OUTER JOIN Course AS tb2
    ON tb1.CId = tb2.CId
    GROUP BY tb1.CId, tb2.Cname;

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

    1
    2
    3
    4
    5
    6
    -- 这道题用到了窗口函数比较全的写法，MySQL在8.X版本中支持窗口函数
    SELECT CId, SId, tb1.Ranking
    FROM (SELECT CId, SId, RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS Ranking
          FROM SC) AS tb1
    WHERE Ranking <= 3
    ORDER BY CId;

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

    1
    2
    3
    SELECT CId AS 'Course', COUNT(*) AS 'Number of Student'
    FROM SC 
    GROUP BY CId;

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

    1
    2
    3
    4
    5
    6
    SELECT SId, Sname
    FROM Student
    WHERE SId IN (SELECT SId
                  FROM SC
                  GROUP BY SId
                  HAVING COUNT(*) = 2);

  21. 查询男生、女生人数

    1
    2
    3
    SELECT Ssex AS StudentSex, COUNT(*) AS 'Student Number'
    FROM Student
    GROUP BY Ssex;

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

    1
    2
    3
    SELECT * 
    FROM Student
    WHERE Sname like '%风%';

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

    1
    2
    3
    4
    SELECT Sname AS 'Name', Ssex AS 'Sex', COUNT(*) AS 'Number of Student'
    FROM Student
    GROUP BY Ssex, Sname 
    HAVING COUNT(*) >= 2;

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

    1
    2
    3
    SELECT SId, Sname, Sage
    FROM Student
    WHERE YEAR(Sage) = 1990;

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

    1
    2
    3
    4
    SELECT CId AS Course, AVG(score) AS AverageScore
    FROM SC
    GROUP BY CId 
    ORDER BY AverageScore DESC, CId;

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

    1
    2
    3
    4
    5
    6
    7
    SELECT tb1.SId AS StudentID, tb2.Sname AS 'Name', AverangeScore
    FROM (SELECT SId, AVG(score) AS AverangeScore
          FROM SC
          GROUP BY SId
          HAVING AVG(score) >= 85) AS tb1
    LEFT OUTER JOIN Student AS tb2
    ON tb1.SId = tb2.SId;

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT tb2.Sname AS 'Name', tb1.score AS Score
    FROM (SELECT SId, score
          FROM SC
          WHERE score < 60 AND
                CId = (SELECT CId
                       FROM Course
                       WHERE Cname = '数学')) AS tb1
    LEFT OUTER JOIN Student AS tb2
    ON tb1.SId = tb2.SId;

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

    1
    2
    3
    4
    5
    6
    7
    SELECT tb1.SId AS StudentID, tb1.Sname AS 'Name', tb2.CId AS CourseID, tb3.Cname AS Course, tb2.score AS Score
    FROM Student AS tb1
    LEFT OUTER JOIN SC AS tb2
    ON tb1.SId = tb2.SId
    LEFT OUTER JOIN Course AS tb3
    ON tb2.CId = tb3.CId
    ORDER BY tb1.SId;

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

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT tb1.Sname AS 'Name', tb3.Cname AS 'Course', tb2.score AS Score
    FROM Student AS tb1
    RIGHT OUTER JOIN SC AS tb2
    ON tb1.SId = tb2.SId
    LEFT OUTER JOIN Course AS tb3
    ON tb2.CId = tb3.CId
    WHERE tb2.score > 70
    ORDER BY tb1.SId;

  30. 查询不及格的课程

    1
    2
    3
    4
    -- 题目有歧义，对付看吧
    SELECT SId, CId, score
    FROM SC
    WHERE SC.score < 60;

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

    1
    2
    3
    4
    5
    SELECT SId AS StudentID, Sname AS Name
    FROM Student 
    WHERE SId IN (SELECT SId
                  FROM SC
                  WHERE CId = '01' AND score >= 80);

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

    1
    2
    3
    SELECT CId AS CouresID, COUNT(*) AS 'number of students'
    FROM SC
    GROUP BY CId;

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT tb4.*, tb1.score
    FROM SC AS tb1
    LEFT OUTER JOIN Course AS tb2
    ON tb1.CId = tb2.CId
    LEFT OUTER JOIN Teacher AS tb3
    ON tb2.TId = tb3.TId
    LEFT OUTER JOIN Student AS tb4
    ON tb1.SId = tb4.SId
    WHERE tb3.Tname = '张三'
    ORDER BY tb1.score DESC
    LIMIT 1;

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT tb1.*, tb4.score AS Score, tb4.CId AS CourseID
    FROM Student AS tb1, Teacher AS tb2, Course AS tb3, SC AS tb4
    WHERE tb2.TId = tb3.TId
          AND tb3.CId = tb4.CId
          AND tb4.SId = tb1.SId
          AND tb2.Tname = '张三'
          AND tb4.score = (SELECT MAX(SC.score)
                           FROM Student, Teacher, Course, SC
                           WHERE Teacher.TId = Course.TId
                           AND Course.CId = SC.CId
                           AND SC.SId = Student.SId
                           AND Teacher.Tname = '张三');

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

    1
    2
    3
    4
    5
    SELECT DISTINCT tb1.SId AS StudentID, tb1.CId AS CourseID, tb1.score AS Score
    FROM SC AS tb1, SC AS tb2
    WHERE tb1.SId = tb2.SId
      AND tb1.CId <> tb2.CId
      AND tb1.score = tb2.score;

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

    1
    2
    3
    4
    5
    -- 使用窗口函数
    SELECT CId AS CourseID, SId AS StudentID, Ranking
    FROM (SELECT CId, SId, RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS Ranking
          FROM SC) AS tb1
    WHERE Ranking <= 2;
    1
    2
    3
    4
    5
    6
    -- 不使用窗口函数
    SELECT tb1.SId, tb1.CId, tb1.score
    FROM SC AS tb1
    WHERE (SELECT COUNT(tb2.score)
           FROM SC AS tb2
           WHERE tb1.CId = tb2.CId AND tb2.score > tb1.score) <= 1;

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

    1
    2
    3
    4
    SELECT CId AS CourseID, COUNT(*) AS NumberofStudent
    FROM SC AS tb1
    GROUP BY CId
    HAVING COUNT(*) >= 5;

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

    1
    2
    3
    4
    SELECT SId AS StudentID, COUNT(*) AS NumberofCourses
    FROM SC 
    GROUP BY SId
    HAVING COUNT(*) >= 2;

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

    1
    2
    3
    4
    5
    6
    7
    SELECT tb1.*
    FROM Student AS tb1
    WHERE tb1.SId IN (SELECT Student.SId
                      FROM Student, SC
                      WHERE SC.SId = Student.SId
                      GROUP BY SC.SId
                      HAVING COUNT(*) = (SELECT DISTINCT COUNT(*) FROM Course));

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

    1
    2
    SELECT SId AS StudentID, Sname AS 'Name', YEAR(CURDATE())-YEAR(Sage) AS 'Age'
    FROM Student;

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

    1
    2
    SELECT SId AS StudentID, Sname AS 'Name', TIMESTAMPDIFF(YEAR, Sage, CURDATE()) AS 'Age'
    FROM Student;

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

    1
    2
    3
    SELECT *
    FROM Student
    WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURDATE());

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

    1
    2
    3
    SELECT *
    FROM Student
    WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURDATE()) + 1;

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

    1
    2
    3
    SELECT *
    FROM Student
    WHERE MONTH(Sage) = MONTH(CURDATE());

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

    1
    2
    3
    SELECT *
    FROM Student
    WHERE MONTH(Sage) = MONTH(CURDATE()) + 1;