--11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
--第一步：先初步筛选出存在不合格成绩学生的id
SELECT DISTINCT s.SId
FROM SC s
where s.score <60;
--第二步.找出不合格成绩中大于等于两门课程的学生,
--需要对sc中的学生进行分组并且只需要第一步里面查出来的学生并且不及格课程大于等于2
SELECT s.SId ,AVG(s.score) as avgscore
from SC s
group by s.SId
HAVING s.SId IN (SELECT DISTINCT s.SId
FROM SC s
where s.score <60) and COUNT(*)>=2 ;--先找出不合格课程的学生，然后在这些学生里面再找出不及格课程大于等于2
--第三步.用第二步中sid去关联student表
SELECT s.sid,s.Sname,a.avgscore
FROM Student s
join (SELECT s.SId ,AVG(s.score) as avgscore
from SC s
group by s.SId
HAVING s.SId IN (SELECT DISTINCT s.SId
FROM SC s
where s.score <60) and COUNT(*)>=2 )a
ON s.SId =a.sid ;
--12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
--第一步：先找出01课程分数小于60的学生sid
SELECT s.SId
from SC s
WHERE s.CId ='01' and s.score <60;
--第二步：把第一步查询出来的sid和student表做关联然后得出01课程小于60的学生具体信息
SELECT s.*,a.score
FROM Student s
join (SELECT s.SId,s.score
from SC s
WHERE s.CId ='01' and s.score <60)a
ON s.SId =a.sid
ORDER BY a.score DESC ;
--13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--第一步：显然要对sc表进行分组得出每个学生的平均分
SELECT s.SId ,AVG(s.score) as avgscore
FROM SC s
group by s.SId
--第二步:把第一步得出的avgscore关联sc表得出所有成绩
SELECT *
FROM sc s2
join (SELECT s.SId ,AVG(s.score) as avgscore
FROM SC s
group by s.SId) a ON s2.sid =a.sid
ORDER BY avgscore DESC ;
/*14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
*/
--像这种需求不要慌，采用分而治之思想，从复制拆解到简单，再到从简单到复制的过程进行处理
--第一步：对sc表根据课程进行分组
SELECT s2.CId
from SC s2
group by s2.CId
--第二步：根据每门课程找出对应需求即可
SELECT s.CId as "课程id"--课程id
,MAX(s.score) as "课程最高分"--课程最高分
,MIN(s.score) as "课程最低分"--课程最低分
,AVG(s.score)as "课程平均分"--课程平均分
,COUNT(*) as "课程选修人数"-- 课程选修人数
,SUM(CASE when s.score>=60 then 1 else 0 end) as "及格率"
,SUM(CASE when s.score>=70 and s.score <80 THEN 1 ELSE 0 END) as "中等率"
,SUM(CASE WHEN s.score>=80 and s.score<90 THEN 1 ELSE 0 END) as "优良率 "
,SUM(CASE WHEN s.score>=90 then 1 else 0 end) as "优秀率 "
FROM SC s
group by s.CId
order BY COUNT(*) DESC ,s.CId;
--15.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
--第一步:先对sc成绩表根据学生sid进行分组得出每个学生的总成绩
SELECT s.SId ,SUM(s.score) as sum_score,count(s.score)+1 as rank
FROM SC s
group by s.SId
ORDER BY sum_score desc;
--第二步: 不知道怎么得出排序
--16.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
--第一步：需要对科目进行分组
SELECT c2.CId ,c2.Cname
FROM SC s2
join Course c2 on s2.CId =c2.CId
GROUP BY c2.CId ,c2.Cname ;
--第二步:对每一组科目中的学生成绩进行成绩评估
SELECT c.CId as "课程编号"--课程编号
,c.Cname as "课程名称"--课程名称
,SUM(case when s.score>=85 and s.score<=100 then 1 else 0 END) as "[100-85]" --[100-85]
,SUM(CASE WHEN s.score>=70 and s.score<85 then 1 ELSE 0 END) as "[85-70]" --[85-70]
,SUM(case WHEN s.score>=60 and s.score<70 then 1 ELSE 0 end) as "[70-60]" --[70-60]
,SUM(case when s.score>=0 AND s.score<60 THEN 1 ELSE 0 END) as "[60-0]" --[60-0]
from SC s
join Course c on s.CId =c.CId
group by c.CId,c.Cname
-- 17.根据课程编号顺序升序，分数高低进降序进行排序(查询各科成绩记录)
SELECT s.CId,s.score
FROM sc s
group by s.CId,s.score
order by s.CId, s.score DESC;
-- 18.查询各科成绩前三名的记录
--暂时不会做，不知道如何取每个课程的前三名,有会的小伙伴请给我留言，非常感谢！！
--19.查询每门课程被选修的学生数
--对sc表根据cid进行分组并统计每个分组中数据的条数
SELECTs.CId,COUNT(*)
FROM SC s
group by s.CId;
--20.查询出只选修两门课程的学生学号和姓名
--第一步:对sc表根据sid进行分组并统计每个学生学的课程数，并保存满足条件的学生sid
SELECT s.SId
FROM SC s
group by s.SId --对学生进行分组
HAVING COUNT(*)=2 ;
--第二步：把student表和第一步查出来的学生sid进行关联并输出对应学生的信息
SELECT s.*
FROM Student s
join (SELECT s.SId
FROM SC s
group by s.SId
HAVINGCOUNT(*)=2)aons.SId=a.sid;
