1
00:00:04,950 --> 00:00:08,910
alright so i'm actually quit out of the
sql lite and I've started it up again I'm

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

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

4
00:00:16,790 --> 00:00:18,900
scrolling up as you can see there's tons of
them

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

6
00:00:23,070 --> 00:00:27,990
thease and you can actually use ctrl z to
stop the listing and that also let you

7
00:00:27,990 --> 00:00:30,990
see some record from the middle of the
list rather than just the same view from

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

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

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

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

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

13
00:00:55,230 --> 00:00:55,730
well

14
00:00:55,730 --> 00:00:59,910
alright so let's just changed
around a little bit and it might

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

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

17
00:01:10,220 --> 00:01:24,530
....

18
00:01:24,530 --> 00:01:38,570
....as you

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

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

21
00:01:45,860 --> 00:01:49,850
they appear in the table nor in the
order the actual table are joined either

22
00:01:49,850 --> 00:01:52,850
alright time for another mini challenge

23
00:01:59,510 --> 00:02:03,800
alright so the challenges is to produce a
list of all the artists with their

24
00:02:03,800 --> 00:02:09,320
albums in alphabetical order of artists
name so go away and see if you can figure out

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

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

27
00:02:20,120 --> 00:02:28,250
alright so the way to solve that would
be....

28
00:02:28,250 --> 00:02:51,350
....

29
00:02:51,350 --> 00:02:56,570
and because I can't use my up arrow i'm
going to type in in again so...

30
00:02:56,570 --> 00:03:18,530
....

31
00:03:18,530 --> 00:03:24,920
....okay there you go that's the solution
now what if you wanted to find out which

32
00:03:24,920 --> 00:03:29,630
artist produced a song though now the
songs table doesn't have any direct

33
00:03:29,630 --> 00:03:33,980
links to artists and we just go back and
look at the relationship between the

34
00:03:33,980 --> 00:03:39,700
tables again it will help us to see how
we can get the artist for a song alright

35
00:03:39,700 --> 00:03:44,690
here's a representation just zoom in
so you can see that little bit better so

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

37
00:03:48,010 --> 00:03:52,040
to help to see how we can get that
artist for a particular song now although

38
00:03:52,040 --> 00:03:56,900
we can't go directly to an artist from a
song record we can find out which album

39
00:03:56,900 --> 00:04:00,620
contains the song and from there it
should be easy to find out who the

40
00:04:00,620 --> 00:04:06,260
artist is and we do that by joining
songs to albums and then albums to

41
00:04:06,260 --> 00:04:09,260
artists let's have a go at typing
the code for that

42
00:04:11,130 --> 00:04:23,010
so we type.....

43
00:04:23,010 --> 00:04:59,070
....

44
00:04:59,070 --> 00:05:06,570
....so that's actually quite a lot of

45
00:05:06,570 --> 00:05:10,620
statements you can see so it was good
that sql lite allows us to split

46
00:05:10,620 --> 00:05:15,060
over more than one line and doesn't try
to execute the statement until it finds

47
00:05:15,060 --> 00:05:17,160
an ending semicolon

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

49
00:05:21,150 --> 00:05:26,040
songs inner join albums inner join artists now of course we have to specify

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

51
00:05:30,330 --> 00:05:36,540
sense and we'll just run this to make sure
it works and you can see that we've got

52
00:05:36,540 --> 00:05:41,880
the results that we're looking for the
actual artist who produced the song so

53
00:05:41,880 --> 00:05:45,750
the Select statement is pretty flexible
we can include as many columns as we

54
00:05:45,750 --> 00:05:50,700
need joining tables as we need them and
then sort of as many columns as we need to

55
00:05:50,700 --> 00:05:55,620
produce decent output and you can also
nest select inside another select

56
00:05:55,620 --> 00:06:00,030
statement but if you get to the stage of
needing to do that then you really

57
00:06:00,030 --> 00:06:03,120
getting into more advanced sql and
that's above what we have time to cover

58
00:06:03,120 --> 00:06:04,680
in this course

59
00:06:04,680 --> 00:06:07,890
just keep in mind that the sql
language is very powerful and really

60
00:06:07,890 --> 00:06:12,150
quite simple considering what you can do
with it now one thing that i haven't

61
00:06:12,150 --> 00:06:16,950
mentioned so far is that the ordering of
the clauses is important so you can't go

62
00:06:16,950 --> 00:06:21,410
putting the order by Clause before the
joins for example the order is strict

63
00:06:21,410 --> 00:06:24,760
in that regard so the order that we've
been doing things so far is actually

64
00:06:24,760 --> 00:06:28,630
correct way to do it now if you want to
include a where clause it has to go

65
00:06:28,630 --> 00:06:33,280
before the order by Clause let's
restrict the previous query to just the

66
00:06:33,280 --> 00:06:36,070
album do little which has the id 19

67
00:06:36,070 --> 00:06:41,920
alright so what I actually did was I
actually copy and pasted off-screen so

68
00:06:41,920 --> 00:06:45,790
you ignore those little dots and the
greater than sign it's because i pasted

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

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

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

72
00:06:57,280 --> 00:07:02,290
on the end so this should work
when I press enter so we're only getting the

73
00:07:02,290 --> 00:07:06,850
rows back for the album do little which
again have the ID 19 that's where the

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

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

76
00:07:15,310 --> 00:07:16,210
little tricky

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

78
00:07:19,870 --> 00:07:24,370
working for you is to actually keep
pressing the up arrow to call back the

