1
00:00:00,320 --> 00:00:02,750
And a series of six on databases.

2
00:00:02,750 --> 00:00:06,000
The topic of this module is Advanced SQL.

3
00:00:07,700 --> 00:00:10,450
In the last module we talked about

4
00:00:10,450 --> 00:00:15,790
the basics of SQL syntax that you
use to talk to relational databases.

5
00:00:17,100 --> 00:00:18,850
We went through select and joins,

6
00:00:18,850 --> 00:00:23,440
aggregate functions, we talked about
creating databases and tables.

7
00:00:23,440 --> 00:00:27,310
Showing and describing tables and
databases that are available,

8
00:00:27,310 --> 00:00:31,760
how to insert data both
from an individual row and

9
00:00:31,760 --> 00:00:36,710
also from a bulk perspective and
then updating and deleting.

10
00:00:36,710 --> 00:00:41,012
So, those were from the, the basic
operations that you need to use to,

11
00:00:41,012 --> 00:00:43,320
to know to use a relational database.

12
00:00:44,550 --> 00:00:49,270
This time, in this module, we are talking
more about some of the advanced features.

13
00:00:53,680 --> 00:00:57,770
First one is what if I want to

14
00:00:57,770 --> 00:01:02,940
alter the structure of a table
that I've already created?

15
00:01:02,940 --> 00:01:06,648
It may be that this is simple enough
just to delete the whole thing and

16
00:01:06,648 --> 00:01:08,990
create a new table and
reload the data, but

17
00:01:08,990 --> 00:01:13,412
it may be that I've, I've done a lot of
ref operations on this table already, or

18
00:01:13,412 --> 00:01:17,800
that there's a lot of data in it and
therefore it's simpler to alter the table.

19
00:01:17,800 --> 00:01:21,309
The syntax for
this is the Alter SQL command.

20
00:01:22,690 --> 00:01:25,880
Alter table, table name and

21
00:01:25,880 --> 00:01:29,500
then the operations you want
to make to change the table.

22
00:01:30,600 --> 00:01:34,300
Let's say that I want to add
a column called B magnitude to

23
00:01:34,300 --> 00:01:38,240
my Star table that we
introduced last time.

24
00:01:38,240 --> 00:01:40,840
And I want to put it after my V magnitude.

25
00:01:40,840 --> 00:01:44,780
But the purpose of this
first command there.

26
00:01:44,780 --> 00:01:51,720
So I'd alter table star, add column,
the column that I'm adding, B magnitude.

27
00:01:51,720 --> 00:01:55,570
The data type of that column,
a double in this case.

28
00:01:55,570 --> 00:01:57,610
And I'm adding it after the V magnitude.

29
00:01:59,740 --> 00:02:04,860
If I want to remove that B magnitude
column at a, at a later date, then I can

30
00:02:04,860 --> 00:02:09,970
do it simply by doing ALTER TABLE star
DROP COLUMN and then the column name.

31
00:02:12,522 --> 00:02:16,260
There're other operations that
I can do with the ALTER TABLE.

32
00:02:17,980 --> 00:02:21,090
Changing names, changing data types,
that sort of thing.

33
00:02:21,090 --> 00:02:22,356
You can look those up.

34
00:02:26,967 --> 00:02:32,973
Now often when you're working with a
table, there's a particular operation that

35
00:02:32,973 --> 00:02:39,233
you're going to be doing on the table that
is repetitive a particular type of query,

36
00:02:39,233 --> 00:02:44,379
particular query for a certain subset
of it that you're going to use, and

37
00:02:44,379 --> 00:02:48,780
you could put that information
into a separate table.

38
00:02:48,780 --> 00:02:52,840
But one thing you can do with a relational
database is what's called CREATE a VIEW,

39
00:02:54,080 --> 00:02:59,040
which is a,
a specification of a subset of a table,

40
00:02:59,040 --> 00:03:03,610
a set of tables, which you're going to
treat as a table in its own right.

41
00:03:03,610 --> 00:03:06,820
But you don't want to go through all
the hassle of creating that as a table in

42
00:03:06,820 --> 00:03:08,740
its own right because it may be that the,

