Databases SQL Reporting by Example Day 1: Joining Tables 7th Grade Science

Greg Kaleka
Greg Kaleka
39,006 Points

More Subqueries!

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
  )
);

4 Answers

Göktuğ BATI
Göktuğ BATI
Python 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

kevin hudson
kevin hudson
Pro Student 9,652 Points

Suppose you didn't know the subject ID from a list of 1 billion subjects that were not ordered and Science happened to be somewhere between 789,000,001 and 937,000,675?

Evgeni Vershinin
Evgeni Vershinin
13,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 Handoko
Harris Handoko
3,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