All right.
So in the last lesson, you would have heard about the pros and cons of SQL databases as well
as NoSQL databases, but nothing is truer than having tried it yourself.
So let's have a go at creating a database using a SQL-based database and seeing for ourselves exactly
how it works.
A great resource in terms of documentation for SQL is on W3Schools.
So if you head over to w3schools.com/sql or just hit the tab here,
then you can see all of the SQL or the Structured Query Language syntax being documented here, and
it's a really good guide on getting familiar with this essentially new programming language. The way
the SQL works is you have these keywords such as SELECT, or FROM, or CREATE, or TABLE and you tend
to write them in uppercase and using this Structured Query Language we can create tables, manipulate
them, update, destroy, et cetera.
Now, with every single type of database the main things that you'll be doing with it is simply Create,
Read, Update, Destroy, or in database lingo,
it's known as CRUD.
So for every single database the first thing to do is to get yourself used to doing CRUD using that
particular database.
So let's try it out with a SQL-based database.
In the resources for this lesson you will find a link to
sqliteonline.com
and
this is a website that creates a playground environment which allows you to try out using a
SQLite database and to get familiar with some of the query language and just see how it works.
If you click on the link you will get taken to this database where I've already created a single table called, customers,
and if you right-click on customers and click on Show table, then you can see that it's got two rows
or two records already pre-populated. There's only four fields or four columns and that's the id, the
first name, last name, and the address of our customer.
The first thing we're going to do is we're going to replicate that table structure that we saw earlier
on in the last lesson.
We're going to recreate this Products table that we had but this time using real SQL code.
So what do we do whenever we need to do something with a new piece of technology?
We check out the documentation.
If you scroll through this left side pane, you can see that there's a whole lot of different things that
you can do.
But in this case we want to create a new table to store our products.
And if you scroll through it you'll find this section called SQL Create Table. And here is the syntax
for how you would create a table using SQL.
So the keywords here, are in all caps and that's CREATE TABLE.
And then we provide a name for the table and then we open a set of parentheses and inside the parentheses
we detail the names of each column and the data type that it will contain.
And these are separated by commas.
So let's try and create this table using what we just learned.
If we head into our SQLite browser and we delete all the code that's in this section, and we're
going to write our own SQL code.
So remember the key words were CREATE and TABLE.
So this creates a new table and then we provide the name of our table.
So it's going to be called, products, and then we open a set of parentheses,
so round brackets. And inside the round brackets we add in all of the names and the data types of our
columns.
So the first one is going to be an id
and this is going to be an integer data type (INT) and that is going to store the Unique id or the Primary
Key for our products table.
So we'll be able to identify each row by its id. The next one we're going to call name,
and this is going to be a STRING.
Now, remember we're not writing Javascript here anymore,
so there's no colons and there's actually just a space between the name and the data type.
And there's a comma in between each one of these columns.
The next one we need to store is the price of a particular product.
And so it's going to be called price.
But the data type is actually going to be something slightly different because we want to store a data
type that is going to hold a monetary value.
If you head back to the documentation and you scroll all the way down, you can see that there's a reference
for SQL data types. And there's loads and loads of different data types,
the most commonly used ones are things such as string or text or characters of a particular size.
So if you said CHAR(255) then it can only store up to 255 characters and you can limit your
data in that way.
Now if you scroll down to the number data types, you can see one of these is really applicable to us.
We can use a data type such as money or even smallmoney to save our data type.
And this will make it formatted in the same way that most prices are formatted with commas and decimal
places.
Let's go back and let's say that the data format for price is going to be of type MONEY. The final thing
that we're going to add to our schema or the structure of our table is something called a Primary Key.
Again scrolling through the documentation you will find a section on SQL Primary Keys. And what this
does is it allows a particular column to uniquely identify each record in a database.
So that means that this record here with name of pen and price of 1.20 will be uniquely
identified by this 1, and there won't be another product with the id of 1.
So whenever we say products with id of 1, then it refers to one specific record. And to do this we
have to set a particular column as the Primary Key for the table. In order to do that using SQL
you can see that we have to write the words, PRIMARY KEY,
so these are special keywords,
and then inside a set of parentheses, we specify the field that is going to be the Primary Key which
in our case is going to be this one called id.
So let's go ahead and add PRIMARY KEY and a set of parentheses and then the name of the field, or of the column
that is going to be the PRIMARY KEY.
So in our case it's again id.
Now if you see in this documentation something else you might notice is that when they created their
id field, they added another keyword next to it which is NOT NULL.
This guarantees that whenever new records are being created inside this table if the record doesn't
provide an id, then it will not allow it to be created so it cannot be null.
And this makes a lot of sense when you're setting a field as a Primary Key, because if you had a record
that didn't even have an id, then that will be a big problem when you try to identify it later on.
So let's go ahead and do that for our field as well.
Let's make our id field not null. Now that we've created the schema for our new table called, products,
we can go ahead, check to make sure that we've got a comma between each of these fields, that we don't
have any colons anywhere,
and then we can go ahead and click Run. All going well you should end up with a new table.
Now if you end up with some errors down here with error messages then make sure you read the error message
and take a look back at the video and make sure that you haven't got any typos anywhere and that everything
looks exactly the same as what you see on the screen.
So now, let's go ahead and check out this table, products.
So let's click on Show table and you can see we currently don't have anything for our table.
It's completely empty.
So we have to add some data into it.
The data that we want to add is an id of 1, name of Pen and a price of 1.20.
Let's head back to our documentation and if you click on, "SQL Insert Into," then you'll find the documentation
for how to add data or insert data into your table. As they say
there's two ways of inserting data.
One is you write INSERT INTO table_name and then you provide the names of the columns that you want
to insert data to, and then you provide the values one by one.
Now this is a little bit more roundabout.
If you were to insert values for each and every single column, then you don't actually need this part
and you can simply just specify all the values.
Let's go ahead and do that for our product table.
So again let's delete this line and we're going to write INSERT INTO and then the name of our table
which is products with an 's' and then we're going to provide the VALUES.
And this is going to be contained inside a set of parentheses and then we separate each of the values
with a comma.
The first value is going to be the id which is going to be 1, and then the second value is the string
that is pen.
So because it's a string it also has to be inside some quotation marks and we're going to put the string
that is pen, and the last item is the price which is £1.20
if you remember.
So now check your code.
Make sure that there's nothing at the end of the sentence, especially no semicolons because this is
actually expressed in SQL on pretty much the same line.
It's a single-line statement but you'll often see it separated like this to make it easier to read.
Once you've checked your code go ahead and hit Run.
And that should have added this value into our products.
So now if you right-click on products and hit Show table, we now have a single record that is the product
name Pen, price £1.20 with an id of 1.
Now, if we wanted to skip a field so for example if we were to add another record, which is another product
we have which is pencil, but at the moment we haven't yet priced up the pencils so we don't have a value
for its price yet. If we wanted to INSERT INTO our table, products, but we don't yet have the value for all
of the columns,
then you can simply open up a set of parentheses as you see over here and provide the names of the columns
that you have values for.
So we can INSERT INTO our products table the data that we have by specifying the columns that we have
data for.
So in this case we have data for the column for id, as well as name, but that's it,
we don't have the price data. And the values will be contained in a set of parentheses and the id will
be 2 and the name will be the string that is,
"Pencil". And now make sure you check your code.
Make sure that all the keywords are colored purple and in all caps and then go ahead and hit Run.
And now when we show our products table again, then you can see the second record has an id, has a name,
but it doesn't have a price.
Price is actually NULL right now. Now remember earlier on when we created our products schema,
so you if right-click and say SQL Schema, you can see that the id field cannot be null.
So if we were to write the code for our products table and we said something like INSERT INTO products,
but we were only going to insert into the field that's name and price then provide the values which
is, I don't know, "Rubber" and £1.30,
if I hit Run you'll see that I get an error in here and it says, "NOT NULL constraint failed:".
It's because products.id cannot be null.
And in this case we're making it NULL because we're not providing a value for it when we're creating
this new record.
This is just a little bit of validation to keep your database well organized and following the structure
that we specified.
So that was how you would create a new table using the Structured Query Language and also how you would
start inserting pieces of data into your table. In the next lesson,
we're going to look at how we would read and how we would search through our table to find particular
pieces of data.