Chapter 4 Computer 6 ACCP-SQL

-- Number of students to and from the last Java Logic test
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 'Should Be' + cast (@ num1 as varchar (10 ))
Print 'real-time' + cast (@ num2 as varchar (10 ))
Print 'test missing '+ cast (@ num1-@ num2) as varchar (10 ))
-- Java Logic scores for the last test and saves the results
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'
)
)

-- Scores lower than the average score for the exam
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 <@
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 >=@)

Select StudentName as name, StudentNo as student ID, score = case
When StudentResult is null then 'missing Test'
Else cast (StudentResult as varchar (5 ))
End
, Whether to pass = case
When isPass = 1 then 'is'
Else 'no'
End
From TempResult

-- Displays the final score and pass rate of the Student After score extraction.
Total select count (*), pass count = sum (isPass), pass rate = cast (avg (isPass * 100) as varchar (5) + '%'
From TempResult