Greg Kaleka39,006 Points
Here's an alternative solution that uses subqueries.
SELECT FIRST_NAME, LAST_NAME FROM TEACHERS WHERE ID IN ( SELECT TEACHER_ID FROM CLASSES WHERE SUBJECT_ID IN ( SELECT ID FROM SUBJECTS WHERE NAME = "Science" AND GRADE = 7 ) );
Göktuğ BATIPython Web Development Techdegree Student 6,287 Points
it can be solved without using subqueries or joins
SELECT DISTINCT T.ID, T.FIRST_NAME, T.LAST_NAME FROM TEACHERS T, CLASSES C WHERE T.ID = C.TEACHER_ID AND C.SUBJECT_ID = 21
Evgeni Vershinin13,352 Points
I also found this aproach much easier for the last three videos, simply begin with selecting what we need then narrow it down to the specifics with WHERE IN subqueries later on. Teacher name -> teacher class -> teacher subject. It seems like such a more logical aproach then what he does in the videos
Harris Handoko3,394 Points
Just to contribute a little bit, the Common Table Expressions (CTE) version with derived table method: Credit to the poster Thomas who showed this method for the previous video.
-- Which teacher teaches 7th grade science? WITH DATA AS ( SELECT T.ID, T.FIRST_NAME, T.LAST_NAME, S.NAME AS SUBJECT_NAME, S.GRADE FROM TEACHERS AS T JOIN CLASSES AS C ON T.ID = C.TEACHER_ID JOIN SUBJECTS AS S ON C.SUBJECT_ID = S.ID ) SELECT DISTINCT FIRST_NAME, LAST_NAME FROM DATA WHERE SUBJECT_NAME LIKE "%SCIENCE%" AND GRADE = 7