经典SQL50题

建库建表和插入数据

# 建库
create database practice_sql50;
use practice_sql50;

# 学生表
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('08' , '王菊' , '1990-01-20' , '女');
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' , '女');


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


# 教师表
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);

SQL50题

# 1 查询" 01 "课程比" 02 "课程成绩高的学生的学号及课程分数
select t1.SId, class1, class2
from (select SId, score as class1 from SC where CId = '01') as t1,
     (select SId, score as class2 from SC where CId = '02') as t2
where t1.SId = t2.SId
  and class1 > class2;

# 2 查询同时存在" 01 "课程和" 02 "课程的情况
select t1.*, t2.CId, t2.score
from (select * from SC where CId = '01') as t1,
     (select * from SC where CId = '02') as t2
where t1.SId = t2.SId;

# 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select t1.*, t2.CId, t2.score
from (select * from SC where CId = '01') as t1
         left join
         (select * from SC where CId = '02') as t2
         on t1.SId = t2.SId;

# 4 查询不存在" 01 "课程但存在" 02 "课程的情况
select *
from SC
where SC.SId not in
      (select SId
       from SC
       where SC.CId = '01')
  and SC.CId = '02';

# 5 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select SId,avg(score) as avg_score
from SC
group by SId;

# 6 查询在 SC 表存在成绩的学生信息
select *
from Student
where SId in (
    select SId
    from SC
    where score is not null);

# 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select Student.SId,
       Sname,
       CId_count,
       score_sum
from Student
         left join (
    select SId,
           count(CId) as CId_count,
           sum(score) as score_sum
    from SC
    group by SId) t1
                   on Student.SId = t1.SId;

# 8 查有成绩的学生信息
select *
from Student
where SId in (
    select SId
    from SC
    where score is not null);

# 9 查询「李」姓老师的数量
select count(*)
from Teacher
where Tname like '李%';

# 10 查询学过「张三」老师授课的同学的信息
select Student.*
from Student,
     Teacher,
     SC,
     Course
where Student.SId = SC.SId
  and SC.CId = Course.CId
  and Course.TId = Teacher.TId
  and Teacher.Tname = '张三';

# 11 查询没有学全所有课程的同学的信息
select *
from Student
where SId not in
      (select SId
       from SC
       group by SId
       having count(CId) = (select count(*) from Course));

# 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select SId
from SC
where CId in (
    select CId
    from SC
    where SId = '01')
  and SId <> '01'
group by SId;

# 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select *
from Student
where SId in (select SId # 查询所选课程数量和01学生一样的学生
              from SC
              where SId <> '01'
              group by SId
              having count(CId) = (select count(CId) from SC where SId = '01')
                 # 去掉所选课程中选了01学生没有选课程的人
                 and SId not in (select distinct SId from SC where CId not in (select CId from SC where SId = '01')));

# 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
select Sname
from Student
where SId not in (select SC.SId
                  from SC,
                       Course,
                       Teacher
                  where SC.CId = Course.CId
                    and Course.TId = Teacher.TId
                    and Tname = '张三');

# 15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.SId,
       Sname,
       avg(score) as avg_score
from Student stu,
     SC
where stu.SId = SC.SId
  and score < 60
group by stu.SId, Sname
having count(CId) >= 2;

# 16 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select stu.*, score
from Student stu,
     SC
where stu.SId = SC.SId
  and CId = '01'
  and score < 60
order by score desc;

# 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select SId,
       sum(case when CId = '01' then score else null end) as '01',
       sum(case when CId = '02' then score else null end) as '02',
       sum(case when CId = '03' then score else null end) as '03',
       avg(score)                                         as avg_score
from SC
group by SId
order by avg_score desc;

/*18 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
 */
select SC.CId,
       Cname,
       max(score)                                                             as max_score,
       min(score)                                                             as min_score,
       avg(score)                                                             as avg_score,
       sum(case when score >= 60 then 1 else 0 end) / count(*)                as '及格率',
       sum(case when score >= 70 and score < 80 then 1 else 0 end) / count(*) as '中等率',
       sum(case when score >= 80 and score < 90 then 1 else 0 end) / count(*) as '优良率',
       sum(case when score >= 90 then 1 else 0 end) / count(*)                as '优秀率'
from SC,
     Course
where SC.CId = Course.CId
group by SC.CId, Cname
order by count(*) desc, CId;

# 19 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select t1.CId,
       t1.SId,
       t1.score,
       count(t2.score) + 1 as `rank`
from SC t1
         left join SC t2
                   on t1.score < t2.score and t1.CId = t2.CId
group by t1.CId, t1.SId, t1.score
order by t1.CId, `rank`;

# 20 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select SId,
       sum(score),
       rank() over (order by sum(score) desc ) as `rank`
from SC
group by SId;

# 21 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select SC.CId,
       C.Cname,
       sum(case when score between 85 and 100 then 1 else 0 end)            as '[100-85]人数',
       sum(case when score between 70 and 85 then 1 else 0 end)             as '[85-70]人数',
       sum(case when score between 60 and 70 then 1 else 0 end)             as '[70-60]人数',
       sum(case when score between 0 and 60 then 1 else 0 end)              as '[60-0]人数',
       sum(case when score between 85 and 100 then 1 else 0 end) / count(*) as '[100-85]人数所占百分比',
       sum(case when score between 70 and 85 then 1 else 0 end) / count(*)  as '[85-70]人数所占百分比',
       sum(case when score between 60 and 70 then 1 else 0 end) / count(*)  as '[70-60]人数所占百分比',
       sum(case when score between 0 and 60 then 1 else 0 end) / count(*)   as '[60-0]人数所占百分比'
