WEBVTT

1
00:00:01.700 --> 00:00:05.660
alright so i'm actually quit out of the
sql lite and I've started it up again I'm

2
00:00:05.660 --> 00:00:09.500
just going to type this command in and you can
just see that there's actually a

3
00:00:09.500 --> 00:00:13.549
tremendous number of Records here that
have appeared on the screen and

4
00:00:13.549 --> 00:00:15.650
scrolling up as you can see there's tons of
them

5
00:00:15.650 --> 00:00:19.820
now depending on the speed of your Mac
you might find it a lot slower to output

6
00:00:19.820 --> 00:00:24.740
thease and you can actually use ctrl z to
stop the listing and that also let you

7
00:00:24.740 --> 00:00:27.740
see some record from the middle of the
list rather than just the same view from

8
00:00:27.740 --> 00:00:32.270
the end now you can do the same thing on
windows with ctrl Z but there's

9
00:00:32.270 --> 00:00:37.550
apparently a bug that causes ctrl z to
quit sql lite as well so you might

10
00:00:37.550 --> 00:00:43.130
have to start sql lite again with the
sql lite 3 space music.DB to get back

11
00:00:43.130 --> 00:00:47.239
into the database if you're on windows
and you press ctrl z on linux you

12
00:00:47.239 --> 00:00:51.980
might find as well that you should find ctrl z will stop the listing as

13
00:00:51.980 --> 00:00:52.489
well

14
00:00:52.489 --> 00:00:56.660
alright so let's just changed
around a little bit and it might

15
00:00:56.660 --> 00:00:59.960
actually look neater the other way
around if we list the album name before

16
00:00:59.960 --> 00:01:06.979
the song title so to do that we type....

17
00:01:06.979 --> 00:01:21.289
....

18
00:01:21.289 --> 00:01:35.329
....as you

19
00:01:35.329 --> 00:01:39.319
can see that does look a little bit
neater so you're free to return any

20
00:01:39.319 --> 00:01:42.619
columns you want in any order you don't
have to keep them in the same order that

21
00:01:42.619 --> 00:01:46.609
they appear in the table nor in the
order the actual table are joined either

22
00:01:46.609 --> 00:01:49.609
alright time for another mini challenge

23
00:01:52.490 --> 00:01:56.780
alright so the challenges is to produce a
list of all the artists with their

24
00:01:56.780 --> 00:02:02.300
albums in alphabetical order of artists
name so go away and see if you can figure out

25
00:02:02.300 --> 00:02:05.870
the sql code for that pause the video
and when you're ready to see my solution

26
00:02:05.870 --> 00:02:09.170
come back toward and i'll show you how
to do it so pause the video now

27
00:02:13.100 --> 00:02:21.230
alright so the way to solve that would
be....

28
00:02:21.230 --> 00:02:44.330
....

29
00:02:44.330 --> 00:02:49.550
and because I can't use my up arrow i'm
going to type in in again so...

30
00:02:49.550 --> 00:03:11.510
....

31
00:03:11.510 --> 00:03:17.900
....okay there you go that's the solution
now what if you wanted to find out which

32
00:03:17.900 --> 00:03:22.610
artist produced a song though now the
songs table doesn't have any direct

33
00:03:22.610 --> 00:03:26.960
links to artists and we just go back and
look at the relationship between the

34
00:03:26.960 --> 00:03:32.680
tables again it will help us to see how
we can get the artist for a song alright

35
00:03:32.680 --> 00:03:37.670
here's a representation just zoom in
so you can see that little bit better so

36
00:03:37.670 --> 00:03:40.990
if we have a look at the relationships
between these tables again that's going

37
00:03:40.990 --> 00:03:45.020
to help to see how we can get that
artist for a particular song now although

38
00:03:45.020 --> 00:03:49.880
we can't go directly to an artist from a
song record we can find out which album

39
00:03:49.880 --> 00:03:53.600
contains the song and from there it
should be easy to find out who the

40
00:03:53.600 --> 00:03:59.240
artist is and we do that by joining
songs to albums and then albums to

41
00:03:59.240 --> 00:04:02.240
artists let's have a go at typing
the code for that

42
00:04:04.110 --> 00:04:15.990
so we type.....

43
00:04:15.990 --> 00:04:52.050
....

44
00:04:52.050 --> 00:04:59.550
....so that's actually quite a lot of

45
00:04:59.550 --> 00:05:03.600
statements you can see so it was good
that sql lite allows us to split

46
00:05:03.600 --> 00:05:08.040
over more than one line and doesn't try
to execute the statement until it finds

47
00:05:08.040 --> 00:05:10.140
an ending semicolon

48
00:05:10.140 --> 00:05:14.130
so what I've done here is just chain
the inner joins together so we have

49
00:05:14.130 --> 00:05:19.020
songs inner join albums inner join artists now of course we have to specify

