Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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