All language subtitles for 001 SQL Commands CREATE Table and INSERT Data_en

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 a 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, etc..

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 SQL

like 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 key word 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 SQL-like browser and would 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 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 cha (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 small money 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 uniqueli

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 word 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 on 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 for 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 a 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 one.

Now if we wanted to skip a field so for example if we were to add another record which is not the 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 key words 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.