43
00:03:08,740 --> 00:03:15,280
that's repeating data you don't
necessarily need to do that.

44
00:03:15,280 --> 00:03:22,740
So a view is very useful, and for
that context the syntax is CREATE VIEW and

45
00:03:22,740 --> 00:03:29,610
the viewName AS, and
then you specify a select statement

46
00:03:29,610 --> 00:03:37,310
which defines the the, the data
that is associated with that view.

47
00:03:37,310 --> 00:03:41,117
So let's say I just,
there's a particular region of, of,

48
00:03:41,117 --> 00:03:47,270
of sky which I'm going to be using for
a lot of operations and

49
00:03:47,270 --> 00:03:50,940
I can define of that as a view,
through select star from star.

50
00:03:50,940 --> 00:03:52,160
How, how,

51
00:03:52,160 --> 00:03:57,210
where I then define the positional
constraints defining that region.

52
00:03:58,380 --> 00:04:04,140
I can then do a regular select statement
and instead of using the table name,

53
00:04:04,140 --> 00:04:09,780
I can use the, the name of my view,
in this case region one view, as the table

54
00:04:09,780 --> 00:04:16,020
and it will only use the data that
is defined by that particular view.

55
00:04:16,020 --> 00:04:19,544
So it will use the data within
the positional constraints on

56
00:04:19,544 --> 00:04:24,020
the sky that I've defined as the first
data set that it's working on.

57
00:04:24,020 --> 00:04:26,980
And then apply the additional constraints
that I'm using in the WHERE statement and

58
00:04:26,980 --> 00:04:28,317
my SELECT statement.

59
00:04:28,317 --> 00:04:34,790
Similarly I can create a view

60
00:04:34,790 --> 00:04:39,490
as the result of an inter join in this
particular case with more information.

61
00:04:40,711 --> 00:04:46,370
And because I've specified that in a join

62
00:04:46,370 --> 00:04:51,940
there will be those column names in
the view that inner join rule look

63
00:04:51,940 --> 00:04:57,030
as though it's a table tool intended
purposes and I can query it

64
00:04:57,030 --> 00:05:02,420
as if it were a table in its own right,
even though from a programmatic

65
00:05:02,420 --> 00:05:07,260
perspective it's been created on
the flight by the relational database.

66
00:05:07,260 --> 00:05:13,421
So views can be incredibly powerful ways
of making your life more manageable.

67
00:05:15,796 --> 00:05:20,439
Next indexes when you're
working with a database and

68
00:05:20,439 --> 00:05:26,207
you're doing querying, sometimes
the performance can be very slow.

69
00:05:26,207 --> 00:05:33,799
This can be because of the way that
the data is arranged on the disk and

70
00:05:33,799 --> 00:05:39,401
ways around this are to
create specific lookup tables

71
00:05:39,401 --> 00:05:44,870
to allow fast access or specific lookup.

72
00:05:44,870 --> 00:05:48,090
Data structures to allow our
fast access to, for data.

73
00:05:49,560 --> 00:05:53,360
Syntax for this is Create Index,
Index Name, or

74
00:05:53,360 --> 00:05:57,030
a Table Name and
the columns that you're eating in that.

75
00:05:57,030 --> 00:06:00,740
So, it may be that I'm going to be
running a number of queries against the V

76
00:06:00,740 --> 00:06:02,190
magnitude column in my table.

77
00:06:03,760 --> 00:06:05,710
And I'm finding that they're
being particularly slow.

78
00:06:06,810 --> 00:06:10,400
I would hope that by
creating an index on them,

79
00:06:10,400 --> 00:06:12,950
any queries running against
that will actually be faster.

80
00:06:14,380 --> 00:06:17,050
There're two types of
queries that you can,

81
00:06:17,050 --> 00:06:21,330
two types of indexes that
you can have on a table.

82
00:06:21,330 --> 00:06:23,980
You have what's called a clustered index,
first of all.

83
00:06:23,980 --> 00:06:27,509
And this is one in which the ordering of
data entry is, is actually the same as

84
00:06:27,509 --> 00:06:31,000
the ordering of the data records as
they are physically put on to disc.

85
00:06:32,304 --> 00:06:37,080
I've already said in
the previous talks that if you

