30道SQL经典笔试题及其答案解析

前言

搭配该文章食用更佳：MySQL常用操作指令大全

欢迎在评论区对该文章进行勘误。

一、建表

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
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” 课程成绩高的学生的学号、姓名
2. 查询平均成绩大于60分的学生的学号、平均成绩
3. 查询各个学生的学号、姓名、选课数量、总成绩
4. 查询姓 “李” 的老师的个数
5. 查询没学过 “张三” 老师的课程的学生的学号、姓名
6. 查询学过 “01” 课程和 “02” 课程的学生的学号、姓名
7. 查询各个课程成绩均小于60分的学生的学号、姓名
8. 查询没有学全所有课程的学生的学号、姓名
9. 查询至少有一门课与学号为 “01” 的学生所学相同的学生的学号、姓名
10. 查询和学号为 “01” 的学生学习课程完全相同的其他学生的学号、姓名
11. 查询 “张三” 老师所教的课程的平均成绩
12. 查询没有学习过 “张三” 老师所教的任一门课程的学生姓名
13. 查询两门及其以上课程不及格的学生的学号，姓名、平均成绩
14. 查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率
15. 查询各个老师的职工号、所教课程的平均成绩，按所教课程的平均成绩降序排列
16. 查询各个课程中，成绩排名为第2名或第3名的学生的学号、课程号、课程成绩、成绩排名
17. 查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为 [85-100]、[70-85]、[60-70]、[0-60]
18. 查询各个学生的学号、平均成绩、平均成绩的排名
19. 查询各课程成绩前三名的课程号、学号、成绩排名、课程成绩
20. 查询所有学生中男生数量、女生数量
21. 查询姓名中含有 “风” 字的学生的学号、姓名
22. 查询同名同性的学生的姓名、性别、数量
23. 查询1990年出生的学生的学号、出生日期（注：Student 表中 sage 列的类型是 datetime）
24. 查询所有选修 “张三” 老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩
25. 查询选修了全部课程的学生的学号
26. 查询各个学生的学号、年龄
27. 查询在本周过生日的学生的学号
28. 查询在下周过生日的学生的学号
29. 查询在本月过生日的学生的学号
30. 查询在下月过生日的学生的学号

三、答案

1. 查询 “01” 课程比 “02” 课程成绩高的学生的学号、姓名

   select t4.sid   as sid,
          t4.sname as sname
   from (
            select distinct t1.sid as sid
            from (select * from sc where sc.cid = '01') as t1
                     left join (select * from sc where sc.cid = '02') as t2
                               on t1.sid = t2.sid
            where t1.score > t2.score
        ) as t3
            left join student as t4
                      on t3.sid = t4.sid;
   

2. 查询平均成绩大于60分的学生的学号、平均成绩

   select t1.sid        as sid,
          avg(t1.score) as avg_score
   from sc as t1
   group by t1.sid
   having avg_score > 60;
   

3. 查询各个学生的学号、姓名、选课数量、总成绩

   select t1.sid                 as sid,
          t1.sname               as sname,
          count(distinct t2.cid) as count_course,
          sum(t2.score)          as sum_score
   from student as t1
            left join sc as t2
                      on t1.sid = t2.sid
   group by t1.sid, t1.sname;
   

4. 查询姓 “李” 的老师的个数

   select count(distinct t1.tid) as tname_count
   from teacher as t1
   where t1.tname like '李%';
   

5. 查询没学过 “张三” 老师的课程的学生的学号、姓名

   select t1.sid   as sid,
          t1.sname as sname
   from student as t1
   where t1.sid not in (
       select t4.sid as sid
       from teacher as t2
                left join course as t3
                          on t2.tid = t3.tid
                left join sc as t4
                          on t3.cid = t4.cid
       where t2.tname = '张三'
   );
   

6. 查询学过 “01” 课程和 “02” 课程的学生的学号、姓名

   select t2.sid   as sid,
          t3.sname as sname
   from (
            select t1.sid as sid,
                   count(if(t1.cid = '01', t1.score, null)) as count1,
                   count(if(t1.cid = '02', t1.score, null)) as count2
            from sc as t1
            group by t1.sid
            having count1 > 0
               and count2 > 0
        ) as t2
            left join student as t3
                      on t2.sid = t3.sid;
   

