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