1
00:00:09,256 --> 00:00:10,536
>> Okay, good evening, everyone.

2
00:00:10,536 --> 00:00:14,086
I'm covering section
tonight on SQL.

3
00:00:15,146 --> 00:00:19,206
And first question I would
like to ask is what is SQL?

4
00:00:19,486 --> 00:00:21,936
Does anyone know what SQL is?

5
00:00:23,256 --> 00:00:27,936
SQL stands for structure
query language

6
00:00:27,936 --> 00:00:31,146
and it is a standard
language used

7
00:00:31,146 --> 00:00:33,886
to query relational databases.

8
00:00:35,796 --> 00:00:36,656
What does it look like?

9
00:00:36,656 --> 00:00:38,996
It looks like what
you see in green,

10
00:00:38,996 --> 00:00:42,446
the first green statement
is actually select statement

11
00:00:43,706 --> 00:00:44,346
in the sequel.

12
00:00:44,746 --> 00:00:48,876
And, and this example, we
are selecting all the rows

13
00:00:48,946 --> 00:00:52,616
from a table called employees
where an ID is equal to 5.

14
00:00:53,156 --> 00:00:59,166
The second point in this,
in this illustration states

15
00:00:59,166 --> 00:01:02,156
that a queries and alters
relational databases.

16
00:01:02,676 --> 00:01:05,546
The reason why I mention
alters is the fact

17
00:01:05,636 --> 00:01:09,696
that SQL does not only
query the database as it,

18
00:01:10,166 --> 00:01:11,536
as it statement intends,

19
00:01:11,996 --> 00:01:16,686
but it also does inserts
data into the database.

20
00:01:16,686 --> 00:01:17,896
It modifies data.

21
00:01:18,396 --> 00:01:23,016
It deletes data as well
as alters database,

22
00:01:23,156 --> 00:01:24,436
structures in the database.

23
00:01:24,576 --> 00:01:28,296
For example, it might add
some new fields to a table

24
00:01:28,296 --> 00:01:31,056
or remove some fields
or add a primary key,

25
00:01:31,226 --> 00:01:34,206
remove a primary key, and
a foreign key and so forth.

26
00:01:35,166 --> 00:01:41,646
Similarly, we have, I, I've
listed 2 sequel examples

27
00:01:41,686 --> 00:01:44,966
for how it's, would alter
or would query the database.

28
00:01:45,616 --> 00:01:48,736
When I say query, you would be
most likely using the select

29
00:01:49,646 --> 00:01:55,486
command and whereas alters could
be updates, insert, delete,

30
00:01:55,796 --> 00:01:58,346
drop add and so forth.

31
00:01:59,336 --> 00:02:04,986
Now, these relational databases
are typically stored in a piece

32
00:02:04,986 --> 00:02:08,516
of software that is named,

33
00:02:09,416 --> 00:02:12,796
that is called the relational
database management system.

34
00:02:13,056 --> 00:02:17,426
And what this does typically
is that it, the database,

35
00:02:17,426 --> 00:02:21,046
the RDBMS typically manages
the relational databases

36
00:02:21,046 --> 00:02:21,686
stored inside.

37
00:02:21,866 --> 00:02:24,756
So, it would do tasks
such as backups,

38
00:02:24,756 --> 00:02:26,016
replication and the like.

39
00:02:27,026 --> 00:02:33,646
The prominent, the prominent
RDBMSs out there are my sequel,

40
00:02:34,006 --> 00:02:37,506
sequel server, which is created
by Microsoft, Oracle by Oracle,

41
00:02:37,506 --> 00:02:40,206
MS Access, Microsoft Access

42
00:02:40,206 --> 00:02:42,686
which is obviously
created by Microsoft.

43
00:02:43,706 --> 00:02:49,236
And the last 3 that I mentioned
are actually proprietary

44
00:02:49,236 --> 00:02:50,736
and cost money to,
it costs money

45
00:02:50,776 --> 00:02:55,466
to actually implement them
whereas My sequel is free.

46
00:02:56,296 --> 00:02:57,676
My S, My SQL.

47
00:02:57,986 --> 00:02:59,106
That's how it's supposed
to be said.

48
00:02:59,886 --> 00:03:05,756
So, in, in this class, we
will be covering the My SQL

49
00:03:06,216 --> 00:03:08,846
relational database
management system.

50
00:03:08,846 --> 00:03:12,256
And in addition, we're,
we're going to be using a,

51
00:03:12,306 --> 00:03:15,366
an application, a web
application created in php

52
00:03:15,366 --> 00:03:17,736
to interface with this
relational database

53
00:03:17,736 --> 00:03:19,196
management system.

54
00:03:19,196 --> 00:03:22,666
The web application which we
will be using is actually called

55
00:03:22,666 --> 00:03:23,166
php my admin.

56
00:03:24,296 --> 00:03:30,746
And I will cover this
briefly and shortly.

57
00:03:31,166 --> 00:03:34,206
So, what are the most
used sequel commands?

58
00:03:35,716 --> 00:03:40,336
There are, in my opinion
there are 4 prominently used

59
00:03:40,336 --> 00:03:41,066
sequel commands.

60
00:03:41,846 --> 00:03:45,716
One is insert, the other is
update, delete and select.

61
00:03:46,176 --> 00:03:49,226
Think of, for example,
navigating to Facebook.

62
00:03:49,506 --> 00:03:51,896
Once you load the, the
first page, you're more,

63
00:03:52,006 --> 00:03:53,536
you're more than likely

64
00:03:53,776 --> 00:03:56,896
to encounter a select
command on the database side.

65
00:03:57,496 --> 00:03:59,966
Select was going to go
out and query information

66
00:03:59,966 --> 00:04:05,436
which will be pulled out
and rendered by the browser.

67
00:04:06,456 --> 00:04:08,126
Insert enters the data.

68
00:04:08,126 --> 00:04:10,196
So, in this same
example, let's say you,

69
00:04:10,336 --> 00:04:11,746
you posted a comment
that's going

70
00:04:11,746 --> 00:04:13,266
to be inserted in the database.

71
00:04:13,926 --> 00:04:17,566
Update will update an existing
piece of data for example,

72
00:04:18,806 --> 00:04:20,436
and delete will remove
something.

73
00:04:20,436 --> 00:04:22,336
Like, let's say you were
trying to remove a thread

74
00:04:22,336 --> 00:04:24,496
or an event and so forth.

75
00:04:24,666 --> 00:04:27,916
That would entail the usage
of something like delete.

76
00:04:28,766 --> 00:04:30,826
So, insert, update, delete

77
00:04:30,826 --> 00:04:34,656
and select are 4
prominent sequel commands.

78
00:04:35,786 --> 00:04:39,426
And the second 4 that I
find important to mention,

79
00:04:39,496 --> 00:04:43,316
important enough to mention,
are create, alter, add and drop.

80
00:04:43,786 --> 00:04:44,636
Although there are still,

81
00:04:44,636 --> 00:04:46,966
there are several
more sequel commends.

82
00:04:47,096 --> 00:04:50,616
But these, I mention these in
particular because they pertain

83
00:04:50,616 --> 00:04:51,756
to the database structure.

84
00:04:53,066 --> 00:04:54,586
And what do I mean by this?

85
00:04:54,586 --> 00:04:59,896
I mean in the case of you
wanting to create a database

86
00:04:59,896 --> 00:05:05,006
for example, you would use
the create command in the case

87
00:05:05,006 --> 00:05:07,016
that you would like to create a,

88
00:05:07,176 --> 00:05:09,356
a new table you would
use a create command.

89
00:05:10,266 --> 00:05:14,086
And for, if you choose to
for example alter a table,

90
00:05:14,086 --> 00:05:18,306
add some columns, remove
columns or add some constraints

91
00:05:18,836 --> 00:05:23,386
or fields, you would
use the add commands.

92
00:05:23,446 --> 00:05:30,676
And lastly, the drop command is
similar to a delete but it works

93
00:05:30,676 --> 00:05:32,576
on the database structure side.

94
00:05:32,606 --> 00:05:35,796
So, drop would, would
be used for example

95
00:05:35,796 --> 00:05:38,726
to remove a database, to
remove a table permanently

96
00:05:38,726 --> 00:05:43,436
from your system or to remove
constraints such as indexes,

97
00:05:43,626 --> 00:05:48,646
primary keys, foreign
keys and the like.

98
00:05:54,036 --> 00:05:56,046
Okay, so example time.

99
00:05:56,646 --> 00:06:04,586
I will go ahead and
pull up php my admin

100
00:06:06,576 --> 00:06:09,416
which should be included
in your appliance.

101
00:06:09,926 --> 00:06:15,826
And the way to access it is
simply to open a web browser

102
00:06:16,966 --> 00:06:24,966
and navigate to appliance
forward slash php my admin.

103
00:06:25,276 --> 00:06:27,426
First thing you will notice
is that you will be prompted

104
00:06:27,426 --> 00:06:28,606
for a user name and password.

105
00:06:28,606 --> 00:06:31,236
The appliance is
configured in such a way

106
00:06:31,326 --> 00:06:36,816
that the user name is j Harvard
and the password is crimson.

107
00:06:40,596 --> 00:06:43,966
So, this is the php my
admin web interface,

108
00:06:44,406 --> 00:06:48,086
which is right now interfacing

109
00:06:48,086 --> 00:06:51,716
with the my SQL database
management system.

110
00:06:56,296 --> 00:06:57,856
I would like to mention

111
00:06:57,856 --> 00:07:03,586
that right now we are using
the j Harvard user but for,

112
00:07:03,586 --> 00:07:07,206
for future usage and post
this class, if you choose

113
00:07:07,206 --> 00:07:13,656
to deploy your own databases and
so forth, you, I would advise

114
00:07:13,656 --> 00:07:17,596
that you create a separate user
for each additional database

115
00:07:18,516 --> 00:07:22,076
such that it will be, security
will be compartmentalized

116
00:07:22,126 --> 00:07:31,526
and it's much, it's much better
for the long term, I think.

117
00:07:31,626 --> 00:07:34,956
Okay, so I'm going to briefly
go over the, this interface,

