1
00:00:05,530 --> 00:00:08,950
alright so as I mentioned at the end of
the last video we finish the

2
00:00:08,950 --> 00:00:12,700
introduction to databases in the sql
language and what we're going to start

3
00:00:12,700 --> 00:00:18,340
working on the next video is how we can
use sql lite in java programs but

4
00:00:18,340 --> 00:00:22,240
before we actually get to that video
let's get through into a few more things

5
00:00:22,240 --> 00:00:26,290
we're going to start with backing up the
database again I'm going to type....

6
00:00:26,290 --> 00:00:34,690
.....so what I suggest you do

7
00:00:34,690 --> 00:00:38,910
now is experiment with the commands that
we've used to search for different

8
00:00:38,910 --> 00:00:43,650
groups of records from the three tables
and also try creating queries that use

9
00:00:43,650 --> 00:00:48,570
joins as well as they already joined view
that we created and actually there's not

10
00:00:48,570 --> 00:00:52,170
much point showing you how to back up the
database if I don't show you how to get

11
00:00:52,170 --> 00:00:55,740
back should you need to I know we have
restored it previously but we're going

12
00:00:55,740 --> 00:01:00,550
to restore this music one now database is
restored from the backup as you saw

13
00:01:00,550 --> 00:01:05,160
using the .restore command but if we
restore right now you have no real

14
00:01:05,160 --> 00:01:09,520
indication that it worked so let's
actually trash some songs first so that

15
00:01:09,520 --> 00:01:14,200
we can confirm that now most of the
album's have fewer than 50 tracks so we

16
00:01:14,200 --> 00:01:18,370
can delete all songs whos track number is
less than 50 and that should leave us

17
00:01:18,370 --> 00:01:21,880
with very few records so let's go ahead
and do that i'm going to type....

18
00:01:21,880 --> 00:01:34,240
....

19
00:01:34,240 --> 00:01:38,140
....obviously we've got too much smaller
list than what we had before and in

20
00:01:38,140 --> 00:01:43,480
fact we're left with songs from only two
albums and that is easy to see if we use

21
00:01:43,480 --> 00:01:49,630
the view so if you look at the views so....

22
00:01:49,630 --> 00:01:55,630
...you can see they're clearly there's
only two now as you saw for the

23
00:01:55,630 --> 00:01:59,530
condition in the where clause you can
use less than greater than less than or

24
00:01:59,530 --> 00:02:02,490
equal to and so on just like
you'd expect

25
00:02:02,490 --> 00:02:07,210
now sql uses less than or greater than
or not equal to which makes

26
00:02:07,210 --> 00:02:12,550
sense when you read it and avoid having
to introduce another symbol other than

27
00:02:12,550 --> 00:02:16,830
that the comparison operators are the
same you'd normally expect in Java so we

28
00:02:16,830 --> 00:02:18,670
can drop a song from the list

29
00:02:18,670 --> 00:02:23,380
only selecting songs with a track number
not equal to 71 so we could do that with

30
00:02:23,380 --> 00:02:35,700
select....so you can see at that point

31
00:02:35,700 --> 00:02:39,910
now recycled vinyl blues which was
showing in the previous list is now no

32
00:02:39,910 --> 00:02:44,410
longer showing now the other thing you
can do which we haven't talked about is

33
00:02:44,410 --> 00:02:48,220
you can include functions in the Select
statement now i'm not going to go into a

34
00:02:48,220 --> 00:02:52,270
range of functions available but one
useful one is count so you can do....

35
00:02:52,270 --> 00:03:13,080
....

36
00:03:13,080 --> 00:03:19,950
....you can see we've got
24 entries for songs 439

37
00:03:19,950 --> 00:03:23,200
records for albums and 202 for artists

38
00:03:24,090 --> 00:03:28,560
alright so let's now restore the database
and actually see how many records you've

39
00:03:28,560 --> 00:03:36,670
got back so do . restore music back up 2
remember there's no semicolon because

40
00:03:36,670 --> 00:03:41,400
it's a sql lite command because it
starts with the . let's do the

41
00:03:41,400 --> 00:03:49,140
equivalent commands now the select....

42
00:03:49,140 --> 00:04:04,590
....

43
00:04:04,590 --> 00:04:09,750
....so clearly

44
00:04:09,750 --> 00:04:13,830
the restore worked alright so let's
finish this video now with a challenge

45
00:04:13,830 --> 00:04:16,829
for you to help get you started

46
00:04:18,959 --> 00:04:21,500
so I got a number of things for
you too

47
00:04:21,500 --> 00:04:26,460
come up with the sql commands to
return these the results that I'm asking

48
00:04:26,460 --> 00:04:31,710
for so first one here number one select
the titles of all the songs on the album

