1
00:00:05,520 --> 00:00:09,060
alright so i'm actually quit out of the
sql lite and I've started it up again I'm

2
00:00:09,060 --> 00:00:12,900
just going to type this command in and you can
just see that there's actually a

3
00:00:12,900 --> 00:00:16,940
tremendous number of Records here that
have appeared on the screen and

4
00:00:16,940 --> 00:00:19,050
scrolling up as you can see there's tons of
them

5
00:00:19,050 --> 00:00:23,220
now depending on the speed of your Mac
you might find it a lot slower to output

6
00:00:23,220 --> 00:00:28,140
thease and you can actually use ctrl z to
stop the listing and that also let you

7
00:00:28,140 --> 00:00:31,140
see some record from the middle of the
list rather than just the same view from

8
00:00:31,140 --> 00:00:35,670
the end now you can do the same thing on
windows with ctrl Z but there's

9
00:00:35,670 --> 00:00:40,950
apparently a bug that causes ctrl z to
quit sql lite as well so you might

10
00:00:40,950 --> 00:00:46,530
have to start sql lite again with the
sql lite 3 space music.DB to get back

11
00:00:46,530 --> 00:00:50,630
into the database if you're on windows
and you press ctrl z on linux you

12
00:00:50,630 --> 00:00:55,380
might find as well that you should find ctrl z will stop the listing as

13
00:00:55,380 --> 00:00:55,880
well

14
00:00:55,880 --> 00:01:00,060
alright so let's just changed
around a little bit and it might

15
00:01:00,060 --> 00:01:03,360
actually look neater the other way
around if we list the album name before

16
00:01:03,360 --> 00:01:10,370
the song title so to do that we type....

17
00:01:10,370 --> 00:01:24,680
....

18
00:01:24,680 --> 00:01:38,720
....as you

19
00:01:38,720 --> 00:01:42,710
can see that does look a little bit
neater so you're free to return any

20
00:01:42,710 --> 00:01:46,010
columns you want in any order you don't
have to keep them in the same order that

21
00:01:46,010 --> 00:01:50,000
they appear in the table nor in the
order the actual table are joined either

22
00:01:50,000 --> 00:01:53,000
alright time for another mini challenge

23
00:01:55,890 --> 00:02:00,180
alright so the challenges is to produce a
list of all the artists with their

24
00:02:00,180 --> 00:02:05,700
albums in alphabetical order of artists
name so go away and see if you can figure out

25
00:02:05,700 --> 00:02:09,270
the sql code for that pause the video
and when you're ready to see my solution

26
00:02:09,270 --> 00:02:12,570
come back toward and i'll show you how
to do it so pause the video now

27
00:02:16,500 --> 00:02:24,630
alright so the way to solve that would
be....

28
00:02:24,630 --> 00:02:47,730
....

29
00:02:47,730 --> 00:02:52,950
and because I can't use my up arrow i'm
going to type in in again so...

30
00:02:52,950 --> 00:03:14,910
....

31
00:03:14,910 --> 00:03:21,300
....okay there you go that's the solution
now what if you wanted to find out which

32
00:03:21,300 --> 00:03:26,010
artist produced a song though now the
songs table doesn't have any direct

33
00:03:26,010 --> 00:03:30,360
links to artists and we just go back and
look at the relationship between the

34
00:03:30,360 --> 00:03:36,080
tables again it will help us to see how
we can get the artist for a song alright

35
00:03:36,080 --> 00:03:41,070
here's a representation just zoom in
so you can see that little bit better so

36
00:03:41,070 --> 00:03:44,390
if we have a look at the relationships
between these tables again that's going

37
00:03:44,390 --> 00:03:48,420
to help to see how we can get that
artist for a particular song now although

38
00:03:48,420 --> 00:03:53,280
we can't go directly to an artist from a
song record we can find out which album

39
00:03:53,280 --> 00:03:57,000
contains the song and from there it
should be easy to find out who the

40
00:03:57,000 --> 00:04:02,640
artist is and we do that by joining
songs to albums and then albums to

41
00:04:02,640 --> 00:04:05,640
artists let's have a go at typing
the code for that

42
00:04:07,510 --> 00:04:19,390
so we type.....

43
00:04:19,390 --> 00:04:55,450
....

44
00:04:55,450 --> 00:05:02,950
....so that's actually quite a lot of

45
00:05:02,950 --> 00:05:07,000
statements you can see so it was good
that sql lite allows us to split

46
00:05:07,000 --> 00:05:11,440
over more than one line and doesn't try
to execute the statement until it finds

47
00:05:11,440 --> 00:05:13,540
an ending semicolon

48
00:05:13,540 --> 00:05:17,530
so what I've done here is just chain
the inner joins together so we have

49
00:05:17,530 --> 00:05:22,420
songs inner join albums inner join artists now of course we have to specify

