My answer to the challenge. Let me know what you think.

Question

I decided to use a CTE with a few joins.

-- This CTE allows you to pull any class schedule for a student_id
WITH all_schedules AS (
SELECT * FROM CLASSES
JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
JOIN PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
)

SELECT STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME, PERIOD_ID, NAME, START_TIME, DURATION, ROOM_ID, TEACHERS.LAST_NAME
FROM all_schedules
JOIN TEACHERS ON TEACHERS.ID = all_schedules.TEACHER_ID
JOIN STUDENTS ON STUDENTS.ID = all_schedules.STUDENT_ID
WHERE STUDENTS.FIRST_NAME = 'Rex'
ORDER BY PERIOD_ID;

I thought a schedule should have any information that would be relevant to the student. They need to know where to go, what time to be there, how long the class it, and who teaches. The reason I did not join teachers and students in the CTE is because they both have first and last names, which would make them ambiguous when selecting them in the query later.

Answer 1 · 2019-03-24T04:06:49Z

March 24, 2019 4:06am

You could also use column aliases to make them unambiguous and not need a CTE:

SELECT STUDENTS.FIRST_NAME,
       STUDENTS.LAST_NAME,
       PERIOD_ID as "PERIOD",
       NAME as "CLASS",
       START_TIME,
       DURATION,
       ROOM_ID as "ROOM",
       TEACHERS.LAST_NAME as "TEACHER"
FROM CLASSES
JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
JOIN PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
JOIN TEACHERS ON TEACHERS.ID = CLASSES.TEACHER_ID
JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID
WHERE STUDENTS.FIRST_NAME = 'Rex'
ORDER BY PERIOD_ID;

For the benefit of other readers, could you post a link to the course page you were working with?

Welcome to the Treehouse Community

Looking to learn something new?

Michael Nanni

Michael Nanni

My answer to the challenge. Let me know what you think.

1 Answer

Steven Parker

Steven Parker