50
00:05:19.020 --> 00:05:23.310
which columns to join on but hopefully
the syntax as I've shown up there does make

51
00:05:23.310 --> 00:05:29.520
sense and we'll just run this to make sure
it works and you can see that we've got

52
00:05:29.520 --> 00:05:34.860
the results that we're looking for the
actual artist who produced the song so

53
00:05:34.860 --> 00:05:38.730
the Select statement is pretty flexible
we can include as many columns as we

54
00:05:38.730 --> 00:05:43.680
need joining tables as we need them and
then sort of as many columns as we need to

55
00:05:43.680 --> 00:05:48.600
produce decent output and you can also
nest select inside another select

56
00:05:48.600 --> 00:05:53.010
statement but if you get to the stage of
needing to do that then you really

57
00:05:53.010 --> 00:05:56.100
getting into more advanced sql and
that's above what we have time to cover

58
00:05:56.100 --> 00:05:57.660
in this course

59
00:05:57.660 --> 00:06:00.870
just keep in mind that the sql
language is very powerful and really

60
00:06:00.870 --> 00:06:05.130
quite simple considering what you can do
with it now one thing that i haven't

61
00:06:05.130 --> 00:06:09.930
mentioned so far is that the ordering of
the clauses is important so you can't go

62
00:06:09.930 --> 00:06:14.390
putting the order by Clause before the
joins for example the order is strict

63
00:06:14.390 --> 00:06:17.740
in that regard so the order that we've
been doing things so far is actually

64
00:06:17.740 --> 00:06:21.610
correct way to do it now if you want to
include a where clause it has to go

65
00:06:21.610 --> 00:06:26.260
before the order by Clause let's
restrict the previous query to just the

66
00:06:26.260 --> 00:06:29.050
album do little which has the id 19

67
00:06:29.050 --> 00:06:34.900
alright so what I actually did was I
actually copy and pasted off-screen so

68
00:06:34.900 --> 00:06:38.770
you ignore those little dots and the
greater than sign it's because i pasted

69
00:06:38.770 --> 00:06:42.370
it all in the at the same time then it's
actually come up with what would have

70
00:06:42.370 --> 00:06:45.640
happened if we had press enter but the
point is that what i've done is i put

71
00:06:45.640 --> 00:06:50.260
the where clause you can see before the
order by clause and I've got a semicolon

72
00:06:50.260 --> 00:06:55.270
on the end so this should work
when I press enter so we're only getting the

73
00:06:55.270 --> 00:06:59.830
rows back for the album do little which
again have the ID 19 that's where the

74
00:06:59.830 --> 00:07:04.210
where clause came in now splitting
the command over several lines like this

75
00:07:04.210 --> 00:07:08.290
does make it easy to understand but it
does make calling the command back a

76
00:07:08.290 --> 00:07:09.190
little tricky

77
00:07:09.190 --> 00:07:12.850
you have to do it line by line so the
trick would be there if this up arrow was

78
00:07:12.850 --> 00:07:17.350
working for you is to actually keep
pressing the up arrow to call back the

79
00:07:17.350 --> 00:07:21.400
first line in the statement the select
line here then press enter then use the

80
00:07:21.400 --> 00:07:23.650
up arrow to call back the next line and
so on

81
00:07:23.650 --> 00:07:26.590
I can't actually show you that because as I've outlined its not working

82
00:07:26.590 --> 00:07:31.630
properly on the mac but what i can do is
paste in part of the command like so

83
00:07:31.630 --> 00:07:38.500
then what i'm going to do is add the
claus again so...

84
00:07:38.500 --> 00:07:52.450
....

85
00:07:52.450 --> 00:07:59.650
....so as you can see the
structure of the Select statement is

86
00:07:59.650 --> 00:08:04.030
quite straightforward you specify the
columns that you're interested in you

87
00:08:04.030 --> 00:08:07.510
join any other tables that are needed
filter the selection using a where

88
00:08:07.510 --> 00:08:13.360
clause and finally you order the results
now sometimes you have a rough idea of

89
00:08:13.360 --> 00:08:15.820
what you want to find but you don't know
what exactly

90
00:08:15.820 --> 00:08:19.240
or perhaps you're interested in several
rows have similar but not identical

91
00:08:19.240 --> 00:08:24.640
names now the sql where clause can
use wildcards to match on partial

92
00:08:24.640 --> 00:08:29.980
strings to cope with these situations
now I'm going to actually change sql

93
00:08:29.980 --> 00:08:31.360
commands that span a few lines

94
00:08:31.360 --> 00:08:35.200
in this next bit and as you've seen
editing the statements from within the

95
00:08:35.200 --> 00:08:39.970
sql lite show there's a bit
fiddly a useful tip when working with the

96
00:08:39.970 --> 00:08:44.290
sql lite shell is to keep a text editor
handy and copy and paste between the two