50
00:05:22,420 --> 00:05:26,710
which columns to join on but hopefully
the syntax as I've shown up there does make

51
00:05:26,710 --> 00:05:32,920
sense and we'll just run this to make sure
it works and you can see that we've got

52
00:05:32,920 --> 00:05:38,260
the results that we're looking for the
actual artist who produced the song so

53
00:05:38,260 --> 00:05:42,130
the Select statement is pretty flexible
we can include as many columns as we

54
00:05:42,130 --> 00:05:47,080
need joining tables as we need them and
then sort of as many columns as we need to

55
00:05:47,080 --> 00:05:52,000
produce decent output and you can also
nest select inside another select

56
00:05:52,000 --> 00:05:56,410
statement but if you get to the stage of
needing to do that then you really

57
00:05:56,410 --> 00:05:59,500
getting into more advanced sql and
that's above what we have time to cover

58
00:05:59,500 --> 00:06:01,060
in this course

59
00:06:01,060 --> 00:06:04,270
just keep in mind that the sql
language is very powerful and really

60
00:06:04,270 --> 00:06:08,530
quite simple considering what you can do
with it now one thing that i haven't

61
00:06:08,530 --> 00:06:13,330
mentioned so far is that the ordering of
the clauses is important so you can't go

62
00:06:13,330 --> 00:06:17,790
putting the order by Clause before the
joins for example the order is strict

63
00:06:17,790 --> 00:06:21,140
in that regard so the order that we've
been doing things so far is actually

64
00:06:21,140 --> 00:06:25,010
correct way to do it now if you want to
include a where clause it has to go

65
00:06:25,010 --> 00:06:29,660
before the order by Clause let's
restrict the previous query to just the

66
00:06:29,660 --> 00:06:32,450
album do little which has the id 19

67
00:06:32,450 --> 00:06:38,300
alright so what I actually did was I
actually copy and pasted off-screen so

68
00:06:38,300 --> 00:06:42,170
you ignore those little dots and the
greater than sign it's because i pasted

69
00:06:42,170 --> 00:06:45,770
it all in the at the same time then it's
actually come up with what would have

70
00:06:45,770 --> 00:06:49,040
happened if we had press enter but the
point is that what i've done is i put

71
00:06:49,040 --> 00:06:53,660
the where clause you can see before the
order by clause and I've got a semicolon

72
00:06:53,660 --> 00:06:58,670
on the end so this should work
when I press enter so we're only getting the

73
00:06:58,670 --> 00:07:03,230
rows back for the album do little which
again have the ID 19 that's where the

74
00:07:03,230 --> 00:07:07,610
where clause came in now splitting
the command over several lines like this

75
00:07:07,610 --> 00:07:11,690
does make it easy to understand but it
does make calling the command back a

76
00:07:11,690 --> 00:07:12,590
little tricky

77
00:07:12,590 --> 00:07:16,250
you have to do it line by line so the
trick would be there if this up arrow was

78
00:07:16,250 --> 00:07:20,750
working for you is to actually keep
pressing the up arrow to call back the

79
00:07:20,750 --> 00:07:24,800
first line in the statement the select
line here then press enter then use the

80
00:07:24,800 --> 00:07:27,050
up arrow to call back the next line and
so on

81
00:07:27,050 --> 00:07:29,990
I can't actually show you that because as I've outlined its not working

82
00:07:29,990 --> 00:07:35,030
properly on the mac but what i can do is
paste in part of the command like so

83
00:07:35,030 --> 00:07:41,900
then what i'm going to do is add the
claus again so...

84
00:07:41,900 --> 00:07:55,850
....

85
00:07:55,850 --> 00:08:03,050
....so as you can see the
structure of the Select statement is

86
00:08:03,050 --> 00:08:07,430
quite straightforward you specify the
columns that you're interested in you

87
00:08:07,430 --> 00:08:10,910
join any other tables that are needed
filter the selection using a where

88
00:08:10,910 --> 00:08:16,760
clause and finally you order the results
now sometimes you have a rough idea of

89
00:08:16,760 --> 00:08:19,220
what you want to find but you don't know
what exactly

90
00:08:19,220 --> 00:08:22,640
or perhaps you're interested in several
rows have similar but not identical

91
00:08:22,640 --> 00:08:28,040
names now the sql where clause can
use wildcards to match on partial

92
00:08:28,040 --> 00:08:33,380
strings to cope with these situations
now I'm going to actually change sql

93
00:08:33,380 --> 00:08:34,760
commands that span a few lines

94
00:08:34,760 --> 00:08:38,600
in this next bit and as you've seen
editing the statements from within the

95
00:08:38,600 --> 00:08:43,370
sql lite show there's a bit
fiddly a useful tip when working with the

96
00:08:43,370 --> 00:08:47,690
sql lite shell is to keep a text editor
handy and copy and paste between the two

