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