50个常用sql语句

nbsp;   WHERE R.C# = IR.C#
                  GROUP BY IR.C#
                    );
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
    SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
    FROM SC T,Course
    where t.C#=course.C#
    GROUP BY t.C#
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
    SELECT SUM(CASE WHEN C# =''001'' THEN score ELSE 0 END)/SUM(CASE C# WHEN ''001'' THEN 1 ELSE 0 END) AS 企业管理平均分
        ,100 * SUM(CASE WHEN C# = ''001'' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ''001'' THEN 1 ELSE 0 END) AS 企业管理及格百分数
        ,SUM(CASE WHEN C# = ''002'' THEN score ELSE 0 END)/SUM(CASE C# WHEN ''002'' THEN 1 ELSE 0 END) AS 马克思平均分
        ,100 * SUM(CASE WHEN C# = ''002'' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ''002'' THEN 1 ELSE 0 END) AS 马克思及格百分数
        ,SUM(CASE WHEN C# = ''003'' THEN score ELSE 0 END)/SUM(CASE C# WHEN ''003'' THEN 1 ELSE 0 END) AS UML平均分
        ,100 * SUM(CASE WHEN C# = ''003'' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ''003'' THEN 1 ELSE 0 END) AS UML及格百分数
        ,SUM(CASE WHEN C# = ''004'' THEN score ELSE 0 END)/SUM(CASE C# WHEN ''004'' THEN 1 ELSE 0 END) AS 数据库平均分
        ,100 * SUM(CASE WHEN C# = ''004'' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ''004'' THEN 1 ELSE 0 END) AS 数据库及格百分数
  FROM SC
21、查询不同老师所教不同课程平均分从高到低显示
  SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
    FROM SC AS T,Course AS C ,Teacher AS Z
    where T.C#=C.C# and C.T#=Z.T#
  GROUP BY C.C#
  ORDER BY AVG(Score) DESC
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
    SELECT  DISTINCT top 3
      SC.S# As 学生学号,
        Student.Sname AS 学生姓名 ,
      T1.score AS 企业管理,
  &nbs