查询OOP课程

--检测有没有名称为MySchool的数据库
use master


if  exists (select * from sysdatabases where name='myschool')
begin
 print 'has'
end
else
begin
  print 'has not'
end

--没有难度
--检查“oop”课程最近一次考试。
    --如果有80分以上的成绩，则每人提2分；
    --否则，每人提5分。最终的成绩不得大于100分
    use MySchool
    
if exists 
(
  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'
    )
  )
  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


  --+2分  总分不能超过100
  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'
    )
  )
  and StudentResult<=95
  
end

 

转载于:https://www.cnblogs.com/WuXuanKun/p/5280950.html