86
00:06:37,080 --> 00:06:42,620
have a primary key on a table that
is automatically a clustered index.

87
00:06:43,812 --> 00:06:46,630
Since you, there can only be
one clustered index per table,

88
00:06:46,630 --> 00:06:51,490
if you already have a primary key on
your table, any other indexes you

89
00:06:51,490 --> 00:06:56,470
create on that table will be
unclustered and will make use therefore

90
00:06:57,690 --> 00:07:01,770
of specific structures in the relational
database for doing their lookup.

91
00:07:02,860 --> 00:07:07,898
However, you can have multiple
unclustered indexes on the same table.

92
00:07:10,009 --> 00:07:13,732
They are typically implemented
as B plus trees which are a very

93
00:07:13,732 --> 00:07:18,458
efficient data look up structure,
but there are alternate types, for

94
00:07:18,458 --> 00:07:23,400
implementation depending on the type
of data that you're working with, and

95
00:07:23,400 --> 00:07:26,654
the relational database
system that you're using.

96
00:07:31,633 --> 00:07:36,591
It may be that there is a,
a collection of operations that I'm

97
00:07:36,591 --> 00:07:41,463
constructing that I'm,
I'm carrying out on my database or

98
00:07:41,463 --> 00:07:47,830
my database table that I'm going
to be repeating a number of times.

99
00:07:47,830 --> 00:07:52,560
And in the same way that if I was
writing doing this programmatically,

100
00:07:52,560 --> 00:07:57,860
I might actually write this up as
a as a function in the database,

101
00:07:57,860 --> 00:08:00,770
well if you can write this
as a stored procedure.

102
00:08:02,000 --> 00:08:04,070
It is the set of operations
that you can neglect.

103
00:08:04,070 --> 00:08:08,340
The, the set of select statements a, and,
and joins and, and such, that you're

104
00:08:08,340 --> 00:08:13,480
going to work to give you the, the answer
that you want for a particular operation.

105
00:08:14,650 --> 00:08:16,500
Syntax is CREATE PROCEDURE,

106
00:08:16,500 --> 00:08:21,150
the procedureName, and then the parameters
that the procedure can take.

107
00:08:21,150 --> 00:08:26,260
The arguments of the function and then
the declaration of what it's required.

108
00:08:27,602 --> 00:08:32,303
In this particular case,
example given here, I'm creating a,

109
00:08:32,303 --> 00:08:36,157
a procedure called
findNearestNeighbour to a star.

110
00:08:39,621 --> 00:08:42,950
Where I'm going to just
take that as the name.

111
00:08:43,970 --> 00:08:49,100
I'm assuming that there is already
an existing function in my database,

112
00:08:49,100 --> 00:08:50,520
an existing procedure,

113
00:08:50,520 --> 00:08:55,290
which will give me the nearest neighbor
giving, given a position on the sky.

114
00:08:55,290 --> 00:09:02,080
In writing this procedure, the idea is
that it, the procedure will do a lookup

115
00:09:02,080 --> 00:09:09,080
to get the position of the starName
I've given, and then pass the position

116
00:09:09,080 --> 00:09:14,770
information to the existing procedure and
return the result of that.

117
00:09:14,770 --> 00:09:16,420
So what I'd first of all do,

118
00:09:16,420 --> 00:09:21,390
is CREATE PROCEDURE,
name of the procedure the argument.

119
00:09:21,390 --> 00:09:24,740
It's going to be type of the argument
is varchar, variable character,

120
00:09:24,740 --> 00:09:26,730
of up to the length 20.

121
00:09:26,730 --> 00:09:33,910
And then I declare the internal
parameters for this procedure.

122
00:09:33,910 --> 00:09:36,350
I'm, those are den, denoted by the @ sign.

123
00:09:36,350 --> 00:09:39,490
I'm declaring an RA in the deck.

124
00:09:39,490 --> 00:09:40,936
Variables, these are of type float.

125
00:09:40,936 --> 00:09:47,240
I am declaring a name variable that of
varchar 20 and then I say Select and

126
00:09:47,240 --> 00:09:54,180
then I set the RA variable to
the value of the RA column.

