1
00:00:00,240 --> 00:00:04,150
Okay, So let's go ahead and try and define these tables.

2
00:00:04,170 --> 00:00:07,370
So we'll start with customers, which is simple ish.

3
00:00:07,380 --> 00:00:10,620
We have an ID that will be our primary key.

4
00:00:10,620 --> 00:00:13,920
Remember primary key there and it will auto increment.

5
00:00:13,920 --> 00:00:14,760
It's an integer.

6
00:00:14,760 --> 00:00:16,860
Then we'll have first name, last name and email.

7
00:00:16,890 --> 00:00:18,300
They'll all just be simple.

8
00:00:18,300 --> 00:00:19,920
Var chars var cars.

9
00:00:19,920 --> 00:00:21,090
Let's start there.

10
00:00:21,120 --> 00:00:29,550
So I'll create a table called customers and we know that we're going to have an ID in there.

11
00:00:30,910 --> 00:00:39,790
IDI is an int, and I'll just say that it's primary key auto underscore increment.

12
00:00:39,790 --> 00:00:44,350
And then we have first underscore name and last underscore name.

13
00:00:45,960 --> 00:00:48,090
So that will be a var cha.

14
00:00:48,990 --> 00:00:49,470
I don't know.

15
00:00:49,470 --> 00:00:53,010
Let's just say 50 and then last name.

16
00:00:53,160 --> 00:00:54,900
Var cha 50.

17
00:00:56,160 --> 00:00:58,320
And then finally email.

18
00:01:00,110 --> 00:01:03,710
And email is going to be a bar chart 50 as well.

19
00:01:04,010 --> 00:01:05,870
So that's our customers table.

20
00:01:06,380 --> 00:01:12,500
And now we need to work on the orders table, which the first three columns are self self explanatory

21
00:01:12,500 --> 00:01:16,820
or straightforward enough and ID order, date and amount.

22
00:01:16,820 --> 00:01:25,250
So let's do create table orders and we're going to have an ID at the beginning, just like we did with

23
00:01:25,250 --> 00:01:25,730
customers.

24
00:01:25,730 --> 00:01:34,310
So it's an integer primary key auto increment and then we're going to have order date.

25
00:01:36,210 --> 00:01:37,850
Which will make an actual date.

26
00:01:37,860 --> 00:01:40,500
We don't need date time or time or time stamps.

27
00:01:40,500 --> 00:01:47,880
We'll just do date and then amount and amount is going to be a decimal, I would imagine.

28
00:01:48,000 --> 00:01:52,440
Right, with up to two decimal points and let's do it.

29
00:01:52,440 --> 00:01:52,800
I don't know.

30
00:01:52,800 --> 00:01:55,470
Let's do like eight comma two.

31
00:01:57,240 --> 00:01:58,770
So that part's simple enough.

32
00:01:58,770 --> 00:02:00,180
That should all be review.

33
00:02:00,720 --> 00:02:04,680
But now we need to add a column in for the foreign key.

34
00:02:04,680 --> 00:02:07,530
So this column, we can call it customer ID.

35
00:02:07,560 --> 00:02:10,949
I mean, we definitely can't call it ID because we already have ID.

36
00:02:10,949 --> 00:02:12,630
And also that would be confusing.

37
00:02:12,630 --> 00:02:19,620
We need to make it clear that this column belongs to or at least is associated with the customer's table,

38
00:02:19,620 --> 00:02:20,850
doesn't belong to it.

39
00:02:20,850 --> 00:02:26,460
I shouldn't have said that, but it is associated with data in an ID from the customer's table, so

40
00:02:26,460 --> 00:02:29,280
we can just do customer ID as a column.

41
00:02:29,460 --> 00:02:31,620
And what type should it be?

42
00:02:31,920 --> 00:02:36,060
Well, it's an integer over here, so it should be an integer over here.

43
00:02:36,300 --> 00:02:41,820
If we're going to be storing one or two, which are the IDs from the customer table, then of course

44
00:02:41,820 --> 00:02:44,400
it should be the same integer format over here.

45
00:02:45,000 --> 00:02:48,510
Now I'm going to show you a couple of versions of this.

46
00:02:48,510 --> 00:02:50,610
This is the naive version for now.

