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

    SELECT * FROM SC LEFT JOIN
    (SELECT SId, AVG(score)ag FROM SC GROUP BY sid)r
    ON SC.SId = r.SId
    ORDER BY ag DESC

    1. 查询各科成绩最高分、最低分和平均分：

    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
    SELECT
    SC.CId,
    Course.Cname,
    MAX(SC.score)最高分,
    MIN(SC.score)最低分,
    AVG(SC.score)平均分,
    COUNT() 选修人数,
    SUM(CASE WHEN SC.score >=60 THEN 1 ELSE 0 END)/COUNT(    )及格率,
    SUM(CASE WHEN SC.score >=70 AND SC.score <80 THEN 1 ELSE 0 END)/COUNT(*)中等率,
    SUM(CASE WHEN SC.score >=80 AND SC.score <80 THEN 1 ELSE 0 END)/COUNT(*)优良率,
    SUM(CASE WHEN SC.score >=90 THEN 1 ELSE 0 END)/COUNT()优秀率
    FROM SC,Course
    WHERE SC.CId = Course.CId
    GROUP BY SC.CId
    ORDER BY COUNT(    ) DESC,SC.CId ASC

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

    SELECT a.cid,a.sid,a.score,COUNT(b.score)+1 rank
    FROM SC a
    LEFT JOIN SC b
    ON a.scoreGROUP BY a.cid,a.sid,a.score
    ORDER BY a.cid,rank ASC;

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

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

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

    SET @crank=0;
    SELECT q.sid,total,@crank:=@crank+1 rank FROM
    (SELECT SC.SId,SUM(SC.score)total FROM SC
    GROUP BY SC.CId
    ORDER BY total DESC)q

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

    SELECT SC.CId,
    Course.Cname,
    SUM(CASE WHEN SC.score<=100 AND SC.score >85 THEN 1 ELSE 0 END)/COUNT()100’[100-85]’,
    SUM(CASE WHEN SC.score<=85 AND SC.score >70 THEN 1 ELSE 0 END)/COUNT()100’[85-70]’,
    SUM(CASE WHEN SC.score<=70 AND SC.score >60 THEN 1 ELSE 0 END)/COUNT()100’[70-60]’,
    SUM(CASE WHEN SC.score<=60 AND SC.score >0 THEN 1 ELSE 0 END)/COUNT()100’[60-0]’
    FROM SC LEFT JOIN Course
    ON SC.CId = Course.CId
    GROUP BY SC.CId