Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this video we'll create the TICKET_HOLDERS table and learn about the AUTOINCREMENT keyword.
Insert Statements
INSERT INTO TICKET_HOLDERS VALUES (1, 'Jessica', 'Rodgers', 'jessrodg23@hotmail.com');
INSERT INTO TICKET_HOLDERS VALUES (2, 'Mary', 'O’Hara', 'mohara@gmail.com');
INSERT INTO TICKET_HOLDERS VALUES (3, 'Dominik', 'Smith', 'dsmith1120@aol.com');
INSERT INTO TICKET_HOLDERS VALUES (4, 'Marcella', 'Childs', 'mcchilds@gmail.com');
Related Links
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
One table down, two to go.
0:00
Now that we're done with
our CONCERTS table,
0:02
let's start by clicking reset
to give us a nice clean canvas.
0:05
And delete that comment again,
0:09
then let's move onto creating
our ticket holders table.
0:11
If you'd like to take a stab
at it yourself, pause me and
0:15
see what you come up with.
0:18
It should be mostly the same as what
we did with the CONCERTS table.
0:20
Okay, here's what I came up with,
first we're going
0:24
to create the table and
name it Ticket_Holders.
0:29
Then we're going to add the ID column,
give it an INT data type,
0:37
and then make it the PRIMARY KEY.
0:42
Since we'll probably have more
than 32,000 TICKET_HOLDERS,
0:45
we can't use small INT for the data type.
0:49
Instead, we should use INT,
0:52
which will let us have just over
two billion TICKET_HOLDERS.
0:54
Next, let's declare
the FIRST_NAME column as
0:58
a VARCHAR with space for
up to 255 characters.
1:03
Then let's copy and paste that line twice.
1:08
Rename FIRST_NAME to LAST_NAME and
FIRST_NAME to EMAIL,
1:17
get rid of this last comma,
and there we go.
1:23
Let's run it.
1:28
And now we've got a TICKET_HOLDERS table,
great work.
1:31
Back in the query, let's copy and
1:34
paste on the insert statements from the
teacher's notes to give us some test data.
1:36
Then let's run it and write, if we want
to recreate the table on each run,
1:41
we need to start by dropping the table.
1:47
Let's add a line at the top and
1:50
type, DROP TABLE TICKET_HOLDERS and
1:53
a semi colon, and
then try running it again.
1:58
Perfect, also,
if we'd like to check what's in the table,
2:04
we can always use a select statement.
2:08
Below our insert statements,
let's select everything from
2:10
the TICKET_HOLDERS table,
SELECT star FROM TICKET_HOLDERS.
2:16
Then if we run it again,
2:21
we should be able to see what's in
the table without needing to click on it.
2:23
Perfect, we're almost
done with this table,
2:28
there's just one more thing we should
talk about, and that's the ID column.
2:31
Each time we insert a new row,
we have to come up with a unique ID,
2:36
which can be kind of a hassle.
2:40
Luckily, SQL gives us an easy
way to deal with this by using
2:42
the AUTOINCREMENT keyword.
2:47
If you had AUTOINCREMENT after an integer
PRIMARY KEY, then anytime you try
2:49
to insert null it will just give you
the next number, let's try it out.
2:54
Let's have a space after PRIMARY KEY,
and then type AUTOINCREMENT.
2:59
And then instead of using ID's one, two,
3:06
three and four, let's go with one,
two, null and null.
3:12
And if we run it, we get an error that
3:19
AUTOINCREMENT is only allowed
on an INTEGER PRIMARY KEY.
3:22
You know how I've mentioned that
sequel it doesn't use data types?
3:27
Well, there is one exception and
it's the INTEGER PRIMARY KEY
3:31
if you declare an INTEGER PRIMARY KEY
sequel will actually
3:37
make sure that whatever goes
in there is an INTEGER.
3:42
So it makes sense that AUTOINCREMENT,
3:47
which requires integers would only be
allowed on an INTEGER PRIMARU KEY.
3:50
Okay, let's change INT to INTEGER,
3:56
and try running it again.
4:00
But now it's telling us that
there's no TICKET_HOLDERS tables.
4:06
Since last time we got an error
in our CREATE TABLE statement,
4:09
the table was dropped but
it never got re-created.
4:14
So now we're trying to drop a table
that doesn't exist, lucky for
4:19
us there's an easy solution that
helps make testing a lot easier.
4:24
And the DROP TABLE statement
right after the word table,
4:29
we just need to add IF EXISTS to make it
only drop the table if it actually exists.
4:34
Let's run it, and now we can finally
see our AUTOINCREMENT in action.
4:40
We didn't specify IDs for Dominik or
Marcella but thanks to AUTOINCREMENT,
4:46
the database was able to pick up where
we left off and give us three and four.
4:52
Pretty cool, right?
4:57
Now that we know how AUTOINCREMENT works,
let's go ahead and use null for
4:59
all the IDs, instead of just the last two.
5:03
Also while we're changing things,
5:10
just like we added IF EXISTS
to the DROP TABLE statement.
5:13
We can do something similar with
the CREATE TABLE statement,
5:16
by adding IF NOT EXIST
before the table name.
5:20
This way, we won't try to create
any already existing tables.
5:27
All right, let's run this one more
time to make sure everything works.
5:32
And in the next video,
5:37
we'll tie everything together
with the tickets table.
5:39
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up