WEBVTT 1 00:00:00.000 --> 00:00:01.410 Hi Guys, and welcome back. 2 00:00:01.410 --> 00:00:02.800 In this video, we're going to learn 3 00:00:02.800 --> 00:00:05.010 about the CREATE TABLE command 4 00:00:05.010 --> 00:00:08.540 that we've already seen in the SQLite Viewer. 5 00:00:08.540 --> 00:00:10.280 We're going to explore what it means 6 00:00:10.280 --> 00:00:14.330 and how we can code our own CREATE TABLE commands. 7 00:00:14.330 --> 00:00:16.870 So the CREATE TABLE command is pretty straightforward, 8 00:00:16.870 --> 00:00:17.900 all things considered. 9 00:00:17.900 --> 00:00:21.200 We've got CREATE TABLE accounts in this case, 10 00:00:21.200 --> 00:00:24.440 and that tells Postgres that we want to create a new table, 11 00:00:24.440 --> 00:00:27.120 and the new table name should be accounts. 12 00:00:27.120 --> 00:00:28.640 Then we give Postgres 13 00:00:28.640 --> 00:00:30.560 the columns between brackets. 14 00:00:30.560 --> 00:00:32.410 We give each column name 15 00:00:32.410 --> 00:00:35.920 and the type of data that we want to store in that column. 16 00:00:35.920 --> 00:00:38.350 So for example, TEXT or INTEGER. 17 00:00:38.350 --> 00:00:40.820 Notice that the column name and data types 18 00:00:40.820 --> 00:00:42.090 are separated by commas. 19 00:00:42.090 --> 00:00:44.390 So here we're creating two columns 20 00:00:44.390 --> 00:00:45.940 with two distinct data types. 21 00:00:45.940 --> 00:00:47.300 In this case, they're the same data type, 22 00:00:47.300 --> 00:00:48.930 but they could be different. 23 00:00:48.930 --> 00:00:51.120 Finally, we've got this semicolon at the end 24 00:00:51.120 --> 00:00:52.810 to signal the end of the query. 25 00:00:52.810 --> 00:00:53.860 This is quite important, 26 00:00:53.860 --> 00:00:57.770 and most queries are going to have a semicolon at the end. 27 00:00:57.770 --> 00:00:59.130 There are other types of data 28 00:00:59.130 --> 00:01:01.980 and every RDBMS has different types of data, 29 00:01:01.980 --> 00:01:03.420 although they're often quite similar. 30 00:01:03.420 --> 00:01:05.460 And a lot of the different data types 31 00:01:05.460 --> 00:01:07.470 that an RDBMS can have, 32 00:01:07.470 --> 00:01:09.130 sometimes are quite similar as well. 33 00:01:09.130 --> 00:01:12.500 So you have to sort of be careful with which one you pick. 34 00:01:12.500 --> 00:01:14.030 But SQLite is pretty straightforward. 35 00:01:14.030 --> 00:01:17.050 There's only five major types of data in SQLite. 36 00:01:17.050 --> 00:01:19.060 There's the INTEGER for numbers, 37 00:01:19.060 --> 00:01:21.390 There's TEXt for text. 38 00:01:21.390 --> 00:01:23.320 There's BLOB for binary data, 39 00:01:23.320 --> 00:01:25.810 or data that doesn't really have any structure. 40 00:01:25.810 --> 00:01:29.750 There's REAL and NUMERIC for other types of number as well. 41 00:01:29.750 --> 00:01:31.100 Now in CREATE TABLE, 42 00:01:31.100 --> 00:01:32.960 we've also got a clause that's pretty important, 43 00:01:32.960 --> 00:01:35.530 the IF NOT EXISTS clause. 44 00:01:35.530 --> 00:01:37.150 So if you're trying to create a table, 45 00:01:37.150 --> 00:01:38.250 but it already exists, 46 00:01:38.250 --> 00:01:39.810 you're always gonna get an error. 47 00:01:39.810 --> 00:01:42.220 But if we use the IF NOT EXISTS clause, 48 00:01:42.220 --> 00:01:44.090 then we won't get an error. 49 00:01:44.090 --> 00:01:45.710 So this is what that looks like. 50 00:01:45.710 --> 00:01:48.400 CREATE TABLE IF NOT EXISTS, 51 00:01:48.400 --> 00:01:50.360 then the table name and then the columns. 52 00:01:50.360 --> 00:01:51.610 So you can see that this is starting 53 00:01:51.610 --> 00:01:53.728 to not look like English at all. 54 00:01:53.728 --> 00:01:54.760 Remember at the start of this section 55 00:01:54.760 --> 00:01:57.380 we said that SQL looks a bit like English, 56 00:01:57.380 --> 00:02:01.110 I wouldn't really say IF NOT EXISTS in normal conversation. 57 00:02:01.110 --> 00:02:04.160 But nonetheless, this is how the command is written. 58 00:02:04.160 --> 00:02:06.570 All right, let's go over to the editor 59 00:02:06.570 --> 00:02:10.040 and look at writing this code ourselves in there. 60 00:02:10.040 --> 00:02:13.510 So here we are in the data.db file, 61 00:02:13.510 --> 00:02:16.290 and I've deleted all the tables and everything else. 62 00:02:16.290 --> 00:02:19.550 So we're going to go ahead and execute some SQL, 63 00:02:19.550 --> 00:02:23.060 and we're going to type away our CREATE TABLE command. 64 00:02:23.060 --> 00:02:24.730 This is just to show you how it works, 65 00:02:24.730 --> 00:02:28.520 how you can do this in your own data viewer. 66 00:02:28.520 --> 00:02:30.780 And so the CREATE TABLE commands always start 67 00:02:30.780 --> 00:02:33.450 with CREATE TABLE. 68 00:02:33.450 --> 00:02:34.900 Now, once we've got that, 69 00:02:34.900 --> 00:02:36.440 we type the table name, 70 00:02:36.440 --> 00:02:38.670 which is gonna be users in this case, 71 00:02:38.670 --> 00:02:41.200 and then between a pair of brackets, 72 00:02:41.200 --> 00:02:44.980 we're going to type the columns of the table. 73 00:02:44.980 --> 00:02:47.830 So here we're gonna have first underscore name, 74 00:02:47.830 --> 00:02:50.370 and then the data type which is TEXT, 75 00:02:50.370 --> 00:02:52.580 and then surname and the datatype, 76 00:02:52.580 --> 00:02:54.020 which is TEXT. 77 00:02:54.020 --> 00:02:56.510 Remember there's a comma separating the columns. 78 00:02:56.510 --> 00:03:00.240 So here we've got two distinct columns. 79 00:03:00.240 --> 00:03:02.330 Alright, now we can run this query. 80 00:03:02.330 --> 00:03:03.900 And we should see that the result 81 00:03:03.900 --> 00:03:06.660 is that the query executed successfully. 82 00:03:06.660 --> 00:03:08.530 Now, because it executed successfully, 83 00:03:08.530 --> 00:03:09.990 we have a table created. 84 00:03:09.990 --> 00:03:11.690 If we go over to database structure, 85 00:03:11.690 --> 00:03:14.660 we will see that the table now exists. 86 00:03:14.660 --> 00:03:18.020 Let's go back to execute SQL and try to run this again. 87 00:03:18.020 --> 00:03:20.470 And now you'll see that we get an error. 88 00:03:20.470 --> 00:03:23.460 It says, "Result: table users already exists." 89 00:03:23.460 --> 00:03:24.700 So the error is pretty nice. 90 00:03:24.700 --> 00:03:26.880 And it tells you where the error happened 91 00:03:26.880 --> 00:03:30.600 or the start of the query that caused this error to happen. 92 00:03:30.600 --> 00:03:34.180 So it says the CREATE TABLE users is the start of the error. 93 00:03:34.180 --> 00:03:35.680 Again, as we saw in the presentation, 94 00:03:35.680 --> 00:03:39.500 we can say CREATE TABLE IF NOT EXISTS. 95 00:03:39.500 --> 00:03:43.130 And then we can run this and there'll be no error. 96 00:03:43.130 --> 00:03:46.000 You can see that the query now executed successfully. 97 00:03:46.000 --> 00:03:48.700 And it doesn't tell us that it didn't do anything. 98 00:03:48.700 --> 00:03:50.290 It just said that it worked. 99 00:03:50.290 --> 00:03:52.520 What you asked for happened, 100 00:03:52.520 --> 00:03:54.880 which is that we created the table if it didn't exist, 101 00:03:54.880 --> 00:03:57.530 and it just so happened that he did exist in this occasion. 102 00:03:57.530 --> 00:04:00.310 All right, that is how you can run these commands here 103 00:04:00.310 --> 00:04:03.030 on DB Browser for SQLite. 104 00:04:03.030 --> 00:04:05.070 And remember to keep practising these commands, 105 00:04:05.070 --> 00:04:07.650 and don't forget them so that later on 106 00:04:07.650 --> 00:04:10.450 when you do need to use them in a proper application, 107 00:04:10.450 --> 00:04:12.380 you'll remember how to do that. 108 00:04:12.380 --> 00:04:13.310 Thanks for joining me. 109 00:04:13.310 --> 00:04:14.860 I'll see you in the next video.