All right.
So we've seen the basic components of crud creating reading updating and deleting.
You've learned how to do all those operations and sequel and now it's time for the super ultra hyper
mega critics or size.
Actually let me try that one more time.
It's time for the Pied Piper American regular protesters of Tony Benn.
Half a second.
I already regret that.
So let's move on.
So the idea here is that you're going to get to put all the different things you've learned to use.
So this will be one exercise in this case.
We're working on closet inventory.
Super exciting I know.
So basically the idea is that I or you or somebody has a bunch of clothing in their closet that they
want to go through an inventory and figure out sizes and colors.
And we're just focusing on shirts so different types of shirts tank tops polo shirts T-shirts and so
on.
And we can go through and we'll just perform some basic operations on them.
I do want to add a note that I know that up until now our data has been sort of trivial and this included
T-shirts.
It's not that exciting or that realistic really.
But we still don't have the pieces to go into complex real world data that you would see in a web app
but we will get there.
And so hold tight.
I promise we'll be seeing a lot of real world potentially more exciting definitely more challenging
data.
So we're working on spring cleaning.
And you're going to have a bunch of operations to do and the first one is to create a brand new database.
So think back to how we did that.
No more working in the database.
We need a new one called Shared DB.
To create that and then the next step is to create a new table called shirts and to do that you need
a little more information.
You need to know what does that table look like.
So here you go.
Here is a graphic representation.
We've got a shirt I.D. which can not be null and is a primary key.
We have article which should be something like T-shirt or polo shirt or tank top.
It's text.
And we have the color of the shirt which is also text white green black blue and so on.
We have the shirt size in this case Smalls and mediums text.
And then we have last oh actually before I move on I do want to call your attention to the fact that
I did shirt underscore size rather than just size.
And that's because size is a bit of a reserved word.
You can still use it but it's a word that my ass will use just internally size.
And so it's better to avoid any conflicts there so I just want my shirt size.
And then last worn is a number and that refers to roughly how many days ago the shirt was last for.
So the idea is that we would inventory our closet.
Maybe you do this maybe you don't.
But you go through and you figure out what you don't wear all that often and then either sell it or
donate it or I don't know make tie dye with it.
Whatever you do.
So that's what we're trying to calculate or what we're trying to store here and it's just the number
for now.
So create that table.
It should be called shirts.
And once you do that you can move on to the next step which is getting the starter data in there.
So I want all this data in the table but I don't want you to have to type it up yourself.
It can be obnoxious to insert that manually.
So I've given you a basically a starter seed data that contains the exact same thing.
So a white T-shirt that small with 10 days ago.
So you can see it's the same as this first shirt here.
And I've done it for all eight.
So you could copy this.
It's in the text of this video as well.
And then what you can do is just insert it all at once.
So remember how to do a multi insert.
Get all that data in there with a single line.
And then the next step is to add a single new shirt.
So insert something manually not using a multi answer but just one at a time.
A purple polo shirt that's medium.
That was last worn 50 days ago.
All right.
So then once we have those nine shirts in there we can now start to do some stuff with them.
First thing we'll do is select all shirts but only print out article and color.
So we should only see T-shirt purple polo shirt black.
We don't want to see ID we don't want to see size or last worn.
Then once you do that the next step is to select all medium shirts.
So all shirts that have size of medium and print out everything but the shirt ID.
So we want all of this here article color shirt size last worn but not this.
But only for the medium's OK.
Then next up we're moving on to update.
So we've done that create in the read we're working on update now which is update all polo shirts to
be size large.
Maybe I don't know you realize that the company that makes your polo shirts it might say medium on them
but in reality they're pretty large.
And so you want it.
Update your database.
It's a dumb story.
Just let's just go with it.
So change their size to large So that's where article equals polo shirt.
Next up update a shirt that was last worn 15 days ago and change last one to zero.
So this would mean we are sure that was born 15 days ago that we decided to wear today.
So we're going to change the database to say last warn is now zero.
So once you do that.
Now our final update.
We had a bit of a mishap.
We were doing laundry.
We took all the white shirts and we washed them with I don't know.
Some colored stuff that we shouldn't have and we shrink them at the same time so we have a double update
here.
You need to update all the white shirts to now be an extra small size and their color is no longer white
but it should be off white.
So you need to update both of those at once with one line just to be clear.
All right.
Next we're deleting.
So delete all old shirts and in our case all just means if it was worn 200 days ago or last one 200
days ago we will see very shortly how you can do things like delete all old shirts that have been last
worn greater than 50 days ago.
But for now we're doing exactly 200 days.
OK.
Next up our taste has changed we've decided we no longer like wearing tank tops.
We don't like the person that it turned us into.
So we're going to delete all tank tops from our database so delete all data.
The Paul shirts were article is tank top then finally catastrophe strikes.
We have to do the all shirts.
I'll let you decide why.
But we need to delete all the shirts.
That doesn't mean delete the table or the database just delete the shirts in the table.
So it should be an empty table.
Then finally drop the entire Schertz table.
Another catastrophe.
So it should be an empty table at this point.
Drop it entirely and that's it.
So go through all those steps.
I recommend just pausing the video as you go.
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.
OK.