Databases SQL Reporting by Example Day 3: Getting Good at Grouping Finding the Troublemaker

Tommy Gebru
Tommy Gebru
29,403 Points

Solution: Finding the Troublemaker

-- Which students have 5th period science and 7th period art?

I tried following along with the video but ended up confused I can share my work below but for now I want to share a solution by Ryan Dainton and his use of the INTERSECT keyword

SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM CLASSES
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
  JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID
  WHERE SUBJECTS.NAME = 'Science' AND PERIOD_ID = '5'

INTERSECT

SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM CLASSES
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
  JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID
  WHERE SUBJECTS.NAME = 'Art' AND PERIOD_ID = '7';

1 Answer

Tommy Gebru
Tommy Gebru
29,403 Points

This is what I was struggling with before I decided to seek out solutions in the forum :point_left:

WITH SCIENCE_PERIOD_FIVE AS (SELECT FIRST_NAME, LAST_NAME, NAME, PERIOD_ID
FROM STUDENTS
JOIN SCHEDULE
ON STUDENTS.ID = SCHEDULE.STUDENT_ID
JOIN CLASSES
ON CLASSES.ID = SCHEDULE.CLASS_ID
JOIN SUBJECTS
ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE PERIOD_ID = 5 AND SUBJECTS.NAME = "Science"),

WITH ART_PERIOD_SEVEN AS (SELECT FIRST_NAME, LAST_NAME, NAME, PERIOD_ID
FROM STUDENTS
JOIN SCHEDULE
ON STUDENTS.ID = SCHEDULE.STUDENT_ID
JOIN CLASSES
ON CLASSES.ID = SCHEDULE.CLASS_ID
JOIN SUBJECTS
ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE PERIOD_ID = 7 AND SUBJECTS.NAME = "Art")

SELECT STUDENT_ID
FROM SCIENCE_PERIOD_FIVE AS SCI_FIVE
JOIN ART_PERIOD_SEVEN AS ART_SEVEN
ON SCI_FIVE.STUDENT_ID = ART_SEVEN.STUDENT_ID
JOIN