SQL语句面试题求解——学生分数问题

已知3个表格如下：

 

StudentId   StudentName          Gender
----------- -------------------- ------
1           stu1                 NULL
2           stu2                 NULL
3           stu3                 NULL
4           stu4                 NULL
5           stu5                 NULL
6           stu6                 NULL

 

CourseID    CourseName                                         CourseTeacher
----------- -------------------------------------------------- --------------------------------------------------
1           English                                            LiMing
2           Math                                               ZhangSan
3           Music                                              Lily
4           History                                            Fenky
5           Geography                                          Hua

 

StudentID   CourseID    Score
----------- ----------- -----------
1           1           70
2           2           80
2           3           20
2           4           40
2           5           50
3           2           60
3           3           80
3           4           20
3           5           10
4           3           50
4           4           90
4           5           100

 

问题及答案：

 

-- 1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

select
 distinct StudentName
from
 TStudent, TCourse, TStudentCourse
where
 TStudent.StudentId = TStudentCourse.StudentId
 and TCourse.CourseId = TStudentCourse.CourseId
 and CourseTeacher <> 'LiMing'

 

-- 2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select
 StudentName, Avg(Score)
from
 TStudent, TCourse, TStudentCourse
where
 TStudent.StudentId = TStudentCourse.StudentId
 and TCourse.CourseId = TStudentCourse.CourseId
group by
 StudentName
having
 Sum(CASE WHEN Score < 60 THEN 1 ELSE 0 END) >=2

 

-- 3. 列出既学过“2”号课程，又学过“3”号课程的所有学生姓名

select
 StudentName
from
 TStudent, TCourse, TStudentCourse
where
 TStudent.StudentId = TStudentCourse.StudentId
 and TCourse.CourseId = TStudentCourse.CourseId
 and (TCourse.CourseId=2 or TCourse.CourseId=3)
group by
 StudentName
having
 Count(StudentName) =2

 

-- 4. 列出“3”号课成绩比“2”号课成绩高的所有学生的学号

select
 StudentName, TStudent.StudentId, TCourse.CourseId, S3=A.Score, S2=B.Score
from
 TStudent, TCourse, TStudentCourse A, TStudentCourse B
where
 TStudent.StudentId = A.StudentId
 and TCourse.CourseId = A.CourseId
 and A.StudentId = B.StudentId
 and A.CourseId = 3
 and B.CourseId = 2
 and A.Score > b.Score