WEBVTT

1
00:00:01.760 --> 00:00:05.180
alright so as I mentioned at the end of
the last video we finish the

2
00:00:05.180 --> 00:00:08.930
introduction to databases in the sql
language and what we're going to start

3
00:00:08.930 --> 00:00:14.570
working on the next video is how we can
use sql lite in java programs but

4
00:00:14.570 --> 00:00:18.470
before we actually get to that video
let's get through into a few more things

5
00:00:18.470 --> 00:00:22.520
we're going to start with backing up the
database again I'm going to type....

6
00:00:22.520 --> 00:00:30.920
.....so what I suggest you do

7
00:00:30.920 --> 00:00:35.149
now is experiment with the commands that
we've used to search for different

8
00:00:35.149 --> 00:00:39.889
groups of records from the three tables
and also try creating queries that use

9
00:00:39.889 --> 00:00:44.809
joins as well as they already joined view
that we created and actually there's not

10
00:00:44.809 --> 00:00:48.409
much point showing you how to back up the
database if I don't show you how to get

11
00:00:48.409 --> 00:00:51.979
back should you need to I know we have
restored it previously but we're going

12
00:00:51.979 --> 00:00:56.780
to restore this music one now database is
restored from the backup as you saw

13
00:00:56.780 --> 00:01:01.399
using the .restore command but if we
restore right now you have no real

14
00:01:01.399 --> 00:01:05.750
indication that it worked so let's
actually trash some songs first so that

15
00:01:05.750 --> 00:01:10.430
we can confirm that now most of the
album's have fewer than 50 tracks so we

16
00:01:10.430 --> 00:01:14.600
can delete all songs whos track number is
less than 50 and that should leave us

17
00:01:14.600 --> 00:01:18.110
with very few records so let's go ahead
and do that i'm going to type....

18
00:01:18.110 --> 00:01:30.470
....

19
00:01:30.470 --> 00:01:34.370
....obviously we've got too much smaller
list than what we had before and in

20
00:01:34.370 --> 00:01:39.710
fact we're left with songs from only two
albums and that is easy to see if we use

21
00:01:39.710 --> 00:01:45.860
the view so if you look at the views so....

22
00:01:45.860 --> 00:01:51.860
...you can see they're clearly there's
only two now as you saw for the

23
00:01:51.860 --> 00:01:55.760
condition in the where clause you can
use less than greater than less than or

24
00:01:55.760 --> 00:01:58.729
equal to and so on just like
you'd expect

25
00:01:58.729 --> 00:02:03.440
now sql uses less than or greater than
or not equal to which makes

26
00:02:03.440 --> 00:02:08.780
sense when you read it and avoid having
to introduce another symbol other than

27
00:02:08.780 --> 00:02:13.069
that the comparison operators are the
same you'd normally expect in Java so we

28
00:02:13.069 --> 00:02:14.900
can drop a song from the list

29
00:02:14.900 --> 00:02:19.610
only selecting songs with a track number
not equal to 71 so we could do that with

30
00:02:19.610 --> 00:02:31.939
select....so you can see at that point

31
00:02:31.939 --> 00:02:36.140
now recycled vinyl blues which was
showing in the previous list is now no

32
00:02:36.140 --> 00:02:40.640
longer showing now the other thing you
can do which we haven't talked about is

33
00:02:40.640 --> 00:02:44.450
you can include functions in the Select
statement now i'm not going to go into a

34
00:02:44.450 --> 00:02:48.500
range of functions available but one
useful one is count so you can do....

35
00:02:48.500 --> 00:03:09.319
....

36
00:03:09.319 --> 00:03:16.189
....you can see we've got
24 entries for songs 439

37
00:03:16.189 --> 00:03:19.430
records for albums and 202 for artists

38
00:03:20.329 --> 00:03:24.799
alright so let's now restore the database
and actually see how many records you've

39
00:03:24.799 --> 00:03:32.900
got back so do . restore music back up 2
remember there's no semicolon because

40
00:03:32.900 --> 00:03:37.639
it's a sql lite command because it
starts with the . let's do the

41
00:03:37.639 --> 00:03:45.379
equivalent commands now the select....

42
00:03:45.379 --> 00:04:00.829
....

43
00:04:00.829 --> 00:04:05.989
....so clearly

44
00:04:05.989 --> 00:04:10.069
the restore worked alright so let's
finish this video now with a challenge

45
00:04:10.069 --> 00:04:13.069
for you to help get you started

46
00:04:15.190 --> 00:04:17.730
so I got a number of things for
you too

47
00:04:17.730 --> 00:04:22.690
come up with the sql commands to
return these the results that I'm asking

48
00:04:22.690 --> 00:04:27.940
for so first one here number one select
the titles of all the songs on the album

