1.统计每门课程不及格的学生人数(课程编号,不及格人数)
select cid,count(sid) from sp where sp.score<60 group by cid ;
2.统计每门课程不及格的学生的姓名(课程编号,不及格学生姓名集合)形如: 20 [“Eric”,“Joy”]
select cid,collect_list(cname) from sp,st5 where sp.sid=st5.sid and sp<60 group by cid;
3.查询两门及其以上不及格课程的学生的学号,姓名
Select st5.sid,st5.cname from st5, sp where st5.sid=sp.sid and sp.score<60 group by st5.sid,st5.cname having count(sp.score)>=2;
4.查询学生的总成绩并进行排名(姓名 总分 名次)
Select cname,sum(score),rank() over(order by sum(score) desc) from st5,sp where st5.sid=sp.sid group by st5.sid,st5.cname;
5.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 (sid,cid,score,average)
Select sid,cid,score,avg(score) over (partition by sid ) average from sp order by average desc;
6.查询各科成绩最高分、最低分和平均分(课程编号,最高分,最低分,平均分)
Select cid,max(score),min(score),avg(score) from sp group by cid;