97
00:08:47,690 --> 00:08:52,340
windows and you also need to know to copy
from that terminal command line if you

98
00:08:52,340 --> 00:08:55,340
want to take the output from the .
schema command and paste it into your

99
00:08:55,340 --> 00:09:00,770
code going to digress slightly and show
you how to do that so i can just come up

100
00:09:00,770 --> 00:09:04,550
here and actually just copy these
commands that I've type in here selected here

101
00:09:06,860 --> 00:09:09,950
I do get a line at time I can just select the part you've seen

102
00:09:09,950 --> 00:09:15,410
me doing this in the previous videos I
can actually copy that and what i can do

103
00:09:15,410 --> 00:09:19,460
is i can drag that down with my mouse
and put into this line here and you can

104
00:09:19,460 --> 00:09:22,400
see that's been added or I could have
done that or I could have right-click it

105
00:09:22,400 --> 00:09:28,460
and select and copy and paste which is
also saw me do previously now on Windows

106
00:09:28,460 --> 00:09:31,910
you can copy the selected text into the
clipboard after you've selected by

107
00:09:31,910 --> 00:09:35,690
pressing enter and on linux you need to
click the right mouse button and choose

108
00:09:35,690 --> 00:09:38,990
copy from the context menu that actually
appears so it does depend on the

109
00:09:38,990 --> 00:09:42,590
operating system as to how you go about
copying that the bottom line is at that

110
00:09:42,590 --> 00:09:46,040
point the text is now in the clipboard
and you can paste it into the text

111
00:09:46,040 --> 00:09:51,740
editor that you want to use and
manipulate the sql statement on the

112
00:09:51,740 --> 00:09:54,530
case of here what I'm going to do
is I'm just going to copy all of this

113
00:09:54,530 --> 00:09:59,720
here and i can do a copy here if I could do it that way and i can open

114
00:09:59,720 --> 00:10:05,960
the text editor in my case I'm just
going to open text edit but you can use

115
00:10:05,960 --> 00:10:10,790
notepad or a linux editor as appropriate
and I can just paste it in there and

116
00:10:10,790 --> 00:10:14,990
notice that if we just zoom in there we
might have to clean a little bit of this

117
00:10:14,990 --> 00:10:18,680
up so we need to just delete this part
here with those extra parts were added

118
00:10:18,680 --> 00:10:22,340
by sql lite when enter was meant to be pressed and I just press enter the

119
00:10:22,340 --> 00:10:26,210
relevant place to just to build up the
clause like that and eventually I got the

120
00:10:26,210 --> 00:10:30,740
thing working and ready to be
manipulated and what do is I'm just

121
00:10:30,740 --> 00:10:34,580
going to put this to the side so we can
see both things at the same time for now

122
00:10:34,580 --> 00:10:38,150
so you can see we're now ready to
actually be able to work in both windows

123
00:10:38,150 --> 00:10:43,040
so just gonna close that off and come
back here and obviously i can just

124
00:10:43,040 --> 00:10:46,760
select this here i can copy it and i can
paste it directly in

125
00:10:47,370 --> 00:10:50,160
because it's got a semicolon at the end
I can press enter and i can get the

126
00:10:50,160 --> 00:10:53,310
results that I want and I can go back to
my text editor and have been

127
00:10:53,310 --> 00:10:57,900
manipulating anything that i actually
need to do so this is also useful when

128
00:10:57,900 --> 00:11:01,410
you're working you're going to be
working with Android code because you'll

129
00:11:01,410 --> 00:11:05,160
be typing in a command interactively to
make sure that it works then you'll be

130
00:11:05,160 --> 00:11:09,540
taking this code which i've copied into
my in this case and text edit which is a

131
00:11:09,540 --> 00:11:13,170
standard text editor that comes with the
mac but you might also be pasting that

132
00:11:13,170 --> 00:11:15,800
into code
as well so it's a good sort of thing to

133
00:11:15,800 --> 00:11:19,040
know how to do because you'll be doing
that that would normally be the sequence

134
00:11:19,040 --> 00:11:22,550
of things you test to make sure that the
queries the sql code that you're

135
00:11:22,550 --> 00:11:27,020
typing is correct and valid in sql lite first and then once you sure that

136
00:11:27,020 --> 00:11:27,860
it's working

137
00:11:27,860 --> 00:11:30,890
that's when you copy the code and then
put it back in Android studio in the

138
00:11:30,890 --> 00:11:32,750
the relevant java file

139
00:11:32,750 --> 00:11:36,170
alright so I'm going to finish the video
here now in the next video we're going

140
00:11:36,170 --> 00:11:40,280
to talk about the wild-card where
I started telling you about the fact that

141
00:11:40,280 --> 00:11:42,980
you can actually match on partial
strings so we'll actually have a look at

142
00:11:42,980 --> 00:11:44,690
how to do that in the next video