49
00:04:27.940 --> 00:04:33.880
forbidden and second one repeat the
previous query but this time display the

50
00:04:33.880 --> 00:04:37.990
songs in track order now you may want to
include the track number in the output

51
00:04:37.990 --> 00:04:42.250
to verify that it worked okay number
three display all songs for the band

52
00:04:42.250 --> 00:04:48.550
deep purple number 4 rename the band
mehitable to one kitten hope I pronounce

53
00:04:48.550 --> 00:04:53.050
that right and note that this is an
exception to the advice to always fully

54
00:04:53.050 --> 00:04:57.840
qualify your column names set space
artist.name won't work

55
00:04:57.840 --> 00:05:01.180
you just need to specify name and you'll
see that when you give that a go

56
00:05:01.720 --> 00:05:05.080
number five check that the record was
correctly renamed that you did in part

57
00:05:05.080 --> 00:05:07.440
four

58
00:05:07.440 --> 00:05:12.780
continuing on number six select the
title of all the songs by aerosmith in

59
00:05:12.780 --> 00:05:18.630
alphabetical order include only the
title in the output number 7 replace the

60
00:05:18.630 --> 00:05:23.220
column that you used in the previous
answer with count title in parentheses

61
00:05:23.220 --> 00:05:27.840
to get just a count of the number of the
songs number of songs number 8

62
00:05:27.840 --> 00:05:31.900
search the internet to find out how to
get a list of the songs from step 6

63
00:05:31.900 --> 00:05:35.190
without any duplicates number 9

64
00:05:35.190 --> 00:05:39.270
search the internet again this time to
find out how to get a count of the songs

65
00:05:39.270 --> 00:05:43.900
without duplicates and hint it uses the
same keyword as step 8

66
00:05:43.900 --> 00:05:49.650
but the syntax may not be obvious and
number ten repeat the previous query to

67
00:05:49.650 --> 00:05:53.280
find the number of artists which
obviously should be one and the number

68
00:05:53.280 --> 00:05:57.430
of albums so that's it that's your
challenge 10 things for you to have

69
00:05:57.430 --> 00:06:01.740
a go at so pause the video and
go away and see if you can figure those out and

70
00:06:01.740 --> 00:06:04.960
when you're ready to see me come up with
the answers start the video again so

71
00:06:04.960 --> 00:06:12.240
pause the video now and i'll see you
when you get back alright so how did you go

72
00:06:12.240 --> 00:06:14.020
hopefully you managed to figure it out

73
00:06:14.020 --> 00:06:19.210
let's have a go so start the first one
and if you recall the first

74
00:06:19.210 --> 00:06:24.810
challenge was select the titles of all
the songs on the album forbidden to do

75
00:06:24.810 --> 00:06:36.090
that we do.....

76
00:06:36.090 --> 00:06:47.130
....

77
00:06:47.130 --> 00:06:54.960
alright so that's all the title from
the album forbidden number two was to

78
00:06:54.960 --> 00:07:00.000
repeat the previous query but this time
display the songs in track order and you

79
00:07:00.000 --> 00:07:03.810
may want to include the track number in
the output to verify that work ok so

80
00:07:03.810 --> 00:07:10.240
let's type that up....

81
00:07:10.240 --> 00:07:13.650
.....

82
00:07:15.200 --> 00:07:29.900
.....

83
00:07:29.900 --> 00:07:41.600
...so we got the equivalent to the previous query

84
00:07:41.600 --> 00:07:44.930
but this time sorted in track order and
you can see we've got the track order on the

85
00:07:44.930 --> 00:07:49.370
screen they're verifying that worked all
right number three display all songs for

86
00:07:49.370 --> 00:07:59.210
the band deep purple alright so....

87
00:07:59.210 --> 00:08:37.940
....

88
00:08:37.940 --> 00:08:42.680
....you can see we've got

89
00:08:42.680 --> 00:08:45.230
quite a few entries there for deep
purple

90
00:08:45.230 --> 00:08:48.980
alternatively also you could have used
the artists_list view that

91
00:08:48.980 --> 00:08:52.040
would have been acceptable as well so
that would have been much easier

92
00:08:52.040 --> 00:09:00.920
actually just select....

93
00:09:00.920 --> 00:09:09.050
....

94
00:09:09.050 --> 00:09:14.300
alright continuing on now number 4 we want
to rename the band mehitable

95
00:09:14.300 --> 00:09:19.040
to one kitten and
this was the one where I mention that there was

96
00:09:19.040 --> 00:09:22.610
an exception to the advice to always
fully qualify your column

97
00:09:22.610 --> 00:09:26.060
names because set artist.name
won't work

98
00:09:26.060 --> 00:09:28.070
you just need to specify name in this
scenario

99
00:09:28.070 --> 00:09:40.280
so to do that we do update.....

