--in和not in子查询
--采用in子查询获得参加考试的在读学生名单
select StudentId,StudentName from Student where StudentId in
(
select StudentId from Result
where subjectId=(
--获得参加java考试最近一次考试的学生学号
select subjectId from Subject
where SubjectName='java'
)and ExamDate=(
--获得java课程最近一次的考试日期
select max(ExamDate) from Result
where subjectId=(
--获得java课程的课程编号
select subjectId from Subject
where SubjectName='java'
)
)
)
--采用not in子查询，查看未参加考试的在读学生名单
select StudentId,StudentName from Student
where StudentId not in(
select StudentId from Result
where subjectId=(
select subjectId from Subject
where SubjectName='java'
)
and ExamDate=(
select max(ExamDate) from Result
where subjectId=(
select subjectId from Subject
where SubjectName='java'
)
)
)
--分页查询
select top 2 * from Student
where StudentId not in
(
select top 2 StudentId from Student
)
--exists子查询
--采用exists子查询，进行酌情加分
if exists(
--查询java课程最近一次考试成绩大于80分的记录
select * from Result where subjectId=(
select subjectId from Subject where SubjectName='java'
)and ExamDate=(
select max(ExamDate)from Result where subjectId=(
select subjectId from Subject
where SubjectName='java')
)and StudentResult>80
)
--如果存在考试成绩高于80分的学生，则参加本次考试的学生每人加2分
--加分前的最高成绩不得高于98分
begin
update Result set StudentResult=StudentResult+2
where subjectId=(
select subjectId from Subject where SubjectName='java'
)and ExamDate=(
select max(ExamDate) from Result where subjectId=(
select subjectId from Subject
where SubjectName='java')
)and StudentResult<=98
end
else
--如果考试成绩都低于80分，则参加考试的学生每人加5分
begin
update Result set StudentResult=StudentResult+5
where subjectId=(
select subjectId from Subject where SubjectName='java'
)and ExamDate=(
select max(ExamDate) from Result where subjectId=(
select subjectId from Subject
where SubjectName='java')
)
end
