第四章高级查询

--案例1：检查“oop”课程最近一次考试。--如果有80分以上的成绩，则每人提2分；
	--否则，每人提5分。最终的成绩不得大于100分
	if exists
	(
	  select studentresult from Result
	  where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='oop'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='oop'
		)
	  )
	  and StudentResult>80
	)
	begin
	   --有，每人提2分  99
	   update Result set StudentResult=100 
	   where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='oop'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='oop'
		)
	  )
	  and StudentResult>98
	   
	   update Result set StudentResult+=2 
	   where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='oop'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='oop'
		)
	  )
	  and StudentResult<=98
	end
	else
	begin
	   --没有，整体+5
	   update Result set StudentResult+=5
	   where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='oop'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='oop'
		)
	  )
	   
	end


select * from Result
 where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='oop'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='oop'
		)
	  )

---分页：双Top 双Order by  每页显示3条记录,我想要第二页数据 4-6条
select top 3 * from Student
where StudentNo not in
(
   select top 0 StudentNo from Student
)

select *from Student
子查询
select *from student
where Birthday >(select Birthday from Student where StudentNo='23311')

--最高分   最低分
select MAX (studentresult),MIN(studentresult)from result 
where examdate=
(
select MAX (examdate) from result where subjectid =
(

select subjectid from subject where subjectname ='oop'
)
)
and subjectid=
(
select subjectid from subject where subjectname ='oop'
)


--查询参加'oop'课程最近一次的在读学生名单(学生姓名,学生编号)
select studentname,studentno
from Student
where StudentNo in
(
select StudentNo from Result 
where SubjectId=
(
select SubjectId from Subject
where SubjectName='oop'
)
and ExamDate=
(
select MAX (ExamDate) from Result 
where SubjectId=
(
select SubjectId from Subject
where SubjectName='oop'
)
)
)