from SC
         left join Course C
                   on SC.CId = C.CId
group by SC.CId, C.Cname;

# 22 查询各科成绩前三名的记录
select *
from (
         select CId,
                SId,
                score,
                dense_rank() over (partition by CId order by score desc ) as `rank`
         from SC) t1
where t1.`rank` <= 3;

# 23 查询每门课程被选修的学生数
select CId,
       count(SId) as '选修的学生数'
from SC
group by CId;

# 24 查询出只选修两门课程的学生学号和姓名
select SId,
       Sname
from Student
where SId in (
    select SId
    from SC
    group by SId
    having count(CId) = 2);

# 25 查询男生、女生人数
select Ssex,
       count(*) '人数'
from Student
group by Ssex;

# 26 查询名字中含有「风」字的学生信息
select *
from Student
where Sname like '%风%';

# 27 查询同名同姓学生名单，并统计同名人数
select Sname,
       count(SId) - 1 as '同名人数'
from Student
group by Sname;

# 28 查询 1990 年出生的学生名单
select *
from Student
where year(Sage)='1990';

# 29 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程编号降序排列
select CId,
       avg(score) as avg_score
from SC
group by CId
order by avg_score,
         CId desc;

# 30 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select stu.SId,
       stu.Sname,
       avg(score) as avg_score
from Student stu
         left join SC
                   on stu.SId = SC.SId
group by stu.SId, stu.Sname
having avg_score >= 85;

# 31 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select Sname,
       score
from Student stu,
     SC,
     Course C
where stu.SId = SC.SId
  and SC.CId = C.CId
  and Cname = '数学'
  and score < 60;

# 32 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select SId,
       sum(case when Cname = '语文' then score else null end) as '语文成绩',
       sum(case when Cname = '数学' then score else null end) as '数学成绩',
       sum(case when Cname = '英语' then score else null end) as '英语成绩'
from SC
         join Course C
              on C.CId = SC.CId
group by SId;

# 33 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Sname,
       Cname,
       score
from Student stu
         left join SC
                   on stu.SId = SC.SId
         left join Course C
                   on SC.CId = C.CId
where score >= 70;

# 34 查询不及格的课程并按课程号从大到小排列
select SC.CId,
       Cname,
       score
from SC
         inner join Course C
                    on SC.CId = C.CId
where score < 60
order by SC.CId desc;

# 35 查询课程编号为 03 且课程成绩在 80 分以上的学生的学号和姓名
select stu.SId,
       Sname
from Student stu
         left join SC
                   on stu.SId = SC.SId
where CId = '03'
  and score > 80;

# 36 求每门课程的学生人数
select CId,
       count(SId) as '学生人数'
from SC
group by CId;

# 37 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.SId,
       Sname,
       score
from Student stu,
     SC,
     Course C,
     Teacher T
where stu.SId = SC.SId
  and SC.CId = C.CId
  and C.TId = T.TId
  and Tname = '张三'
order by score desc
limit 1;

# 38 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.*,
       score
from Student stu
left join (
    select *,dense_rank() over (order by score desc ) `dense_rank` from SC
    where CId in (select CId from Course where TId in (select TId from Teacher where Tname = '张三'))) t1
on stu.SId = t1.SId
where t1.`dense_rank` = 1;

# 39 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.SId,
       a.CId,
       b.CId,
       a.score,
       b.score
from SC a,
     SC b
where a.SId = b.SId
  and a.score = b.score
  and a.CId <> b.CId;

# 40 查询每门功课成绩最好的前两名
select *
from (
         select *,
                dense_rank() over (partition by CId order by score desc ) as `dense_rank`
         from SC) t1
where t1.`dense_rank` in (1, 2);

# 41 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select *
from (
         select CId,
                count(SId) as num
         from SC
         group by CId) t1
where t1.num >= 5;

# 42 检索至少选修两门课程的学生学号
select *
from (
         select SId,
                count(CId) as num
         from SC
         group by SId) t1
where t1.num >= 2;

# 43 查询选修了全部课程的学生信息
select stu.*
from Student stu
left join (select SId,count(CId) as num from SC group by SId) t1
on stu.SId = t1.SId
where t1.num = (select count(CId) from Course);

# 44 查询各学生的年龄，只按年份来算
select SId,
       Sname,
       (year(now()) - year(Sage)) as '年龄'
from Student;

# 45 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
/*
 TIMESTAMPDIFF函数：有参数设置，可以精确到年（YEAR）、天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），
 使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。
datediff函数：返回值是相差的天数，不能定位到小时、分钟和秒。
 */
select SId,
       Sname,
       timestampdiff(year, Sage, now()) as '年龄'
from Student;

# 46 查询本周过生日的学生
/*
week(时间)默认从0开始，并却星期天默认为第一天，国外的算法
week(时间，1)从1开始，并却星期一为第一天，国内算法
 */
select SId,
       Sname
from Student
where week(Sage)=week(now(),1);

# 47 查询下周过生日的学生
select SId,
       Sname
from Student
where week(Sage)=week(now(),1)+1;

# 48 查询本月过生日的学生
select SId,
       Sname
from Student
where month(Sage)=month(now());

# 49 查询下月过生日的学生
select SId,
       Sname
from Student
where month(Sage)=month(now())+1;

# 50 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select *
from (
         select t1.SId, class1, class2
         from (select SId, score as class1 from SC where CId = '01') as t1,
              (select SId, score as class2 from SC where CId = '02') as t2
         where t1.SId = t2.SId
           and class1 > class2) as t
         left join Student
                   on t.SId = Student.SId;