make status fail & pass, from treshold score

April 12, 2022

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

>Solution :

You can use CASE to test whether WHEN the score is less than the pass_threshold, in which case THEN we put ‘fail’ otherwise ELSEwe 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