mysql查询不同老师所教不同课程_sql面试题（学生表_课程表_成绩表_教师表）

1 --21、查询不同老师所教不同课程平均分从高到低显示

2 SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS平均成绩3 FROM SC AS T,Course AS C ,Teacher ASZ4 where T.C#=C.C# and C.T#=Z.T#5 GROUP BYC.C#6 ORDER BY AVG(Score) DESC

7

8 --22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)

9 [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩10 SELECT DISTINCT top 3

11 SC.S# As学生学号,12 Student.Sname AS学生姓名 ,13 T1.score AS企业管理,14 T2.score AS马克思,15 T3.score ASUML,16 T4.score AS数据库,17 ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as总分18 FROM Student,SC LEFT JOIN SC AST119 ON SC.S# = T1.S# AND T1.C# = '001'

20 LEFT JOIN SC AST221 ON SC.S# = T2.S# AND T2.C# = '002'

22 LEFT JOIN SC AST323 ON SC.S# = T3.S# AND T3.C# = '003'

24 LEFT JOIN SC AST425 ON SC.S# = T4.S# AND T4.C# = '004'

26 WHERE student.S#=SC.S# and

27 ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)28 NOT IN

29 (SELECT

30 DISTINCT

31 TOP 15 WITHTIES32 ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0