Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Creating and Modifying Database Tables!
You have completed Creating and Modifying Database Tables!
Preview
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
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
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up