Combine 3 tables and get name according to average scores

Question

I have a question about sql. I have 3 tables.

• Table 1 is student. It has student id and student name

• Table 2 is school. It has school id and school name

• Table 3 is scores. It has school id, student id and scores

I am trying to write a query where you select school name, if average of the scores for that school is above 70.

The SCORES table looks like this. I know, same student goes to 2 schools sounds stupid. Ignore that logic

SCORES

STUDENT_ID  SCHOOL_ID  SCORE
    1          4         90
    1          7         67
    3          5         87
    3          4         78
    5          3         56
    6          4         95

Answer 1

You can aggregate and filter with a having clause. If you just want the id of the school, you can get the result you want by looking at the scores table only:

select school_id, avg(scores) avg_score
from scores 
group by school_id
having avg(scores) > 70

If you want the shool name, then use a join:

select sh.school_id, sh.school_name, avg(sc.scores) avg_score
from schools sh
inner join scores sc on sc.school_id = sh.school_id
group by sh.school_id, sh.school_name
having avg(sc.scores) > 70