SQL经典45题 - （11~20）

表字段如图所示：

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

# 中间表
select distinct student.Sname
from student inner join sc s on student.SId = s.SId
inner join course c on s.CId = c.CId
inner join teacher t on c.TId = t.Tid
where t.Tname = '张三';

select distinct student.Sname
from student inner join sc s2 on student.SId = s2.SId
where student.Sname not in (
    select student.Sname
    from student inner join sc s on student.SId = s.SId
    inner join course c on s.CId = c.CId
    inner join teacher t on c.TId = t.Tid
    where t.Tname = '张三'
    );

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

select student.SId, student.Sname, avg(s.score)
from student inner join sc s on student.SId = s.SId
where s.score<60
having count(s.score)>2;

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

select student.*, score
from student inner join sc s on student.SId = s.SId
where CId='01' and score<60
order by score desc;

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

select sc.*, t.meanScore
from sc inner join (
    select sc.SId, avg(sc.score) meanScore
    from sc
    group by sc.SId
    ) t on sc.SId=t.SId;

14.查询各科成绩最高分、最低分和平均分,以如下形式显示：

select
       c.CId as '课程ID',
       c.Cname as '课程 name',
       count(sc.SId) as '选修人数',
       max(score) as '最高分',
       min(score) as '最低分',
       avg(score) as '平均分',
       sum(case when score>=60 then 1 else 0 end) / count(score) as '及格率',
       sum(case when score>=70 and score<80 then 1 else 0 end) / count(score) as '中等率',
       sum(case when score>=80 and score<90 then 1 else 0 end) / count(score) as '优良率',
       sum(case when score>=90 then 1 else 0 end) / count(score) as '优秀率'
from
     sc inner join course c on sc.CId = c.CId
group by
     c.CId
order by count(sc.SId) desc, c.CId asc;

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

select *
from (
     select sc.CId,
            sc.SId,
            sc.score,
            rank() over (partition by sc.CId order by sc.score desc) as score_order
     from sc
         ) t;

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

select *
from (
    select
           sc.CId,
           sc.SId,
           sc.score,
           dense_rank() over (partition by sc.CId order by sc.score desc) as score_order
    from sc
         ) t;

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

select *
from (
    select
           sc.SId,
           sc.CId,
           sc.score,
           rank() over (partition by sc.SId order by sc.score desc) as score_order
    from SC
         )t;

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

select sc.CId,
       c.Cname,
       sum(case when score>=85 and score<=100 then 1 else 0 end ) / count(score) * 100 as '[100-85]',
       sum(case when score>=70 and score<85 then 1 else 0 end ) / count(score) * 100 as '[85-70]',
       sum(case when score>=60 and score<=70 then 1 else 0 end ) / count(score) * 100 as '[70-60]',
       sum(case when score<60 then 1 else 0 end ) / count(score) * 100 as '[60-0]'
from sc inner join Course C on SC.CId = C.CId
group by sc.CId;

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

select *
from (
     select sc.SId,
            s.Sname,
            sc.CId,
            sc.score,
            row_number() over (partition by sc.CId order by sc.score desc ) as score_order
    from sc inner join student s on SC.SId = s.SId
         ) t
where score_order<=3;

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

select sc.CId, count(sc.SId)
from sc
group by sc.CId;

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

select Student.SId, Student.Sname
from student inner join sc on Student.SId=sc.SId
group by Student.SId
having count(sc.score)=2;

关于窗口函数的知识，课查看如下博客：
MySQL操作实战（二）：窗口函数