118
00:07:34,956 --> 00:07:38,126
although David kind of
touched on this in class.

119
00:07:39,366 --> 00:07:44,846
The databases tab
enumerates or, you know,

120
00:07:44,996 --> 00:07:48,086
lists all the databases that,
that are currently living

121
00:07:48,086 --> 00:07:51,436
in your current installation
of my SQL.

122
00:07:53,356 --> 00:07:57,946
And this is also where you can
actually create a new database.

123
00:07:58,476 --> 00:08:01,616
If I click on here for example,

124
00:08:01,616 --> 00:08:06,736
I can type in let's say CS
75 test would be the name

125
00:08:06,736 --> 00:08:07,676
of my new database.

126
00:08:07,676 --> 00:08:09,696
The collation is
the character set

127
00:08:10,496 --> 00:08:12,276
which this database
will support.

128
00:08:13,296 --> 00:08:16,996
By character sets I mean,
like, something like UTF 8.

129
00:08:17,536 --> 00:08:19,646
So, for example if,
if this database were

130
00:08:19,646 --> 00:08:23,646
to store Chinese characters,

131
00:08:23,646 --> 00:08:26,706
the current character set would
not be able to support it.

132
00:08:27,066 --> 00:08:28,976
And so you would have
to search through

133
00:08:28,976 --> 00:08:30,766
and find the appropriate
character set.

134
00:08:31,716 --> 00:08:37,016
So, the collation
is, is first default,

135
00:08:37,016 --> 00:08:40,936
it defaults to Latin 1
underscore Swedish underscore CI

136
00:08:41,926 --> 00:08:43,586
with the current installation.

137
00:08:44,026 --> 00:08:47,446
And this cascades down to tall
the newly created tables inside

138
00:08:47,536 --> 00:08:51,646
this database as well as all the
newly created fields inside the

139
00:08:51,646 --> 00:08:53,726
database, all the new,
newly created columns.

140
00:08:53,726 --> 00:08:55,966
Fields and columns are the
same thing, essentially.

141
00:08:57,246 --> 00:08:59,636
Though you can still, you
can override this setting

142
00:08:59,786 --> 00:09:05,096
on the database level, on the,
on the table level as well

143
00:09:05,096 --> 00:09:05,876
as on the field level.

144
00:09:06,476 --> 00:09:12,816
Okay, the next tab across
is sequel and here's

145
00:09:12,816 --> 00:09:15,796
where I can actually
run sequel commands.

146
00:09:16,586 --> 00:09:20,336
And when I run a sequel
command here and I click on go,

147
00:09:20,826 --> 00:09:23,946
it will send that sequel command
to the my SQL installation

148
00:09:23,946 --> 00:09:31,266
and then that will give me
back the resulting output.

149
00:09:33,206 --> 00:09:35,366
Users is where you
manage your users.

150
00:09:36,236 --> 00:09:38,986
Export is where you can use,
you can use this feature

151
00:09:38,986 --> 00:09:43,756
to export your database out
to perhaps another server.

152
00:09:44,966 --> 00:09:46,886
Import is the reverse of this

153
00:09:47,076 --> 00:09:50,596
in that you can import
an existing database

154
00:09:50,596 --> 00:09:51,036
from somewhere.

155
00:09:52,236 --> 00:09:54,866
And for the most
part, I'm not going

156
00:09:54,866 --> 00:09:57,076
to mention the remaining
items here.

157
00:09:57,206 --> 00:10:02,066
You're, feel free
to check them out.

158
00:10:02,776 --> 00:10:02,876
Okay.

159
00:10:05,316 --> 00:10:10,806
Let's go back to this.

160
00:10:11,006 --> 00:10:12,136
So, example time.

161
00:10:12,276 --> 00:10:15,546
In my example, I would
like to keep track

162
00:10:15,546 --> 00:10:19,696
of CS 75 staffs CD collection.

163
00:10:20,846 --> 00:10:24,536
And this will entail
several steps.

164
00:10:24,536 --> 00:10:27,986
First off, we need to record
staff member's first name

165
00:10:27,986 --> 00:10:28,546
and last name.

166
00:10:29,386 --> 00:10:32,426
Then, we need to record
each CDs, artists,

167
00:10:32,946 --> 00:10:34,426
every CD's artist and title.

168
00:10:35,206 --> 00:10:41,546
And lastly we need to record
what CDs each staff member has

169
00:10:41,856 --> 00:10:42,826
and when it was acquired.

170
00:10:51,056 --> 00:10:52,596
Okay, so the first step.

171
00:10:52,596 --> 00:10:54,526
What would the first
step be here.

172
00:10:55,776 --> 00:11:00,346
Would anyone like
to participate?

173
00:11:00,806 --> 00:11:02,086
>> Create table?

174
00:11:02,226 --> 00:11:03,846
>> One thing before that.

175
00:11:03,846 --> 00:11:06,156
You're pretty close.

176
00:11:07,046 --> 00:11:11,486
One, so, so the answer
was create a table.

177
00:11:12,356 --> 00:11:16,366
And I had mentioned that there's
a, just one step before that,

178
00:11:16,676 --> 00:11:17,546
before you'd create a table.

179
00:11:18,516 --> 00:11:21,676
[ Inaudible audience comment ]

180
00:11:22,176 --> 00:11:25,426
Okay, pretty, pretty close,

181
00:11:25,426 --> 00:11:27,276
but first off we need
to create a database.

182
00:11:27,276 --> 00:11:30,686
Because ultimately
this my SQL can,

183
00:11:30,766 --> 00:11:33,286
can store one or more databases.

184
00:11:34,376 --> 00:11:41,666
So, let's give this
name, CS 75 staff CDs.

185
00:11:41,876 --> 00:11:46,216
And we're going to leave
the default, the collation

186
00:11:46,316 --> 00:11:48,136
to default because
for the most part,

187
00:11:48,136 --> 00:11:51,136
it will be using
Latin character set.

188
00:11:51,846 --> 00:11:55,506
And so I click on create.

189
00:11:56,046 --> 00:12:01,216
I can go ahead and actually, I'm
going to delete this and do it

190
00:12:01,256 --> 00:12:04,116
through the sequel line
command, sequel command line,

191
00:12:05,396 --> 00:12:07,886
just for demonstrative purposes.

192
00:12:09,416 --> 00:12:14,536
So, I'm going to issue a, okay.

193
00:12:14,536 --> 00:12:18,866
Need to go back out here so
I can have rights to do this.

194
00:12:19,826 --> 00:12:24,986
Draw up database
CS 75 staff CDs.

195
00:12:25,506 --> 00:12:28,936
And there you go.

196
00:12:28,936 --> 00:12:30,496
Now, now the database is gone.

197
00:12:31,256 --> 00:12:33,886
So I'm going to create
the same database

198
00:12:34,086 --> 00:12:36,226
but using sequel this time.

199
00:12:37,506 --> 00:12:41,376
And I will use the
create command.

200
00:12:41,706 --> 00:12:45,376
So, create and then, can
anyone guess what the next word

201
00:12:45,376 --> 00:12:50,366
after this would be?

202
00:12:50,626 --> 00:12:51,306
>> Database.

203
00:12:51,976 --> 00:12:52,306
>> Database.

204
00:12:53,196 --> 00:12:57,426
So, the answer was database
and, which his correct.

205
00:12:58,556 --> 00:13:00,666
Create database and
then database name.

206
00:13:00,886 --> 00:13:05,056
CS 75 staff CDs, and
that's all I really need.

207
00:13:05,626 --> 00:13:10,966
And now I have my, my
newly created database.

208
00:13:14,886 --> 00:13:16,466
Okay, now onto the next step.

209
00:13:16,566 --> 00:13:19,206
And someone had mentioned that
we need to create a table.

210
00:13:19,466 --> 00:13:23,626
And yes we do, we
will create a table.

211
00:13:23,626 --> 00:13:26,366
Create, and what
comes after create?

212
00:13:26,786 --> 00:13:30,266
>> Create a table.

213
00:13:30,546 --> 00:13:31,656
>> Table. Exactly.

214
00:13:32,206 --> 00:13:32,886
>> Table name.

215
00:13:33,116 --> 00:13:33,666
>> Table name.

216
00:13:33,666 --> 00:13:36,046
Let's, what, what should
we call this table?

217
00:13:36,046 --> 00:13:43,926
Something that will be
representative of a table

218
00:13:43,926 --> 00:13:47,566
that stores first name,
last name, CD artist,

219
00:13:47,566 --> 00:13:49,316
CD title and date acquired.

220
00:13:55,506 --> 00:13:57,886
Let's just, let's call
it staff collections.

221
00:14:00,096 --> 00:14:03,126
And open parenthesis,
close parenthesis.

222
00:14:03,806 --> 00:14:05,966
In other systems, I believe
it's open curly brackets,

223
00:14:05,966 --> 00:14:07,396
close curly brackets.

224
00:14:07,516 --> 00:14:09,876
But for this, it's parenthesis.

225
00:14:11,086 --> 00:14:15,876
So, in here, we need to start
actually enumerating the actual,

226
00:14:15,876 --> 00:14:20,886
or creating the fields that
will store the different types

227
00:14:20,886 --> 00:14:23,166
of data that we like to keep.

228
00:14:23,716 --> 00:14:27,826
Let's start with the
first column name,

229
00:14:27,826 --> 00:14:29,586
which is first name.

230
00:14:31,086 --> 00:14:36,156
And what data type would
you recommend us using?

231
00:14:36,836 --> 00:14:37,086
>> Varchar.

232
00:14:37,816 --> 00:14:39,156
>> So, the answer was varchar.

233
00:14:39,686 --> 00:14:45,486
Sure, varchar is
a good candidate.

234
00:14:45,486 --> 00:14:48,566
And how many characters
should we be able to support?

235
00:14:49,106 --> 00:14:49,236
>> 30?

236
00:14:51,446 --> 00:14:54,716
>> 30? 30 sounds reasonable.

237
00:14:55,846 --> 00:14:57,346
So, first name.

238
00:14:57,346 --> 00:15:01,056
we're going to do the
same thing with last name.

