mysql面试题 学生成绩表_经典sql面试题（学生表_课程表_成绩表_教师表）

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 ASZwhere T.C#=C.C# and C.T#=Z.T#GROUP BYC.C#ORDER BY AVG(Score) DESC

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩SELECT DISTINCT top 3SC.S#As学生学号,

Student.SnameAS学生姓名 ,

T1.scoreAS企业管理,

T2.scoreAS马克思,

T3.scoreASUML,

T4.scoreAS数据库,ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as总分FROM Student,SC LEFT JOIN SC AST1ON SC.S# = T1.S# AND T1.C# = '001'

LEFT JOIN SC AST2ON SC.S# = T2.S# AND T2.C# = '002'

LEFT JOIN SC AST3ON SC.S# = T3.S# AND T3.C# = '003'

LEFT JOIN SC AST4ON SC.S# = T4.S# AND T4.C# = '004'

<