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

前言

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

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

✨一个值得尝试的AI变现小项目✨

一、建表

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变现小项目✨

#sql##数据库##sql面试题##java#
全部评论

相关推荐

11-23 14:51
大连理工大学 算法工程师
秋招的最后一问，关于海康威视
经历了漫长的4个月折磨，已经打算不再考虑工作的事情了，顺其自然就是最好的安排。sad，无实习真的太难了几个月来不断的到各个社交平台看池子的开奖情况，就像一只下水道的小老鼠看到井口的阳光一样幻想着自己出去后自由自在的样子。不过很可惜，目前仅达成了保底的成就，就如同走出了阴暗的水渠，但发现外面的怪物们可以一脚给我踩死，真正期待的oc可望而不可求。来这里最后问问友友们一个关于海康机器人的问题，之后就专心写论文了，是否能从池子泡出来也不再关心了。本人方向决策算法，9月末一面，十一过后三面完成，HR沟通还有一轮线下面试。10月20号开始，海康一些部门已经陆续开奖，而我也迟迟没有等到面试消息。本来...
海康威视求职进展汇总
点赞 评论 收藏
分享
11-19 16:05
中山大学 业务管理
点赞 评论 收藏
分享
09-26 19:08
烟台汽车工程职业学院 Python
金光纸业
xd回去要被开了吧
想润的芹菜人狠话不多：把其中一个老总放中间都会得罪另一个
点赞 评论 收藏
分享
头像
11-23 22:43
已编辑
电子科技大学 算法工程师
点赞 评论 收藏
分享
3 9 评论
分享

招聘动态

查看更多

字节跳动

2025校园招聘

阿里云管培生

2025届校园招聘

快手Star

2025届招聘

快手

销售类投递专区

全站热榜

正在热议
# 25届秋招总结 #
300551次浏览 2660人参与
# 如果不工作真的会快乐吗 #
59185次浏览 516人参与
# 阿里云管培生offer #
17272次浏览 294人参与
# 地方国企笔面经互助 #
3791次浏览 10人参与
# 美团求职进展汇总 #
1327208次浏览 12448人参与
# 选完offer后，你后悔学本专业吗 #
19919次浏览 144人参与
# 百度开奖 #
162238次浏览 974人参与
# 正在实习的你，几点下班 #
51839次浏览 389人参与
# 国央企薪资爆料 #
8295次浏览 67人参与
# 如何一边实习一边秋招 #
992085次浏览 12638人参与
# 提前批简历挂麻了怎么办 #
146417次浏览 1948人参与
# 学历or实习经历，哪个更重要 #
50995次浏览 402人参与
# 海康威视求职进展汇总 #
398779次浏览 3406人参与
# 米哈游求职进展汇总 #
175894次浏览 1458人参与
# 求职遇到的搞笑事件 #
70778次浏览 577人参与
# 投递实习岗位前的准备 #
1179167次浏览 18394人参与
# 面试体验感最好的是哪家？ #
85075次浏览 845人参与
# 实习生应该准时下班吗 #
167400次浏览 1159人参与
# 得物求职进展汇总 #
66224次浏览 682人参与
# 网申一定要掌握的小技巧 #
5326次浏览 53人参与
# 招聘要求与实际实习内容不符怎么办 #
10267次浏览 273人参与
# 0offer是寒冬太冷还是我太菜 #
898224次浏览 8012人参与
牛客网
牛客企业服务