sql45题

作者: anti酱 | 来源:发表于2021-04-21 17:13 被阅读0次

    一、数据准备
    练习数据
    数据表
    --1.学生表 Student(SId,Sname,Sage,Ssex)
    SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
    --2.课程表 Course(CId,Cname,TId)
    CId 课程编号,Cname 课程名称,TId 教师编号
    --3.教师表 Teacher(TId,Tname)
    TId 教师编号,Tname 教师姓名
    --4.成绩表 SC(SId,CId,score)
    SId 学生编号,CId 课程编号,score 分数
    各表关联关系如下图所示:

    创建测试数据
    学生表 Student:
    导入数据方法:将以下 mysql 语句,完整复制到 workbench 语句窗口(或者是 mysql 的黑窗口),然后运行即可导入,不需要另外创建表,下面表的操作一样。这些语句第一条是创建表(create table),后面都是插入数据到表中(insert into table )。
    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-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('09' , '张三' , '2017-12-20' , '女');
    insert into Student values('10' , '李四' , '2017-12-25' , '女');
    insert into Student values('11' , '李四' , '2017-12-30' , '女');
    insert into Student values('12' , '赵六' , '2017-01-01' , '女');
    insert into Student values('13' , '孙七' , '2018-01-01' , '女');

    科目表 Course
    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');

    教师表 Teacher
    create table Teacher(TId varchar(10),Tname varchar(10));
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');

    成绩表 SC
    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 "课程成绩高的学生的信息及课程分数
    关键词:01课程、02课程
    审题:01课程比02课程成绩高,有两种情况,第一种,两个成绩都存在,第二种,01课程成绩存在,另一个不存在(为0)
    1.1查询同时存在" 01 "课程和" 02 "课程的情况

    易错:1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

    易错:1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

    2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    关键词:平均成绩、学生编号、学生姓名
    审题:涉及两张表,成绩表+学生表,运用子查询/join解决

    3.查询在 SC 表存在成绩的学生信息
    关键词:SC表存在成绩、学生信息
    审题: SC表存在成绩,等于找出sc中所有信息,学生信息存在于学生表,两表left join解决

    易错:4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
    审题:学生编号、学生姓名在学生表中可直接获取,但选课总数、所有课程总成绩在sc中需要分组统计,所以将后者统计后的结果与前表连接,而且要显示没成绩的,就要把学生表作为主体left join

    4.1 查有成绩的学生信息
    关键词:有成绩(sc表)、学生信息(学生表)
    审题:join/子查询

    5.查询「李」姓老师的数量
    关键词:姓李、数量
    审题:根据关键词,仅涉及老师表,使用count聚合计算数量即可

    6.查询学过「张三」老师授课的同学的信息
    关键词:张三老师、授课、同学
    审题:通过表关联关系知,本题涉及表过多,使用子查询也要用到join,因此最简单关系就是把所有的表join起来,再用张三老师限定条件即可。

    7.查询没有学全所有课程的同学的信息
    关键词:没有学全课程、同学信息

    8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    关键词:与01同学课程相同、至少一门课

    易错、难点:9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
    关键词:01同学课程、完全相同、其他同学信息
    审题:group_concat(列名 order by 列名2)——按列名2顺序把列名连接起来

    10.查询没学过"张三"老师讲授的任一门课程的学生姓名
    关键词:没学过、张三老师课程
    审题:先把学过的学生选出来,没学过就是not in

    11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    关键词:两门以上课程不及格、学号、姓名、平均成绩
    审题:先把不及格课程数和平均成绩求出来,筛选出不及格课程数大于2的同学然后作为新表与学生表合并

    12.检索" 01 "课程分数小于 60,按分数降序排列的学生信息
    关键词:01课程小于60、分数降序、学生信息
    审题:两表连接筛选条件即可

    13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    关键词:平均成绩从高到低、所有学生、所有课程成绩、平均成绩
    审题:学生、所有课程成绩信息在SC表中,而平均成绩需要统计求得,所以思路先分别求不需要groupby的量(学生编号、课程编号、成绩)和需要统计的量(平均成绩) ,在两者聚合起来查询

    14.查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程 ID,课程 name,最高分,最低
    分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀

    为:>=90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

    15-16.1 题思路:这几题思路基本一致,考察窗口函数的理解运用
    注意:
    重复时保留名次空缺——rank()值相同会跳过次序,如,1,1,3
    重复时合并名次/不保留名次空缺——dense_rank()值相同排名相同,后面不跳过次序,如1,1,2
    (重中之重)15.按各科成绩进行排序,并显示排名, Score 重复时继续排名

    (重中之重)15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次

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

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

    17.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
    关键词:100-85/85-70/70-60/60-0,课程编号、课程名称,百分比
    思路:case when结合聚合函数求数量
    易错点:范围是左闭右开区间

    (重中之重)18.查询各科成绩前三名的记录
    关键词:各科成绩、前三名
    思路:各科成绩前三名 = 按科目分组按成绩排序,窗口函数,前三名=求出窗口函数<4的数据即可

    19.查询每门课程被选修的学生数
    关键词:每门课程、选修学生数
    思路:每门课程=所有课程表里面有点课程,选修学生数=学生id的数量,两者left join即可得到

    20.查询出只选修两门课程的学生学号和姓名
    关键词:只选修两门课程、学生学号+姓名
    思路:使用group by + having把选修课程=2的学生筛选出来

    21.查询男生、女生人数
    关键词:男生、女生,人数
    思路:字段+聚合函数的group by语句

    22.查询名字中含有「风」字的学生信息
    关键词:名字中带有[风]、学生信息
    思路:使用like‘%_%'限定条件

    易错、难点:23.查询同名同性学生名单,并统计同名人数
    关键词:同名同性、同名人数
    思路:先求出每个名字、性别的人数,再找到人数大于1,性别、名字都相同的学生

    24.查询 1990 年出生的学生名单
    关键词:1990年出生、学生
    思路:时间转换函数+where条件语句限定即可

    25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    思路:每门课程平均成绩=avg+group by,按平均成绩降序排列+平均成绩相同,课程编号升序排列 = order by 平均成绩desc,课程编号

    26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    关键词:平均成绩>=85,学号/姓名/平均成绩
    思路:聚合函数+groupby

    27.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    关键词:课程名称为数学、分数低于60、学生姓名+分数
    思路:由关联关系知,这道题涉及三个表,使用inner join 更为方便,然后使用where字句限定两个条件即可

    28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    关键词:所有学生、课程、分数
    思路:所有学生=left join,课程+分数=sc表,即使用left join 连接学生表和sc表查询即可

    29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    关键词:任何一门课程成绩、70分以上、姓名+课程名称+分数
    思路:由表关系知该题涉及三个表,把学生表、成绩表、课程表连接起来即可求出每个同学每个课程的分数,再使用where字句从中筛选出成绩>70的数据即可

    30.查询不及格的课程
    关键词:不及格、课程
    思路:不及格=成绩<60,课程=课程名称/课程id,两表联立,where条件语句限定即可

    31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
    关键词:课程编号01、成绩80分以上、学号+姓名
    思路:学生表、课程表联立查询,where条件限定
    歧义:包不包含80,如果不包含没有学生满足要求

    32.求每门课程的学生人数
    关键词:每门课程、学生人数
    思路:每门课程=课程表里面所有课程,学生人数=学生id/学生姓名的去重人数,两表联立+聚合函数+group by

    33.假设成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    关键词:张三老师教的学生、成绩最高、学生信息+成绩
    思路:由于涉及到所有表,先把所有表联立查询,再用where限定条件,最后order by+limit确定最高成绩

    (重中之重)34.假设成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    关键词:成绩重复、张三老师教的学生、成绩最高、学生信息+ 成绩
    思路:成绩重复要使用dense_rank()进行排序,联立其他表查询学生信息、课程编号、成绩和排名,在第一步的基础上使用where语句筛选出排名为1的学生即可

    易错:35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    关键词:不同课程、成绩相同、学生编号+课程编号+学生成绩
    思路:联立成绩表多次,条件筛选出结果

    (重中之重)36.查询每门科目成绩最好的前两名
    关键词:每门科目成绩最好、前两名
    思路:首先按照科目分组成绩逆序排序,然后筛选出排序等于1和2的结果

    37.统计每门课程的学生选修人数(超过 5 人的课程才统计)
    关键词:每门课程、选修人数、超过5人
    思路:左连接课程表、成绩表+group by +having条件限定

    38.检索至少选修两门课程的学生学号
    关键词:至少选修两门、学生学号
    思路:联立学生表+成绩表,group by汇总+having筛选课程数>=2

    39.查询选修了全部课程的学生信息
    关键词:选修全部课程、学生信息
    思路:联立学生表、成绩表查询,group by 分组,having条件筛选课程数等于课程表的所有课程数

    40.查询各学生的年龄,只按年份来算
    关键词:各学生年龄、只按年份算
    思路:使用year算出年份,相减求年龄

    41.按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    关键词:按日期计算年龄
    思路:使用timestampdiff(year,出生日期,出生日期2)求年龄

    易错、难点:42.查询本周过生日的学生
    关键词:本周过生日、学生

    易错、难点:43.查询下周过生日的学生
    与42题思路相似

    44.查询本月过生日的学生
    关键词:本月过生日、学生
    思路:使用month提取生日月份,和现在月份相等就是本月过生日

    45.查询下月过生日的学生
    关键词:下月过生日
    思路:使用month提取生日月份,和现在月份+1相等就是下月过生日

    相关文章

      网友评论

          本文标题:sql45题

          本文链接:https://www.haomeiwen.com/subject/xpdnlltx.html