Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQL Reporting by Example!
You have completed SQL Reporting by Example!
Preview
Which students have Physical Education during first period?
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Great job.
0:00
Also, while you were working on that,
0:01
the school got a few calls from parents,
upset that their kids
0:03
were going to be sweaty all day from
having PE first thing in the morning.
0:06
Could you figure out how many students
have physical education during
0:10
first period?
0:13
For this query, just like the last query,
0:14
we'll need to start with
the STUDENTS table.
0:17
And then join through to a couple other
tables to be able to get all this
0:19
information.
0:22
So let's start by selecting
everything from the STUDENTS table.
0:24
And let's join it to the SCHEDULE table.
0:29
On STUDENTS.ID = SCHEDULE.STUDENT_ID.
0:35
And remember, this gives us a results
set of all the students and
0:43
all the classes those students are in.
0:47
So, for example, if we ordered this,
so let's add ORDER BY,
0:50
and we order it by a STUDENT.ID, or
0:56
rather, STUDENTS.ID.
1:00
Then we should see each student
listed seven times, which we do.
1:05
So now that we have the students and
each of their schedules,
1:12
we need to get a little more information
about which classes these are.
1:15
So just like last time,
let's use this CLASS_ID
1:19
column that we get from the SCHEDULE
table to join to the CLASSES table.
1:22
JOIN CLASSES on CLASSES.ID
= SCHEDULE.CLASS_ID.
1:26
And there we go.
1:37
And then to get this Physical Education
piece of information,
1:38
we'll need to join to the SUBJECT
table on the SUBJECT_ID.
1:42
And we already have which period it is
right here as the PERIOD_ID column.
1:45
So let's join to the SUBJECT_ID table,
or rather, to the SUBJECTS table.
1:49
So JOIN SUBJECTS ON, and
we'll use SUBJECTS.ID =.
1:53
And remember this SUBJECT_ID column
is coming from the CLASSES table.
2:00
So we'll need to do CLASSES.SUBJECT_ID.
2:04
Then in the WHERE clause,
we can add that we want the PERIOD_ID.
2:09
And I think we should only have one of
those, but we might have more than one.
2:13
So we'll see if this gives us an error,
= 1.
2:16
And let's see what that's named in
the SUBJECT table, SUBJECTS.NAME.
2:19
I don't know if that'll give us
a collision error, but let's see.
2:24
And NAME =, Physical Education.
2:28
And if we run this, looks like it works.
2:34
And there's Physical Education.
2:39
And we've got a bunch of 1s over here for
the PERIOD_ID.
2:41
Nice, so from here,
all we need to do is add a COUNT.
2:49
COUNT, and whatever you wanna count,
FROM STUDENTS.
2:54
And there you go, 47 students have
Physical Education for this period.
2:59
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up