day07

select sc.*,t.avscore  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    ) t
on sc.sid = t.sid
order by avscore desc;

select 
sc.CId ,
course.Cname,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from sc,course
WHERE sc.CId = course.CId
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC