1 00:00:00,640 --> 00:00:04,200 Our tables exist and we've started in sorting some data. 2 00:00:04,200 --> 00:00:06,690 However, we've got a small problem. 3 00:00:06,690 --> 00:00:10,390 We've choose book another concert in Vancouver, Canada. 4 00:00:10,390 --> 00:00:12,600 But before we can add to our concerts table, 5 00:00:12,600 --> 00:00:15,310 we should really add a country column. 6 00:00:15,310 --> 00:00:19,130 Let's see how we can do that by using an ALTER TABLE statement. 7 00:00:19,130 --> 00:00:25,570 In SQLite, ALTER TABLE can be used to either rename a table or add a column. 8 00:00:25,570 --> 00:00:31,150 Let's start by clicking Reset on our Query to give us a nice, clean working space. 9 00:00:31,150 --> 00:00:31,985 And let's delete the comment. 10 00:00:34,072 --> 00:00:38,294 Then let's type ALTER TABLE followed by the name of the table, 11 00:00:38,294 --> 00:00:40,056 which for us is CONCERTS. 12 00:00:41,747 --> 00:00:45,870 Then to add a column, you just type add, and 13 00:00:45,870 --> 00:00:50,887 then specify the column you'd like to add, which for 14 00:00:50,887 --> 00:00:55,811 us is a country column with a type of VARCHAR 255. 15 00:00:56,920 --> 00:00:59,640 Now if we ran this, it would work fine. 16 00:00:59,640 --> 00:01:01,880 And we'd get a country column. 17 00:01:01,880 --> 00:01:04,810 But that country column would be completely empty for 18 00:01:04,810 --> 00:01:06,480 all of our already existing data. 19 00:01:07,480 --> 00:01:09,790 Wouldn't it be nice if we could use a default value? 20 00:01:10,810 --> 00:01:12,620 Well, we can. 21 00:01:12,620 --> 00:01:16,722 Whenever you're declaring a column, whether it's a CREATE statement or 22 00:01:16,722 --> 00:01:21,277 an ALTER statement, you could always add a default value by adding the word default 23 00:01:21,277 --> 00:01:22,399 and then the value. 24 00:01:22,399 --> 00:01:26,568 Let's default and then add USA and 25 00:01:26,568 --> 00:01:30,604 quotes followed by a semicolon. 26 00:01:32,602 --> 00:01:35,735 Then let's run it and go check out the concerts table. 27 00:01:38,436 --> 00:01:42,310 Awesome, we added the country column and thanks to our default, 28 00:01:42,310 --> 00:01:46,264 it's already populated with all the right values. 29 00:01:46,264 --> 00:01:47,594 Now to add that last concert. 30 00:01:49,961 --> 00:01:52,955 Back in the Query, let's get rid of that ALTER statement and 31 00:01:52,955 --> 00:01:56,137 paste in the INSERT statement from the teacher's notes below. 32 00:02:00,076 --> 00:02:03,043 Then let's run it., And there we go. 33 00:02:04,979 --> 00:02:06,954 We've got our first Canadian concert. 34 00:02:09,169 --> 00:02:10,813 Back on the query page, 35 00:02:10,813 --> 00:02:17,220 I mentioned earlier that the ALTER TABLE statement can be used to rename tables. 36 00:02:17,220 --> 00:02:19,600 Our tables are pretty well named, but 37 00:02:19,600 --> 00:02:23,250 let's investigate how you would do that by looking through the documentation. 38 00:02:24,380 --> 00:02:28,050 Here's the SQLite help page for the ALTER TABLE statement. 39 00:02:28,050 --> 00:02:31,780 At the top is a diagram showing us the syntax. 40 00:02:31,780 --> 00:02:34,675 To follow the diagram, you just start at the beginning and 41 00:02:34,675 --> 00:02:35,924 choose a path to the end. 42 00:02:37,169 --> 00:02:41,542 So for the statement we just used, we would go ALTER TABLE, 43 00:02:41,542 --> 00:02:45,400 skip around to the table name, which was concerts. 44 00:02:47,000 --> 00:02:54,660 Then we'd go ADD, skip the optional column keyword, and finally, define our column. 45 00:02:55,940 --> 00:02:59,940 And if we wanted to rename the table, then we'd use ALTER TABLE, 46 00:03:01,000 --> 00:03:07,570 then the table name followed by rename, to, and then the new table name. 47 00:03:07,570 --> 00:03:10,540 SQLite has these kinds of diagrams for everything. 48 00:03:10,540 --> 00:03:13,610 So if you're ever confused about syntax, or you just want to know 49 00:03:13,610 --> 00:03:17,860 all the different ways to write a create statement, then check out the docs. 50 00:03:17,860 --> 00:03:23,440 And with that, you should be ready to CREATE, ALTER, and DROP all on your own. 51 00:03:23,440 --> 00:03:23,994 Until next time.