239
00:15:01,056 --> 00:15:02,436
And CD artist.

240
00:15:03,856 --> 00:15:07,596
Perhaps we could do
the same with this.

241
00:15:07,836 --> 00:15:09,506
And CD title.

242
00:15:12,456 --> 00:15:15,056
Lastly, date acquired.

243
00:15:15,776 --> 00:15:22,746
And what type of, what data
type should this be or,

244
00:15:22,996 --> 00:15:24,376
is preferable to be?

245
00:15:25,196 --> 00:15:25,366
>> Date.

246
00:15:26,416 --> 00:15:29,166
>> Date. So, there
is a date, there,

247
00:15:29,166 --> 00:15:34,306
there is a native date
data type in my SQL.

248
00:15:35,696 --> 00:15:38,546
And so when I'm done with this,
all I need to do is click on go.

249
00:15:42,976 --> 00:15:49,196
And you can see here under
the CS 75 staff CDs database,

250
00:15:49,196 --> 00:15:53,166
I mention of staff under,
underscore collections,

251
00:15:53,166 --> 00:15:54,756
which is the table
we just had created.

252
00:15:55,726 --> 00:15:56,946
And I can click on this.

253
00:15:56,946 --> 00:16:01,146
When I click on this, I
can see every, each field

254
00:16:01,356 --> 00:16:03,926
and its corresponding values.

255
00:16:03,926 --> 00:16:04,726
I met the values.

256
00:16:05,636 --> 00:16:09,866
The type column specifies
what data type this is.

257
00:16:09,926 --> 00:16:12,916
The collation, as mentioned
earlier, is the character set

258
00:16:12,916 --> 00:16:15,836
that this, this field
will support.

259
00:16:16,086 --> 00:16:19,616
Again, I can over-ride the
system default and modify it

260
00:16:19,616 --> 00:16:23,256
to the character, the
collation of my choice.

261
00:16:24,166 --> 00:16:27,376
And null I will just
skip over attributes.

262
00:16:27,536 --> 00:16:33,266
Null specifies whether this,
this column can be empty or not.

263
00:16:34,076 --> 00:16:36,936
If, if I say no, if I
change this yes to no,

264
00:16:37,456 --> 00:16:43,736
then when I'm entering new data
and I do not specify anything

265
00:16:43,736 --> 00:16:45,756
for this, it will error out

266
00:16:45,756 --> 00:16:49,776
and will not let me insert
the newly, the intended data.

267
00:16:50,666 --> 00:16:53,596
Default is the actual
default value

268
00:16:53,596 --> 00:16:58,446
that will appear each time
I create a new instance of,

269
00:16:58,896 --> 00:16:59,536
of this table.

270
00:16:59,646 --> 00:17:01,736
So, if I create a new instance
whereby there's a first name,

271
00:17:01,736 --> 00:17:04,456
last name, CD artist, CD
title and date acquired.

272
00:17:05,026 --> 00:17:07,676
If I say for example
I'm only going

273
00:17:07,676 --> 00:17:11,286
to be covering certain
artists, I can just put

274
00:17:11,406 --> 00:17:12,496
that artist's name in here.

275
00:17:13,406 --> 00:17:16,286
I can modify this to default
to the artist's title,

276
00:17:16,286 --> 00:17:18,246
and therefore I would
not have to input

277
00:17:18,246 --> 00:17:19,696
that artists' title each time.

278
00:17:19,826 --> 00:17:24,566
But this is a very
unrealistic example.

279
00:17:24,566 --> 00:17:32,126
Change actually is, is a gooey
component to php my admin

280
00:17:32,186 --> 00:17:33,396
which will let you actually,

281
00:17:33,536 --> 00:17:37,376
which will let you change
the method data for this,

282
00:17:37,376 --> 00:17:40,666
the different constraints
and so forth for this field.

283
00:17:41,116 --> 00:17:44,976
Drop simply deletes entire
column, whatever column your,

284
00:17:44,976 --> 00:17:46,406
you choose, and I'm
sure you could do more

285
00:17:46,406 --> 00:17:48,546
than one at the same time.

286
00:17:48,546 --> 00:17:52,866
Browse distinct values will
specify, will enumerate, let's,

287
00:17:52,866 --> 00:17:56,646
let's say for example
we have 3 Davids,

288
00:17:56,826 --> 00:17:59,656
or 3 Davids in this class.

289
00:17:59,806 --> 00:18:01,296
If I click on browse
distinct values it,

290
00:18:01,296 --> 00:18:03,946
it would ultimately show
me just one David one time.

291
00:18:04,696 --> 00:18:08,366
It won't show it more than once.

292
00:18:08,536 --> 00:18:09,456
Primary key.

293
00:18:09,456 --> 00:18:11,016
The primary key command.

294
00:18:11,176 --> 00:18:17,586
The primary key command
will, will change the column

295
00:18:17,586 --> 00:18:19,016
to become a primary key.

296
00:18:19,736 --> 00:18:21,006
What is a primary key?

297
00:18:22,456 --> 00:18:27,596
It's actually 1 or more columns
in your database that determine

298
00:18:27,596 --> 00:18:31,126
or determines or determine
all the remaining columns.

299
00:18:31,606 --> 00:18:35,086
So, for, for instance,
in this case,

300
00:18:35,086 --> 00:18:42,606
can anyone suggest the
primary key for this table?

301
00:18:42,846 --> 00:18:46,166
Is there, is there a field or
a column, one or more columns,

302
00:18:46,346 --> 00:18:48,786
put, if, if we put them
together, like, if,

303
00:18:48,946 --> 00:18:52,426
is there one column or
more than one column

304
00:18:53,866 --> 00:18:56,466
that if joined would
determine the remaining columns

305
00:18:57,296 --> 00:18:58,756
in this, logically speaking?

306
00:18:59,046 --> 00:19:00,556
SO, for example, if
I put a first name,

307
00:19:00,556 --> 00:19:01,966
if I put first name
as primary key.

308
00:19:01,966 --> 00:19:05,796
If I put, like, let's say David,
will it determine the last name,

309
00:19:05,796 --> 00:19:07,356
CD artist, CD title,
date acquired?

310
00:19:07,446 --> 00:19:08,556
Absolutely not.

311
00:19:09,846 --> 00:19:13,626
Okay, I'll, I'll cover this
in more detail a little later

312
00:19:13,626 --> 00:19:18,316
but this is just us
visiting this section.

313
00:19:18,316 --> 00:19:19,446
So, I'm going to go ahead

314
00:19:19,446 --> 00:19:26,836
and start inputting some
data in, in this table.

315
00:19:26,926 --> 00:19:29,866
Does anyone know what command
I should be using for this

316
00:19:30,676 --> 00:19:37,166
to actually input data inside
the table, inside this table?

317
00:19:37,576 --> 00:19:37,946
>> Insert.

318
00:19:37,946 --> 00:19:38,606
>> Insert, correct.

319
00:19:39,336 --> 00:19:42,726
So, insert, the, the
syntax says insert into

320
00:19:43,276 --> 00:19:45,006
and the name of the table.

321
00:19:45,706 --> 00:19:49,076
And I go, I, I return
to the next line

322
00:19:49,076 --> 00:19:51,046
because it is not going
to affect anything

323
00:19:51,046 --> 00:19:52,196
but it looks better visually.

324
00:19:52,726 --> 00:19:56,006
Insert into staff collections.

325
00:19:56,506 --> 00:20:01,626
Now, I will need to enumerate
the fields that I need to fill.

326
00:20:02,406 --> 00:20:04,246
So, for example, if
I only need, if I,

327
00:20:04,246 --> 00:20:07,036
if I'm creating a record
here and I only want

328
00:20:07,036 --> 00:20:10,046
to add first name and last name
and I do not know what CD they,

329
00:20:10,046 --> 00:20:13,226
that person has or when it
was acquired, I could just say

330
00:20:13,306 --> 00:20:15,826
for example, Insert into
staff collections first name,

331
00:20:17,956 --> 00:20:21,416
last name and then values.

332
00:20:24,096 --> 00:20:28,176
And I need to put the, the
corresponding value in quotes.

333
00:20:28,236 --> 00:20:31,706
So, the first value, the first
in parenthesis will correspond

334
00:20:31,706 --> 00:20:34,466
to the first field
in those parenthesis

335
00:20:34,906 --> 00:20:36,316
and the first set
of parenthesis.

336
00:20:36,566 --> 00:20:41,186
Let's put David Malan.

337
00:20:42,486 --> 00:20:45,786
And this is, and the reason
why I put a semi colon is

338
00:20:45,946 --> 00:20:47,446
so I could put some
more commands later

339
00:20:47,446 --> 00:20:48,916
down in this page.

340
00:20:48,916 --> 00:20:51,186
For example, I could do
another insert here, et cetera.

341
00:20:51,186 --> 00:20:56,486
So, let's see what
happens when I do this.

342
00:20:56,526 --> 00:20:57,316
Click on go.

343
00:20:58,596 --> 00:21:01,126
And it says one row inserted.

344
00:21:01,996 --> 00:21:07,676
If I click on staff collections,
I can see that for first name,

345
00:21:07,786 --> 00:21:11,826
there's a mention of David,
last name, CD artist is null,

346
00:21:11,826 --> 00:21:17,716
CD title is null,
date acquired is null.

347
00:21:17,716 --> 00:21:19,706
Let's go back here.

348
00:21:19,706 --> 00:21:21,376
I'm going to actually delete.

349
00:21:22,316 --> 00:21:26,266
Now, I would like to delete
that record I just created.

350
00:21:26,266 --> 00:21:27,206
How can I delete this.

351
00:21:27,516 --> 00:21:30,466
Does anyone know what
command I can use,

352
00:21:30,786 --> 00:21:36,616
what sequel command I can use?

353
00:21:37,186 --> 00:21:42,136
Delete. So, the answer
is delete.

354
00:21:42,796 --> 00:21:50,586
And so delete from,
delete from users, I mean,

355
00:21:50,586 --> 00:21:53,736
sorry, staff collections.

356
00:21:54,966 --> 00:21:58,976
This, what this essentially
says is delete all the records