49
00:04:31,710 --> 00:04:37,650
forbidden and second one repeat the
previous query but this time display the

50
00:04:37,650 --> 00:04:41,760
songs in track order now you may want to
include the track number in the output

51
00:04:41,760 --> 00:04:46,020
to verify that it worked okay number
three display all songs for the band

52
00:04:46,020 --> 00:04:52,320
deep purple number 4 rename the band
mehitable to one kitten hope I pronounce

53
00:04:52,320 --> 00:04:56,820
that right and note that this is an
exception to the advice to always fully

54
00:04:56,820 --> 00:05:01,610
qualify your column names set space
artist.name won't work

55
00:05:01,610 --> 00:05:04,950
you just need to specify name and you'll
see that when you give that a go

56
00:05:05,490 --> 00:05:08,850
number five check that the record was
correctly renamed that you did in part

57
00:05:08,850 --> 00:05:11,210
four

58
00:05:11,210 --> 00:05:16,550
continuing on number six select the
title of all the songs by aerosmith in

59
00:05:16,550 --> 00:05:22,400
alphabetical order include only the
title in the output number 7 replace the

60
00:05:22,400 --> 00:05:26,990
column that you used in the previous
answer with count title in parentheses

61
00:05:26,990 --> 00:05:31,610
to get just a count of the number of the
songs number of songs number 8

62
00:05:31,610 --> 00:05:35,670
search the internet to find out how to
get a list of the songs from step 6

63
00:05:35,670 --> 00:05:38,960
without any duplicates number 9

64
00:05:38,960 --> 00:05:43,040
search the internet again this time to
find out how to get a count of the songs

65
00:05:43,040 --> 00:05:47,670
without duplicates and hint it uses the
same keyword as step 8

66
00:05:47,670 --> 00:05:53,420
but the syntax may not be obvious and
number ten repeat the previous query to

67
00:05:53,420 --> 00:05:57,050
find the number of artists which
obviously should be one and the number

68
00:05:57,050 --> 00:06:01,200
of albums so that's it that's your
challenge 10 things for you to have

69
00:06:01,200 --> 00:06:05,510
a go at so pause the video and
go away and see if you can figure those out and

70
00:06:05,510 --> 00:06:08,730
when you're ready to see me come up with
the answers start the video again so

71
00:06:08,730 --> 00:06:16,010
pause the video now and i'll see you
when you get back alright so how did you go

72
00:06:16,010 --> 00:06:17,790
hopefully you managed to figure it out

73
00:06:17,790 --> 00:06:22,980
let's have a go so start the first one
and if you recall the first

74
00:06:22,980 --> 00:06:28,580
challenge was select the titles of all
the songs on the album forbidden to do

75
00:06:28,580 --> 00:06:39,860
that we do.....

76
00:06:39,860 --> 00:06:50,900
....

77
00:06:50,900 --> 00:06:58,730
alright so that's all the title from
the album forbidden number two was to

78
00:06:58,730 --> 00:07:03,770
repeat the previous query but this time
display the songs in track order and you

79
00:07:03,770 --> 00:07:07,580
may want to include the track number in
the output to verify that work ok so

80
00:07:07,580 --> 00:07:14,010
let's type that up....

81
00:07:14,010 --> 00:07:17,420
.....

82
00:07:18,970 --> 00:07:33,670
.....

83
00:07:33,670 --> 00:07:45,370
...so we got the equivalent to the previous query

84
00:07:45,370 --> 00:07:48,700
but this time sorted in track order and
you can see we've got the track order on the

85
00:07:48,700 --> 00:07:53,140
screen they're verifying that worked all
right number three display all songs for

86
00:07:53,140 --> 00:08:02,980
the band deep purple alright so....

87
00:08:02,980 --> 00:08:41,710
....

88
00:08:41,710 --> 00:08:46,450
....you can see we've got

89
00:08:46,450 --> 00:08:49,000
quite a few entries there for deep
purple

90
00:08:49,000 --> 00:08:52,750
alternatively also you could have used
the artists_list view that

91
00:08:52,750 --> 00:08:55,810
would have been acceptable as well so
that would have been much easier

92
00:08:55,810 --> 00:09:04,690
actually just select....

93
00:09:04,690 --> 00:09:12,820
....

94
00:09:12,820 --> 00:09:18,070
alright continuing on now number 4 we want
to rename the band mehitable

95
00:09:18,070 --> 00:09:22,810
to one kitten and
this was the one where I mention that there was

96
00:09:22,810 --> 00:09:26,380
an exception to the advice to always
fully qualify your column

97
00:09:26,380 --> 00:09:29,830
names because set artist.name
won't work

98
00:09:29,830 --> 00:09:31,840
you just need to specify name in this
scenario

99
00:09:31,840 --> 00:09:44,050
so to do that we do update.....

