All right. 1
So in the last lesson you would have heard about the pros and cons of SQL databases as well 2
as noSQL databases but nothing is truer than having tried it yourself. 3
So let's have a go at creating a database using a SQL based database and seeing for ourselves exactly 4
how it works. 5
A great resource in terms of documentation for a SQL is on w3schools. 6
So if you head over to w3schools.com/sql or just hit the tab here 7
then you can see all of the SQL or the Structured Query Language syntax being documented here and 8
it's a really good guide on getting familiar with this essentially new programming language. The way 9
the SQL works is you have these keywords such as SELECT or FROM or CREATE or TABLE and you tend 10
to write them in uppercase and using this Structured Query Language we can create tables, manipulate 11
them, update, destroy, etc.. 12
Now with every single type of database the main things that you'll be doing with it is simply create, 13
read, update, destroy. Or in database lingo 14
it's known as CRUD. 15
So for every single database the first thing to do is to get yourself used to doing CRUD using that 16
particular database. 17
So let's try it out with a SQL based database. 18
In the resources for this lesson you will find a link to 19
sqliteonline 20
.com 21
and this is a website that creates a playground environment which allows you to try out using a SQL 22
like database and to get familiar with some of the query language and just see how it works. 23
If you click on the link you will get taken to this database where I've already created a single table called customers. 24
And if you right click on customers and click on Show table then you can see that it's got two rows 25
or two records already pre-populated. There's only four fields or four columns and that's the id, the 26
first name, last name, and the address of our customer. 27
The first thing we're going to do is we're going to replicate that table structure that we saw earlier 28
on in the last lesson. 29
We're going to recreate this Products table that we had but this time using real SQL code. 30
So what do we do whenever we need to do something with a new piece of technology? 31
We check out the documentation. 32
If you scroll through this left side pane, you can see that there's a whole lot of different things that 33
you can do. 34
But in this case we want to create a new table to store our products. 35
And if you scroll through it you'll find this section called SQL Create Table. And here is the syntax 36
for how you would create a table using SQL. 37
So the key word here are in all caps and that's CREATE TABLE. 38
And then we provide a name for the table and then we open a set of parentheses and inside the parentheses 39
we detail the names of each column and the data type that it will contain. 40
And these are separated by commas. 41
So let's try and create this table using what we just learned. 42
If we head into our SQL-like browser and would delete all the code that's in this section and we're 43
going to write our own SQL code. 44
So remember the key words were CREATE and TABLE. 45
So this creates a new table and then we provide the name of our table. 46
So it's going to be called products and then we open a set of parentheses, 47
so round brackets. And inside the round brackets we add in all of the names and the data types of our 48
columns. 49
So the first one is going to be an id 50
and this is going to be an integer data type and that is going to store the unique id or the primary 51
key for our products table. 52
So we'll be able to identify each row by its id. The next one we're going to call name 53
and this is going to be a string. 54
Now remember we're not writing Javascript here anymore 55
so there's no colons and there's actually just a space between the name and the data type. 56
And there's a comma in between each one of these columns. 57
The next one we need to store is the price of a particular product. 58
And so it's going to be called price. 59
But the data type is actually going to be something slightly different because we want to store a data 60
type that is going to hold a monetary value. 61
If you head back to the documentation and you scroll all the way down you can see that there's a reference 62
for SQL data types and there's loads and loads of different data types. 63
The most commonly used ones are things such as string or text or characters of a particular size. 64
So if you said cha (255) then it can only store up to 255 characters and you can limit your 65
data in that way. 66
Now if you scroll down to the number data types, you can see one of these is really applicable to us. 67
We can use a data type such as money or even small money to save our data type. 68
And this will make it formatted in the same way that most prices are formatted with commas and decimal 69
places. 70
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
that we're going to add to our schema or the structure of our table is something called a primary key. 72
Again scrolling through the documentation you will find a section on SQL primary keys. And what this 73
does is it allows a particular column to uniquely identify each record in a database. 74
So that means that this record here with name of pen and price of 1.20 will be uniqueli 75
identified by this 1 and there won't be another product with the id of 1. 76
So whenever we say products with id of 1 then it refers to one specific record. And to do this we 77
have to set a particular column as the primary key for the table. In order to do that using SQL 78
you can see that we have to write the word primary key, 79
so these are special keywords, 80
and then inside a set of parentheses we specify the field that is going to be the primary key which 81
in our case is going to be this one called id. 82
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
that is going to be the primary key. 84
So in our case it's again id. 85
Now if you see in this documentation something else you might notice is that for when they created their 86
id field, they added another keyword next to it which is not null. 87
This guarantees that whenever new records are being created inside this table if the record doesn't 88
provide an ID then it will not allow it to be created so it cannot be null. 89
And this makes a lot of sense when you're setting a field as a primary key because if you had a record 90
that didn't even have an ID then that will be a big problem when you try to identify it later on. 91
So let's go ahead and do that for our field as well. 92
Let's make our ID field not null. Now that we've created the schema for our new table called products 93
we can go ahead, check to make sure that we've got a comma between each of these fields that we don't 94
have any colons anywhere 95
and then we can go ahead and click run. All going well you should end up with a new table. 96
Now if you end up with some errors down here with error messages then make sure you read the error message 97
and take a look back at the video and make sure that you haven't got any typos anywhere and that everything 98
looks exactly the same as what you see on the screen. 99
So now let's go ahead and check out this table products. 100
So let's click on Show table and you can see we currently don't have anything for our table. 101
It's completely empty. 102
So we have to add some data into it. 103
The data that we want to add is an id of 1, name of Pen and a price of 1.20. 104
Let's head back to our documentation and if you click on a SQL insert into, then you'll find the documentation 105
for how to add data or insert data into your table. As they say 106
there's two ways of inserting data. 107
One is you write INSERT INTO table_name and then you provide the names of the columns that you want 108
to insert data to and then you provide the values one by one. 109
Now this is a little bit more roundabout. 110
If you were to insert values for each and every single column, then you don't actually need this part 111
and you can simply just specify all the values. 112
Let's go ahead and do that for our product table. 113
So again let's delete this line and we're going to write INSERT INTO and then the name of our table 114
which is products with an 's' and then we're going to provide the values. 115
And this is going to be contained inside a set of parentheses and then we separate each of the values 116
with a comma. 117
The first value is going to be the id which is going to be 1 and then the second value is the string 118
that is pen. 119
So because it's a string it also has to be inside some quotation marks and we're going to put the string 120
that is pen and the last item is the price which is £1.20 121
if you remember. 122
So now check your code. 123
Make sure that there's nothing at the end of the sentence especially no semicolons because this is 124
actually expressed in SQL on pretty much the same line. 125
It's a single line statement but you'll often see it separated like this to make it easier to read. 126
Once you've checked your code go ahead and hit run. 127
And that should have added this value into our products. 128
So now if you right click on products and hit show table we now have a single record that is the product 129
name Pen, price 1.20 with an id of one. 130
Now if we wanted to skip a field so for example if we were to add another record which is not the product 131
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
for its price yet. If we wanted to insert into our table products but we don't yet have the value for all 133
of the columns 134
then you can simply open up a set of parentheses as you see over here and provide the names of the columns 135
that you have values for. 136
So we can insert into our products table the data that we have by specifying the columns that we have 137
data for. 138
So in this case we have data for the column for ID as well as name but that's it. 139
We don't have the price data. And The values will be contained in a set of parentheses and the id will 140
be 2 and the name will be the string that is 141
Pencil. And now make sure you check your code. 142
Make sure that all the key words are colored purple and in all caps and then go ahead and hit run. 143
And now when we show our products table again then you can see the second record has an id, has a name 144
but it doesn't have a price. 145
Price is actually null right now. Now remember earlier on when we created our products schema, 146
so you if right click and say SQL schema, you can see that the id field cannot be null. 147
So if we were to write the code for our products table and we said something like INSERT INTO products 148
but we were only going to insert into the field that's name and price then provide the values which 149
is, I don't know, Rubber and 1.30. 150
If I hit run you'll see that I get an error in here and it says, 'NOT NULL constraint failed'. 151
It's because products.id cannot be null. 152
And in this case we're making it NULL because we're not providing a value for it when we're creating 153
this new record. 154
This is just a little bit of validation to keep your database well organized and following the structure 155
that we specified. 156
So that was how you would create a new table using the Structured Query Language and also how you would 157
start inserting pieces of data into your table. In the next lesson 158
we're going to look at how we would read and how we would search through our table to find particular 159
pieces of data.