ACCP-SQL第四章上機六

--Java Logic 最近一次考試應到和實到人數
declare @num1 int
declare @num2 int
select @num1=count(*) from Student
where GradeId = (select Subject.GradeId from Subject where SubjectName = 'java')

select @num2=count(*) from Result
where ExamDate = (select Max(ExamDate)
      from Result
      where SubjectNo = (select SubjectNo
          from Subject
          where SubjectName = 'java'
          )
    ) and StudentResult >= 0
print '應到' + cast(@num1 as varchar(10))
print '實到' + cast(@num2 as varchar(10))
print '缺考' + cast((@num1-@num2) as varchar(10))
--Java Logic 最近一次考試的成績資訊並儲存結果
if exists (select * from sysobjects where name = 'TempResult')
 drop table TempResult
select Studentname,Result.StudentNo,StudentResult,isPass=case
 when Result.StudentResult >= 60 then 1
 else 0
 end
into TempResult
from Result
left join Student  on Result.StudentNo = Student.StudentNo
where ExamDate = (select Max(ExamDate)
      from Result
      where SubjectNo = (select SubjectNo
          from Subject
          where SubjectName = 'java'
          )
    )

select * from TempResult
---------
select Studentname,Result.StudentNo,StudentResult
from Result
left join Student  on Result.StudentNo = Student.StudentNo
where ExamDate = (select Max(ExamDate)
      from Result
      where SubjectNo = (select SubjectNo
          from Subject
          where SubjectName = 'java'
          )
    )

--根據考試平均分為低於平均分的學生加分
declare @a numeric(4,2)
select @a=avg(StudentResult)
from TempResult

declare @n int
while(1 = 1)
 begin
  select @n = count(*) from TempResult
   where StudentResult < @a
  if(@n > 0)
   update TempResult set StudentResult = StudentResult + 1
   where (StudentResult < @a and StudentResult <= 97 )
  else
   break
end
update TempResult set isPass = 1
where ( StudentResult >= @a )

select StudentName as 姓名,StudentNo as 學號,成績 = case
 when StudentResult is null then '缺考'
 else cast(StudentResult as varchar(5)) 
 end
,是否通過=case
 when isPass = 1 then '是'
 else '否' 
end
 from TempResult

--顯示提分後學生最終成績和通過率
select 總人數 = count(*) ,通過人數 = sum(isPass),通過率 = cast (avg(isPass * 100) as varchar(5)) + '%'
from TempResult