127
00:09:54,180 --> 00:09:58,160
The deck variable to the value of
the Deck column from my table star,

128
00:09:58,160 --> 00:10:03,350
where the name column is like
the star name that I've given

129
00:10:04,770 --> 00:10:08,680
as the argument to my procedure and
then I select

130
00:10:08,680 --> 00:10:14,180
name from getNearestNeighbour
with theRA deck and end.

131
00:10:14,180 --> 00:10:19,840
And then to execute this, I use the exact
statement, exact FindNearestNeighbor,

132
00:10:19,840 --> 00:10:27,400
Sirius, in quotes and that would then
give me the nearest neighbor star.

133
00:10:27,400 --> 00:10:31,699
The name of the nearest neighbor
star to Sirius in my database.

134
00:10:31,699 --> 00:10:40,620
cursors, are a way of
actually physically doing

135
00:10:42,600 --> 00:10:48,210
going through a table an op, op, doing an
operation on a table, one row at a time.

136
00:10:50,570 --> 00:10:53,600
And there is a,
a particular set of syntax for them.

137
00:10:54,960 --> 00:10:58,485
The next slide we will give an example of,
of exactly how to do that.

138
00:10:58,485 --> 00:11:03,710
And it has to be said though that
cursors are the slowest way of

139
00:11:03,710 --> 00:11:07,710
accessing data in a relational
database there's operation of

140
00:11:07,710 --> 00:11:12,740
physically going through one row at a time
in a table is, is very inefficient.

141
00:11:12,740 --> 00:11:17,110
And not utilizing the,
the, the joining power of,

142
00:11:17,110 --> 00:11:19,350
of the database rationally to this.

143
00:11:19,350 --> 00:11:22,830
But sometimes it's necessary for
the type of operation that you want to do,

144
00:11:22,830 --> 00:11:25,790
that you want to make use of
this sort of functionality.

145
00:11:29,030 --> 00:11:34,050
In this particular case, for
each row in our data set scenarios, we

146
00:11:34,050 --> 00:11:39,160
want to update a particular StellarModel
that we have that exists elsewhere.

147
00:11:40,604 --> 00:11:46,379
And for some reason we can't do
this as the result of a drawing.

148
00:11:47,908 --> 00:11:54,920
So syntaxes we first of all
declare a number of variables.

149
00:11:54,920 --> 00:12:00,110
We declare a name variable and
the data type on that, varchar 20,

150
00:12:00,110 --> 00:12:06,330
we declare a magnitude variable and
it's of the float data type, and

151
00:12:06,330 --> 00:12:11,120
then we declare our cursor, this is the
thing that's going to go through each row.

152
00:12:11,120 --> 00:12:17,440
We say it's called starCursor and then we
say what it does is it selects the name

153
00:12:17,440 --> 00:12:22,820
and the average V magnitude from our table
star when I'm grouping by stellarType.

154
00:12:26,030 --> 00:12:32,628
so, that's going to give me those
two pieces of information for

155
00:12:32,628 --> 00:12:39,370
my from my star table and
then I open my star cursor.

156
00:12:39,370 --> 00:12:44,531
What I say is that I, I fetch into
my cursor that information and

157
00:12:44,531 --> 00:12:47,840
the name and the magnitude and

158
00:12:47,840 --> 00:12:53,760
then I have some stored procedure
already called Update Stellar Model.

159
00:12:53,760 --> 00:12:58,400
And that I'm going to run that based
on the values of, of the, the name and

160
00:12:58,400 --> 00:13:07,230
magnitude that I've loaded from running
my cursor on my particular operations.

161
00:13:07,230 --> 00:13:12,775
So in this particular way,
I'm going to be going through

162
00:13:12,775 --> 00:13:18,977
the different types of stellar
type that I have in my star table.

163
00:13:18,977 --> 00:13:24,142
I'm getting their average magnitudes, and,
and the name of that type of stellar type

164
00:13:24,142 --> 00:13:29,043
and I'm updating my stellar model with
the values of those average V magnitudes.

165
00:13:31,513 --> 00:13:35,527
yeah.

166
00:13:35,527 --> 00:13:40,615
Triggers are a way of having particular
operations in the database happen

