i need help to add new column that has status pass or fail if exam score is high than pass treshold. heres the syntax
SELECT ID, COURSE_ID, PASS_THRESHOLD, UPDATE_DT
FROM univ.exam;
SELECT ID, STUDENT_ID, EXAM_ID, EXAM_DT, SCORE
FROM univ.exam_submission;
select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD
from exam_submission es
left join student s
on es.STUDENT_ID = s.ID
left join exam e
on es.EXAM_ID = e.ID
here is the result now:
STUDENT_ID EXAM_ID SCORE PASS_THRESHOLD
1 3 88 65
1 5 71 70
2 1 53 55
2 3 77 65
2 4 85 63
i want to make like this :` here is the result :
STUDENT_ID EXAM_ID SCORE PASS_THRESHOLD Status
1 3 88 65 Pass
1 5 71 70 Pass
2 1 53 55 Fail
2 3 77 65 Pass
2 4 85 63` Fail
CodePudding user response:
select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD,
case
when SCORE>=PASS_THRESHOLD then 'PASSED'
else 'Fail'
end as Flag
from exam_submission es
left join student s on es.STUDENT_ID = s.ID
left join exam e on es.EXAM_ID = e.ID
CodePudding user response:
You can use CASE
to test whether WHEN
the score is less than the pass_threshold, in which case THEN
we put 'fail' otherwise ELSE
we put fail.
We could have done it the other way around but <
is more concise then >=
.
select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD,
CASE WHEN PASS_THRESHOLD > SCORE THEN 'fail' ELSE 'pass' END status
from exam_submission es
left join student s
on es.STUDENT_ID = s.ID
left join exam e
on es.EXAM_ID = e.ID