357
00:21:59,086 --> 00:22:01,426
from this table if I
just say delete from

358
00:22:01,936 --> 00:22:02,836
and the name of the table.

359
00:22:04,286 --> 00:22:07,356
When I have a much
bigger table and I would

360
00:22:07,356 --> 00:22:10,796
like to delete some data, I can
be a little bit more specific

361
00:22:10,796 --> 00:22:12,186
and put a clause, like,

362
00:22:12,656 --> 00:22:19,036
like where something equals
something else and so forth.

363
00:22:19,256 --> 00:22:21,386
But for, for this case,
I can just use this

364
00:22:21,386 --> 00:22:22,606
because I only have one record.

365
00:22:23,616 --> 00:22:25,166
So, I will click on go.

366
00:22:26,336 --> 00:22:29,336
And one row deleted and
if I go, go to browse,

367
00:22:30,246 --> 00:22:32,276
I really don't have
anymore records in my table.

368
00:22:37,256 --> 00:22:39,576
Okay, so I'm going to
actually insert some data

369
00:22:40,146 --> 00:22:44,296
into this table to
make a, a point.

370
00:22:45,926 --> 00:22:50,386
Insert into, let's enter
data for 2 or 3 people.

371
00:22:51,566 --> 00:22:53,866
Insert into staff collections.

372
00:22:54,336 --> 00:23:01,676
First name, last name.

373
00:23:04,226 --> 00:23:09,816
Now, I, I will be
entering more information,

374
00:23:10,646 --> 00:23:15,506
more than last time at least.

375
00:23:16,036 --> 00:23:17,836
Date acquired.

376
00:23:18,436 --> 00:23:20,516
Okay. Values.

377
00:23:21,106 --> 00:23:24,496
So David Malan.

378
00:23:25,456 --> 00:23:28,886
And for CD artist, I will put
let's say Michael Jackson,

379
00:23:29,276 --> 00:23:32,166
because I am familiar with him.

380
00:23:33,756 --> 00:23:38,556
Thriller. And date acquired,
dates should be put in quotes.

381
00:23:39,506 --> 00:23:41,356
And one thing I would like
to mention about dates,

382
00:23:41,356 --> 00:23:44,756
the way date is laid
out in my SQL is

383
00:23:44,756 --> 00:23:49,766
that your typical date
format is month, day, year,

384
00:23:50,486 --> 00:23:54,826
but with my SQL, it's
actually year, month, day.

385
00:23:55,886 --> 00:23:58,546
So, when I enter the date
here, I will need to put

386
00:23:58,546 --> 00:24:02,416
in for example 2012 5 5.

387
00:24:03,576 --> 00:24:06,066
So, this is our first record.

388
00:24:06,186 --> 00:24:07,566
I'm going to copy this.

389
00:24:08,416 --> 00:24:10,186
I'm going to cookie
vet it, pretty much.

390
00:24:10,886 --> 00:24:12,286
Past it again, paste it again.

391
00:24:12,286 --> 00:24:13,106
I'll do one for me.

392
00:24:22,406 --> 00:24:28,706
And let's just change this song
here, let me, let's say Beat It.

393
00:24:34,106 --> 00:24:37,496
And one more person.

394
00:24:48,096 --> 00:24:51,436
Okay. So, now I'm
ready to submit this.

395
00:24:51,546 --> 00:24:56,466
I'm, my plan is to submit 3
records into the database.

396
00:24:56,466 --> 00:24:57,656
I will click on Go.

397
00:24:58,906 --> 00:25:01,926
And if everything was
successful, and it was,

398
00:25:02,456 --> 00:25:04,566
I should see, when
I click on Browse,

399
00:25:04,566 --> 00:25:12,506
I should see all the data
that I just inputted.

400
00:25:12,646 --> 00:25:16,596
Okay, can anyone see an
inefficiency in this design?

401
00:25:18,576 --> 00:25:25,866
>> The CD artist is repeated?

402
00:25:25,866 --> 00:25:26,946
>> CD artist is repeated.

403
00:25:27,716 --> 00:25:32,746
That's taking up space
on, on disc storage.

404
00:25:33,176 --> 00:25:34,096
That's, that's one thing.

405
00:25:35,736 --> 00:25:39,046
Another, another disadvantage
here is that let's suppose

406
00:25:39,046 --> 00:25:43,836
that I have, I have 200 CDs
and there are 10,000, there's,

407
00:25:43,836 --> 00:25:45,916
there are 10,000
rows to this table.

408
00:25:47,686 --> 00:25:51,676
If I have, like, let's say
10 CDs and I change my name.

409
00:25:52,086 --> 00:25:54,536
This would entail my
going into the database

410
00:25:54,536 --> 00:25:57,166
and locating each
single instance

411
00:25:57,166 --> 00:25:59,396
where my name is mentioned
and changing my name.

412
00:25:59,936 --> 00:26:01,076
That's another disadvantage.

413
00:26:02,046 --> 00:26:09,206
Lastly, let's suppose that I
would like to delete, let's,

414
00:26:10,766 --> 00:26:12,756
let's suppose that I would not,

415
00:26:13,056 --> 00:26:16,556
I would like to not include
myself in this table,

416
00:26:16,556 --> 00:26:17,586
I would like to delete myself.

417
00:26:18,356 --> 00:26:21,516
So, if I go ahead and delete
this, what ends up happening is,

418
00:26:21,516 --> 00:26:22,606
well, it's going to prompt me.

419
00:26:23,486 --> 00:26:26,146
But what will end up happening
in terms of information is

420
00:26:26,146 --> 00:26:29,276
that I will also be deleting
the CD artist and CD title,

421
00:26:29,636 --> 00:26:32,206
leaving no mention of it.

422
00:26:32,456 --> 00:26:35,836
So, those are at least
3 disadvantages we have

423
00:26:36,016 --> 00:26:36,736
with this structure.

424
00:26:37,216 --> 00:26:43,066
And so what we need to do is
pursue a process called database

425
00:26:43,066 --> 00:26:46,716
normalization and which, which
I encourage you to research.

426
00:26:48,156 --> 00:26:52,286
By normalizing the data,
you're actually reducing

427
00:26:52,286 --> 00:26:56,186
or eliminating redundancies
and inefficiencies in terms

428
00:26:56,186 --> 00:26:58,216
of the structure of your data.

429
00:26:58,766 --> 00:27:03,176
What I will do now is I will
delete everything I just did

430
00:27:03,176 --> 00:27:08,276
and start from scratch to
adhere to the database,

431
00:27:08,576 --> 00:27:12,506
relational database
normalization principles.

432
00:27:13,006 --> 00:27:15,406
So, I click on sequel
and I would

433
00:27:15,406 --> 00:27:17,176
like to delete this table, okay?

434
00:27:19,036 --> 00:27:23,896
How can I delete this table,
what, what do I type now?

435
00:27:24,126 --> 00:27:24,346
>> Drop.

436
00:27:24,916 --> 00:27:26,336
>> Drop? Okay.

437
00:27:27,336 --> 00:27:29,896
Drop. And then table.

438
00:27:31,016 --> 00:27:31,966
Table name.

439
00:27:32,536 --> 00:27:36,996
And I will click on go.

440
00:27:37,096 --> 00:27:43,606
there we go, it's gone.

441
00:27:43,866 --> 00:27:45,026
All right.

442
00:27:52,346 --> 00:27:54,186
Okay, so how do we
move forward from here?

443
00:27:54,186 --> 00:27:56,606
What, how can I make
this efficient?

444
00:27:56,606 --> 00:27:59,266
>> You can make 2 tables.

445
00:27:59,266 --> 00:28:00,206
>> Sorry, what was that?

446
00:28:00,616 --> 00:28:02,036
>> Connect the tables.

447
00:28:04,246 --> 00:28:04,936
>> Connect the tables.

448
00:28:07,716 --> 00:28:08,526
Sorry, I can't hear you.

449
00:28:08,526 --> 00:28:09,366
>> I said you could
make 2 tables.

450
00:28:09,446 --> 00:28:10,036
>> Make 2 tables?

451
00:28:10,106 --> 00:28:10,606
Sure, sure.

452
00:28:11,076 --> 00:28:12,676
Make 2 tables is a
good recommendation.

453
00:28:13,166 --> 00:28:15,336
So, what, what can I
put in the first table?

454
00:28:16,006 --> 00:28:18,936
What information, what type
of information at least?

455
00:28:19,516 --> 00:28:21,556
[ Inaudible audience comment ]

456
00:28:22,056 --> 00:28:26,056
Okay. Yeah, so, the, the answer
was information about staff.

457
00:28:26,726 --> 00:28:28,726
And which is pretty good.

458
00:28:30,016 --> 00:28:32,096
So, create, I will go
ahead and issue a command.

459
00:28:32,156 --> 00:28:32,876
Create table.

460
00:28:32,876 --> 00:28:34,566
And let's call this table staff.

461
00:28:37,566 --> 00:28:41,026
Okay, I need to cover
first name.

462
00:28:42,636 --> 00:28:44,276
Put varchar 30.

463
00:28:45,686 --> 00:28:46,916
Last name.

464
00:28:50,496 --> 00:28:51,666
Varchar 30 as well.

465
00:28:52,456 --> 00:28:53,406
Am I missing anything here?

466
00:28:53,706 --> 00:28:57,296
Do you got, do you see the need
for anything additional in this?

467
00:28:57,616 --> 00:29:03,446
>> Maybe we need a, a stop
[inaudible] for the primary key?

468
00:29:03,446 --> 00:29:04,416
>> Okay, a primary key.

469
00:29:04,756 --> 00:29:09,736
Sure. Let's go ahead and put ID.

470
00:29:09,736 --> 00:29:10,986
And this will be an integer.

471
00:29:11,306 --> 00:29:14,456
We, we won't be having
too many people here,

472
00:29:14,456 --> 00:29:15,806
so let's just give
it a length of 4,

473
00:29:15,806 --> 00:29:20,196
which means it can
accommodate up to 9,999.

474
00:29:20,466 --> 00:29:22,046
That number.

475
00:29:22,046 --> 00:29:25,906
Okay. So, I will delineate
this with a semi colon