167
00:13:40,615 --> 00:13:45,161
depending on, on other things that
you've done, so it may be that if I

168
00:13:45,161 --> 00:13:50,632
insert data into one table, there's
another table which needs to be updated or

169
00:13:50,632 --> 00:13:55,980
if I update data one table, an operation
has to have been formed on another table.

170
00:13:55,980 --> 00:14:00,090
Or if I delete data, another
operation has to be, has to be done.

171
00:14:01,900 --> 00:14:07,330
So this, in the syntax here I'm creating
a trigger called star trigger on my table

172
00:14:07,330 --> 00:14:14,320
star for an update,
saying that if if the V magnitude

173
00:14:14,320 --> 00:14:21,090
column is updated in any way through
an update statement, then I would execute

174
00:14:21,090 --> 00:14:26,400
this a stalled procedure that I've called
refreshed models which would presumably do

175
00:14:26,400 --> 00:14:31,300
something to other tables on the basis
of that information that I've updated.

176
00:14:31,300 --> 00:14:34,589
So other operations in
the database are triggered by

177
00:14:34,589 --> 00:14:37,290
an operation I've created on that table.

178
00:14:40,315 --> 00:14:44,284
finally, I'll give an example
of how you can work with a,

179
00:14:44,284 --> 00:14:48,580
a relational database from
a programmatic perspective.

180
00:14:49,910 --> 00:14:55,155
A lot of relational databases
have web-based interfaces, or

181
00:14:55,155 --> 00:14:57,220
command-line-based interfaces for

182
00:14:57,220 --> 00:15:02,630
you to specifically type SQL
statements in, but often it's

183
00:15:02,630 --> 00:15:06,130
very useful to actually be able to access
the database through an interface layer.

184
00:15:07,150 --> 00:15:09,250
So that you can write code to do it.

185
00:15:09,250 --> 00:15:14,410
And this little code snippet here is
an example of how to do that with Python.

186
00:15:15,820 --> 00:15:18,910
So I am, there's a particular
Python module, in this case called,

187
00:15:18,910 --> 00:15:24,530
MySQLdb, which I'm importing,
and then I specify

188
00:15:24,530 --> 00:15:29,630
the connection details for
my code to be able to

189
00:15:29,630 --> 00:15:34,580
connect to a database server somewhere
which is listing on a particular port.

190
00:15:34,580 --> 00:15:38,200
I give it my, my username for the
relational database that I'm accessing,

191
00:15:38,200 --> 00:15:42,000
my password and the name of
the database that I'm accessing.

192
00:15:43,120 --> 00:15:48,383
I then construct a cursor in
this particular case this is

193
00:15:48,383 --> 00:15:54,690
just the data structure that is
defined in the MySQL statement.

194
00:15:54,690 --> 00:15:57,030
It's not a database cursor.

195
00:15:59,148 --> 00:16:01,120
I then define the SQL statement.

196
00:16:01,120 --> 00:16:07,750
In this case I'm just wanted to return
everything from my table staff,

197
00:16:07,750 --> 00:16:13,130
I execute that so the Python codes
sends the appropriate SQL command

198
00:16:13,130 --> 00:16:19,080
over the wire to the database and the
information is piped back, and then I can

199
00:16:19,080 --> 00:16:24,960
fetch all the results, or I could fetch
the results one at a time, or, or do, or

200
00:16:24,960 --> 00:16:30,750
all manner of operations to retrieve
the data and work with it appropriately.

201
00:16:30,750 --> 00:16:35,660
And those are specified in
the MySQLDB documentation.

202
00:16:35,660 --> 00:16:41,390
But, there you have an example
of how you can access a relation

203
00:16:41,390 --> 00:16:45,960
base programatically,
making use of your your,

204
00:16:45,960 --> 00:16:49,540
your SQL but
you're not using the interface that's been

205
00:16:49,540 --> 00:16:53,610
provided by the DBMS you're doing it
through a standard programmatic interface.

206
00:16:53,610 --> 00:16:56,170
And such interfaces, such modules are,

207
00:16:56,170 --> 00:17:00,219
are defined for any manner of computer
languages and most databases.

208
00:17:01,485 --> 00:17:02,860
And that is the end of this module.