All right.
In this part of the solution, I want to walk through setting up our database and creating the table.
So the first thing we'll do is make a new database called Shirts DB.
Now, we don't actually have to do this all as one step at a time.
You know, I don't have to create the the database and then use it and then create my table.
If I want to work in a script, I can and I can do all these things at once and run the entire script.
So I'll show that here.
But it doesn't really make a difference.
If you want to just run these in your shell one line at a time, that's also totally valid.
But what I'm going to do is create database and I think I called it shirts underscore DB.
Like that.
And then I'm going to use that database.
So I didn't even tell you about that part.
You're supposed to remember that.
Otherwise, we may not be creating the shirts table in the correct database.
If I just created a table here, create table shirts.
This table is not guaranteed to be in our shirts.
DB.
It just depends on whatever database I'm using at that time.
We can create a database from anywhere, but we don't automatically use it.
So I would then say use shirts.
DB.
Or of course, if I'm using the guy here, I could after I create the database, I could refresh my
view and then double click and find that database and use it using the graphical way.
Like I'm using chicken coop right now.
I'm using pet shop right now, but this is a foolproof way and this is the the actual command that I
want you to know.
I don't want you to rely only on the interface.
So create our table is the next step.
The first thing we have is our shirt ID, which is the primary key.
It's an integer.
So let's call that shirt.
ID or ID is fine too, but let's just follow the slides.
Shirt ID is an int auto increment and I'll do primary key on the same line again.
I can do that after the fact as well.
I can add that constraint, but I'll leave it like this.
Then we have article and I didn't specify if any of these are knowable or not.
Let's just keep it simple and say that everything can be null of course, except the shirt id, so article
will be a var article, var cha var car and that will be, I don't know, let's just say 50 characters
and then we have color.
I believe the order doesn't matter, but color is of our car shirt.
Sizes of our car.
Let's say, size.
Is it?
Shirt, underscore size.
All right.
Well, be specific.
And we could limit this and say it's five characters at most, right?
Because we would have small, medium, large, extra, extra large, extra, extra, extra, large.
Do we want to support this many XL's?
Probably not.
But what about do we want to support the word medium?
That's more than five characters.
It just depends on how we structure things.
So I'll keep it short here.
Why not?
And then we have last worn, which is an integer, right?
It's a number of days ago we wore the shirt OC.
So if we run this query or really queries the entire script, we're we're going to create an empty database,
use that empty database, and then inside of there, create the shirts table.
And why don't we do a describe shirts afterwards and just make sure we see something printed out here.
We'll know that it worked.
So I'm going to run the entire script.
If you're in workbench, you do it using that lightning bolt here.
This one to the right there, so tiny, but this one to the right only runs where your cursor is.
This will run the entire file.
But remember, you can do this one line at a time directly in the shell.
It doesn't matter.
Okay, Let's see if I have any errors.
It looks good.
And we get back our results from describe at the bottom and we have shirt.
ID is an integer.
Can't be null.
It's a primary key.
Let's go over here and remember how we can tell what database we're using.
Select database.
We're still using pet shop over here.
If I do show databases, we should see shirt or shirts.
DB Let's use it.
And of course now if I select database, we see we're using shirts.
DB Let's do a show tables Great.
We see the shirts table.
Let's describe the shirts table.
There we are.
All right.
So the next thing I'd like to do is get our basic data in there.
So I gave you this.
If you copy it from the slides or from my code, what we want to do is take that and insert all of those
rows at once.
So I'm going to do this from a script again, but I don't want to put it in here and then run all of
this again, because I would create the database again, I would use it, I'd create the table again.
I'd run into errors if I try and do that, but I don't want to run all of that code again.
So I'm going to select it for now and comment it out.
So again, for me, that's command forward slash.
So it's still here.
So we have a record of it, but now if I run this entire file, this is not treated as code, it's completely
ignored.
So this is not valid code either, right?
This is going to be our values list.
But I need to do an insert into shirts and then I have to be clear on the order here.
So we have the article first, then the color and then the size and then the last worn.
So article, color, shirt to size.
I have to match the column names that I created and last worn, and then I'll have my values just like
that.
And I could keep this on its own line if I wanted to.
I could put that up there.
I could indent it all.
It looks a little janky as it is, but it's fine.
I copied and pasted it, so it's fine, but I'm going to put my semicolon in there.
Make sure this works just before I run it.
Make sure it seems like it should work.
Insert into shirts articles.
First color is next, shirt size and then last worn.
Let's try it.
Run the script.
Okay, let's head over here and let's just end the video with a simple select star from shirts.
Do we see H shirts?
Yes, we do.
And does the data make sense?
In other words, do we have colors showing up in shirt size or size?
Is showing up in article?
No, we don't.
It's working the way we expected it.
So now that we have our data in, the next thing we're going to do is insert a new shirt.
So I guess this is really the last thing that I want to do in this video, because then we move on to
reading and selecting.
But we've got one more part of the creating part of CRUD.
Add a new shirt, purple polo shirt, medium last worn 50 days ago, and maybe I'll do this one directly
here in the shell insert into shirts and we're going to have article color, shirt size last worn.
And then values.
And it was polo shirt, I think.
And then I don't remember.
Is there a dash?
No.
For whatever reason, polo shirt has a space.
If I'm just matching what I already have, which will be nice, because then I can select all polo shirts.
We have purple as the colour medium and last worn 50 days ago, so that should do it.
That's just double check polo shirt, purple m 50 polo shirts.
Well, purple.
There we are.
M 50 OC.
Let's select Star and make sure it's in there.
And there it is.
OC So all that I'm going to do is just copy that line that I ran and put it over here so that we have
a full record of everything we've done.
I have to get rid of this arrow though.
But you don't have to do that, right?
It's it's totally up to you where you run these individual queries.
From next step, we'll move on to the selecting part of this exercise solution.