Student Schedule

[MUSIC] 0:00 Welcome back. 0:02 So far, things are going a lot smoother today. 0:06 The fire department's not around, and the all the teachers seem a bit more relaxed. 0:08 The only issue at the moment is a student who was sick yesterday and 0:12 wasn't able to pick up his schedule. 0:16 Could you print off a schedule for Rex Rios? 0:18 To figure out Rex's schedule, we're gonna need to use the schedule table. 0:20 But first, let's select just Rex himself from the students table. 0:24 So SELECT * 0:29 FROM STUDENTS WHERE FIRST_NAME = Rex AND 0:31 LAST_NAME = Rios. 0:40 Then once we've got Rex, let's look at the schedule table and 0:46 it looks like we can join on the STUDENT_ID to get his schedule. 0:50 So let's add a JOIN on the schedule table and 0:54 let's draw ON STUDENTS.ID 1:01 = SCHEDULE.STUDENT_ID. 1:06 And if we run this, now we have Rex's schedule, and let's see, 1:10 we've got one, two, three, four, five, six, seven classes. 1:15 But we don't know what classes those are, or even what period those classes are. 1:20 To get that information, we are going to have to join to the class table and 1:25 then from the classes table, to the subjects table. 1:29 So, let's add a JOIN, and let's join 1:33 two CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID. 1:38 And if we run it now, 1:45 we should have rows off to the right here which will be the classes table. 1:47 And there we go. 1:54 So here's the class IDs, the subjects, the periods, the teacher and 1:54 what room those are in. 1:58 So to get which subject it is, we need to join to the subject table using this 2:00 subject ID column which comes from the classes table. 2:04 So JOIN on SUBJECTS ON CLASSES.SUBJECT_ID and 2:06 actually since we've been doing this ID first, 2:12 let's do SUBJECTS.ID = CLASSES.SUBJECT_ID. 2:18 And if we run it now, here's the subject information we need. 2:24 So to get this into a nice schedule for Rex let's add an ORDER BY and let's do 2:27 it by PERIOD_ID ascending as well as let's just select a little less information. 2:33 So let's select the PERIOD then the SUBJECTS.NAME so 2:41 the NAME of the subject and then let's also select what grade it is. 2:47 SUBJECTS.GRADE which actually is name. 2:54 Yeah I think we don't need SUBJECTS there and 2:58 doesn't look like we need it here either. 3:01 And if we run this, right, 3:06 it is PERIOD_ID. 3:11 Unfortunately grade is an ambiguous column, so 3:15 let's do SUBJECTS.GRADE, and there we go. 3:18 That's Rex's schedule. 3:22