select 
    test_code, 
    student_code, 
    subject_code, 
    score,
    ranking,
    (score >= s1.avg_score) as passed
from 
    scores as s2
join (
    select distinct(subject_code) as sc, avg(score) as avg_score
    from scores
    group by sc
) as s1 on s2.subject_code = s1.sc