47
00:02:50,880 --> 00:02:52,860
This technically works.

48
00:02:52,860 --> 00:02:56,640
You don't have to run this if you don't want to, but I'm going to create both of these tables.

49
00:02:57,330 --> 00:02:59,340
Let me go to my my SQL shell.

50
00:02:59,370 --> 00:03:02,880
I'm going to create a database first, create database.

51
00:03:02,890 --> 00:03:08,940
Just call it shop and we'll use shop.

52
00:03:09,720 --> 00:03:11,250
Then I'll paste those two in.

53
00:03:11,250 --> 00:03:12,960
We should now have two tables.

54
00:03:12,960 --> 00:03:14,160
Show tables.

55
00:03:14,160 --> 00:03:14,910
Great.

56
00:03:14,910 --> 00:03:21,000
So I have some pre written insert into statements and I'd like if you want to follow along, you should

57
00:03:21,000 --> 00:03:23,520
copy these so you'll have them following this video.

58
00:03:23,820 --> 00:03:30,150
Download the code or view the code, copy and paste these lines so that you have the same data as me.

59
00:03:30,210 --> 00:03:34,410
And of course it would be great to have some crazy complicated data set.

60
00:03:34,410 --> 00:03:39,540
But in order for us to understand the concepts, it really is beneficial to start small.

61
00:03:39,540 --> 00:03:45,810
So we know there's five different users or customers and there's five different orders, you'll quickly

62
00:03:45,810 --> 00:03:47,460
understand the data set.

63
00:03:47,460 --> 00:03:53,460
We'll be able to tell for doing things correctly or not, just based on having memorized what is in

64
00:03:53,460 --> 00:03:53,880
here.

65
00:03:53,940 --> 00:03:58,920
So we're going to have five different customers Boy, George, George Michael, David Bowie, Blue Steele,

66
00:03:58,950 --> 00:03:59,970
Bette Davis.

67
00:03:59,970 --> 00:04:02,640
And then we have five different orders.

68
00:04:02,640 --> 00:04:03,810
So here's one.

69
00:04:03,810 --> 00:04:04,680
Here's the other.

70
00:04:04,680 --> 00:04:11,160
Both of these are supposed to be placed by whoever has user ID one or customer ID one, which will be

71
00:04:11,160 --> 00:04:12,030
Boy George.

72
00:04:12,030 --> 00:04:15,960
Let's just go ahead and run this and you'll see what happens.

73
00:04:16,890 --> 00:04:20,160
Nothing special happens, but we have our data inserted.

74
00:04:20,160 --> 00:04:22,260
I'll select star from customers.

75
00:04:22,530 --> 00:04:23,100
Great.

76
00:04:23,100 --> 00:04:24,480
This is the easy table.

77
00:04:24,480 --> 00:04:26,280
There's no foreign key involved here.

78
00:04:26,370 --> 00:04:30,960
And then I'll select star from orders.

79
00:04:31,470 --> 00:04:32,190
And there we are.

80
00:04:32,220 --> 00:04:39,240
We have our five orders, each with a customer ID, which is supposed to be referencing an actual customer's

81
00:04:39,240 --> 00:04:39,720
ID.

82
00:04:39,750 --> 00:04:43,050
But the thing is, it doesn't have to write.

83
00:04:43,050 --> 00:04:46,890
I could easily insert into orders a new row.

84
00:04:46,890 --> 00:04:48,030
I'll just do it right now.

85
00:04:48,030 --> 00:04:59,130
Insert into orders a row that has an order date and an amount and a customer ID where the order date.

86
00:04:59,130 --> 00:05:05,430
Let's say it's this year 2022, 11, 11 and.

87
00:05:06,230 --> 00:05:07,280
Make sure I have the right format.

88
00:05:07,280 --> 00:05:17,180
Their amount will be, I don't know, 50.68, and then the customer ID is going to be 975.

89
00:05:17,600 --> 00:05:22,940
But there is definitely no customer with that ID, which would be a problem.

90
00:05:23,330 --> 00:05:29,840
We probably don't want that to happen, but nothing is stopping it except for my stupid syntax error.

91
00:05:29,840 --> 00:05:31,010
What did I mess up here?

92
00:05:31,010 --> 00:05:32,660
Insert into orders.