476
00:29:26,556 --> 00:29:28,446
and I will create a
new table as well.

477
00:29:28,866 --> 00:29:29,956
So, staff is covered.

478
00:29:30,076 --> 00:29:33,386
And for the purpose
of this example,

479
00:29:34,146 --> 00:29:36,186
what other information
do I still need?

480
00:29:40,276 --> 00:29:40,586
>> Collections.

481
00:29:40,586 --> 00:29:41,176
>> Collections?

482
00:29:41,956 --> 00:29:44,106
But what, what do
collections entail?

483
00:29:44,266 --> 00:29:48,266
In this case, we're
collecting CDs, so.

484
00:29:48,716 --> 00:29:51,476
>> Artist name and album name.

485
00:29:52,236 --> 00:29:52,696
>> Exactly.

486
00:29:53,566 --> 00:29:55,286
So, artist name, album name.

487
00:29:56,106 --> 00:30:04,546
That's, so let's do artist
varchar 30 and title.

488
00:30:09,166 --> 00:30:11,736
Okay, and we still need
a primary key here,

489
00:30:11,906 --> 00:30:13,386
so let's do the same
as we did earlier.

490
00:30:13,626 --> 00:30:17,226
And ID int 4, comma.

491
00:30:18,056 --> 00:30:19,556
And we can click on go.

492
00:30:20,776 --> 00:30:21,276
Excuse me, sorry?

493
00:30:22,156 --> 00:30:24,156
[ Inaudible audience comment ]

494
00:30:24,296 --> 00:30:26,016
There is a good point,
good observation.

495
00:30:26,756 --> 00:30:35,346
Let's call this CDs and let's
go ahead and click on go.

496
00:30:35,506 --> 00:30:39,646
So, now I have 2
tables, one's called CDs

497
00:30:40,456 --> 00:30:43,386
and one is called staff.

498
00:30:45,376 --> 00:30:49,156
Although I, I included
this to be a primary key,

499
00:30:49,156 --> 00:30:50,526
it's still not a
primary key yet.

500
00:30:51,686 --> 00:30:57,096
So, I can do this by clicking
on the primary key image or link

501
00:30:58,056 --> 00:30:59,786
or I can go into sequel.

502
00:31:00,696 --> 00:31:03,136
Let's create a primary
key for CDs.

503
00:31:03,356 --> 00:31:04,686
I can say alter table.

504
00:31:06,046 --> 00:31:09,826
The name of the table,
which in this case is CDs.

505
00:31:10,026 --> 00:31:10,966
Add primary key.

506
00:31:12,616 --> 00:31:14,406
And in parenthesis
the name of the field,

507
00:31:14,476 --> 00:31:16,156
the name of the column
which I would like to have

508
00:31:16,156 --> 00:31:18,026
as the primary key, which is ID.

509
00:31:19,236 --> 00:31:23,566
And I want to do the same
with the staff table.

510
00:31:28,926 --> 00:31:37,246
Okay. I look at the structure
now, and you can notice

511
00:31:37,516 --> 00:31:40,866
that ID's underline
which means that,

512
00:31:40,866 --> 00:31:43,916
which signifies ID
being at primary key.

513
00:31:44,946 --> 00:31:47,976
And there is just one more
thing I need to do this column.

514
00:31:48,786 --> 00:31:51,156
Does anyone know
what this thing is?

515
00:31:52,066 --> 00:31:54,416
Or what I need to do
left to the primary key?

516
00:31:55,766 --> 00:32:00,386
Think of inserting
new data and so forth.

517
00:32:00,386 --> 00:32:01,856
>> Maybe not null or
other information?

518
00:32:02,346 --> 00:32:04,236
>> By default, the
primary keys are not null

519
00:32:04,236 --> 00:32:07,276
and they're indexed
and unique as well.

520
00:32:07,426 --> 00:32:07,856
So-

521
00:32:08,186 --> 00:32:10,976
>> They should be
auto incremented.

522
00:32:11,756 --> 00:32:12,516
>> Auto incremented.

523
00:32:12,686 --> 00:32:13,606
You had mentioned that.

524
00:32:13,606 --> 00:32:15,936
That was the last on your list.

525
00:32:16,776 --> 00:32:19,636
All right, so I will
click on, on, on this

526
00:32:19,636 --> 00:32:20,596
and actually change it.

527
00:32:21,196 --> 00:32:22,126
Click on change.

528
00:32:23,326 --> 00:32:25,536
Here I'm going to put
none for default value

529
00:32:25,536 --> 00:32:27,216
because it will not
have any default values.

530
00:32:27,946 --> 00:32:30,786
And I will check
AI, A undercore I.

531
00:32:31,546 --> 00:32:32,196
Click on save.

532
00:32:32,786 --> 00:32:37,296
And so now each time a
value, a new name, first name

533
00:32:37,296 --> 00:32:40,226
and last name is inserted
into this table, we will,

534
00:32:40,226 --> 00:32:45,856
the system will generate a new
ID based on the last used ID.

535
00:32:46,086 --> 00:32:48,626
So, if the last ID was
1, the next ID will be 2.

536
00:32:49,546 --> 00:32:53,706
If you have 3 people,
so, with IDs 1, 2, and 3,

537
00:32:54,266 --> 00:32:58,536
and you delete ID, the
one with ID 3, the next,

538
00:32:59,606 --> 00:33:02,726
the next inserted user
will not have an ID of 3.

539
00:33:03,546 --> 00:33:04,756
It will have an ID of 4.

540
00:33:04,756 --> 00:33:06,996
So, it just checks for
the last number used.

541
00:33:07,046 --> 00:33:09,376
It does not care what,
what's available,

542
00:33:09,376 --> 00:33:12,086
what's actually been
used in the table or not.

543
00:33:16,876 --> 00:33:20,286
Now, I'll do the same thing
now for the staff table.

544
00:33:20,936 --> 00:33:24,466
I'll make sure it's
auto incremented.

545
00:33:29,456 --> 00:33:34,016
Okay. So, now we have a CDs
table and a staff table.

546
00:33:35,026 --> 00:33:41,636
How can we record what CDs
a certain individual has,

547
00:33:42,276 --> 00:33:52,546
a certain one of our, individual
from our staff members?

548
00:33:52,546 --> 00:33:55,066
Okay, so the answer is we
need to create a new table

549
00:33:56,066 --> 00:34:02,216
that associates a staff
member with a certain CD

550
00:34:02,216 --> 00:34:03,746
that the staff member owns.

551
00:34:03,746 --> 00:34:09,636
And I will create a table
and call it collections.

552
00:34:10,196 --> 00:34:11,936
So, create table.

553
00:34:13,106 --> 00:34:15,076
The table name is collections.

554
00:34:16,266 --> 00:34:23,856
And let's see, let's see what
columns this should have based

555
00:34:23,856 --> 00:34:28,706
on efficiency and what
we really need from this.

556
00:34:30,356 --> 00:34:30,636
>> Staff ID.

557
00:34:30,636 --> 00:34:31,416
>> Staff ID is one.

558
00:34:32,246 --> 00:34:39,896
Actually, staff ID
is an integer,

559
00:34:41,146 --> 00:34:43,686
and it should be 4 digits long.

560
00:34:44,726 --> 00:34:46,346
And what else?

561
00:34:47,576 --> 00:34:50,366
>> CD, CD name.

562
00:34:50,766 --> 00:34:53,576
>> Someone mentioned CD ID
which is, which is correct.

563
00:34:54,956 --> 00:34:58,726
CD ID, because CD ID will
ultimately identify the CD

564
00:34:58,726 --> 00:35:00,946
artist and title in
that other table.

565
00:35:01,566 --> 00:35:06,446
And these are what
we call foreign keys.

566
00:35:07,706 --> 00:35:09,856
Logically, these
are foreign keys

567
00:35:09,946 --> 00:35:14,546
but they are not technically
foreign keys in that we need

568
00:35:14,546 --> 00:35:16,916
to issue commands to make
them foreign keys and that,

569
00:35:16,916 --> 00:35:18,326
there's a whole discussion
to that.

570
00:35:18,566 --> 00:35:20,566
If you'd like to
research more on this,

571
00:35:21,216 --> 00:35:24,736
look up referential integrity
and what that entails.

572
00:35:26,536 --> 00:35:30,486
And our, and in my
SQL we have 2,

573
00:35:30,696 --> 00:35:33,866
we're going to cover
basically 2 database engines.

574
00:35:33,866 --> 00:35:38,776
One is my ISM and
the other is NLDB.

575
00:35:39,126 --> 00:35:43,746
NLDB INNO DB will support
foreign key constraints

576
00:35:43,746 --> 00:35:46,486
or foreign keys and
my ISM will not.

577
00:35:48,386 --> 00:35:50,636
Okay, so there is one more
thing I need to put in here

578
00:35:51,476 --> 00:35:53,676
which I had mentioned
previously in the,

579
00:35:53,676 --> 00:35:55,276
in the inefficient example.

580
00:35:55,876 --> 00:35:57,456
There's, there's
one more column.

581
00:35:58,026 --> 00:35:59,686
>> Date acquired.

582
00:35:59,846 --> 00:36:00,456
>> Date acquired?

583
00:36:01,476 --> 00:36:02,146
That is correct.

584
00:36:03,506 --> 00:36:08,536
So, data acquired which is
essentially a date value,

585
00:36:09,196 --> 00:36:10,366
date field.

586
00:36:10,366 --> 00:36:15,276
Data type field.

587
00:36:15,346 --> 00:36:17,746
Okay. Now, I will click on go.

588
00:36:18,756 --> 00:36:20,736
Do, are we missing
anything here?

589
00:36:29,196 --> 00:36:30,816
So, I went ahead and
created the table.

590
00:36:30,906 --> 00:36:32,166
This is the structure
of the table.

591
00:36:33,286 --> 00:36:33,666
Where is it.

592
00:36:33,706 --> 00:36:34,396
Collections.

593
00:36:35,786 --> 00:36:41,596
And this table does
not have a primary key.

594
00:36:42,696 --> 00:36:45,176
So, what is the primary
key for this table?

