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

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

3
00:00:12,180 --> 00:00:17,820
working on the next video is how we can
use sql lite in java programs but

4
00:00:17,820 --> 00:00:21,720
before we actually get to that video
let's get through into a few more things

5
00:00:21,720 --> 00:00:25,770
we're going to start with backing up the
database again I'm going to type....

6
00:00:25,770 --> 00:00:34,170
.....so what I suggest you do

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

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

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

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

11
00:00:51,650 --> 00:00:55,220
back should you need to I know we have
restored it previously but we're going

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

13
00:01:00,030 --> 00:01:04,640
using the .restore command but if we
restore right now you have no real

14
00:01:04,640 --> 00:01:09,000
indication that it worked so let's
actually trash some songs first so that

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

16
00:01:13,680 --> 00:01:17,850
can delete all songs whos track number is
less than 50 and that should leave us

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

18
00:01:21,360 --> 00:01:33,720
....

19
00:01:33,720 --> 00:01:37,620
....obviously we've got too much smaller
list than what we had before and in

20
00:01:37,620 --> 00:01:42,960
fact we're left with songs from only two
albums and that is easy to see if we use

21
00:01:42,960 --> 00:01:49,110
the view so if you look at the views so....

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

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

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

25
00:02:01,970 --> 00:02:06,690
now sql uses less than or greater than
or not equal to which makes

26
00:02:06,690 --> 00:02:12,030
sense when you read it and avoid having
to introduce another symbol other than

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

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

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

30
00:02:22,860 --> 00:02:35,180
select....so you can see at that point

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

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

33
00:02:43,890 --> 00:02:47,700
you can include functions in the Select
statement now i'm not going to go into a

34
00:02:47,700 --> 00:02:51,750
range of functions available but one
useful one is count so you can do....

35
00:02:51,750 --> 00:03:12,560
....

36
00:03:12,560 --> 00:03:19,430
....you can see we've got
24 entries for songs 439

37
00:03:19,430 --> 00:03:22,680
records for albums and 202 for artists

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

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

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

41
00:03:40,880 --> 00:03:48,620
equivalent commands now the select....

42
00:03:48,620 --> 00:04:04,070
....

43
00:04:04,070 --> 00:04:09,230
....so clearly

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

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

46
00:04:22,440 --> 00:04:24,980
so I got a number of things for
you too

47
00:04:24,980 --> 00:04:29,940
come up with the sql commands to
return these the results that I'm asking

48
00:04:29,940 --> 00:04:35,190
for so first one here number one select
the titles of all the songs on the album

49
00:04:35,190 --> 00:04:41,130
forbidden and second one repeat the
previous query but this time display the

50
00:04:41,130 --> 00:04:45,240
songs in track order now you may want to
include the track number in the output

51
00:04:45,240 --> 00:04:49,500
to verify that it worked okay number
three display all songs for the band

52
00:04:49,500 --> 00:04:55,800
deep purple number 4 rename the band
mehitable to one kitten hope I pronounce

53
00:04:55,800 --> 00:05:00,300
that right and note that this is an
exception to the advice to always fully

54
00:05:00,300 --> 00:05:05,090
qualify your column names set space
artist.name won't work

55
00:05:05,090 --> 00:05:08,430
you just need to specify name and you'll
see that when you give that a go

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

57
00:05:12,330 --> 00:05:14,690
four

58
00:05:14,690 --> 00:05:20,030
continuing on number six select the
title of all the songs by aerosmith in

59
00:05:20,030 --> 00:05:25,880
alphabetical order include only the
title in the output number 7 replace the

60
00:05:25,880 --> 00:05:30,470
column that you used in the previous
answer with count title in parentheses

61
00:05:30,470 --> 00:05:35,090
to get just a count of the number of the
songs number of songs number 8

62
00:05:35,090 --> 00:05:39,150
search the internet to find out how to
get a list of the songs from step 6

63
00:05:39,150 --> 00:05:42,440
without any duplicates number 9

64
00:05:42,440 --> 00:05:46,520
search the internet again this time to
find out how to get a count of the songs

65
00:05:46,520 --> 00:05:51,150
without duplicates and hint it uses the
same keyword as step 8

66
00:05:51,150 --> 00:05:56,900
but the syntax may not be obvious and
number ten repeat the previous query to

67
00:05:56,900 --> 00:06:00,530
find the number of artists which
obviously should be one and the number

68
00:06:00,530 --> 00:06:04,680
of albums so that's it that's your
challenge 10 things for you to have

69
00:06:04,680 --> 00:06:08,990
a go at so pause the video and
go away and see if you can figure those out and

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

71
00:06:12,210 --> 00:06:19,490
pause the video now and i'll see you
when you get back alright so how did you go

72
00:06:19,490 --> 00:06:21,270
hopefully you managed to figure it out

73
00:06:21,270 --> 00:06:26,460
let's have a go so start the first one
and if you recall the first

74
00:06:26,460 --> 00:06:32,060
challenge was select the titles of all
the songs on the album forbidden to do

75
00:06:32,060 --> 00:06:43,340
that we do.....

76
00:06:43,340 --> 00:06:54,380
....

77
00:06:54,380 --> 00:07:02,210
alright so that's all the title from
the album forbidden number two was to

78
00:07:02,210 --> 00:07:07,250
repeat the previous query but this time
display the songs in track order and you

79
00:07:07,250 --> 00:07:11,060
may want to include the track number in
the output to verify that work ok so

80
00:07:11,060 --> 00:07:17,490
let's type that up....

81
00:07:17,490 --> 00:07:20,900
.....

82
00:07:22,450 --> 00:07:37,150
.....

83
00:07:37,150 --> 00:07:48,850
...so we got the equivalent to the previous query

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

