--查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）selectCid,Course.Cname,sum(casewhenscore>=90then1else0end)*100/count(score)as'优秀率',sum(casewhenscore>=80andscore<90then1else0end)*100/count(score)as'优良率',sum(casewhenscore>=70andscore<80then1else0end)*100/count(score)as'中等率',sum(casewhenscore>=60then1else0end)*100/count(score)as'及格率',max(score)as'最高分',min(score)as'最低分',avg(score)as'平均分'fromSCgroupbyCid;

--成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩selectStudent.*,SC.scorefromStudent,SC,Course,TeacherwhereStudent.Sid=SC.SidandSC.Cid=Course.CidandCourse.Tid=Teacher.TidandTeacher.Tname='张三'orderbySC.scoredesclimit1;

--查询各科成绩前三名的记录selecta.Cid,a.Sid,a.score,count(b.score)+1from(select*fromSCgroupbyCid,Sid,score)aleftjoin(select*fromSCgroupbyCid,Sid,score)bona.Cid=b.Cidanda.score<b.scoregroupbya.Cid,a.Sid,a.scorehavingcount(b.score)<3orderbya.Cidasc,a.scoredesc;

--统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比selectSC.Cid,Course.Cname,count(SC.Sid)sum(casewhenscore>=85then1else0end)*100/count(score)as'[100-85]',sum(casewhenscore>=70andscore<85then1else0end)*100/count(score)as'[85-70]',sum(casewhenscore>=60andscore<70then1else0end)*100/count(score)as'[70-60]',sum(casewhenscore>=0andscore<60then1else0end)*100/count(score)as'[60-0]'fromSC,CoursewhereSC.Cid=Course.CidgroupbyCid;

--按各科成绩进行排序，并显示排名，Score重复时保留名次空缺selecta.cid,a.sid,a.score,count(b.score)+1asrankfromSCasaleftjoinSCasbona.score<b.scoreanda.cid=b.cidgroupbya.cid,a.sid,a.scoreorderbya.cid,rankASC;