1
00:00:00,000 --> 00:00:02,218
[MUSIC]

2
00:00:02,218 --> 00:00:06,020
Welcome back.

3
00:00:06,020 --> 00:00:08,740
So far,
things are going a lot smoother today.

4
00:00:08,740 --> 00:00:12,920
The fire department's not around, and the
all the teachers seem a bit more relaxed.

5
00:00:12,920 --> 00:00:16,240
The only issue at the moment is
a student who was sick yesterday and

6
00:00:16,240 --> 00:00:18,100
wasn't able to pick up his schedule.

7
00:00:18,100 --> 00:00:20,963
Could you print off a schedule for
Rex Rios?

8
00:00:20,963 --> 00:00:24,980
To figure out Rex's schedule, we're
gonna need to use the schedule table.

9
00:00:24,980 --> 00:00:29,071
But first, let's select just Rex
himself from the students table.

10
00:00:29,071 --> 00:00:31,594
So SELECT *

11
00:00:31,594 --> 00:00:40,673
FROM STUDENTS WHERE FIRST_NAME = Rex AND

12
00:00:40,673 --> 00:00:44,707
LAST_NAME = Rios.

13
00:00:46,210 --> 00:00:50,410
Then once we've got Rex,
let's look at the schedule table and

14
00:00:50,410 --> 00:00:54,050
it looks like we can join on
the STUDENT_ID to get his schedule.

15
00:00:54,050 --> 00:01:01,320
So let's add a JOIN on
the schedule table and

16
00:01:01,320 --> 00:01:06,037
let's draw ON STUDENTS.ID

17
00:01:06,037 --> 00:01:10,963
= SCHEDULE.STUDENT_ID.

18
00:01:10,963 --> 00:01:15,179
And if we run this,
now we have Rex's schedule, and let's see,

19
00:01:15,179 --> 00:01:20,360
we've got one, two, three,
four, five, six, seven classes.

20
00:01:20,360 --> 00:01:24,420
But we don't know what classes those are,
or even what period those classes are.

21
00:01:25,620 --> 00:01:29,880
To get that information, we are going
to have to join to the class table and

22
00:01:29,880 --> 00:01:33,110
then from the classes table,
to the subjects table.

23
00:01:33,110 --> 00:01:38,438
So, let's add a JOIN, and let's join

24
00:01:38,438 --> 00:01:45,879
two CLASSES ON CLASSES.ID
= SCHEDULE.CLASS_ID.

25
00:01:45,879 --> 00:01:47,198
And if we run it now,

26
00:01:47,198 --> 00:01:51,960
we should have rows off to the right
here which will be the classes table.

27
00:01:54,000 --> 00:01:54,790
And there we go.

28
00:01:54,790 --> 00:01:58,320
So here's the class IDs, the subjects,
the periods, the teacher and

29
00:01:58,320 --> 00:01:59,100
what room those are in.

30
00:02:00,110 --> 00:02:04,018
So to get which subject it is, we need
to join to the subject table using this

31
00:02:04,018 --> 00:02:06,889
subject ID column which comes
from the classes table.

32
00:02:06,889 --> 00:02:12,589
So JOIN on
SUBJECTS ON CLASSES.SUBJECT_ID and

33
00:02:12,589 --> 00:02:18,288
actually since we've
been doing this ID first,

34
00:02:18,288 --> 00:02:24,410
let's do SUBJECTS.ID = CLASSES.SUBJECT_ID.

35
00:02:24,410 --> 00:02:27,080
And if we run it now,
here's the subject information we need.

36
00:02:27,080 --> 00:02:33,930
So to get this into a nice schedule for
Rex let's add an ORDER BY and let's do

37
00:02:33,930 --> 00:02:39,760
it by PERIOD_ID ascending as well as let's
just select a little less information.

38
00:02:41,230 --> 00:02:47,450
So let's select the PERIOD
then the SUBJECTS.NAME so

39
00:02:47,450 --> 00:02:50,850
the NAME of the subject and
then let's also select what grade it is.

40
00:02:54,647 --> 00:02:56,860
SUBJECTS.GRADE which actually is name.

41
00:02:58,167 --> 00:03:01,806
Yeah I think we don't
need SUBJECTS there and

42
00:03:01,806 --> 00:03:05,270
doesn't look like we need it here either.

43
00:03:06,340 --> 00:03:11,653
And if we run this, right,

44
00:03:11,653 --> 00:03:15,350
it is PERIOD_ID.

45
00:03:15,350 --> 00:03:18,968
Unfortunately grade is
an ambiguous column, so

46
00:03:18,968 --> 00:03:22,810
let's do SUBJECTS.GRADE, and there we go.

47
00:03:22,810 --> 00:03:23,710
That's Rex's schedule.