595
00:36:45,986 --> 00:36:51,276
What field or fields determine
the remainder of the table?

596
00:36:51,876 --> 00:36:53,796
The remaining values?

597
00:36:54,516 --> 00:36:59,386
[ Inaudible audience comment ]

598
00:36:59,886 --> 00:37:01,766
Sure. So, in this case,

599
00:37:01,766 --> 00:37:03,946
for example if we have
a certain staff ID,

600
00:37:04,046 --> 00:37:06,896
staff ID will not determine
CD ID and date acquired.

601
00:37:07,456 --> 00:37:09,946
CD ID will not determine
staff ID and date acquired.

602
00:37:10,216 --> 00:37:13,916
And date acquired will not
determine staff ID and CD ID.

603
00:37:15,126 --> 00:37:16,166
In this case, we need to,

604
00:37:16,226 --> 00:37:20,566
to form what is called a
composite primary key is

605
00:37:20,646 --> 00:37:24,666
that which is formed
by more than one,

606
00:37:25,656 --> 00:37:29,556
at least 2, 2 or more fields.

607
00:37:29,556 --> 00:37:35,946
So, the composite key here
would be staff ID add CD ID.

608
00:37:36,216 --> 00:37:40,266
So, given a certain staff
member and given a certain CD

609
00:37:41,226 --> 00:37:41,996
that one could purchase.

610
00:37:42,146 --> 00:37:44,526
So, let's say I purchase
a certain CD.

611
00:37:44,966 --> 00:37:47,026
It's impossible for me
to have purchased the CD

612
00:37:47,276 --> 00:37:49,476
on 2 different dates
at the same time.

613
00:37:50,616 --> 00:37:55,676
Other, only, it might happen in
a parallel universe but for now,

614
00:37:56,116 --> 00:38:01,466
if I purchase a CD, if I, if I
purchase a CD on a certain date,

615
00:38:01,466 --> 00:38:04,186
that's, I, I can't have
more than one of that date.

616
00:38:04,826 --> 00:38:07,926
So, I will form these
2 as a composite key.

617
00:38:08,946 --> 00:38:11,556
And the way to have
2 primary keys,

618
00:38:12,876 --> 00:38:15,996
and I'm not sure you can
do this here in, in my,

619
00:38:15,996 --> 00:38:20,026
php my admin directly, or at
least I don't know how to do it.

620
00:38:20,946 --> 00:38:24,486
So, you go to the
sequel command line.

621
00:38:26,076 --> 00:38:28,796
Alter table collections.

622
00:38:29,256 --> 00:38:32,236
Add primary key.

623
00:38:32,596 --> 00:38:39,586
And then there'll be
staff ID, comma CD ID.

624
00:38:40,146 --> 00:38:42,946
And I click on go.

625
00:38:43,316 --> 00:38:48,466
if I click on structure,
you can see

626
00:38:48,466 --> 00:38:50,716
that they're both
underlined now,

627
00:38:51,186 --> 00:38:55,436
which means that jointly
they form a primary key.

628
00:38:57,696 --> 00:39:03,136
Okay, onto the fun part,
which is filling out the data.

629
00:39:03,256 --> 00:39:04,436
I have already created some,

630
00:39:04,806 --> 00:39:11,226
just before class I
created some sequel commands

631
00:39:11,226 --> 00:39:15,196
to insert some data.

632
00:39:15,806 --> 00:39:18,626
Let's do this one.

633
00:39:19,526 --> 00:39:26,036
Okay. So, let's run this.

634
00:39:26,646 --> 00:39:33,706
We're going to insert 4, 4 CD,
4 titles for Michael Jackson.

635
00:39:39,036 --> 00:39:42,926
Open up. Click on go.

636
00:39:45,256 --> 00:39:55,006
Now, if I look into my
CDs, I can see that it,

637
00:39:55,006 --> 00:39:59,816
the system automatically
generated the sequential IDs

638
00:40:00,086 --> 00:40:01,106
for me.

639
00:40:02,276 --> 00:40:05,806
And it inputted the
artist and title.

640
00:40:05,806 --> 00:40:09,906
And in case you didn't notice
what the commands looked like,

641
00:40:10,146 --> 00:40:13,786
I basically inserted into
CDs artist and title.

642
00:40:13,786 --> 00:40:15,006
I did not mention DI here

643
00:40:15,006 --> 00:40:17,216
because the system
will generate,

644
00:40:17,216 --> 00:40:18,336
generate that for
me automatically.

645
00:40:19,676 --> 00:40:22,846
And then I give it the
corresponding values.

646
00:40:23,996 --> 00:40:32,116
So, now I'll do the same
thing with, with the staff.

647
00:40:36,956 --> 00:40:39,496
Except I haven't used G edit.

648
00:40:39,496 --> 00:40:42,426
Let me see.

649
00:40:45,356 --> 00:40:47,896
There we go.

650
00:40:48,796 --> 00:40:52,686
Okay, so I'm inserting
it to staff a first name,

651
00:40:52,686 --> 00:40:55,656
the values for first name and
last name for our staff members.

652
00:40:56,066 --> 00:40:59,366
And click on go.

653
00:40:59,936 --> 00:41:05,916
If I hit on browse,
I can see that, well,

654
00:41:05,986 --> 00:41:08,836
that we've mentioned
this before.

655
00:41:09,446 --> 00:41:15,146
I can see that all of our staff
members are inside the table

656
00:41:16,066 --> 00:41:18,786
now, each with a unique I, ID.

657
00:41:26,166 --> 00:41:30,336
Okay, lastly, I will
insert our collections.

658
00:41:41,336 --> 00:41:49,856
Okay, so what this essentially
does is it takes, if,

659
00:41:49,856 --> 00:41:54,936
if you notice that I'm only
using the staff ID and CD ID

660
00:41:55,026 --> 00:41:59,256
for each entry and followed
by the acquisition date,

661
00:42:01,666 --> 00:42:03,616
I'm not using any first
names, last names.

662
00:42:03,916 --> 00:42:06,936
So your, for, for future
applications that you write,

663
00:42:07,836 --> 00:42:11,116
you will need to somehow
know what ID the, you need,

664
00:42:11,176 --> 00:42:14,676
that needs, that needs to
be used in your queries

665
00:42:15,056 --> 00:42:25,486
to be entered in, in your
inserted queries and so forth.

666
00:42:27,406 --> 00:42:34,626
Okay? So, now I have my
collections table filled

667
00:42:34,626 --> 00:42:38,226
with staff ID, CD ID
and date acquired.

668
00:42:39,296 --> 00:42:42,286
And the, let's suppose I
delete this entry which is,

669
00:42:42,326 --> 00:42:44,336
which could mean for
example my ID could be 1.

670
00:42:44,816 --> 00:42:46,606
If I delete this,
this only means

671
00:42:46,606 --> 00:42:47,986
that this entry is deleted.

672
00:42:48,806 --> 00:42:53,366
My name remains intact
here, and the CD,

673
00:42:53,456 --> 00:43:02,086
the mention of the CD is
still here and under CDs.

674
00:43:02,296 --> 00:43:03,466
Okay, now I'd like to move

675
00:43:03,736 --> 00:43:07,486
over to the last command
I will cover tonight,

676
00:43:07,666 --> 00:43:09,976
which is the select command.

677
00:43:10,396 --> 00:43:12,476
The select command which is,

678
00:43:12,566 --> 00:43:15,626
which essentially queries the
database, asks the database

679
00:43:15,686 --> 00:43:20,886
for the database management
system for information from it.

680
00:43:20,886 --> 00:43:23,376
And it is a very
flexible command.

681
00:43:23,376 --> 00:43:25,086
You can do so many
things with it.

682
00:43:25,956 --> 00:43:28,936
Let's, let's try
something very simple.

683
00:43:28,936 --> 00:43:31,646
Select star from staff.

684
00:43:32,636 --> 00:43:37,076
And I will explain
what this means.

685
00:43:37,616 --> 00:43:49,316
If I click on go, it basically
shows the entire table.

686
00:43:50,066 --> 00:43:53,746
Select means query the database.

687
00:43:54,546 --> 00:43:58,206
Star is all fields, all columns.

688
00:43:59,266 --> 00:44:03,326
From what table would I like
to get this information from,

689
00:44:03,776 --> 00:44:05,126
the table called staff.

690
00:44:06,316 --> 00:44:07,786
So, it showed me everything.

691
00:44:07,786 --> 00:44:10,196
All the columns, all the
rows for all the columns

692
00:44:10,196 --> 00:44:11,126
from the table of staff.

693
00:44:11,556 --> 00:44:13,206
Now, if I wanted just
to see first name,

694
00:44:14,426 --> 00:44:17,496
I could put select
first name from staff.

695
00:44:17,496 --> 00:44:21,146
And if I click on go, I will
only see the first name here.

696
00:44:21,726 --> 00:44:30,426
And if I put select
first name as first name

697
00:44:30,916 --> 00:44:34,546
to make it visually more
appealing, from staff,

698
00:44:35,576 --> 00:44:41,486
you can see here
that the header,

699
00:44:41,646 --> 00:44:45,646
header column is
called first name

700
00:44:45,826 --> 00:44:52,966
as I changed it in the query.

701
00:44:53,106 --> 00:44:55,986
Similarly, I can put clauses
in the select command.

702
00:44:56,916 --> 00:45:00,666
So, let's say select
first name from, as,

703
00:45:00,886 --> 00:45:02,226
as first name from staff.

704
00:45:02,226 --> 00:45:08,656
And let's put a clause in
here where ID equals 4.

705
00:45:09,326 --> 00:45:14,066
So, this should only
fetch the row where ID,

706
00:45:14,436 --> 00:45:18,896
the value of ID is equal
to 4 from the staff table.

707
00:45:19,656 --> 00:45:26,196
And, and I can see that first
name Chris has an ID of 4.

708
00:45:31,656 --> 00:45:34,656
And there are some more
sophisticated functions you can

709
00:45:34,656 --> 00:45:36,706
do with select but for
the scope of this class,

710
00:45:36,706 --> 00:45:40,726
I don't think we will
be covering them,

