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.
