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.