711
00:45:40,866 --> 00:45:44,496
like nested queries and other
things, among other things.

712
00:45:45,016 --> 00:45:52,856
You can do something like select
star from staff where first name

713
00:45:53,996 --> 00:46:03,226
like and percentage sign
is wildcard for example.

714
00:46:03,286 --> 00:46:04,916
Wildcard, wild card,
let's say AL.

715
00:46:04,916 --> 00:46:06,536
I want the AL in the
middle of the word.

716
00:46:07,186 --> 00:46:09,996
And this should give
me something like this.

717
00:46:10,326 --> 00:46:19,916
This is my name, because
it has AL in it somewhere.

718
00:46:19,916 --> 00:46:22,716
Okay, so now I have,
I have 3 tables.

719
00:46:22,716 --> 00:46:26,056
I have a CDs table, staff
table and a collections table.

720
00:46:26,866 --> 00:46:32,256
Let's try to make some
meaningful queries given the

721
00:46:32,256 --> 00:46:32,776
3 tables.

722
00:46:32,776 --> 00:46:34,756
So, let's suppose I want to see,

723
00:46:34,966 --> 00:46:41,156
I would like to see each staff
member's CD collection or what,

724
00:46:41,256 --> 00:46:43,236
what they've purchased or
what, what they've acquired.

725
00:46:43,626 --> 00:46:48,186
I will run a sequel,
a select command.

726
00:46:49,016 --> 00:46:50,566
Select first name.

727
00:46:51,826 --> 00:46:53,116
Last name.

728
00:46:54,286 --> 00:46:56,586
Artist. Title.

729
00:46:57,066 --> 00:47:00,816
And date acquired.

730
00:47:01,496 --> 00:47:07,296
From, okay, so from
what tables do I need

731
00:47:07,336 --> 00:47:09,236
to bring these results from?

732
00:47:13,076 --> 00:47:16,146
Okay, staff, what else?

733
00:47:17,236 --> 00:47:19,186
CDs? Any more?

734
00:47:19,996 --> 00:47:20,596
>> Collections.

735
00:47:22,236 --> 00:47:22,896
>> And collections.

736
00:47:22,896 --> 00:47:29,546
Correct. One thing I
would like to note here is

737
00:47:29,546 --> 00:47:33,356
that typically you would use
the table name dot column name.

738
00:47:34,736 --> 00:47:39,916
Like here, I should, I should
be using staff dot first name

739
00:47:40,746 --> 00:47:45,256
but since first name is on, is
unique only to that table, if I,

740
00:47:45,256 --> 00:47:48,176
I don't have it in any other
tables, since it is unique

741
00:47:48,176 --> 00:47:49,236
to that table, I can use it

742
00:47:49,626 --> 00:47:55,746
without using the actually
table operator before it.

743
00:47:57,786 --> 00:47:59,976
And lastly, I need to do
one more thing, which is,

744
00:48:00,476 --> 00:48:03,036
which is Chris will
mention in more detail next,

745
00:48:03,036 --> 00:48:09,116
the next session, next section,
is a join, but I will do a join

746
00:48:09,116 --> 00:48:10,686
in kind of a different way.

747
00:48:10,686 --> 00:48:12,956
I will not using, I will not
be using the keyword join.

748
00:48:14,106 --> 00:48:19,566
So, here we need to actually
join the tables in that I need

749
00:48:19,606 --> 00:48:23,406
to make sure that the
ID from the collection,

750
00:48:23,406 --> 00:48:25,286
the CD ID from the
collections table corresponds

751
00:48:25,286 --> 00:48:27,866
from the CD ID from
the CDs table.

752
00:48:27,866 --> 00:48:30,906
So, I need to put
something like this here

753
00:48:30,986 --> 00:48:32,386
where collections, sorry.

754
00:48:34,876 --> 00:48:44,536
Where collections dot CD
ID equals to CDs dot ID.

755
00:48:45,746 --> 00:48:56,726
And collections dot staff
ID equals 2 staff dot ID.

756
00:49:00,636 --> 00:49:06,876
And if I go ahead and click
on go, now this makes,

757
00:49:07,166 --> 00:49:08,796
this kind of has
some value to us.

758
00:49:09,496 --> 00:49:13,606
I can see people's names
along with the artist,

759
00:49:13,856 --> 00:49:17,436
title and the, when
it was acquired.

760
00:49:17,436 --> 00:49:21,856
Now, you can notice that
these are not too friendly,

761
00:49:21,856 --> 00:49:25,456
the headers, the header labels.

762
00:49:26,586 --> 00:49:33,646
So, what I can do here is use
again use as, as first name.

763
00:49:34,696 --> 00:49:36,006
This is the actual label

764
00:49:36,396 --> 00:49:38,056
that will appear in
the resultant set.

765
00:49:39,386 --> 00:49:39,926
Last name.

766
00:49:40,436 --> 00:49:43,776
Artist, let's just
keep it the same.

767
00:49:43,776 --> 00:49:47,596
And date acquired
as date acquired.

768
00:49:53,086 --> 00:49:57,456
And you can see that
this is reflected here.

769
00:49:58,106 --> 00:49:59,356
It looks much more elegant.

770
00:49:59,566 --> 00:50:01,666
Well I, I could've done the same
thing with artist and title.

771
00:50:03,396 --> 00:50:07,046
There's some more things that I
could do with sequel with, with,

772
00:50:07,046 --> 00:50:08,026
I mean with a select statement.

773
00:50:08,896 --> 00:50:11,796
Let's say we want to find

774
00:50:11,796 --> 00:50:14,526
out how many CDs each
staff member has.

775
00:50:15,876 --> 00:50:18,396
What I could here is
let's delete everything.

776
00:50:19,116 --> 00:50:24,426
I can select first
name, last name

777
00:50:26,106 --> 00:50:30,946
and sequel has some
functions, like count max,

778
00:50:30,946 --> 00:50:34,026
which finds the maximum
value count which counts the,

779
00:50:34,156 --> 00:50:36,536
the occurrences of
a certain value.

780
00:50:37,066 --> 00:50:39,276
So, here I would like
to count the mentions

781
00:50:39,276 --> 00:50:41,866
of collections dot staff ID.

782
00:50:44,226 --> 00:50:48,576
Which logically speaking,
if you're mentioned

783
00:50:48,576 --> 00:50:51,526
in the collections table,
it means that you have,

784
00:50:52,416 --> 00:50:54,306
each time you're mentioned
in the collections table,

785
00:50:54,916 --> 00:50:57,836
it implies that you have
at least that many number,

786
00:50:57,936 --> 00:50:59,166
that many number of CDs.

787
00:50:59,866 --> 00:51:01,756
So, if I'm mentioned 6 times
in the collection table,

788
00:51:01,756 --> 00:51:03,466
it means I, I have
at least 6 CDs,

789
00:51:03,986 --> 00:51:06,456
or I have 6 CDs based
on that information.

790
00:51:06,826 --> 00:51:10,706
And with the count function,
you need to actually use a group

791
00:51:10,706 --> 00:51:12,046
by clause at the very end.

792
00:51:12,866 --> 00:51:17,776
Group by groups certain values
together under the same,

793
00:51:18,126 --> 00:51:21,296
so instead of having me
mentioned 3 times, it would,

794
00:51:21,296 --> 00:51:22,486
it would just mention
me one time.

795
00:51:23,636 --> 00:51:26,976
So, group by collections
dot staff ID again.

796
00:51:27,276 --> 00:51:30,556
And this may be a little
bit beyond the scope,

797
00:51:30,556 --> 00:51:32,346
scope of this course but I
would like to show you what,

798
00:51:32,676 --> 00:51:36,486
how useful this can, how,
how useful sequel can be.

799
00:51:37,016 --> 00:51:40,326
Just copy it.

800
00:51:43,256 --> 00:51:45,306
So, I query this,
and you can see here

801
00:51:45,566 --> 00:51:50,506
that this tells us how many
CDs each individual has.

802
00:51:57,156 --> 00:51:59,676
Okay, does anyone have
any questions on anything

803
00:52:00,376 --> 00:52:04,616
that I mentioned so far from SQL

804
00:52:04,686 --> 00:52:08,226
to database structures
to the example?

805
00:52:08,756 --> 00:52:12,496
>> You mentioned database
normalization before.

806
00:52:12,496 --> 00:52:18,016
Are there tools to take a blasé,
dull database and normalize it?

807
00:52:18,016 --> 00:52:20,776
>> Well, typically
in, in the, the,

808
00:52:20,776 --> 00:52:23,026
so the question is are
there any tools out there

809
00:52:23,026 --> 00:52:27,066
that take an existing
data set or data structure

810
00:52:27,066 --> 00:52:31,616
and I guess convert it
or give suggestions to,

811
00:52:32,546 --> 00:52:34,686
how to normalize the database.

812
00:52:35,366 --> 00:52:37,006
I believe there are tools in,

813
00:52:37,876 --> 00:52:39,876
in the actual relational
database management systems

814
00:52:39,876 --> 00:52:42,126
themselves that would make
recommendations for you.

815
00:52:42,786 --> 00:52:45,576
But ultimately, it's, it's, in
the end, at the end of the day,

816
00:52:45,576 --> 00:52:48,586
it's a, it's a computer
thinking for you.

817
00:52:48,586 --> 00:52:51,406
So, it's best that you do
it yourself if you want

818
00:52:51,406 --> 00:52:53,466
to apply the, the,
the right tenets

819
00:52:53,466 --> 00:52:55,526
of relational database design.

820
00:52:56,286 --> 00:53:00,036
But yes, there are,
there are systems

821
00:53:00,106 --> 00:53:02,076
that will make recommendations
for you

822
00:53:02,796 --> 00:53:08,856
and might do it for you as well.

823
00:53:09,056 --> 00:53:10,486
Any other questions?

824
00:53:11,516 --> 00:53:25,136
[ Inaudible audience comment ]

825
00:53:25,636 --> 00:53:27,216
>> Sorry, what was the
last part of your question?

826
00:53:27,856 --> 00:53:30,536
>> I mean, just the
change in the [inaudible]

827
00:53:30,536 --> 00:53:32,946
onto the database queried?

