WEBVTT 0 00:00.490 --> 00:00.920 All right. 1 00:00.930 --> 00:07.440 So in the last lesson you would have heard about the pros and cons of SQL databases as well 2 00:07.530 --> 00:12.920 as noSQL databases but nothing is truer than having tried it yourself. 3 00:12.930 --> 00:19.800 So let's have a go at creating a database using a SQL based database and seeing for ourselves exactly 4 00:19.800 --> 00:20.770 how it works. 5 00:21.790 --> 00:27.150 A great resource in terms of documentation for a SQL is on w3schools. 6 00:27.190 --> 00:33.960 So if you head over to w3schools.com/sql or just hit the tab here 7 00:33.970 --> 00:42.530 then you can see all of the SQL or the Structured Query Language syntax being documented here and 8 00:42.550 --> 00:48.670 it's a really good guide on getting familiar with this essentially new programming language. The way 9 00:48.670 --> 00:58.180 the SQL works is you have these keywords such as SELECT or FROM or CREATE or TABLE and you tend 10 00:58.180 --> 01:05.140 to write them in uppercase and using this Structured Query Language we can create tables, manipulate 11 01:05.140 --> 01:08.430 them, update, destroy, etc.. 12 01:08.740 --> 01:15.520 Now with every single type of database the main things that you'll be doing with it is simply create, 13 01:15.940 --> 01:21.070 read, update, destroy. Or in database lingo 14 01:21.070 --> 01:23.630 it's known as CRUD. 15 01:23.950 --> 01:30.430 So for every single database the first thing to do is to get yourself used to doing CRUD using that 16 01:30.430 --> 01:31.680 particular database. 17 01:31.870 --> 01:34.350 So let's try it out with a SQL based database. 18 01:37.660 --> 01:42.100 In the resources for this lesson you will find a link to 19 01:42.100 --> 01:43.340 sqliteonline 20 01:43.360 --> 01:44.530 .com 21 01:44.530 --> 01:51.010 and this is a website that creates a playground environment which allows you to try out using a SQL 22 01:51.010 --> 01:57.640 like database and to get familiar with some of the query language and just see how it works. 23 01:58.120 --> 02:05.300 If you click on the link you will get taken to this database where I've already created a single table called customers. 24 02:05.500 --> 02:11.230 And if you right click on customers and click on Show table then you can see that it's got two rows 25 02:11.230 --> 02:18.970 or two records already pre-populated. There's only four fields or four columns and that's the id, the 26 02:18.970 --> 02:22.010 first name, last name, and the address of our customer. 27 02:22.990 --> 02:27.550 The first thing we're going to do is we're going to replicate that table structure that we saw earlier 28 02:27.550 --> 02:29.780 on in the last lesson. 29 02:29.860 --> 02:36.780 We're going to recreate this Products table that we had but this time using real SQL code. 30 02:36.850 --> 02:40.860 So what do we do whenever we need to do something with a new piece of technology? 31 02:40.930 --> 02:42.680 We check out the documentation. 32 02:42.730 --> 02:48.040 If you scroll through this left side pane, you can see that there's a whole lot of different things that 33 02:48.040 --> 02:48.800 you can do. 34 02:49.060 --> 02:53.610 But in this case we want to create a new table to store our products. 35 02:53.860 --> 02:59.950 And if you scroll through it you'll find this section called SQL Create Table. And here is the syntax 36 02:59.980 --> 03:03.690 for how you would create a table using SQL. 37 03:04.090 --> 03:08.670 So the key word here are in all caps and that's CREATE TABLE. 38 03:08.890 --> 03:15.130 And then we provide a name for the table and then we open a set of parentheses and inside the parentheses 39 03:15.220 --> 03:20.170 we detail the names of each column and the data type that it will contain. 40 03:20.200 --> 03:22.970 And these are separated by commas. 41 03:22.990 --> 03:27.140 So let's try and create this table using what we just learned. 42 03:28.030 --> 03:34.390 If we head into our SQL-like browser and would delete all the code that's in this section and we're 43 03:34.390 --> 03:36.780 going to write our own SQL code. 44 03:36.910 --> 03:41.320 So remember the key words were CREATE and TABLE. 45 03:41.320 --> 03:45.370 So this creates a new table and then we provide the name of our table. 46 03:45.400 --> 03:50.170 So it's going to be called products and then we open a set of parentheses, 47 03:50.200 --> 03:57.260 so round brackets. And inside the round brackets we add in all of the names and the data types of our 48 03:57.310 --> 03:58.320 columns. 49 03:58.330 --> 04:00.950 So the first one is going to be an id 50 04:01.150 --> 04:07.870 and this is going to be an integer data type and that is going to store the unique id or the primary 51 04:07.870 --> 04:10.600 key for our products table. 52 04:10.600 --> 04:18.150 So we'll be able to identify each row by its id. The next one we're going to call name 53 04:18.310 --> 04:20.650 and this is going to be a string. 54 04:20.650 --> 04:23.470 Now remember we're not writing Javascript here anymore 55 04:23.480 --> 04:30.300 so there's no colons and there's actually just a space between the name and the data type. 56 04:30.490 --> 04:33.880 And there's a comma in between each one of these columns. 57 04:33.970 --> 04:38.320 The next one we need to store is the price of a particular product. 58 04:38.500 --> 04:40.930 And so it's going to be called price. 59 04:40.930 --> 04:46.900 But the data type is actually going to be something slightly different because we want to store a data 60 04:46.900 --> 04:49.990 type that is going to hold a monetary value. 61 04:50.260 --> 04:55.570 If you head back to the documentation and you scroll all the way down you can see that there's a reference 62 04:55.660 --> 05:01.810 for SQL data types and there's loads and loads of different data types. 63 05:01.840 --> 05:08.220 The most commonly used ones are things such as string or text or characters of a particular size. 64 05:08.230 --> 05:15.670 So if you said cha (255) then it can only store up to 255 characters and you can limit your 65 05:15.670 --> 05:18.250 data in that way. 66 05:18.250 --> 05:24.210 Now if you scroll down to the number data types, you can see one of these is really applicable to us. 67 05:24.280 --> 05:30.250 We can use a data type such as money or even small money to save our data type. 68 05:30.250 --> 05:36.550 And this will make it formatted in the same way that most prices are formatted with commas and decimal 69 05:36.550 --> 05:37.460 places. 70 05:37.780 --> 05:46.990 Let's go back and let's say that the data format for price is going to be of type money. The final thing 71 05:46.990 --> 05:53.980 that we're going to add to our schema or the structure of our table is something called a primary key. 72 05:54.100 --> 06:01.750 Again scrolling through the documentation you will find a section on SQL primary keys. And what this 73 06:01.750 --> 06:10.380 does is it allows a particular column to uniquely identify each record in a database. 74 06:10.450 --> 06:17.530 So that means that this record here with name of pen and price of 1.20 will be uniqueli 75 06:17.530 --> 06:22.580 identified by this 1 and there won't be another product with the id of 1. 76 06:22.600 --> 06:30.520 So whenever we say products with id of 1 then it refers to one specific record. And to do this we 77 06:30.520 --> 06:38.830 have to set a particular column as the primary key for the table. In order to do that using SQL 78 06:38.830 --> 06:43.210 you can see that we have to write the word primary key, 79 06:43.210 --> 06:45.710 so these are special keywords, 80 06:45.820 --> 06:52.840 and then inside a set of parentheses we specify the field that is going to be the primary key which 81 06:52.930 --> 06:56.130 in our case is going to be this one called id. 82 06:56.230 --> 07:05.910 So let's go ahead and add primary key and a set of parentheses and then the name of the field on the column 83 07:05.970 --> 07:07.740 that is going to be the primary key. 84 07:07.980 --> 07:11.340 So in our case it's again id. 85 07:11.430 --> 07:17.070 Now if you see in this documentation something else you might notice is that for when they created their 86 07:17.130 --> 07:22.280 id field, they added another keyword next to it which is not null. 87 07:22.290 --> 07:28.590 This guarantees that whenever new records are being created inside this table if the record doesn't 88 07:28.590 --> 07:34.080 provide an ID then it will not allow it to be created so it cannot be null. 89 07:34.350 --> 07:39.810 And this makes a lot of sense when you're setting a field as a primary key because if you had a record 90 07:40.110 --> 07:46.590 that didn't even have an ID then that will be a big problem when you try to identify it later on. 91 07:46.590 --> 07:49.200 So let's go ahead and do that for our field as well. 92 07:49.260 --> 07:58.370 Let's make our ID field not null. Now that we've created the schema for our new table called products 93 07:58.450 --> 08:04.180 we can go ahead, check to make sure that we've got a comma between each of these fields that we don't 94 08:04.180 --> 08:06.370 have any colons anywhere 95 08:06.370 --> 08:13.370 and then we can go ahead and click run. All going well you should end up with a new table. 96 08:13.440 --> 08:18.930 Now if you end up with some errors down here with error messages then make sure you read the error message 97 08:19.080 --> 08:25.200 and take a look back at the video and make sure that you haven't got any typos anywhere and that everything 98 08:25.200 --> 08:29.460 looks exactly the same as what you see on the screen. 99 08:29.460 --> 08:33.160 So now let's go ahead and check out this table products. 100 08:33.180 --> 08:38.580 So let's click on Show table and you can see we currently don't have anything for our table. 101 08:38.580 --> 08:40.310 It's completely empty. 102 08:40.470 --> 08:43.250 So we have to add some data into it. 103 08:43.350 --> 08:50.160 The data that we want to add is an id of 1, name of Pen and a price of 1.20. 104 08:50.490 --> 08:58.530 Let's head back to our documentation and if you click on a SQL insert into, then you'll find the documentation 105 08:58.830 --> 09:06.230 for how to add data or insert data into your table. As they say 106 09:06.240 --> 09:08.330 there's two ways of inserting data. 107 09:08.550 --> 09:15.270 One is you write INSERT INTO table_name and then you provide the names of the columns that you want 108 09:15.270 --> 09:19.860 to insert data to and then you provide the values one by one. 109 09:19.860 --> 09:22.120 Now this is a little bit more roundabout. 110 09:22.530 --> 09:29.070 If you were to insert values for each and every single column, then you don't actually need this part 111 09:29.220 --> 09:32.390 and you can simply just specify all the values. 112 09:32.400 --> 09:36.260 Let's go ahead and do that for our product table. 113 09:36.410 --> 09:44.210 So again let's delete this line and we're going to write INSERT INTO and then the name of our table 114 09:44.210 --> 09:50.100 which is products with an 's' and then we're going to provide the values. 115 09:50.360 --> 09:55.430 And this is going to be contained inside a set of parentheses and then we separate each of the values 116 09:55.430 --> 09:56.550 with a comma. 117 09:56.840 --> 10:03.980 The first value is going to be the id which is going to be 1 and then the second value is the string 118 10:03.980 --> 10:04.800 that is pen. 119 10:04.910 --> 10:11.720 So because it's a string it also has to be inside some quotation marks and we're going to put the string 120 10:11.720 --> 10:17.600 that is pen and the last item is the price which is £1.20 121 10:17.630 --> 10:19.420 if you remember. 122 10:19.540 --> 10:21.130 So now check your code. 123 10:21.190 --> 10:27.340 Make sure that there's nothing at the end of the sentence especially no semicolons because this is 124 10:27.340 --> 10:31.350 actually expressed in SQL on pretty much the same line. 125 10:31.390 --> 10:37.500 It's a single line statement but you'll often see it separated like this to make it easier to read. 126 10:37.750 --> 10:40.580 Once you've checked your code go ahead and hit run. 127 10:40.780 --> 10:45.730 And that should have added this value into our products. 128 10:45.730 --> 10:52.960 So now if you right click on products and hit show table we now have a single record that is the product 129 10:53.140 --> 10:59.420 name Pen, price 1.20 with an id of one. 130 10:59.630 --> 11:06.630 Now if we wanted to skip a field so for example if we were to add another record which is not the product 131 11:06.660 --> 11:12.900 we have which is pencil but at the moment we haven't yet priced up the pencils so we don't have a value 132 11:12.900 --> 11:23.700 for its price yet. If we wanted to insert into our table products but we don't yet have the value for all 133 11:23.760 --> 11:25.140 of the columns 134 11:25.140 --> 11:32.250 then you can simply open up a set of parentheses as you see over here and provide the names of the columns 135 11:32.250 --> 11:33.790 that you have values for. 136 11:33.990 --> 11:41.610 So we can insert into our products table the data that we have by specifying the columns that we have 137 11:41.610 --> 11:42.260 data for. 138 11:42.270 --> 11:48.150 So in this case we have data for the column for ID as well as name but that's it. 139 11:48.180 --> 11:55.080 We don't have the price data. And The values will be contained in a set of parentheses and the id will 140 11:55.080 --> 11:58.490 be 2 and the name will be the string that is 141 11:58.490 --> 12:02.210 Pencil. And now make sure you check your code. 142 12:02.230 --> 12:08.660 Make sure that all the key words are colored purple and in all caps and then go ahead and hit run. 143 12:08.720 --> 12:15.370 And now when we show our products table again then you can see the second record has an id, has a name 144 12:15.670 --> 12:18.060 but it doesn't have a price. 145 12:18.070 --> 12:25.820 Price is actually null right now. Now remember earlier on when we created our products schema, 146 12:25.830 --> 12:33.450 so you if right click and say SQL schema, you can see that the id field cannot be null. 147 12:33.660 --> 12:43.620 So if we were to write the code for our products table and we said something like INSERT INTO products 148 12:45.050 --> 12:55.190 but we were only going to insert into the field that's name and price then provide the values which 149 12:55.190 --> 13:01.820 is, I don't know, Rubber and 1.30. 150 13:02.090 --> 13:10.250 If I hit run you'll see that I get an error in here and it says, 'NOT NULL constraint failed'. 151 13:10.280 --> 13:14.130 It's because products.id cannot be null. 152 13:14.240 --> 13:19.310 And in this case we're making it NULL because we're not providing a value for it when we're creating 153 13:19.310 --> 13:20.780 this new record. 154 13:20.780 --> 13:27.020 This is just a little bit of validation to keep your database well organized and following the structure 155 13:27.110 --> 13:29.420 that we specified. 156 13:29.470 --> 13:35.200 So that was how you would create a new table using the Structured Query Language and also how you would 157 13:35.200 --> 13:40.090 start inserting pieces of data into your table. In the next lesson 158 13:40.090 --> 13:45.850 we're going to look at how we would read and how we would search through our table to find particular 159 13:45.850 --> 13:47.020 pieces of data.