WEBVTT 00:00.150 --> 00:00.840 All right. 00:00.840 --> 00:05.460 So we've seen the basic components of crud creating reading updating and deleting. 00:05.520 --> 00:10.860 You've learned how to do all those operations and sequel and now it's time for the super ultra hyper 00:10.860 --> 00:12.960 mega critics or size. 00:13.050 --> 00:14.950 Actually let me try that one more time. 00:15.030 --> 00:21.860 It's time for the Pied Piper American regular protesters of Tony Benn. 00:21.850 --> 00:22.790 Half a second. 00:23.010 --> 00:24.260 I already regret that. 00:24.570 --> 00:25.480 So let's move on. 00:25.770 --> 00:30.480 So the idea here is that you're going to get to put all the different things you've learned to use. 00:30.540 --> 00:33.240 So this will be one exercise in this case. 00:33.240 --> 00:35.940 We're working on closet inventory. 00:35.970 --> 00:38.160 Super exciting I know. 00:38.370 --> 00:45.060 So basically the idea is that I or you or somebody has a bunch of clothing in their closet that they 00:45.060 --> 00:48.170 want to go through an inventory and figure out sizes and colors. 00:48.300 --> 00:53.700 And we're just focusing on shirts so different types of shirts tank tops polo shirts T-shirts and so 00:53.700 --> 00:54.160 on. 00:54.390 --> 00:58.240 And we can go through and we'll just perform some basic operations on them. 00:58.620 --> 01:05.310 I do want to add a note that I know that up until now our data has been sort of trivial and this included 01:05.400 --> 01:06.030 T-shirts. 01:06.030 --> 01:08.890 It's not that exciting or that realistic really. 01:08.940 --> 01:14.670 But we still don't have the pieces to go into complex real world data that you would see in a web app 01:15.360 --> 01:16.480 but we will get there. 01:16.710 --> 01:18.320 And so hold tight. 01:18.510 --> 01:23.520 I promise we'll be seeing a lot of real world potentially more exciting definitely more challenging 01:23.520 --> 01:24.010 data. 01:24.300 --> 01:26.270 So we're working on spring cleaning. 01:26.460 --> 01:31.680 And you're going to have a bunch of operations to do and the first one is to create a brand new database. 01:31.770 --> 01:33.910 So think back to how we did that. 01:33.930 --> 01:35.660 No more working in the database. 01:35.700 --> 01:37.750 We need a new one called Shared DB. 01:38.660 --> 01:45.710 To create that and then the next step is to create a new table called shirts and to do that you need 01:45.710 --> 01:47.080 a little more information. 01:47.150 --> 01:49.400 You need to know what does that table look like. 01:49.400 --> 01:50.540 So here you go. 01:50.630 --> 01:53.110 Here is a graphic representation. 01:53.300 --> 01:59.360 We've got a shirt I.D. which can not be null and is a primary key. 01:59.500 --> 02:04.320 We have article which should be something like T-shirt or polo shirt or tank top. 02:04.340 --> 02:05.310 It's text. 02:05.600 --> 02:10.830 And we have the color of the shirt which is also text white green black blue and so on. 02:11.240 --> 02:16.080 We have the shirt size in this case Smalls and mediums text. 02:16.430 --> 02:20.840 And then we have last oh actually before I move on I do want to call your attention to the fact that 02:20.930 --> 02:24.410 I did shirt underscore size rather than just size. 02:24.620 --> 02:28.790 And that's because size is a bit of a reserved word. 02:28.850 --> 02:34.520 You can still use it but it's a word that my ass will use just internally size. 02:34.660 --> 02:39.000 And so it's better to avoid any conflicts there so I just want my shirt size. 02:39.440 --> 02:46.470 And then last worn is a number and that refers to roughly how many days ago the shirt was last for. 02:46.580 --> 02:49.250 So the idea is that we would inventory our closet. 02:49.400 --> 02:51.260 Maybe you do this maybe you don't. 02:51.440 --> 02:56.570 But you go through and you figure out what you don't wear all that often and then either sell it or 02:56.570 --> 03:00.180 donate it or I don't know make tie dye with it. 03:00.320 --> 03:01.490 Whatever you do. 03:01.550 --> 03:06.230 So that's what we're trying to calculate or what we're trying to store here and it's just the number 03:06.230 --> 03:06.850 for now. 03:07.070 --> 03:08.430 So create that table. 03:08.440 --> 03:10.200 It should be called shirts. 03:10.400 --> 03:14.840 And once you do that you can move on to the next step which is getting the starter data in there. 03:14.870 --> 03:20.350 So I want all this data in the table but I don't want you to have to type it up yourself. 03:20.360 --> 03:23.690 It can be obnoxious to insert that manually. 03:23.750 --> 03:30.080 So I've given you a basically a starter seed data that contains the exact same thing. 03:30.080 --> 03:33.370 So a white T-shirt that small with 10 days ago. 03:33.370 --> 03:36.070 So you can see it's the same as this first shirt here. 03:36.080 --> 03:37.620 And I've done it for all eight. 03:37.970 --> 03:39.610 So you could copy this. 03:39.650 --> 03:41.970 It's in the text of this video as well. 03:42.170 --> 03:45.260 And then what you can do is just insert it all at once. 03:45.320 --> 03:47.280 So remember how to do a multi insert. 03:47.510 --> 03:49.770 Get all that data in there with a single line. 03:51.040 --> 03:55.150 And then the next step is to add a single new shirt. 03:55.150 --> 03:59.890 So insert something manually not using a multi answer but just one at a time. 03:59.950 --> 04:02.610 A purple polo shirt that's medium. 04:02.620 --> 04:04.650 That was last worn 50 days ago. 04:05.890 --> 04:06.470 All right. 04:06.770 --> 04:11.030 So then once we have those nine shirts in there we can now start to do some stuff with them. 04:12.170 --> 04:18.480 First thing we'll do is select all shirts but only print out article and color. 04:18.680 --> 04:22.370 So we should only see T-shirt purple polo shirt black. 04:22.550 --> 04:27.050 We don't want to see ID we don't want to see size or last worn. 04:27.230 --> 04:31.900 Then once you do that the next step is to select all medium shirts. 04:31.940 --> 04:38.100 So all shirts that have size of medium and print out everything but the shirt ID. 04:38.180 --> 04:44.490 So we want all of this here article color shirt size last worn but not this. 04:44.990 --> 04:48.410 But only for the medium's OK. 04:48.710 --> 04:51.610 Then next up we're moving on to update. 04:51.840 --> 04:57.150 So we've done that create in the read we're working on update now which is update all polo shirts to 04:57.150 --> 04:58.470 be size large. 04:58.590 --> 05:03.780 Maybe I don't know you realize that the company that makes your polo shirts it might say medium on them 05:03.810 --> 05:05.730 but in reality they're pretty large. 05:05.740 --> 05:06.480 And so you want it. 05:06.480 --> 05:07.830 Update your database. 05:07.830 --> 05:09.010 It's a dumb story. 05:09.010 --> 05:11.000 Just let's just go with it. 05:11.010 --> 05:17.070 So change their size to large So that's where article equals polo shirt. 05:17.070 --> 05:24.110 Next up update a shirt that was last worn 15 days ago and change last one to zero. 05:24.750 --> 05:30.520 So this would mean we are sure that was born 15 days ago that we decided to wear today. 05:30.600 --> 05:34.900 So we're going to change the database to say last warn is now zero. 05:35.650 --> 05:37.540 So once you do that. 05:37.540 --> 05:39.760 Now our final update. 05:39.760 --> 05:41.290 We had a bit of a mishap. 05:41.350 --> 05:42.770 We were doing laundry. 05:42.790 --> 05:47.350 We took all the white shirts and we washed them with I don't know. 05:47.560 --> 05:54.520 Some colored stuff that we shouldn't have and we shrink them at the same time so we have a double update 05:54.520 --> 05:55.380 here. 05:55.480 --> 06:01.720 You need to update all the white shirts to now be an extra small size and their color is no longer white 06:01.810 --> 06:03.710 but it should be off white. 06:03.820 --> 06:08.940 So you need to update both of those at once with one line just to be clear. 06:09.310 --> 06:10.240 All right. 06:10.450 --> 06:12.100 Next we're deleting. 06:12.310 --> 06:19.310 So delete all old shirts and in our case all just means if it was worn 200 days ago or last one 200 06:19.350 --> 06:25.810 days ago we will see very shortly how you can do things like delete all old shirts that have been last 06:25.810 --> 06:28.600 worn greater than 50 days ago. 06:28.780 --> 06:31.710 But for now we're doing exactly 200 days. 06:31.990 --> 06:32.600 OK. 06:32.800 --> 06:38.120 Next up our taste has changed we've decided we no longer like wearing tank tops. 06:38.230 --> 06:40.570 We don't like the person that it turned us into. 06:40.780 --> 06:45.370 So we're going to delete all tank tops from our database so delete all data. 06:45.400 --> 06:52.530 The Paul shirts were article is tank top then finally catastrophe strikes. 06:52.540 --> 06:54.290 We have to do the all shirts. 06:54.310 --> 06:56.680 I'll let you decide why. 06:56.870 --> 06:58.830 But we need to delete all the shirts. 06:58.840 --> 07:03.160 That doesn't mean delete the table or the database just delete the shirts in the table. 07:03.160 --> 07:04.920 So it should be an empty table. 07:05.380 --> 07:08.900 Then finally drop the entire Schertz table. 07:08.890 --> 07:10.320 Another catastrophe. 07:10.630 --> 07:12.850 So it should be an empty table at this point. 07:13.000 --> 07:15.520 Drop it entirely and that's it. 07:15.850 --> 07:17.500 So go through all those steps. 07:17.650 --> 07:19.920 I recommend just pausing the video as you go. 07:19.930 --> 07:25.680 Make sure you do them and then if you'd like watch the solution to see how to do it or how I did it. 07:26.070 --> 07:26.350 OK.