So now that we've seen how you would implement all of CRUD using SQL, the next thing to look at is 1
relationships in SQL. 2
So firstly I want you to add our pencil record back in 3
if you have deleted it. And if you have deleted it then you can see this as a challenge. 4
Pause the video and refer to this table to add back our record of the pencil. 5
So if you remember the key word is INSERT INTO and then the name of the table and then we specify the 6
values 7
which is going to be 2, 8
Pencil, 0.8. 9
And now if I hit run then we now have a complete table with two records. 10
The next thing I'm going to do is I'm going to create a new table and this one is going to be called 11
orders. This orders table has 12
a number of fields. It has an id field which is going to be an integer that is not null, 13
then it'll have a order number field which is going to be an integer and that allow us to keep track of 14
all of the orders that we ever receive at our shop. 15
Now the next one is quite interesting. 16
I'm going to specify something a field called customer_id. 17
And this is going to be an integer. 18
But this is the place where I'll store the customer who made this particular order. 19
So this will be a field that will act as a foreign key to our table which will point to a particular 20
record in our customers table. 21
And I'm also going to do that with a product id as well to form a relationship with our products table. 22
Now as you saw before we can set a primary key for our current table by opening a set of parentheses 23
and putting in the name of the field that will act as the primary key, 24
so it will be the field id. But we can also specify a foreign key. 25
So the foreign key is going to be the key that's going to link all tables together. 26
This is what's going to establish the relationship. As they show you here they have customers and they 27
have orders. 28
And in the orders table if you have a person with id 3 you have a field that is the foreign key. 29
So for the orders table we know that this order was made by somebody in the persons table with an id 30
of 3. 31
So at a later point we can retrieve all of the data in that record that's associated with this order. 32
And this is how you establish relationships using SQL. 33
So in our case we can set our customer_id field as a foreign key which references the customers 34
table, 35
so this is going to be the name the table, and then inside some parentheses is going to be the name of 36
the field inside the customers table 37
that is the primary key. That's going to be the id field. 38
And finally we're going to do the same for our other field as well which is the product id field and 39
this is going to reference the products table again with its primary field which is called id. If the 40
concept of primary key and foreign key are foreign to you 41
then I recommend having a read of this documentation on w3schools to get to grips with the idea of 42
how you establish relationships using SQL via these keys. 43
So now if I hit run, I get an error and it tells me that I accidentally put in a closing curly brace 44
instead of a round bracket. 45
So now that's fixed. 46
Let's hit run again. 47
And we now have a orders table. 48
Now again orders table is completely empty but if we right click on it and we click show schema then 49
you can see it has only four fields. 50
First one the id is the primary key for this orders table and then two other fields act as foreign keys 51
which link this table orders with the customers table as well as the products table. 52
So now I'm going to create my first order record inside my orders table. It'll have an id of 1 an order 53
number of 4362 and the customer who bought it had an id in the customers table of 2 54
and the product had an id of 1. 55
If you take a look, in our customers table the customer with an id of 2 is me, 56
Angela Yu at 12 Sunset Drive and the product with an id of 1 is a Pen. 57
By establishing those relationships I can later create a much larger table where I join together all 58
of the relevant records and all of the useful columns from all three tables that I need. 59
So let me show you how that works by creating the first record. 60
So we'll use INSERT INTO the orders table and the values we want to inserts are 1 as the order id 61
the order number is 4362, the customer id is 2 and the product id is 1. 62
So now I'm going to go ahead and create my first order that's gone through. 63
So basically Angela Yu has bought a pencil that's all this represents. 64
But now we get to use something that's really powerful from SQL which is a join. So we can join our 65
tables together using the key word of join. 66
Now there's a whole bunch of different Joins left join, right join, full join. 67
But the most commonly used one is the inner join and that's the one that we're going to use. 68
We're going to join together the parts of our tables where a particular find key matches. 69
This is the syntax. 70
And if I copied over to our sqlliteonline.com, we're going to select the order number from the orders 71
table. 72
So we would say something like orders.the name of the field. 73
In our case our orders table is lowercase, orders.order_number. 74
The other field that we want is from our customers table and that's the first name, last name and address. 75
So we would write customers.first_name customers.last_name and customers 76
.address. Those are all the fields that we want to join together in a new table we're going to 77
create and it's going to be from the foreign keys inside our orders table. 78
So that's what we'll say here. 79
From the orders table is where you'll find this particular foreign key match. 80
So the next thing is inner join and the table we want to join is our customers table. And after the key 81
word on is going to be the fields that will match. It's going to be the foreign key in our orders table 82
that's called customer_id and it's going to match with the primary key on our customers 83
table which is called simply id. 84
Now if I hit run you get a join table where you've got the order number, the first name, last name, address 85
and you can see that these fields come from different tables. 86
But we've now managed to search through all of our tables and records and we've managed to match up 87
the orders and join them in a new table which is way more useful for us. 88
So for example if we wanted to dispatch order number 4362 then we'll know who is going to be sent 89
to and what their address is. Next 90
ss it going to be a challenge for you. 91
You can see that currently we only have the order joined with the customer. As a challenge 92
I want you to join the order with the product that the order is relating to. Pause the video now and 93
try to change the code so that you can complete this challenge. 94
OK so in this case we're no longer joining the orders table with the customers table. 95
Instead we're joining the orders table with the products table and the fields that we're interested 96
in is the order number as well as some of the fields from our products table. 97
What does our product table have? 98
Well it's got the name, the price and stock quantity. 99
So let's go and join those fields together. 100
It'll be products.name products.price products.stock. 101
And we're joining from the foreign keys in the orders table, we're performing a inner join and we're 102
joining it to the products table and the fields that have to match is orders.product_ 103
id and in the products table the field is simply called id. 104
So products with an 's' is the name of our table 105
as you can see here and it has a field called id which will match with the foreign key in our orders 106
table called product id. 107
So now if we hit run 108
you can see we get the order number but we now join the orders table with the products table and we 109
now have a brand new table that's created based off this inner join. 110
So that is a little bit on the magic of SQL database relationships. 111
And you can see how flexible and how powerful these relationships are when you are searching through 112
your database and you're trying to assemble all of your data from various different tables. 113
Now if you had any problems following along with the tutorial so far or if you just want to have a copy 114
of this completed table then in the resources for this lesson there is a link to a SQL online version 115
where it contains everything that we've done so far including all of the data in our orders, customers 116
and products tables. 117
So you can try out different bits of code from w3schools or anywhere else on it and have a play around 118
with SQL lite.