85
00:07:52,180 --> 00:07:56,620
screen they're verifying that worked all
right number three display all songs for

86
00:07:56,620 --> 00:08:06,460
the band deep purple alright so....

87
00:08:06,460 --> 00:08:45,190
....

88
00:08:45,190 --> 00:08:49,930
....you can see we've got

89
00:08:49,930 --> 00:08:52,480
quite a few entries there for deep
purple

90
00:08:52,480 --> 00:08:56,230
alternatively also you could have used
the artists_list view that

91
00:08:56,230 --> 00:08:59,290
would have been acceptable as well so
that would have been much easier

92
00:08:59,290 --> 00:09:08,170
actually just select....

93
00:09:08,170 --> 00:09:16,300
....

94
00:09:16,300 --> 00:09:21,550
alright continuing on now number 4 we want
to rename the band mehitable

95
00:09:21,550 --> 00:09:26,290
to one kitten and
this was the one where I mention that there was

96
00:09:26,290 --> 00:09:29,860
an exception to the advice to always
fully qualify your column

97
00:09:29,860 --> 00:09:33,310
names because set artist.name
won't work

98
00:09:33,310 --> 00:09:35,320
you just need to specify name in this
scenario

99
00:09:35,320 --> 00:09:47,530
so to do that we do update.....

100
00:09:47,530 --> 00:09:58,600
....

101
00:09:58,600 --> 00:10:02,200
......so now we can confirm that
confirm the update working in otherword

102
00:10:02,200 --> 00:10:14,350
select star.....and we can now

103
00:10:14,350 --> 00:10:18,640
see that we've got an entry for that so
that worked okay and checking it was ok

104
00:10:18,640 --> 00:10:22,090
the records correctly rename which was actually
challenge 5 just to be clear

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

106
00:10:26,410 --> 00:10:30,730
select the titles of all the songs
by aerosmith in alphabetical order

107
00:10:30,730 --> 00:10:35,530
including only the title in the output
that's actually quite simple one so....

108
00:10:35,530 --> 00:10:46,450
....

109
00:10:46,450 --> 00:10:51,910
.....

110
00:10:51,910 --> 00:10:58,540
....alright the next one we want is you
want to replace the column that you used

111
00:10:58,540 --> 00:11:02,920
in the previous answer with count title
to get just a count of the number of the

112
00:11:02,920 --> 00:11:08,680
songs instead of the actual titles that
would be....

113
00:11:08,680 --> 00:11:20,350
.....we should

114
00:11:20,350 --> 00:11:26,140
get the answer 151 their 151 as you can see now note that

115
00:11:26,140 --> 00:11:30,280
the in this particular case the order by
Clause is redundant here because we're

116
00:11:30,280 --> 00:11:31,870
doing a count we don't need that

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

118
00:11:34,180 --> 00:11:34,930
that's fine

119
00:11:34,930 --> 00:11:38,320
alright next we're going to a couple of
the ones that require a bit of research

120
00:11:38,320 --> 00:11:42,370
number 8 search the internet to
find out how to get a list of the songs

121
00:11:42,370 --> 00:11:47,620
from step 6 without any duplicates
hopefully managed to find that so to do

122
00:11:47,620 --> 00:11:48,940
that the command is select....

123
00:11:48,940 --> 00:12:00,040
.....

124
00:12:00,040 --> 00:12:10,510
....and you can see theirs no longer duplicates their

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

126
00:12:13,850 --> 00:12:18,820
duplicates and hint that i gave you was
it uses the same keyword as step 8

127
00:12:18,820 --> 00:12:25,570
but the syntax may not be obvious so to
do that one.....

128
00:12:25,570 --> 00:12:33,190
....

129
00:12:33,190 --> 00:12:46,240
.....

130
00:12:46,240 --> 00:12:49,690
....

131
00:12:49,690 --> 00:12:58,750
.....because we're going on

132
00:12:58,750 --> 00:13:06,130
the actual song title that's from
artists_list where....

133
00:13:06,130 --> 00:13:10,810
....o that should actually give
us the same count before and 128 that's

134
00:13:10,810 --> 00:13:11,480
better

135
00:13:11,480 --> 00:13:15,550
alright so that was number nine and
the last one was to repeat the previous

136
00:13:15,550 --> 00:13:19,120
query to find the number of artists
which obviously should be one and the

137
00:13:19,120 --> 00:13:22,630
number of albums so the first one
already type up there so we're just

138
00:13:22,630 --> 00:13:25,930
going to copy and pasting again this is
the one that I accidentally typed in the

139
00:13:25,930 --> 00:13:26,740
wrong place

140
00:13:26,740 --> 00:13:31,300
so that's going to give us the paste it
in that's going to give us the number of

141
00:13:31,300 --> 00:13:34,780
artists which obviously in this case
should be 1 because we got our where

142
00:13:34,780 --> 00:13:40,360
clause that is looking for aerosmith you can
see that returns 1 then the last one the

143
00:13:40,360 --> 00:13:46,420
number of albums by aerosmith effective
we're going to select...

144
00:13:46,420 --> 00:13:55,060
.....

145
00:13:57,170 --> 00:14:03,470
....that gives us the answers 13 the number
of unique albums by this artist

146
00:14:03,470 --> 00:14:07,160
alright so that's actually it so we're
actually done now with the sql lite

147
00:14:07,160 --> 00:14:10,910
introduction and fiddling around and
playing around a little bit with sql

148
00:14:10,910 --> 00:14:14,570
lite so we can end the video here in the
next one we're finally gonna get to the

149
00:14:14,570 --> 00:14:18,260
stage of going back to android studio
and we're going to actually start work

150
00:14:18,260 --> 00:14:21,820
on our first sql lite android
application see you in the next video