79
00:07:24,370 --> 00:07:28,420
first line in the statement the select
line here then press enter then use the

80
00:07:28,420 --> 00:07:30,670
up arrow to call back the next line and
so on

81
00:07:30,670 --> 00:07:33,610
I can't actually show you that because as I've outlined its not working

82
00:07:33,610 --> 00:07:38,650
properly on the mac but what i can do is
paste in part of the command like so

83
00:07:38,650 --> 00:07:45,520
then what i'm going to do is add the
claus again so...

84
00:07:45,520 --> 00:07:59,470
....

85
00:07:59,470 --> 00:08:06,670
....so as you can see the
structure of the Select statement is

86
00:08:06,670 --> 00:08:11,050
quite straightforward you specify the
columns that you're interested in you

87
00:08:11,050 --> 00:08:14,530
join any other tables that are needed
filter the selection using a where

88
00:08:14,530 --> 00:08:20,380
clause and finally you order the results
now sometimes you have a rough idea of

89
00:08:20,380 --> 00:08:22,840
what you want to find but you don't know
what exactly

90
00:08:22,840 --> 00:08:26,260
or perhaps you're interested in several
rows have similar but not identical

91
00:08:26,260 --> 00:08:31,660
names now the sql where clause can
use wildcards to match on partial

92
00:08:31,660 --> 00:08:37,000
strings to cope with these situations
now I'm going to actually change sql

93
00:08:37,000 --> 00:08:38,380
commands that span a few lines

94
00:08:38,380 --> 00:08:42,220
in this next bit and as you've seen
editing the statements from within the

95
00:08:42,220 --> 00:08:46,990
sql lite show there's a bit
fiddly a useful tip when working with the

96
00:08:46,990 --> 00:08:51,310
sql lite shell is to keep a text editor
handy and copy and paste between the two

97
00:08:51,310 --> 00:08:55,960
windows and you also need to know to copy
from that terminal command line if you

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

99
00:08:58,960 --> 00:09:04,390
code going to digress slightly and show
you how to do that so i can just come up

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

101
00:09:10,480 --> 00:09:13,570
I do get a line at time I can just select the part you've seen

102
00:09:13,570 --> 00:09:19,030
me doing this in the previous videos I
can actually copy that and what i can do

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

104
00:09:23,080 --> 00:09:26,020
see that's been added or I could have
done that or I could have right-click it

105
00:09:26,020 --> 00:09:32,080
and select and copy and paste which is
also saw me do previously now on Windows

106
00:09:32,080 --> 00:09:35,530
you can copy the selected text into the
clipboard after you've selected by

107
00:09:35,530 --> 00:09:39,310
pressing enter and on linux you need to
click the right mouse button and choose

108
00:09:39,310 --> 00:09:42,610
copy from the context menu that actually
appears so it does depend on the

109
00:09:42,610 --> 00:09:46,210
operating system as to how you go about
copying that the bottom line is at that

110
00:09:46,210 --> 00:09:49,660
point the text is now in the clipboard
and you can paste it into the text

111
00:09:49,660 --> 00:09:55,360
editor that you want to use and
manipulate the sql statement on the

112
00:09:55,360 --> 00:09:58,150
case of here what I'm going to do
is I'm just going to copy all of this

113
00:09:58,150 --> 00:10:03,340
here and i can do a copy here if I could do it that way and i can open

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

115
00:10:09,580 --> 00:10:14,410
notepad or a linux editor as appropriate
and I can just paste it in there and

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

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

118
00:10:22,300 --> 00:10:25,960
by sql lite when enter was meant to be pressed and I just press enter the

119
00:10:25,960 --> 00:10:29,830
relevant place to just to build up the
clause like that and eventually I got the

120
00:10:29,830 --> 00:10:34,360
thing working and ready to be
manipulated and what do is I'm just

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

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

123
00:10:41,770 --> 00:10:46,660
so just gonna close that off and come
back here and obviously i can just

124
00:10:46,660 --> 00:10:50,380
select this here i can copy it and i can
paste it directly in

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

126
00:10:53,780 --> 00:10:56,930
results that I want and I can go back to
my text editor and have been

127
00:10:56,930 --> 00:11:01,520
manipulating anything that i actually
need to do so this is also useful when

128
00:11:01,520 --> 00:11:05,030
you're working you're going to be
working with Android code because you'll

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

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

131
00:11:13,160 --> 00:11:16,790
standard text editor that comes with the
mac but you might also be pasting that

132
00:11:16,790 --> 00:11:20,990
into android studio into some java code
as well so it's a good sort of thing to

133
00:11:20,990 --> 00:11:24,230
know how to do because you'll be doing
that that would normally be the sequence

134
00:11:24,230 --> 00:11:27,740
of things you test to make sure that the
queries the sql code that you're

135
00:11:27,740 --> 00:11:32,210
typing is correct and valid in sql lite first and then once you sure that

136
00:11:32,210 --> 00:11:33,050
it's working

137
00:11:33,050 --> 00:11:36,080
that's when you copy the code and then
put it back in Android studio in the

138
00:11:36,080 --> 00:11:37,940
the relevant java file

139
00:11:37,940 --> 00:11:41,360
alright so I'm going to finish the video
here now in the next video we're going

140
00:11:41,360 --> 00:11:45,470
to talk about the wild-card where
I started telling you about the fact that

141
00:11:45,470 --> 00:11:48,170
you can actually match on partial
strings so we'll actually have a look at

142
00:11:48,170 --> 00:11:49,880
how to do that in the next video