SQL高级编程-子查询

---SQL高级编程--子查询

 select * from student
 where BornDate=(select BornDate from Student where StudentName='欧阳燕飞')



 select max(studentresult) as 最大值,min(studentresult) as 最小值 from Result
 where ExamDate=(select max(ExamDate) from Result where SubjectId=
 (select SubjectId from Subject where SubjectName='HTML和CSS网页技术')
 )and SubjectId=(select SubjectId from Subject where SubjectName='HTML和CSS网页技术')


 select studentno,studentname from student where StudentNo in(
 select StudentNo from Result where SubjectId=(select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
 )and ExamDate=(select max(ExamDate) from Result where SubjectId=(
select subjectid from Subject where SubjectName='HTML和CSS网页技术'
))
 )
 


 select 学生姓名=studentname from student where StudentNo in(
 select 学号=studentno from result inner join subject on result.subjectid=subject.subjectid
 where studentresult=60 and subjectname='HTML和CSS网页技术')



 select subjectname,subjectid,Gradeid from Subject
 select studentresult from Result where subjectid=1




select max(studentresult) as 最高分,min(studentresult) as 最低分 from Result inner join student on Student.StudentNo=Result.StudentNo
where Result.ExamDate=(select max(result.ExamDate) from Result inner join Subject on Subject.SubjectId=Result.SubjectId where SubjectName='HTML和CSS网页技术'
)
and Subjectid=(select Subjectid from Subject
where SubjectName='HTML和CSS网页技术'
)



--实例6
select studentno, studentname from student where studentno   in (
select studentno from result where subjectid=(
select subjectid from Subject where SubjectName='HTML和CSS网页技术'
) and ExamDate=(
select max(ExamDate) from Result
where SubjectId=(
select subjectid from Subject where SubjectName='HTML和CSS网页技术'
)
)
)
--NOT IN 子查询
select studentno, studentname,gradeid from student where studentno NOT  in (
select studentno from result where subjectid=(
select subjectid from Subject where SubjectName='HTML和CSS网页技术'
) and ExamDate=(
select max(ExamDate) from Result
where SubjectId=(
select subjectid from Subject where SubjectName='HTML和CSS网页技术'
)
)
)and GradeId=(
select gradeid from Subject
where SubjectName='HTML和CSS网页技术'
)



--实例8
if exists(
select * from Result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)and ExamDate=(
select max(ExamDate) from result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
)and StudentResult>80
)

begin
update Result set StudentResult=100
where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
and examdate=
(
select max(ExamDate) from Result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
)and StudentResult<=98

update Result set StudentResult=StudentResult+2
where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
and examdate=
(
select max(ExamDate) from Result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
)and StudentResult<=98
end
else
begin
update result set StudentResult=StudentResult+5
where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)and ExamDate=(select max(ExamDate) from Result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
)
end


select * from result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
and examdate=
(
select max(ExamDate) from Result where SubjectId=(
select SubjectId from Subject where SubjectName='HTML和CSS网页技术'
)
)




--答案
if exists
	(
	  select studentresult from Result
	  where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='HTML和CSS网页技术'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='HTML和CSS网页技术'
		)
	  )
	  and StudentResult>80
	)
	begin
	   --有，每人提分 99
	   update Result set StudentResult=100 
	   where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='HTML和CSS网页技术'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='HTML和CSS网页技术'
		)
	  )
	  and StudentResult>98
	   
	   update Result set StudentResult+=2 
	   where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='HTML和CSS网页技术'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='HTML和CSS网页技术'
		)
	  )
	  and StudentResult<=98
	end
	else
	begin
	   --没有，整体+5
	   update Result set StudentResult+=5
	   where SubjectId=
	  (
		select SubjectId from Subject
		where SubjectName='HTML和CSS网页技术'
	  )
	  and ExamDate=
	  (
		select MAX(ExamDate) from Result
		where SubjectId=
		(
		  select SubjectId from Subject
		  where SubjectName='HTML和CSS网页技术'
		)
	  )
	   
	end