100
00:09:40.280 --> 00:09:51.350
....

101
00:09:51.350 --> 00:09:54.950
......so now we can confirm that
confirm the update working in otherword

102
00:09:54.950 --> 00:10:07.100
select star.....and we can now

103
00:10:07.100 --> 00:10:11.390
see that we've got an entry for that so
that worked okay and checking it was ok

104
00:10:11.390 --> 00:10:14.840
the records correctly rename which was actually
challenge 5 just to be clear

105
00:10:14.840 --> 00:10:19.160
alright so that's one we've just done
so moving on now number 6 we have to

106
00:10:19.160 --> 00:10:23.480
select the titles of all the songs
by aerosmith in alphabetical order

107
00:10:23.480 --> 00:10:28.280
including only the title in the output
that's actually quite simple one so....

108
00:10:28.280 --> 00:10:39.200
....

109
00:10:39.200 --> 00:10:44.660
.....

110
00:10:44.660 --> 00:10:51.290
....alright the next one we want is you
want to replace the column that you used

111
00:10:51.290 --> 00:10:55.670
in the previous answer with count title
to get just a count of the number of the

112
00:10:55.670 --> 00:11:01.430
songs instead of the actual titles that
would be....

113
00:11:01.430 --> 00:11:13.100
.....we should

114
00:11:13.600 --> 00:11:19.390
get the answer 151 their 151 as you can see now note that

115
00:11:19.390 --> 00:11:23.530
the in this particular case the order by
Clause is redundant here because we're

116
00:11:23.530 --> 00:11:25.120
doing a count we don't need that

117
00:11:25.120 --> 00:11:27.430
however living in there won't caused the
problems so if you have left it in there

118
00:11:27.430 --> 00:11:28.180
that's fine

119
00:11:28.180 --> 00:11:31.570
alright next we're going to a couple of
the ones that require a bit of research

120
00:11:31.570 --> 00:11:35.620
number 8 search the internet to
find out how to get a list of the songs

121
00:11:35.620 --> 00:11:40.870
from step 6 without any duplicates
hopefully managed to find that so to do

122
00:11:40.870 --> 00:11:42.190
that the command is select....

123
00:11:42.190 --> 00:11:53.290
.....

124
00:11:53.290 --> 00:12:03.760
....and you can see theirs no longer duplicates their

125
00:12:03.760 --> 00:12:07.100
number 9 search the internet again to find out
how to get a count of the songs without

126
00:12:07.100 --> 00:12:12.070
duplicates and hint that i gave you was
it uses the same keyword as step 8

127
00:12:12.070 --> 00:12:18.820
but the syntax may not be obvious so to
do that one.....

128
00:12:18.820 --> 00:12:26.440
....

129
00:12:26.440 --> 00:12:39.490
.....

130
00:12:39.490 --> 00:12:42.940
....

131
00:12:42.940 --> 00:12:52.000
.....because we're going on

132
00:12:52.000 --> 00:12:59.380
the actual song title that's from
artists_list where....

133
00:12:59.380 --> 00:13:04.060
....o that should actually give
us the same count before and 128 that's

134
00:13:04.060 --> 00:13:04.730
better

135
00:13:04.730 --> 00:13:08.800
alright so that was number nine and
the last one was to repeat the previous

136
00:13:08.800 --> 00:13:12.370
query to find the number of artists
which obviously should be one and the

137
00:13:12.370 --> 00:13:15.880
number of albums so the first one
already type up there so we're just

138
00:13:15.880 --> 00:13:19.180
going to copy and pasting again this is
the one that I accidentally typed in the

139
00:13:19.180 --> 00:13:19.990
wrong place

140
00:13:19.990 --> 00:13:24.550
so that's going to give us the paste it
in that's going to give us the number of

141
00:13:24.550 --> 00:13:28.030
artists which obviously in this case
should be 1 because we got our where

142
00:13:28.030 --> 00:13:33.610
clause that is looking for aerosmith you can
see that returns 1 then the last one the

143
00:13:33.610 --> 00:13:39.670
number of albums by aerosmith effective
we're going to select...

144
00:13:39.670 --> 00:13:48.310
.....

145
00:13:50.420 --> 00:13:56.720
....that gives us the answers 13 the number
of unique albums by this artist

146
00:13:56.720 --> 00:14:00.410
alright so that's actually it so we're
actually done now with the sql lite

147
00:14:00.410 --> 00:14:04.160
introduction and fiddling around and
playing around a little bit with sql

148
00:14:04.160 --> 00:14:07.820
lite so we can end the video here in the
next one we're gonna start

149
00:14:07.820 --> 00:14:08.160
work

150
00:14:08.160 --> 00:14:11.720
on our first sql lite
application see you in the next video