show who resit and passed and what course was it?

i need help find who fail in exam & resit and pass the exam only,

heres the code:

select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD,s.NAME , c.NAME as Course_name, EXAM_DT, 
case 
 when SCORE>=PASS_THRESHOLD then 'PASS'
 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 
left join course c  on e.COURSE_ID  = c.ID

heres the result:

STUDENT_ID  EXAM_ID SCORE   PASS_THRESHOLD  NAME    Course_name     EXAM_DT      Flag
    1          3      88    65           Anthony    Data Mining     2019-12-17   PASS
    1          5      71    70           Anthony    Statistic       2019-12-19   PASS
    2          1      53    55           Sisca      Machine Learning2019-12-17  Fail
    2          3      77    65           Sisca      Data Mining     2019-12-17  PASS
    2          4      85    63           Sisca      Data Science    2019-12-18  PASS
    2          1      60    55           Sisca      Machine Learning2020-01-08  PASS

I need find like this:

 2         1      53    55           Sisca      Machine Learning2019-12-17  Fail
 2         1      60    55           Sisca      Machine Learning2020-01-08  PASS

Answer

Possibly using a query like below. this is using your query as input. Also we have assumed that it is not possible to have a student have (PASS, FAIL) for a student on same exam on two years chronologically.

; with inputdata as 
(
 select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD,s.NAME , c.NAME as Course_name, EXAM_DT, 
    case 
     when SCORE>=PASS_THRESHOLD then 'PASS'
     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 
    left join course c  on e.COURSE_ID  = c.ID
)
    
 select * from Inputdata I
join 
 ( select student_id, exam_id from 
    inputdata 
    group by student_id, exam_id
    having count(distinct flag)=2
)T on I.student_id=T.student_id and I.exam_id=T.exam_id
order by exam_dt asc