All right.
So we've seen the basic components of crud creating, reading, updating and deleting.
You've learned how to do all of those operations and SQL, and now it's time for the super ultra hyper
mega crud exercise.
Actually, let me try that one more time.
It's time for the super duper ultra high five.
Meghan, Meghan, Meghan.
It's only been half a second, but 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 in 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 this
included t shirts is 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 with 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 CAT database.
We need a new one called Shirts DB.
So 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's a graphic representation.
We've got a shirt ID 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.
Then 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.
So you can still use it, but it's a word that my school uses 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 worn.
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's small with ten 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 could 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 insert, 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 one, but not this, but only for the mediums.
Then next up, we're moving on to update.
So we've done the create and 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 to 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 the shirt that was last worn 15 days ago and changed last 1 to 0.
So this would mean we there's a shirt that was worn 15 days ago that we decided to wear today.
So we're going to change the database to say last worn 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 watched them with, I don't know, some colored stuff that we shouldn't
have, and we shrank 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'd 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.
Next up, our taste has changed.
We've decided we no longer like wearing tank tops.
We don't like the person that it turns us into.
So we're going to delete all tank tops from our database.
So delete all data, delete all shirts where article is tank top.
Then finally, catastrophe strikes.
We have to delete 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 shirts 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.
Okay.