Altering Tables

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