JAVA数据库第四章上机3_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