Oh, right.
It's now your turn to get a little practice creating tables and working with column constraints.
So I'd like you to define a table called employees, and you should have the following columns.
ID last name, first name, middle name, age and current status.
So ID should be a primary key.
It should be an integer and it should automatically increment.
Last name, First name and middle name are all text, but last name and first name are mandatory.
They're required and middle name is not required.
Not everybody has a middle name.
Additionally, all these text columns that you see here, I didn't specify a size, but let's just say
100 characters for last name, first name, middle name and current status.
And then we have age.
Age is also a number we only know integer, so it's an integer for now and it is required.
And then current status would be if the employee is currently employed by us, if they are a contractor,
if they have been let go, if they're on paid leave or something.
But it is text, it is required, it's mandatory.
It cannot be null, but it will have a default value of employed.
So try making this and pause the video please, so that you do it and then I'll be back in 3 to 1 with
a solution.
Here we go.
So we're going to define a table.
I think I'm going to do this all in a file using one of my GIs like SQL workbench.
So I'm going to do a create table employees and employees, and I know that we have ID in here and ID
is going to be an integer, and I know that I want it to auto increment, so I can add that in.
Just remember it's auto underscore increment and then I have to decide how I'm going to make it a primary
key.
Am I going to do this?
Which is fine, and that's probably what I prefer.
Or am I going to do it after the fact?
Like this primary key ID, I think it's better in my opinion, just to do it when I'm creating and defining
the column.
So primary key, remember, there's an underscore for auto increment primary key, though there is not
OC Then we have last name, first name in middle name, all of which are var cars, they're text.
So let's start with last name of our car.
And I said 100 I believe.
But all that matters is that you pick some value that's consistent.
I mean, I guess that's not all that matters.
We don't want you to pick five characters, but we'll do last name, first name, middle name, just
like that.
Put my commas in there.
But then we know that last name and first name are required.
So we'll put in not no.
Same thing here, not null.
And then we have age, which is a required number.
So age is an int and it's also not null.
And then finally we have current status.
So current.
Status, which is a var car 100 and it cannot be null.
It's mandatory, so not null and we have a default value of employed.
Right.
Make sure employed.
Yep.
Okay.
So I think that should be it.
We can always run this entire script.
Or I guess you could just copy and paste it over in your terminal however you want to do this.
I'm doing it on the pet shop database.
Still, it doesn't matter because I'm going to drop the table at the end of this video, but let's make
sure that it works.
And also, here's my solution in this slide.
I guess we should compare and make sure I didn't miss anything when typing this out.
A semicolon.
We should put that in there.
But otherwise it's the same right current status of our car 100 not all default employed primary key
auto increment, all that stuff is there, so we should be good.
Let's just run this script.
So in in workbench, in SQL workbench or my SQL workbench, I can run the entire script by clicking
the lightning bolt.
This entire file will run.
Or if I have my cursor in some part of a single query or a single sort of unit of SQL, write one command
what we call a query.
I can use this other version here right next to the lightning bolt that will only run wherever my cursor
is.
So it doesn't matter because I have all this commented out anyway.
But if I do want to do that, I'll run it there.
We get a green checkmark.
Nothing went wrong as far as we can tell.
I could always just refresh over here.
I should have an employees table now and if I look at it, I can see my values down here.
But I'm going to head to my terminal and I'm going to do a describe employees.
And I'm too zoomed in for the size of this table.
There we go.
I zoomed out a bit, and now we can see all the different columns there type's just int and var cars
and then if they can be null or not.
And what our primary key is and what the default values are, if there are any.
And then lastly, auto increment.
So let's try and just insert an employee.
This is not part of the requirements at all for this exercise, but let's insert a new employee.
So insert into employees and let's say that I'm not going to insert the ID, right?
We have auto increment, so no need to do that.
But I'll do first name and last name and age because those are the only ones required, right?
First, last and age, current status has a default, so I'll rely on that default value.
So then I'll do values here.
And his name is Chicken Man, and that's his last name obviously, and his age is 87.
Thomas Chicken Man.
All right, let's select star from employees and make sure.
We see current status has a default value of employed.
Middle name is null because that's totally fine.
That means this person doesn't have a middle name again.
No, it's not always bad or it's not necessarily bad.
It's just a concept of no value.
And many people will have no middle name, right?
So we don't want to make it required.
We don't want to have a default.
I guess we could put a default in there, but why bother?
If NULL serves the purpose we need, let's just make sure that if I try and insert somebody, let's
say missing an age, that we're going to have a problem.
So let's get rid of age there and then it's duplicated, that's fine.
We'll have to Thomas Chicken Mans and but we get an error anyway, saying age doesn't have a default
value, you can't insert that.
So it's working or auto increment is working.
If I just insert another identical employee a couple of times, we should see if I select star from
employees that we have ID of one, two and then three.
So that's our little exercise for creating a table with all those different constraints, those little
add ons we saw auto increment, primary key, not null and default.
We use those all the time.