93
00:05:32,660 --> 00:05:33,740
There's an s there.

94
00:05:34,370 --> 00:05:35,720
That was bad timing, right?

95
00:05:35,720 --> 00:05:38,660
When I said nothing is stopping us, we actually did get stopped.

96
00:05:38,780 --> 00:05:45,560
So anyway, now nothing is stopping us from inserting a completely fictitious customer ID, and that

97
00:05:45,560 --> 00:05:46,670
might not seem that bad.

98
00:05:46,670 --> 00:05:49,850
So what if there's no ID or customer with that ID?

99
00:05:49,850 --> 00:05:55,280
But then when we start to write queries later on and we want to I mean, we probably want to have a

100
00:05:55,280 --> 00:06:00,260
customer associated with every order, we're going to have a bunch of missing data or data that doesn't

101
00:06:00,260 --> 00:06:01,100
make sense.

102
00:06:01,100 --> 00:06:08,660
We want to enforce a rule if we have a foreign key in the orders column or the orders table called customer

103
00:06:08,660 --> 00:06:15,470
ID that is supposed to be linked to a real customer in the customer's table, why don't we set up a

104
00:06:15,470 --> 00:06:21,590
rule or a constraint that says this has to be a real ID, We're going to do that.

105
00:06:21,590 --> 00:06:25,280
And the way that we do that is by using the foreign key constraint.

106
00:06:25,280 --> 00:06:29,510
So just like we have primary key primary key is relatively simple.

107
00:06:29,510 --> 00:06:32,960
It just says this ID in our case has to be unique.

108
00:06:33,350 --> 00:06:39,500
Now, when we set up a foreign key constraint, it doesn't have to be unique, but it does have to reference

109
00:06:39,530 --> 00:06:42,770
a real ID in the other table.

110
00:06:42,770 --> 00:06:45,230
So I'm going to say customer ID is an integer.

111
00:06:45,230 --> 00:06:46,220
I'm just going to leave that.

112
00:06:46,220 --> 00:06:50,240
But then below I'm going to add my foreign key constraint.

113
00:06:50,240 --> 00:06:53,030
So what we do is we write foreign key.

114
00:06:53,680 --> 00:07:00,850
And then in parentheses, the name of the column in this table that we're trying to add this constraint

115
00:07:00,850 --> 00:07:01,360
to.

116
00:07:01,450 --> 00:07:03,070
So this doesn't do a whole lot just yet.

117
00:07:03,070 --> 00:07:06,550
But we're saying this customer ID is a foreign key.

118
00:07:06,580 --> 00:07:11,770
But then the really important part is we say it's referencing references.

119
00:07:11,800 --> 00:07:13,890
The customer's table, right.

120
00:07:13,930 --> 00:07:20,080
The customer's table, this particular column ID and now no semicolon.

121
00:07:20,110 --> 00:07:22,390
Now, we have cemented this relationship.

122
00:07:22,390 --> 00:07:26,250
We have done essentially with this arrow shows here.

123
00:07:26,260 --> 00:07:29,850
We have told my SQL, this is not just any old integer.

124
00:07:29,860 --> 00:07:32,560
You can't just make 10,000 in here.

125
00:07:32,560 --> 00:07:39,250
You can't put nine, nine, nine as a customer ID unless there is truly an ID in the customer's table

126
00:07:39,250 --> 00:07:40,390
of 999.

127
00:07:41,020 --> 00:07:47,170
So whatever value we have in here has to be a valid existing customer.

128
00:07:47,320 --> 00:07:48,730
So let's test it out.

129
00:07:48,730 --> 00:07:51,370
Foreign key customer ID, which is one more time.

130
00:07:51,370 --> 00:07:57,490
That's what this column is called, is referencing the customer's table ID column.

131
00:07:58,900 --> 00:08:05,290
So I'm going to drop my two existing tables that I made, although I really don't have to drop customers,

132
00:08:05,290 --> 00:08:09,460
but I am going to do it just to start fresh so you could copy and paste this yourself if you want to

133
00:08:09,460 --> 00:08:10,000
run it.

134
00:08:10,330 --> 00:08:20,440
Let's just drop the table customers and I need a semicolon drop table orders and then I will create

135
00:08:20,440 --> 00:08:24,250
these two tables again and then I'll insert our data.

