SQL 第四章高级查询

select * from Student
 --查询 比"微冷的雨" 小的信息
 select  *from Student
 where Birthday >(select Birthday from Student where StudentName='微冷的雨')
 
 --查询 oop 最近一次最高 和最低分
 select MAX(StudentResult) as 最高分,MIN(StudentResult) as 最低分
 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 as 姓名,StudentNo as 编号
 from Student 
 where StudentNo  in
 (
  select StudentNo from  Result --根据成绩表查出所考的科目名称 'oop'
  where  SubjectId=             --根据where 条件 科目的ID 来找差 所考的科目名称 'oop'
  (
   select SubjectId from Subject 
   where SubjectName='oop'
  )
  and ExamDate =(                  --查询出最近一次参加考试 并且科目名称为 'oop' 
  select MAX(ExamDate) from Result --根据成绩表中的最近日期 来查出日期
  where SubjectId=(                --根据where 条件 来找出 在成绩表中的 SubjectId 
  select SubjectId from Subject    --根据where 条件 科目的ID 来找差 所考的科目名称 'oop'
  where SubjectName='oop')
  )
 )
 --案例：检查“oop”课程最近一次考试。--如果有分以上的成绩，则每人提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
	 --
	 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'
		)
	  )
 
 ---分页 方法
 select top 3* from Student
 where StudentNo not in 
 (
   select top 3 StudentNo from Student
 )
select * from Student