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 learn about the ALTER table statement. We'll also take a minute to get familiar with the documentation for SQLite!
SQL for Copy/Paste
INSERT INTO CONCERTS VALUES (10, '2018-03-22', 'Vancouver', 'BC', 'BC Place', 'Canada');
Related Links
Our tables exist and
we've started in sorting some data.
0:00
However, we've got a small problem.
0:04
We've choose book another
concert in Vancouver, Canada.
0:06
But before we can add
to our concerts table,
0:10
we should really add a country column.
0:12
Let's see how we can do that by
using an ALTER TABLE statement.
0:15
In SQLite, ALTER TABLE can be used to
either rename a table or add a column.
0:19
Let's start by clicking Reset on our Query
to give us a nice, clean working space.
0:25
And let's delete the comment.
0:31
Then let's type ALTER TABLE
followed by the name of the table,
0:34
which for us is CONCERTS.
0:38
Then to add a column,
you just type add, and
0:41
then specify the column you'd like to add,
which for
0:45
us is a country column with
a type of VARCHAR 255.
0:50
Now if we ran this, it would work fine.
0:56
And we'd get a country column.
0:59
But that country column would
be completely empty for
1:01
all of our already existing data.
1:04
Wouldn't it be nice if we
could use a default value?
1:07
Well, we can.
1:10
Whenever you're declaring a column,
whether it's a CREATE statement or
1:12
an ALTER statement, you could always add
a default value by adding the word default
1:16
and then the value.
1:21
Let's default and then add USA and
1:22
quotes followed by a semicolon.
1:26
Then let's run it and
go check out the concerts table.
1:32
Awesome, we added the country column and
thanks to our default,
1:38
it's already populated
with all the right values.
1:42
Now to add that last concert.
1:46
Back in the Query,
let's get rid of that ALTER statement and
1:49
paste in the INSERT statement
from the teacher's notes below.
1:52
Then let's run it., And there we go.
2:00
We've got our first Canadian concert.
2:04
Back on the query page,
2:09
I mentioned earlier that the ALTER TABLE
statement can be used to rename tables.
2:10
Our tables are pretty well named, but
2:17
let's investigate how you would do that
by looking through the documentation.
2:19
Here's the SQLite help page for
the ALTER TABLE statement.
2:24
At the top is a diagram
showing us the syntax.
2:28
To follow the diagram,
you just start at the beginning and
2:31
choose a path to the end.
2:34
So for the statement we just used,
we would go ALTER TABLE,
2:37
skip around to the table name,
which was concerts.
2:41
Then we'd go ADD, skip the optional column
keyword, and finally, define our column.
2:47
And if we wanted to rename the table,
then we'd use ALTER TABLE,
2:55
then the table name followed by rename,
to, and then the new table name.
3:01
SQLite has these kinds of diagrams for
everything.
3:07
So if you're ever confused about syntax,
or you just want to know
3:10
all the different ways to write a create
statement, then check out the docs.
3:13
And with that, you should be ready to
CREATE, ALTER, and DROP all on your own.
3:17
Until next time.
3:23
You need to sign up for Treehouse in order to download course files.
Sign up