7. 查询各个课程成绩均小于60分的学生的学号、姓名

   select t3.sid   as sid,
          t3.sname as sname
   from (
            select t1.sid        as sid,
                   max(t1.score) as max_score
            from sc as t1
            group by t1.sid
            having max_score < 60
        ) as t2
            left join student as t3
                      on t2.sid = t3.sid;
   

8. 查询没有学全所有课程的学生的学号、姓名

   select t4.sid   as sid,
          t4.sname as sname
   from (
            select t1.sid        as sid,
                   count(t1.cid) as count_cid
            from sc as t1
            group by t1.sid
            having count_cid < (
                select count(t2.cid) as count_cid
                from course as t2
            )
        ) as t3
            left join student as t4
                      on t3.sid = t4.sid;
   

9. 查询至少有一门课与学号为 “01” 的学生所学相同的学生的学号、姓名

   select t5.sid   as sid,
          t5.sname as sname
   from (
            select distinct t3.sid as sid
            from (
                     select t1.cid as cid
                     from sc as t1
                     where t1.sid = '01'
                 ) as t2
                     left join sc as t3
                               on t2.cid = t3.cid
        ) as t4
            left join student as t5
                      on t4.sid = t5.sid;
   

10. 查询和学号为 “01” 的学生学习课程完全相同的其他学生的学号、姓名

    select t5.sid   as sid,
           t5.sname as sname
    from (
             select t3.sid                 as sid,
                    count(distinct t3.cid) as count_cid
             from (
                      select t1.cid
                      from sc as t1
                      where t1.sid = '01'
                  ) as t2
                      left join sc as t3
                                on t2.cid = t3.cid
             group by t3.sid
             having count_cid = (select count(distinct sc.cid) from sc where sc.sid = '01')
         ) as t4
             left join student as t5
                       on t4.sid = t5.sid
    where t5.sid != '01';
    

11. 查询 “张三” 老师所教的课程的平均成绩

    select avg(t4.score) as avg_score
    from sc as t4
    where t4.cid = (
        select distinct t1.cid as cid
        from sc as t1
                 left join course as t2
                           on t1.cid = t2.cid
                 left join teacher as t3
                           on t2.tid = t3.tid
        where t3.tname = '张三'
    )
    group by t4.cid;
    

12. 查询没有学习过 “张三” 老师所教的任一门课程的学生姓名

    select t4.sname as sname
    from student as t4
    where t4.sid not in (
        select distinct t1.sid as sid
        from sc as t1
                 left join course as t2
                           on t1.cid = t2.cid
                 left join teacher as t3
                           on t2.tid = t3.tid
        where t3.tname = '张三'
    );
    

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

    select t3.sid       as sid,
           t3.sname     as sname,
           t2.avg_score as avg_score
    from (
             select t1.sid                           as sid,
                    avg(score)                       as avg_score,
                    count(if(score < 60, cid, null)) as count_score
             from sc as t1
             group by t1.sid
             having count_score >= 2
         ) as t2
             left join student as t3
                       on t2.sid = t3.sid;
    

14. 查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率

    select t2.cid       as cid,
           t3.cname     as cname,
           t2.max_score as max_score,
           t2.min_score as min_score,
           t2.avg_score as avg_score,
           t2.pass_rate as pass_rate
    from (
             select t1.cid                                                  as cid,
                    max(t1.score)                                           as max_score,
                    min(t1.score)                                           as min_score,
                    avg(t1.score)                                           as avg_score,
                    count(if(t1.score >= 60, t1.sid, null)) / count(t1.sid) as pass_rate
             from sc as t1
             group by t1.cid
         ) as t2
             left join course as t3
                       on t2.cid = t3.cid;
    

15. 查询各个老师的职工号、所教课程的平均成绩，按所教课程的平均成绩降序排列

    select t1.tid        as tid,
           avg(t2.score) as avg_score
    from course as t1
             left join sc as t2
                       on t1.cid = t2.cid
    group by t1.tid
    order by avg_score desc;
    

16. 查询各个课程中，成绩排名为第2名或第3名的学生的学号、课程号、课程成绩、成绩排名

    select t2.sid      as sid,
           t2.cid      as cid,
           t2.score    as score,
           t2.rank_num as rank_num
    from (
             select rank() over (partition by t1.cid order by t1.score desc) as rank_num,
                    t1.sid                                                   as sid,
                    t1.score                                                 as score,
                    t1.cid                                                   as cid
             from sc as t1
         ) as t2
    where t2.rank_num in (2, 3);
    