97
00:08:44.290 --> 00:08:48.940
windows and you also need to know to copy
from that terminal command line if you

98
00:08:48.940 --> 00:08:51.940
want to take the output from the .
schema command and paste it into your

99
00:08:51.940 --> 00:08:57.370
code going to digress slightly and show
you how to do that so i can just come up

100
00:08:57.370 --> 00:09:01.150
here and actually just copy these
commands that I've type in here selected here

101
00:09:03.460 --> 00:09:06.550
I do get a line at time I can just select the part you've seen

102
00:09:06.550 --> 00:09:12.010
me doing this in the previous videos I
can actually copy that and what i can do

103
00:09:12.010 --> 00:09:16.060
is i can drag that down with my mouse
and put into this line here and you can

104
00:09:16.060 --> 00:09:19.000
see that's been added or I could have
done that or I could have right-click it

105
00:09:19.000 --> 00:09:25.060
and select and copy and paste which is
also saw me do previously now on Windows

106
00:09:25.060 --> 00:09:28.510
you can copy the selected text into the
clipboard after you've selected by

107
00:09:28.510 --> 00:09:32.290
pressing enter and on linux you need to
click the right mouse button and choose

108
00:09:32.290 --> 00:09:35.590
copy from the context menu that actually
appears so it does depend on the

109
00:09:35.590 --> 00:09:39.190
operating system as to how you go about
copying that the bottom line is at that

110
00:09:39.190 --> 00:09:42.640
point the text is now in the clipboard
and you can paste it into the text

111
00:09:42.640 --> 00:09:48.340
editor that you want to use and
manipulate the sql statement on the

112
00:09:48.340 --> 00:09:51.130
case of here what I'm going to do
is I'm just going to copy all of this

113
00:09:51.130 --> 00:09:56.320
here and i can do a copy here if I could do it that way and i can open

114
00:09:56.320 --> 00:10:02.560
the text editor in my case I'm just
going to open text edit but you can use

115
00:10:02.560 --> 00:10:07.390
notepad or a linux editor as appropriate
and I can just paste it in there and

116
00:10:07.390 --> 00:10:11.590
notice that if we just zoom in there we
might have to clean a little bit of this

117
00:10:11.590 --> 00:10:15.280
up so we need to just delete this part
here with those extra parts were added

118
00:10:15.280 --> 00:10:18.940
by sql lite when enter was meant to be pressed and I just press enter the

119
00:10:18.940 --> 00:10:22.810
relevant place to just to build up the
clause like that and eventually I got the

120
00:10:22.810 --> 00:10:27.340
thing working and ready to be
manipulated and what do is I'm just

121
00:10:27.340 --> 00:10:31.180
going to put this to the side so we can
see both things at the same time for now

122
00:10:31.180 --> 00:10:34.750
so you can see we're now ready to
actually be able to work in both windows

123
00:10:34.750 --> 00:10:39.640
so just gonna close that off and come
back here and obviously i can just

124
00:10:39.640 --> 00:10:43.360
select this here i can copy it and i can
paste it directly in

125
00:10:43.970 --> 00:10:46.760
because it's got a semicolon at the end
I can press enter and i can get the

126
00:10:46.760 --> 00:10:49.910
results that I want and I can go back to
my text editor and have been

127
00:10:49.910 --> 00:10:54.500
manipulating anything that i actually
need to do so this is also useful when

128
00:10:54.500 --> 00:10:58.010
you're working you're going to be
working with Android code because you'll

129
00:10:58.010 --> 00:11:01.760
be typing in a command interactively to
make sure that it works then you'll be

130
00:11:01.760 --> 00:11:06.140
taking this code which i've copied into
my in this case and text edit which is a

131
00:11:06.140 --> 00:11:09.770
standard text editor that comes with the
mac but you might also be pasting that

132
00:11:09.770 --> 00:11:12.400
into code
as well so it's a good sort of thing to

133
00:11:12.400 --> 00:11:15.640
know how to do because you'll be doing
that that would normally be the sequence

134
00:11:15.640 --> 00:11:19.150
of things you test to make sure that the
queries the sql code that you're

135
00:11:19.150 --> 00:11:23.620
typing is correct and valid in sql lite first and then once you sure that

136
00:11:23.620 --> 00:11:24.460
it's working

137
00:11:24.460 --> 00:11:27.490
that's when you copy the code and then
put it back in Android studio in the

138
00:11:27.490 --> 00:11:29.350
the relevant java file

139
00:11:29.350 --> 00:11:32.770
alright so I'm going to finish the video
here now in the next video we're going

140
00:11:32.770 --> 00:11:36.880
to talk about the wild-card where
I started telling you about the fact that

141
00:11:36.880 --> 00:11:39.580
you can actually match on partial
strings so we'll actually have a look at

142
00:11:39.580 --> 00:11:41.290
how to do that in the next video