0 1 00:00:00,490 --> 00:00:00,920 All right. 1 2 00:00:00,930 --> 00:00:07,440 So in the last lesson you would have heard about the pros and cons of SQL databases as well 2 3 00:00:07,530 --> 00:00:12,920 as noSQL databases but nothing is truer than having tried it yourself. 3 4 00:00:12,930 --> 00:00:19,800 So let's have a go at creating a database using a SQL based database and seeing for ourselves exactly 4 5 00:00:19,800 --> 00:00:20,770 how it works. 5 6 00:00:21,790 --> 00:00:27,150 A great resource in terms of documentation for a SQL is on w3schools. 6 7 00:00:27,190 --> 00:00:33,960 So if you head over to w3schools.com/sql or just hit the tab here 7 8 00:00:33,970 --> 00:00:42,530 then you can see all of the SQL or the Structured Query Language syntax being documented here and 8 9 00:00:42,550 --> 00:00:48,670 it's a really good guide on getting familiar with this essentially new programming language. The way 9 10 00:00:48,670 --> 00:00:58,180 the SQL works is you have these keywords such as SELECT or FROM or CREATE or TABLE and you tend 10 11 00:00:58,180 --> 00:01:05,140 to write them in uppercase and using this Structured Query Language we can create tables, manipulate 11 12 00:01:05,140 --> 00:01:08,430 them, update, destroy, etc.. 12 13 00:01:08,740 --> 00:01:15,520 Now with every single type of database the main things that you'll be doing with it is simply create, 13 14 00:01:15,940 --> 00:01:21,070 read, update, destroy. Or in database lingo 14 15 00:01:21,070 --> 00:01:23,630 it's known as CRUD. 15 16 00:01:23,950 --> 00:01:30,430 So for every single database the first thing to do is to get yourself used to doing CRUD using that 16 17 00:01:30,430 --> 00:01:31,680 particular database. 17 18 00:01:31,870 --> 00:01:34,350 So let's try it out with a SQL based database. 18 19 00:01:37,660 --> 00:01:42,100 In the resources for this lesson you will find a link to 19 20 00:01:42,100 --> 00:01:43,340 sqliteonline 20 21 00:01:43,360 --> 00:01:44,530 .com 21 22 00:01:44,530 --> 00:01:51,010 and this is a website that creates a playground environment which allows you to try out using a SQL 22 23 00:01:51,010 --> 00:01:57,640 like database and to get familiar with some of the query language and just see how it works. 23 24 00:01:58,120 --> 00: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 25 00:02:05,500 --> 00: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 26 00:02:11,230 --> 00:02:18,970 or two records already pre-populated. There's only four fields or four columns and that's the id, the 26 27 00:02:18,970 --> 00:02:22,010 first name, last name, and the address of our customer. 27 28 00:02:22,990 --> 00: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 29 00:02:27,550 --> 00:02:29,780 on in the last lesson. 29 30 00:02:29,860 --> 00:02:36,780 We're going to recreate this Products table that we had but this time using real SQL code. 30 31 00:02:36,850 --> 00:02:40,860 So what do we do whenever we need to do something with a new piece of technology? 31 32 00:02:40,930 --> 00:02:42,680 We check out the documentation. 32 33 00:02:42,730 --> 00: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 34 00:02:48,040 --> 00:02:48,800 you can do. 34 35 00:02:49,060 --> 00:02:53,610 But in this case we want to create a new table to store our products. 35 36 00:02:53,860 --> 00:02:59,950 And if you scroll through it you'll find this section called SQL Create Table. And here is the syntax 36 37 00:02:59,980 --> 00:03:03,690 for how you would create a table using SQL. 37 38 00:03:04,090 --> 00:03:08,670 So the key word here are in all caps and that's CREATE TABLE. 38 39 00:03:08,890 --> 00: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 40 00:03:15,220 --> 00:03:20,170 we detail the names of each column and the data type that it will contain. 40 41 00:03:20,200 --> 00:03:22,970 And these are separated by commas. 41 42 00:03:22,990 --> 00:03:27,140 So let's try and create this table using what we just learned. 42 43 00:03:28,030 --> 00: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 44 00:03:34,390 --> 00:03:36,780 going to write our own SQL code. 44 45 00:03:36,910 --> 00:03:41,320 So remember the key words were CREATE and TABLE. 45 46 00:03:41,320 --> 00:03:45,370 So this creates a new table and then we provide the name of our table. 46 47 00:03:45,400 --> 00:03:50,170 So it's going to be called products and then we open a set of parentheses, 47 48 00:03:50,200 --> 00: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 49 00:03:57,310 --> 00:03:58,320 columns. 49 50 00:03:58,330 --> 00:04:00,950 So the first one is going to be an id 50 51 00:04:01,150 --> 00: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 52 00:04:07,870 --> 00:04:10,600 key for our products table. 52 53 00:04:10,600 --> 00: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 54 00:04:18,310 --> 00:04:20,650 and this is going to be a string. 54 55 00:04:20,650 --> 00:04:23,470 Now remember we're not writing Javascript here anymore 55 56 00:04:23,480 --> 00:04:30,300 so there's no colons and there's actually just a space between the name and the data type. 56 57 00:04:30,490 --> 00:04:33,880 And there's a comma in between each one of these columns. 57 58 00:04:33,970 --> 00:04:38,320 The next one we need to store is the price of a particular product. 58 59 00:04:38,500 --> 00:04:40,930 And so it's going to be called price. 59 60 00:04:40,930 --> 00:04:46,900 But the data type is actually going to be something slightly different because we want to store a data 60 61 00:04:46,900 --> 00:04:49,990 type that is going to hold a monetary value. 61 62 00:04:50,260 --> 00: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 63 00:04:55,660 --> 00:05:01,810 for SQL data types and there's loads and loads of different data types. 63 64 00:05:01,840 --> 00:05:08,220 The most commonly used ones are things such as string or text or characters of a particular size. 64 65 00:05:08,230 --> 00:05:15,670 So if you said cha (255) then it can only store up to 255 characters and you can limit your 65 66 00:05:15,670 --> 00:05:18,250 data in that way. 66 67 00:05:18,250 --> 00: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 68 00:05:24,280 --> 00:05:30,250 We can use a data type such as money or even small money to save our data type. 68 69 00:05:30,250 --> 00:05:36,550 And this will make it formatted in the same way that most prices are formatted with commas and decimal 69 70 00:05:36,550 --> 00:05:37,460 places. 70 71 00:05:37,780 --> 00: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 72 00:05:46,990 --> 00: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 73 00:05:54,100 --> 00:06:01,750 Again scrolling through the documentation you will find a section on SQL primary keys. And what this 73 74 00:06:01,750 --> 00:06:10,380 does is it allows a particular column to uniquely identify each record in a database. 74 75 00:06:10,450 --> 00:06:17,530 So that means that this record here with name of pen and price of 1.20 will be uniqueli 75 76 00:06:17,530 --> 00:06:22,580 identified by this 1 and there won't be another product with the id of 1. 76 77 00:06:22,600 --> 00: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 78 00:06:30,520 --> 00:06:38,830 have to set a particular column as the primary key for the table. In order to do that using SQL 78 79 00:06:38,830 --> 00:06:43,210 you can see that we have to write the word primary key, 79 80 00:06:43,210 --> 00:06:45,710 so these are special keywords, 80 81 00:06:45,820 --> 00:06:52,840 and then inside a set of parentheses we specify the field that is going to be the primary key which 81 82 00:06:52,930 --> 00:06:56,130 in our case is going to be this one called id. 82 83 00:06:56,230 --> 00: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 84 00:07:05,970 --> 00:07:07,740 that is going to be the primary key. 84 85 00:07:07,980 --> 00:07:11,340 So in our case it's again id. 85 86 00:07:11,430 --> 00:07:17,070 Now if you see in this documentation something else you might notice is that for when they created their 86 87 00:07:17,130 --> 00:07:22,280 id field, they added another keyword next to it which is not null. 87 88 00:07:22,290 --> 00:07:28,590 This guarantees that whenever new records are being created inside this table if the record doesn't 88 89 00:07:28,590 --> 00:07:34,080 provide an ID then it will not allow it to be created so it cannot be null. 89 90 00:07:34,350 --> 00: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 91 00:07:40,110 --> 00: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 92 00:07:46,590 --> 00:07:49,200 So let's go ahead and do that for our field as well. 92 93 00:07:49,260 --> 00: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 94 00:07:58,450 --> 00: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 95 00:08:04,180 --> 00:08:06,370 have any colons anywhere 95 96 00:08:06,370 --> 00:08:13,370 and then we can go ahead and click run. All going well you should end up with a new table. 96 97 00:08:13,440 --> 00: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 98 00:08:19,080 --> 00: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 99 00:08:25,200 --> 00:08:29,460 looks exactly the same as what you see on the screen. 99 100 00:08:29,460 --> 00:08:33,160 So now let's go ahead and check out this table products. 100 101 00:08:33,180 --> 00:08:38,580 So let's click on Show table and you can see we currently don't have anything for our table. 101 102 00:08:38,580 --> 00:08:40,310 It's completely empty. 102 103 00:08:40,470 --> 00:08:43,250 So we have to add some data into it. 103 104 00:08:43,350 --> 00: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 105 00:08:50,490 --> 00: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 106 00:08:58,830 --> 00:09:06,230 for how to add data or insert data into your table. As they say 106 107 00:09:06,240 --> 00:09:08,330 there's two ways of inserting data. 107 108 00:09:08,550 --> 00:09:15,270 One is you write INSERT INTO table_name and then you provide the names of the columns that you want 108 109 00:09:15,270 --> 00:09:19,860 to insert data to and then you provide the values one by one. 109 110 00:09:19,860 --> 00:09:22,120 Now this is a little bit more roundabout. 110 111 00:09:22,530 --> 00:09:29,070 If you were to insert values for each and every single column, then you don't actually need this part 111 112 00:09:29,220 --> 00:09:32,390 and you can simply just specify all the values. 112 113 00:09:32,400 --> 00:09:36,260 Let's go ahead and do that for our product table. 113 114 00:09:36,410 --> 00: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 115 00:09:44,210 --> 00:09:50,100 which is products with an 's' and then we're going to provide the values. 115 116 00:09:50,360 --> 00:09:55,430 And this is going to be contained inside a set of parentheses and then we separate each of the values 116 117 00:09:55,430 --> 00:09:56,550 with a comma. 117 118 00:09:56,840 --> 00: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 119 00:10:03,980 --> 00:10:04,800 that is pen. 119 120 00:10:04,910 --> 00: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 121 00:10:11,720 --> 00:10:17,600 that is pen and the last item is the price which is £1.20 121 122 00:10:17,630 --> 00:10:19,420 if you remember. 122 123 00:10:19,540 --> 00:10:21,130 So now check your code. 123 124 00:10:21,190 --> 00:10:27,340 Make sure that there's nothing at the end of the sentence especially no semicolons because this is 124 125 00:10:27,340 --> 00:10:31,350 actually expressed in SQL on pretty much the same line. 125 126 00:10:31,390 --> 00: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 127 00:10:37,750 --> 00:10:40,580 Once you've checked your code go ahead and hit run. 127 128 00:10:40,780 --> 00:10:45,730 And that should have added this value into our products. 128 129 00:10:45,730 --> 00: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 130 00:10:53,140 --> 00:10:59,420 name Pen, price 1.20 with an id of one. 130 131 00:10:59,630 --> 00: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 132 00:11:06,660 --> 00: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 133 00:11:12,900 --> 00: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 134 00:11:23,760 --> 00:11:25,140 of the columns 134 135 00:11:25,140 --> 00: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 136 00:11:32,250 --> 00:11:33,790 that you have values for. 136 137 00:11:33,990 --> 00:11:41,610 So we can insert into our products table the data that we have by specifying the columns that we have 137 138 00:11:41,610 --> 00:11:42,260 data for. 138 139 00:11:42,270 --> 00:11:48,150 So in this case we have data for the column for ID as well as name but that's it. 139 140 00:11:48,180 --> 00: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 141 00:11:55,080 --> 00:11:58,490 be 2 and the name will be the string that is 141 142 00:11:58,490 --> 00:12:02,210 Pencil. And now make sure you check your code. 142 143 00:12:02,230 --> 00: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 144 00:12:08,720 --> 00: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 145 00:12:15,670 --> 00:12:18,060 but it doesn't have a price. 145 146 00:12:18,070 --> 00:12:25,820 Price is actually null right now. Now remember earlier on when we created our products schema, 146 147 00:12:25,830 --> 00:12:33,450 so you if right click and say SQL schema, you can see that the id field cannot be null. 147 148 00:12:33,660 --> 00:12:43,620 So if we were to write the code for our products table and we said something like INSERT INTO products 148 149 00:12:45,050 --> 00:12:55,190 but we were only going to insert into the field that's name and price then provide the values which 149 150 00:12:55,190 --> 00:13:01,820 is, I don't know, Rubber and 1.30. 150 151 00:13:02,090 --> 00: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 152 00:13:10,280 --> 00:13:14,130 It's because products.id cannot be null. 152 153 00:13:14,240 --> 00: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 154 00:13:19,310 --> 00:13:20,780 this new record. 154 155 00:13:20,780 --> 00:13:27,020 This is just a little bit of validation to keep your database well organized and following the structure 155 156 00:13:27,110 --> 00:13:29,420 that we specified. 156 157 00:13:29,470 --> 00:13:35,200 So that was how you would create a new table using the Structured Query Language and also how you would 157 158 00:13:35,200 --> 00:13:40,090 start inserting pieces of data into your table. In the next lesson 158 159 00:13:40,090 --> 00: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 160 00:13:45,850 --> 00:13:47,020 pieces of data.