1 00:00:00,180 --> 00:00:00,960 Okey dokey. 2 00:00:00,990 --> 00:00:03,700 Let's go ahead and write these three tables. 3 00:00:03,719 --> 00:00:10,440 We'll start with reviewers and series and then we'll create the reviews table that has the series ID 4 00:00:10,440 --> 00:00:12,430 and reviewer ID foreign keys. 5 00:00:12,450 --> 00:00:13,920 So let's start with reviewers. 6 00:00:13,920 --> 00:00:17,820 Nice and simple ID, first name and last name. 7 00:00:18,330 --> 00:00:23,100 So create table reviewers. 8 00:00:25,260 --> 00:00:28,920 And we're going to have an ID that will be an integer. 9 00:00:29,040 --> 00:00:33,900 It should be a primary key and we want it to auto increment. 10 00:00:34,380 --> 00:00:37,870 And then we have first name, which is I don't know. 11 00:00:37,890 --> 00:00:41,220 Var cha 50 for each name. 12 00:00:42,390 --> 00:00:44,100 I think that's probably a safe threshold. 13 00:00:44,100 --> 00:00:45,810 50 characters and your first name. 14 00:00:46,560 --> 00:00:47,910 Let's make it required. 15 00:00:48,390 --> 00:00:51,750 And then last name also var cha 50. 16 00:00:53,400 --> 00:00:54,270 Not? 17 00:00:54,540 --> 00:00:55,170 No. 18 00:00:56,250 --> 00:00:58,020 So that should be our reviewers table. 19 00:00:58,960 --> 00:01:02,350 Next up, let's make the series table. 20 00:01:04,400 --> 00:01:08,450 Series ID title released here genre. 21 00:01:08,810 --> 00:01:11,360 So we'll do the same thing with ID. 22 00:01:11,390 --> 00:01:12,920 We want a primary key. 23 00:01:12,920 --> 00:01:14,720 So ID. 24 00:01:15,300 --> 00:01:16,140 INT. 25 00:01:17,090 --> 00:01:22,940 Primary key and auto underscore increment. 26 00:01:23,840 --> 00:01:26,720 And then we want to have the series title. 27 00:01:27,740 --> 00:01:32,390 And we'll make that a var cha 100 because titles can be pretty long. 28 00:01:33,020 --> 00:01:38,600 And then we have released year and for this we could do an integer, but I'm going to do something a 29 00:01:38,600 --> 00:01:39,260 little fancier. 30 00:01:39,260 --> 00:01:40,400 We haven't talked about. 31 00:01:40,400 --> 00:01:48,140 There is a data type called data type called year, and it will display and hold the value of a year 32 00:01:48,260 --> 00:01:51,800 implicitly with a width of four characters. 33 00:01:51,800 --> 00:01:55,550 So we don't have to do this, but it's an opportunity to show you this data type. 34 00:01:55,550 --> 00:02:03,260 So let's say that release year is of type year, and then we have genre, which will also be a var char, 35 00:02:03,260 --> 00:02:06,440 let's say 100 in case there's some long genres. 36 00:02:07,010 --> 00:02:09,620 Then we have the reviews table. 37 00:02:09,620 --> 00:02:20,990 So reviews has an ID, we'll start there, create table, put my E there, create table reviews and 38 00:02:20,990 --> 00:02:25,340 we want an ID and primary key auto increment. 39 00:02:26,060 --> 00:02:28,190 Probably tired of typing that by now and then. 40 00:02:28,190 --> 00:02:29,150 The rating. 41 00:02:29,150 --> 00:02:30,860 What data type should the rating be? 42 00:02:30,860 --> 00:02:36,500 If a rating is going to look something like this right here, 8.99.5. 43 00:02:36,740 --> 00:02:40,610 So probably a decimal afloat double. 44 00:02:40,640 --> 00:02:47,870 I'm going to do a decimal and limit it to be two total characters, one of which is after the the decimal 45 00:02:47,870 --> 00:02:48,350 point. 46 00:02:48,350 --> 00:02:51,320 So rating will be a decimal two characters. 47 00:02:51,320 --> 00:02:55,580 Here are two numbers, two digits, one of which can come after the decimal. 48 00:02:55,580 --> 00:03:05,060 So we can have like 8.9 or 9.9, but we're not going to have 9.9877987 rather OC So that's rating. 49 00:03:05,120 --> 00:03:13,160 Then we get the trickier pieces series ID and reviewer ID so we know that series ID, we'll start, 50 00:03:13,160 --> 00:03:15,980 there is going to be an integer. 51 00:03:16,100 --> 00:03:25,040 We also know that reviewer ID will be an integer because they are both going to reference in an integer 52 00:03:25,040 --> 00:03:29,090 column, series ideas and int reviewer's ideas and int. 53 00:03:29,300 --> 00:03:32,900 But then we want to add on the foreign key constraint and say, Oh yeah. 54 00:03:32,900 --> 00:03:42,050 By the way, my SQL, this series ID is in fact a foreign key and it references the series table and 55 00:03:42,050 --> 00:03:44,540 the ID column in that table. 56 00:03:45,080 --> 00:03:49,490 And then after that we'll do the same thing for review or ID. 57 00:03:49,520 --> 00:03:51,080 So foreign key. 58 00:03:52,010 --> 00:04:01,970 Reviewer IDE references the reviewers table, the ID column, and then we have the choice whether to 59 00:04:01,970 --> 00:04:05,180 add on the on delete cascade or not. 60 00:04:05,180 --> 00:04:12,140 Meaning if a series is deleted, should its reviews be deleted, or if a reviewer deletes their account, 61 00:04:12,140 --> 00:04:15,260 do the reviews associated with that account go away? 62 00:04:15,260 --> 00:04:21,200 For now, we'll say no, because maybe we want to keep if somebody deletes their account, we might 63 00:04:21,200 --> 00:04:25,550 want to keep the review and we'll just display it with no user name associated with it. 64 00:04:25,610 --> 00:04:27,710 But that is a choice that we have. 65 00:04:27,950 --> 00:04:28,580 All right. 66 00:04:28,580 --> 00:04:29,890 So let's try running this. 67 00:04:29,900 --> 00:04:31,760 See if I made any syntax errors. 68 00:04:31,760 --> 00:04:33,050 It's a good place to start. 69 00:04:33,050 --> 00:04:34,970 I'm going to make a new database. 70 00:04:36,020 --> 00:04:38,180 I'll call this TV. 71 00:04:38,180 --> 00:04:40,760 DB Use TVP. 72 00:04:40,820 --> 00:04:49,520 DB I'll run my three queries that create my tables and let's do a show tables. 73 00:04:50,240 --> 00:04:52,880 Looks good reviewers reviews and series. 74 00:04:53,150 --> 00:04:57,080 And then the final thing I want us to do is all work with the same data. 75 00:04:57,770 --> 00:05:05,780 So I'd like for us all to run this code that inserts into series like 15 or 20 different TV shows and 76 00:05:05,780 --> 00:05:11,300 then this code that inserts ten or so reviewers, and then this code that inserts 100 ish, I think 77 00:05:11,300 --> 00:05:17,810 it's less than a hundred, but a bunch of reviews where we have the series ID, the reviewer ID and 78 00:05:17,810 --> 00:05:18,440 the rating. 79 00:05:18,440 --> 00:05:20,480 So it's just giving us a starting point. 80 00:05:20,510 --> 00:05:23,990 We're all going to be on the same page, so let's run these three queries. 81 00:05:24,050 --> 00:05:26,180 The first one looks good. 82 00:05:26,180 --> 00:05:28,010 Select star from series. 83 00:05:28,010 --> 00:05:30,440 We now have 14 series in there. 84 00:05:31,050 --> 00:05:34,230 Then let's run reviewers that we insert. 85 00:05:34,380 --> 00:05:36,140 So let's do that. 86 00:05:36,150 --> 00:05:38,790 Select star from reviewers. 87 00:05:41,270 --> 00:05:43,220 And now we have seven reviewers. 88 00:05:43,610 --> 00:05:50,750 And finally we create a bunch of reviews that are a series ID, a reviewer ID and a rating. 89 00:05:51,290 --> 00:05:55,310 Okay, let's try 47. 90 00:05:55,310 --> 00:05:56,630 I don't know where I got 100 from. 91 00:05:56,630 --> 00:05:59,720 I was, I guess, high out of my mind. 92 00:06:00,700 --> 00:06:06,130 Not actually select star from reviews. 93 00:06:07,270 --> 00:06:13,390 We have 47 reviews, and when we look at this reviews table on its own, it's pretty meaningless unless 94 00:06:13,390 --> 00:06:19,450 you happen to know what series has the idea of nine and what reviewer has the ID of five. 95 00:06:19,480 --> 00:06:23,500 You're not going to know who left this review and what show it corresponds to, but that's the whole 96 00:06:23,500 --> 00:06:24,250 point of this section. 97 00:06:24,250 --> 00:06:27,820 We're going to get practice using some joints, but we have the data. 98 00:06:27,820 --> 00:06:28,900 That's all that matters. 99 00:06:28,900 --> 00:06:32,980 We have a starting point, we have our three tables, we have our foreign key constraints, and now 100 00:06:32,980 --> 00:06:38,740 all we have left to do is, is try to do some stuff with these tables. 101 00:06:38,740 --> 00:06:40,060 So that's what's coming up next. 102 00:06:40,060 --> 00:06:43,000 We're going to work on a series of little challenges together.