100
00:09:44,050 --> 00:09:55,120
....

101
00:09:55,120 --> 00:09:58,720
......so now we can confirm that
confirm the update working in otherword

102
00:09:58,720 --> 00:10:10,870
select star.....and we can now

103
00:10:10,870 --> 00:10:15,160
see that we've got an entry for that so
that worked okay and checking it was ok

104
00:10:15,160 --> 00:10:18,610
the records correctly rename which was actually
challenge 5 just to be clear

105
00:10:18,610 --> 00:10:22,930
alright so that's one we've just done
so moving on now number 6 we have to

106
00:10:22,930 --> 00:10:27,250
select the titles of all the songs
by aerosmith in alphabetical order

107
00:10:27,250 --> 00:10:32,050
including only the title in the output
that's actually quite simple one so....

108
00:10:32,050 --> 00:10:42,970
....

109
00:10:42,970 --> 00:10:48,430
.....

110
00:10:48,430 --> 00:10:55,060
....alright the next one we want is you
want to replace the column that you used

111
00:10:55,060 --> 00:10:59,440
in the previous answer with count title
to get just a count of the number of the

112
00:10:59,440 --> 00:11:05,200
songs instead of the actual titles that
would be....

113
00:11:05,200 --> 00:11:16,870
.....we should

114
00:11:17,370 --> 00:11:23,160
get the answer 151 their 151 as you can see now note that

115
00:11:23,160 --> 00:11:27,300
the in this particular case the order by
Clause is redundant here because we're

116
00:11:27,300 --> 00:11:28,890
doing a count we don't need that

117
00:11:28,890 --> 00:11:31,200
however living in there won't caused the
problems so if you have left it in there

118
00:11:31,200 --> 00:11:31,950
that's fine

119
00:11:31,950 --> 00:11:35,340
alright next we're going to a couple of
the ones that require a bit of research

120
00:11:35,340 --> 00:11:39,390
number 8 search the internet to
find out how to get a list of the songs

121
00:11:39,390 --> 00:11:44,640
from step 6 without any duplicates
hopefully managed to find that so to do

122
00:11:44,640 --> 00:11:45,960
that the command is select....

123
00:11:45,960 --> 00:11:57,060
.....

124
00:11:57,060 --> 00:12:07,530
....and you can see theirs no longer duplicates their

125
00:12:07,530 --> 00:12:10,870
number 9 search the internet again to find out
how to get a count of the songs without

126
00:12:10,870 --> 00:12:15,840
duplicates and hint that i gave you was
it uses the same keyword as step 8

127
00:12:15,840 --> 00:12:22,590
but the syntax may not be obvious so to
do that one.....

128
00:12:22,590 --> 00:12:30,210
....

129
00:12:30,210 --> 00:12:43,260
.....

130
00:12:43,260 --> 00:12:46,710
....

131
00:12:46,710 --> 00:12:55,770
.....because we're going on

132
00:12:55,770 --> 00:13:03,150
the actual song title that's from
artists_list where....

133
00:13:03,150 --> 00:13:07,830
....o that should actually give
us the same count before and 128 that's

134
00:13:07,830 --> 00:13:08,500
better

135
00:13:08,500 --> 00:13:12,570
alright so that was number nine and
the last one was to repeat the previous

136
00:13:12,570 --> 00:13:16,140
query to find the number of artists
which obviously should be one and the

137
00:13:16,140 --> 00:13:19,650
number of albums so the first one
already type up there so we're just

138
00:13:19,650 --> 00:13:22,950
going to copy and pasting again this is
the one that I accidentally typed in the

139
00:13:22,950 --> 00:13:23,760
wrong place

140
00:13:23,760 --> 00:13:28,320
so that's going to give us the paste it
in that's going to give us the number of

141
00:13:28,320 --> 00:13:31,800
artists which obviously in this case
should be 1 because we got our where

142
00:13:31,800 --> 00:13:37,380
clause that is looking for aerosmith you can
see that returns 1 then the last one the

143
00:13:37,380 --> 00:13:43,440
number of albums by aerosmith effective
we're going to select...

144
00:13:43,440 --> 00:13:52,080
.....

145
00:13:54,190 --> 00:14:00,490
....that gives us the answers 13 the number
of unique albums by this artist

146
00:14:00,490 --> 00:14:04,180
alright so that's actually it so we're
actually done now with the sql lite

147
00:14:04,180 --> 00:14:07,930
introduction and fiddling around and
playing around a little bit with sql

148
00:14:07,930 --> 00:14:11,590
lite so we can end the video here in the
next one we're gonna start

149
00:14:11,590 --> 00:14:11,930
work

150
00:14:11,930 --> 00:14:15,490
on our first sql lite
application see you in the next video