MySql面试题

Student（S#,Sname,Sbirthday,Ssex）学生表：学号、姓名、生日、性别
Course（C#,Cname,T#）课程表：课程号、课程名称、教师工号
SC（S#,C#,Score）成绩表：学生学号、课程号、分数
Teacher（T#,Tname）教师表：教师工号、教师姓名
备注：成绩表中分数在60及以上代表及格；成绩为空代表缺考
请用SQL写出下列查询：
(1)查询缺考学生的学号、姓名、缺考课程名称、对应缺考科目老师姓名。

(2)查询2门及2门以上课程不及格同学的学号、姓名和不及格课程名称。

(3)查询各科目学生成绩排名，返回课程名称、学生学号、学生姓名、学生该课程排名。
说明：同一科目成绩相同时，排名并列；缺考学生不参与缺考科目排名；

答案

1.SELECT s.S#, s.Sname, c.Cname, t.Tname FROM Student s, Course c, SC sc, Teacher t WHERE s.S# = sc.S# AND c.C# = sc.C# AND t.T# = c.T# AND sc.Score IS NULL;

2.SELECT s.S#, s.Sname, c.Cname FROM Student s, Course c, SC sc WHERE s.S# = sc.S# AND c.C# = sc.C# AND sc.Score < 60 GROUP BY s.S# HAVING COUNT(*) >= 2;

3.SELECT c.Cname, sc.S#, s.Sname, COUNT(DISTINCT sc2.Score) + 1 AS rank FROM Course c, SC sc, SC sc2, Student s WHERE c.C# = sc.C# AND sc.S# = s.S# AND sc.C# = sc2.C# AND (sc.Score < sc2.Score OR (sc.Score = sc2.Score AND sc.S# > sc2.S#))) OR (sc.Score IS NULL AND sc.C# = sc2.C# AND sc.S# > sc2.S#) GROUP BY c.Cname, sc.S# ORDER BY c.Cname, rank;