828
00:53:33,556 --> 00:53:36,816
>> Oh, so the question is can,
can we change the character set

829
00:53:36,816 --> 00:53:39,206
of a database after
it has been created.

830
00:53:39,206 --> 00:53:40,246
Is that, that's your question?

831
00:53:40,416 --> 00:53:40,656
>> Right.

832
00:53:40,716 --> 00:53:41,526
>> The answer is yes.

833
00:53:42,576 --> 00:53:43,356
>> Same character set?

834
00:53:43,856 --> 00:53:44,506
>> Character set.

835
00:53:45,396 --> 00:53:46,536
The collation, right?

836
00:53:47,776 --> 00:53:49,846
The collation, like
the, the character set.

837
00:53:50,026 --> 00:53:50,996
Yes, the answer's yes.

838
00:53:51,776 --> 00:53:54,086
I can go ahead right now
and I'll show you how.

839
00:53:54,606 --> 00:53:58,486
Let's suppose I go
into structure.

840
00:53:59,676 --> 00:54:06,486
And date acquired,
where is it, collation.

841
00:54:06,556 --> 00:54:08,226
Oh, okay, so this
is date acquired.

842
00:54:08,546 --> 00:54:11,146
I can go ahead and change
it to ask a general,

843
00:54:11,606 --> 00:54:13,156
I just picked one
by, by randomly.

844
00:54:14,276 --> 00:54:14,576
That's it.

845
00:54:14,576 --> 00:54:17,146
And I changed it.

846
00:54:17,146 --> 00:54:22,406
And if I look at the
data, of collections,

847
00:54:23,356 --> 00:54:24,236
it still looks the same.

848
00:54:24,926 --> 00:54:28,806
So, unless you're, unless you're
already in a character set

849
00:54:29,396 --> 00:54:31,306
that doesn't have an
equivalent for it, in,

850
00:54:31,306 --> 00:54:32,576
in the other character
set you're not going

851
00:54:32,576 --> 00:54:33,196
to see a difference.

852
00:54:33,736 --> 00:54:36,826
But if this were, for
example, if I initially had my,

853
00:54:37,416 --> 00:54:41,826
my database storing Chinese
characters and then I decided

854
00:54:41,826 --> 00:54:42,856
to change it to Latin, right?

855
00:54:42,856 --> 00:54:44,846
You, you're probably going to
see a bunch of question marks

856
00:54:44,916 --> 00:54:48,006
down at, for, for the Chinese
values once you convert it.

857
00:54:48,476 --> 00:54:50,256
But it will not change
anything otherwise.

858
00:54:50,826 --> 00:54:54,006
I mean, it's fine,
you can do it.

859
00:54:57,536 --> 00:54:58,286
Any more questions?

860
00:55:08,276 --> 00:55:11,126
Good. So, I guess we could,
we can conclude this session,

861
00:55:11,166 --> 00:55:17,846
and well, before I go actually,
let me mention one thing

862
00:55:18,826 --> 00:55:23,116
on the Power Point,
on the presentation.

863
00:55:25,666 --> 00:55:27,906
So, recommended things
to research.

864
00:55:27,906 --> 00:55:30,526
I do recommend, I know
you're working hard

865
00:55:30,526 --> 00:55:33,296
on your projects right now,
but when you do have time,

866
00:55:33,976 --> 00:55:36,446
I do recommend that you
research certain things.

867
00:55:36,696 --> 00:55:40,066
My sequel data types,
because you will be working

868
00:55:40,066 --> 00:55:44,176
with my sequel, my SQL,
I keep saying it wrong.

869
00:55:44,726 --> 00:55:48,326
My SQL in the next project, so I
recommend that you visit those,

870
00:55:48,326 --> 00:55:53,116
the different data types that it
supports and what each one does.

871
00:55:53,116 --> 00:55:55,406
Primary key and foreign
key constraints.

872
00:55:55,766 --> 00:55:57,016
Don't stress too much on the,

873
00:55:57,016 --> 00:56:01,786
on the actual constraints
themselves but it would be good

874
00:56:01,876 --> 00:56:04,686
for you to understand what
primary key and foreign key,

875
00:56:05,426 --> 00:56:10,106
what each means, and so that
you can conceptually apply them.

876
00:56:10,156 --> 00:56:14,606
Even, albeit not in
the technical sense,

877
00:56:14,896 --> 00:56:15,686
as I mentioned earlier.

878
00:56:15,686 --> 00:56:17,756
Like using through the,
through the database engine.

879
00:56:17,906 --> 00:56:18,816
You might not be able to,

880
00:56:18,916 --> 00:56:23,086
depending on which database
engine you choose to go with.

881
00:56:23,616 --> 00:56:28,636
The third thing that I recommend
you research is normalization.

882
00:56:29,436 --> 00:56:31,676
And when you're, when
you're doing your research

883
00:56:31,676 --> 00:56:34,196
on normalization,
again you don't want

884
00:56:34,196 --> 00:56:37,136
to spend too much time
on it, but enough time

885
00:56:37,136 --> 00:56:39,816
to understand to, how you can
reach the third normal form.

886
00:56:40,446 --> 00:56:41,616
Because in normalization,

887
00:56:41,616 --> 00:56:43,706
there are different
norm, normal forms.

888
00:56:44,216 --> 00:56:45,936
Again, this is beyond
the scope of this class

889
00:56:46,846 --> 00:56:51,456
but I think third normal form
would suf, suffice for now.

890
00:56:51,456 --> 00:56:53,816
Lastly, the sequel
commands we mentioned today.

891
00:56:55,216 --> 00:56:59,226
Select, put, put some
focus on select, insert,

892
00:56:59,866 --> 00:57:05,826
delete and update because
these are the commands

893
00:57:05,826 --> 00:57:07,616
that you'll be mostly
using in your code

894
00:57:07,616 --> 00:57:10,166
to manipulate the data.

895
00:57:10,166 --> 00:57:12,576
It is very unlikely that
you'll be using create,

896
00:57:12,576 --> 00:57:13,776
alter, add and drop.

897
00:57:14,556 --> 00:57:18,166
They probably won't be even
used at all in your projects.

898
00:57:19,406 --> 00:57:21,466
But these, these are
the more prominent ones

899
00:57:21,566 --> 00:57:23,626
that will be more used.

900
00:57:27,636 --> 00:57:34,226
And as always, if you have any
questions you can visit CS 75

901
00:57:34,226 --> 00:57:36,266
dot net forward slash discuss

902
00:57:37,766 --> 00:57:39,916
and we'll be there
to assist you.

903
00:57:41,546 --> 00:57:43,636
One more thing.

904
00:57:44,586 --> 00:57:48,596
I know I didn't go through,
I mentioned everything

905
00:57:48,596 --> 00:57:51,936
with the exception of updates.

906
00:57:53,406 --> 00:57:56,206
The update command, before I go.

907
00:57:56,436 --> 00:58:02,606
So, the update command
basically updates a target,

908
00:58:03,076 --> 00:58:04,606
updates information for a target

909
00:58:04,606 --> 00:58:06,536
of your choice inside
a certain table.

910
00:58:07,136 --> 00:58:09,796
Let's suppose that I would

911
00:58:09,796 --> 00:58:13,246
like to update my name
from the staff table.

912
00:58:13,426 --> 00:58:16,226
So, I can say update, and the,

913
00:58:16,276 --> 00:58:18,686
the syntax for this
is update table name.

914
00:58:19,306 --> 00:58:23,236
Set, I'll put it on
the next, next line,

915
00:58:23,236 --> 00:58:24,056
just so it looks good.

916
00:58:24,396 --> 00:58:27,066
Set, and then the column name.

917
00:58:27,226 --> 00:58:28,726
Let's say, let's, first name.

918
00:58:28,726 --> 00:58:33,656
I would like to set first
name to, I would to all,

919
00:58:33,656 --> 00:58:36,176
my name to be in all, all caps.

920
00:58:37,296 --> 00:58:40,946
Where I don't know my, I
do not know what my ID is

921
00:58:41,056 --> 00:58:48,106
but let's just say where first
name like, it's, it looks like,

922
00:58:48,106 --> 00:58:54,496
I mean, it starts
with, or it has this,

923
00:58:54,566 --> 00:58:55,606
the following sequence.

924
00:58:56,116 --> 00:59:00,006
ALA and then whatever happens.

925
00:59:00,676 --> 00:59:04,926
So, the, the percentage
sign here could be zero

926
00:59:05,296 --> 00:59:06,166
or many characters.

927
00:59:11,876 --> 00:59:13,296
So, when I run this command,

928
00:59:14,136 --> 00:59:16,106
what should happen,
one row is affected.

929
00:59:16,796 --> 00:59:19,916
What I should see happen here
is, oh, it's under staff.

930
00:59:20,426 --> 00:59:23,936
You can see now that
my name is capitalized.

931
00:59:25,166 --> 00:59:27,596
I could have essentially
done where ID equals the 2

932
00:59:27,596 --> 00:59:30,006
but I don't know my ID and I
didn't feel like going back.

933
00:59:30,356 --> 00:59:33,486
Now, does anyone have any
questions about update

934
00:59:33,486 --> 00:59:36,056
or would you like to see an
example of, of an update,

935
00:59:36,086 --> 00:59:39,516
insert, select, delete?

936
00:59:39,976 --> 00:59:47,526
Just to shed light,
some more light on this?

937
00:59:47,736 --> 00:59:49,516
All good? Okay.

938
00:59:50,476 --> 00:59:53,346
Okay. Any questions over there?

939
00:59:53,586 --> 00:59:53,996
No, you're good?

940
00:59:54,146 --> 00:59:59,766
Okay. So, I guess we can
conclude this session, and next,

941
00:59:59,846 --> 01:00:05,576
in our next section Chris Gerber
will be covering some more

942
01:00:06,986 --> 01:00:11,206
concepts in sequel
such as indexes,

943
01:00:11,956 --> 01:00:15,216
transactions, commits, so forth.

944
01:00:16,686 --> 01:00:18,666
Thanks, thank you, everyone.

945
01:00:22,516 --> 01:00:33,010
[ Applause ]