136
00:08:24,250 --> 00:08:26,330
This is perfectly valid data, right?

137
00:08:26,920 --> 00:08:28,120
We don't have to worry about this.

138
00:08:28,120 --> 00:08:29,800
I've written this data so that it works.

139
00:08:29,800 --> 00:08:32,919
We have ID of one, two and five.

140
00:08:32,980 --> 00:08:35,850
Well, this should have ID of one, two, three, four, five.

141
00:08:35,870 --> 00:08:38,980
So all of these should be real IDs.

142
00:08:39,340 --> 00:08:44,650
But now if I try and insert, let's just do a select star from order some more time.

143
00:08:46,420 --> 00:08:52,810
If I try and insert into orders, we'll do order date once again.

144
00:08:52,840 --> 00:08:53,710
Amount.

145
00:08:53,920 --> 00:08:55,330
Customer ID.

146
00:08:56,050 --> 00:08:58,630
I'll insert something again that doesn't exist.

147
00:08:58,630 --> 00:09:00,130
So order.

148
00:09:00,130 --> 00:09:11,380
Date 2022 1111 Amount 78.99 And then customer ID I'll insert 54.

149
00:09:11,380 --> 00:09:15,400
But there is no customer with that ID and I hit enter.

150
00:09:15,760 --> 00:09:16,780
We get an error.

151
00:09:16,810 --> 00:09:17,620
It doesn't work.

152
00:09:17,650 --> 00:09:19,540
Says cannot add or update a child row.

153
00:09:19,630 --> 00:09:22,180
A foreign key constraint fails.

154
00:09:22,750 --> 00:09:24,460
In other words, I can't do that.

155
00:09:24,460 --> 00:09:31,210
That customer ID you gave me is not a real customer ID so that is our first and most important step

156
00:09:31,210 --> 00:09:32,830
in setting up this relationship.

157
00:09:32,830 --> 00:09:40,720
Not only do we have to tables where we've added a relationship that we as humans and programmers have

158
00:09:40,720 --> 00:09:47,290
to keep in mind, we have cemented that relationship in my SQL in writing, we've said this customer

159
00:09:47,290 --> 00:09:54,310
ID column, every value has to be a real ID from the real customers table, a foreign table.

160
00:09:54,730 --> 00:09:58,390
A key from that table will be stored in orders.

161
00:09:58,780 --> 00:10:00,730
Okay, so we've set that up.

162
00:10:00,760 --> 00:10:01,750
Simple enough.

163
00:10:01,960 --> 00:10:07,870
I guess I should show it will work if I use an ID that is a valid users ID.

164
00:10:07,870 --> 00:10:12,820
So if we select star from I said users, but customers rather.

165
00:10:12,820 --> 00:10:16,960
If we take David Bowie, who doesn't have any orders right now.

166
00:10:16,990 --> 00:10:18,930
One one, two, two five.

167
00:10:18,940 --> 00:10:19,130
Right.

168
00:10:19,180 --> 00:10:22,270
There's no one, no orders with the customer ID of three.

169
00:10:22,600 --> 00:10:27,580
If I instead said insert with the customer ID of three, it works.

170
00:10:27,850 --> 00:10:29,170
So that's just proof.

171
00:10:29,200 --> 00:10:34,450
It's not going to reject every single ID, it only rejects the ones that don't actually exist in our

172
00:10:34,450 --> 00:10:35,680
customers table.

173
00:10:36,430 --> 00:10:42,850
Okay, so the next thing and the real meat of this section is understanding now that we have a relationship

174
00:10:42,850 --> 00:10:48,730
setup, this one to many relationship where every customer can have many orders, but each order belongs

175
00:10:48,730 --> 00:10:50,050
to one customer.

176
00:10:50,080 --> 00:10:52,360
How do we do some basic operations?

177
00:10:52,360 --> 00:10:53,740
How do we combine the two?

178
00:10:53,770 --> 00:10:58,810
How do I look at this row and write a query that tells me who ordered this?

179
00:10:58,810 --> 00:11:02,320
Or what about finding the total amount spent by each user?

180
00:11:02,320 --> 00:11:06,580
How do we work with these separate tables and join them together?

181
00:11:06,610 --> 00:11:07,540
That's coming up.

