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
Find out how Jefferson Middle School structures their database!
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
Before you start writing queries,
0:00
it's usually a good idea to spend a little
time getting acquainted with the database.
0:01
So lets take a minute and see what tables
we'll be using, and how they fit together.
0:06
The database at Jefferson Middle School
contains seven tables.
0:11
The first table you'll need to
know about is the STUDENTS table.
0:14
Which contains an ID primary key,
and then the first and
0:17
last names of the student,
followed by the student's grade.
0:20
Next is the TEACHERS table,
which contains an ID primary key and
0:24
then the teacher's first and last names.
0:28
After that, we've got the SUBJECTS table,
0:31
which contains the different subjects,
like math and science.
0:33
This table contains an ID primary key,
0:37
followed by the subject's name, what
grade the subject is intended for, and
0:39
then a quick description of
what that subject teaches.
0:43
There's also a ROOMS table, which contains
one row for each room in the school.
0:46
In here, there's an ID primary key,
as well as an integer
0:51
identifying the maximum number
of students the room can hold.
0:55
A school day at Jefferson Middle School
is made up of 7 periods,
0:57
summarized in the PERIODS table.
1:02
It's got an ID primary key, followed by
the start time and duration of the period.
1:04
Also, the ID of each period
represents which period it is.
1:09
So the period with an ID of one is going
to represent the first period of the day.
1:14
Getting to something a bit more
complicated, we've got the CLASSES table.
1:19
Each row in the CLASSES table
represents one individual class.
1:23
So for each class, we've got an ID primary
key followed by four foreign keys.
1:27
Telling us which subject is being taught,
which period it is,
1:32
which teacher teaches it,
and which room it's in.
1:36
Finally, we've got the SCHEDULE table,
which contains scheduling information for
1:40
all of the students.
1:44
Each row is a pairing between
a student and a class.
1:46
So, if we've scheduled
everything correctly,
1:49
that means each student should
have seven rows in this table.
1:51
All right, now that you know a bit more
about how their database is structured,
1:56
lets get you warmed up with
a few practice problems.
2:00
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