17. 查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为 [85-100]、[70-85]、[60-70]、[0-60]

    select t1.cid                                                               as cid,
           t2.cname                                                             as name,
           count(if(t1.score between 85 and 100, t1.sid, null)) / count(t1.sid) as '[85-100]_pct',
           count(if(t1.score between 70 and 85, t1.sid, null)) / count(t1.sid)  as '[70-85]_pct',
           count(if(t1.score between 60 and 70, t1.sid, null)) / count(t1.sid)  as '[60-70]_pct',
           count(if(t1.score between 0 and 60, t1.sid, null)) / count(t1.sid)   as '[0-60]_pct'
    from sc as t1
             left join course as t2
                       on t1.cid = t2.cid
    group by t1.cid, t2.cname;
    

18. 查询各个学生的学号、平均成绩、平均成绩的排名

    select t2.sid                                   as sid,
           t2.avg_score                             as avg_score,
           rank() over (order by t2.avg_score desc) as rank_avg_score
    from (
             select t1.sid        as sid,
                    avg(t1.score) as avg_score
             from sc as t1
             group by t1.sid
         ) as t2;
    

19. 查询各课程成绩前三名的课程号、学号、成绩排名、课程成绩

    select t2.cid        as cid,
           t2.sid        as sid,
           t2.rank_score as rank_score,
           t2.score      as score
    from (
             select t1.cid                                                as cid,
                    t1.sid                                                as sid,
                    rank() over (partition by t1.cid order by score desc) as rank_score,
                    t1.score                                              as score
             from sc as t1
         ) as t2
    where rank_score between 1 and 3;
    

20. 查询所有学生中男生数量、女生数量

    select t1.ssex                as ssex,
           count(distinct t1.sid) as count_sid
    from student as t1
    group by t1.ssex;
    

21. 查询姓名中含有 “风” 字的学生的学号、姓名

    select t1.sid   as sid,
           t1.sname as sname
    from student as t1
    where t1.sname like '%风%';
    

22. 查询同名同性的学生的姓名、性别、数量

    select t1.sname      as sname,
           t1.ssex       as ssex,
           count(t1.sid) as count_sid
    from student as t1
    group by t1.sname, t1.ssex
    having count_sid >= 2;
    

23. 查询1990年出生的学生的学号、出生日期（注：Student 表中 sage 列的类型是 datetime）

    select t1.sid as sid,
           t1.sage as sage
    from student as t1
    where year(t1.sage) = 1990;
    

24. 查询所有选修 “张三” 老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩

    select t1.sid   as sid,
           t1.score as score
    from sc as t1
             left join course as t2
                       on t1.cid = t2.cid
             left join teacher t3
                       on t2.tid = t3.tid
    where t3.tname = '张三'
    order by t1.score desc
    limit 1;
    

25. 查询选修了全部课程的学生的学号

    select t2.sid as sid
    from sc as t2
    group by t2.sid
    having count(t2.cid) = (
        select count(distinct t1.cid)
        from sc as t1
    );
    

26. 查询各个学生的学号、年龄

    select t1.sid                       as sid,
           year(curdate()) - year(sage) as sage
    from student as t1;
    

27. 查询在本周过生日的学生的学号

    select t1.sid as sid
    from student as t1
    where weekofyear(sage) = weekofyear(curdate());
    

28. 查询在下周过生日的学生的学号

    select t1.sid as sid
    from student as t1
    where weekofyear(t1.sage) = weekofyear(date_add(curdate(), interval 1 week));
    

29. 查询在本月过生日的学生的学号

    select t1.sid as sid
    from student as t1
    where month(t1.sage) = month(curdate());
    

30. 查询在下月过生日的学生的学号

    select t1.sid as sid
    from student as t1
    where month(t1.sage) = month(date_add(curdate(), interval 1 month));
    

写在最后：

一个值得尝试的 AI 赚钱小项目

扫描下方二维码或关注微信公众号：CodeFish 回复关键字 “电子书” 即可获取本文章相关书籍资料！

更多优质电子书资源持续更新中…