已知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