It's time to get some more practice with crud.
The basics of inserting and selecting and deleting and updating, and then also creating tables and
dropping tables.
So this is a larger exercise that will be broken up into a couple of videos.
The scenario here, it's still very simple.
We're not working with some real world complex data, but it's still a little more complicated than
cats.
We are spring cleaning.
We're cleaning out our closet.
Let's say that we've got a closet with lots of shirts in there, which is actually not really true in
my case.
I in desperate need of new shirts at the moment.
But let's say that I've got a surplus or you have a surplus.
We're going to create a database to keep track of all of our shirts like insane tech person would just
for whatever reason, we have to just put everything into tech and into a database.
So we're going to make a new database, not the pets database, a whole new one.
We need to create it and use it called shirts.
DB Then create a new table called shirts.
Now this shirts table will look something like this.
Obviously it will be empty when you make it, but it's going to have these columns, shirt ID, article,
color, shirt size last worn.
So shirt ID should be a primary key.
It should auto increment.
Whoops.
Article is the type of shirt.
Let's just say there's a handful of options.
Tank top polo shirt, t shirt.
Technically there's a bunch of others.
Right Button up long sleeves, Hawaiian shirts, just long sleeve t shirts.
It doesn't matter.
It's just text.
So we don't care about that.
When we're creating the actual column, we just need to say it's text and come up with some reasonable
limit for the number of characters.
Then we have color.
Another piece of text shirt size is a piece of text.
Now eventually we'll learn a better option for this shirt size where it's just a single character always,
although that's actually not true.
What about extra large or extra?
Extra large?
That's not exactly a single character, so it can vary in length.
So we're going to do VAR.
That's all we know so far, but there might be a better option later on.
And then last worn is an integer.
It's a number how many days ago we last wore this shirt.
So that's all you need to do when you're creating the table.
Just make sure you have the primary key set up as well.
And then I'd like you to take this batch of data I've given you.
Copy it from the slides or from the code following this video and insert all of these rows in one go.
So don't copy this line and do an insert with this and then an insert with this.
Just insert all of it.
I formatted it for you so that it could work, assuming that you write the correct insert into query.
So once you've done that, we have a little bit of data in there.
I'd like you to get a little more add in a new shirt that is purple.
It's a polo shirt, size em or medium.
And it was last worn 50 days ago.
And then select all of the shirts from the entire database, or rather from the entire shirts table,
but only print out their article and their color.
A little bit more selecting Select all medium shirts, but don't print everything out.
So we're not selecting star.
Print out everything except the shirt ID.
So almost everything, but not exactly.
Then I'd like you to work on updating things.
Update all polo shirts.
For whatever reason, their sizing is wrong in our database.
All of them need to be sized large.
And then update the shirt that was last worn 15 days ago and change its last 1 to 0, meaning we just
wore it today.
So last worn is zero days ago.
And then update all the shirts that have a color of white and change their size to be extra small and
the color to be off white.
Very important stuff.
Now we move on to deletion, delete all the old shirts, which will say is any shirt that was last worn
exactly 200 days ago and then delete all the tank tops.
Our tastes have changed.
We're now a 50 year old man.
It's not a good look to be wearing a tank top around town, which if you do, I'm sure you pull it off.
But in my hypothetical 50 year old man does not pull it off.
So we're deleting all tank tops and then we're deleting all shirts, regardless of type or color or
size.
Just delete all of them, empty the table out and then finally drop the entire shirts table.
Oh, no.
Maybe it was an accident.
Maybe not.
But let's say we want to drop the entire search table.
It's part of the exercise.
Just follow the instructions.
There's no rational explanation why, and that is it.
So let's go ahead and.
Well, I hope you pause the video or open up the slides.
There's a lot of pieces to this.
So go through each one of these one step at a time.
First, of course, create the table.
Actually, that's a lie.
First, create the database and then work with that database and create the table.
And I'll be back with the solution.
I'm going to break it up into a couple of videos just so I don't have like a ten, 15 minute solution
walk through.
But I, I pray, I plead with you that you go through and do this just to get some practice and it will
be quick.
All right.
I'll see you on the other side.