1 00:00:05,540 --> 00:00:10,790 ok so let's look a bit more at querying the data including how we can make sure 2 00:00:10,790 --> 00:00:15,310 that we get the data back in a sensible order if as I mentioned ordering of rows 3 00:00:15,310 --> 00:00:20,630 is undefined in a relational database so when we display all the artists records 4 00:00:20,630 --> 00:00:25,720 actually come out in the same order each time i can do that again select.... 5 00:00:25,720 --> 00:00:32,870 .....so we get the same every time we do we get the same order and that's 6 00:00:32,870 --> 00:00:37,490 because we have a primary key so the records will automatically be selected 7 00:00:37,490 --> 00:00:42,410 based on the ordering of the primary key note that the actual order of the record 8 00:00:42,410 --> 00:00:47,300 in the database is undefined and if we didn't have a primary key that will be coming 9 00:00:47,300 --> 00:00:52,010 out in an undefined order now we can actually specify a different order in 10 00:00:52,010 --> 00:00:57,800 our select statement by using an order by clause so we can type..... 11 00:00:57,800 --> 00:01:08,480 ....you can see now that the 12 00:01:08,480 --> 00:01:13,400 records have appeared in alphabetical order and I can do exactly the same for 13 00:01:13,400 --> 00:01:24,970 the album's.....and you notice right down the bottom 14 00:01:24,970 --> 00:01:27,620 the two black beards Tea Party albums 15 00:01:27,620 --> 00:01:30,730 heavens to Betsy and whip jamboree are out of order 16 00:01:31,270 --> 00:01:35,650 that's because they start with lowercase letters now you can actually ignore case 17 00:01:35,650 --> 00:01:44,080 by using the collate no case clause so we can do..... 18 00:01:44,080 --> 00:01:56,020 ....and you can see there are the ID 430 about 80% towards 19 00:01:56,020 --> 00:01:59,150 the bottom of the screen which is whipped jamboree in that appears with 20 00:01:59,150 --> 00:02:03,110 the other albums beginning with W in other words instead now ignoring case 21 00:02:03,110 --> 00:02:08,289 when it's actually returning results now it is also possible to specify ascending 22 00:02:08,289 --> 00:02:14,600 or descending order using the keys keywords asc desc or ASC or desc 23 00:02:14,600 --> 00:02:17,430 respectively which stands for ascending or descending order 24 00:02:17,430 --> 00:02:24,510 so we can do select.... 25 00:02:25,290 --> 00:02:28,470 .... 26 00:02:28,470 --> 00:02:34,410 .....that's fine but what if we want to group albums together 27 00:02:34,410 --> 00:02:39,630 so that all the albums by each artist appear together well the order by clause 28 00:02:39,630 --> 00:02:43,080 can actually contain more than one column so we can do something like 29 00:02:43,080 --> 00:02:53,040 .... 30 00:02:53,670 --> 00:03:03,360 ....and what that does it sorts first by artist ID and then by album 31 00:03:03,360 --> 00:03:09,120 name so all the deep purple albums artists 196 you can see a group of them 32 00:03:09,120 --> 00:03:14,670 here with the number 196 at the end of the list of data they appear together near the 33 00:03:14,670 --> 00:03:18,840 end of the list you can see it also starting with burn this one up here 34 00:03:18,840 --> 00:03:23,820 starting burn their and ending right down here with who do we think we are 35 00:03:23,820 --> 00:03:26,920 remastered edition 36 00:03:26,920 --> 00:03:29,850 ok time for another mini challenge 37 00:03:29,850 --> 00:03:35,070 the challenges is to list all the songs so that songs from the same album appear 38 00:03:35,070 --> 00:03:39,380 together in track order so that's the challenge have a go at doing that by 39 00:03:39,380 --> 00:03:43,590 type in the sql code that necessary to achieve that pause the 40 00:03:43,590 --> 00:03:47,270 video and when you're ready to see me type it in start the video again so pause 41 00:03:47,270 --> 00:03:53,240 the video and I'll see you when you get back alright so to achieve that what we 42 00:03:53,240 --> 00:03:57,180 need to do to list all the songs so that song from the same album appear together 43 00:03:57,180 --> 00:04:08,910 in track order we type.... 44 00:04:08,910 --> 00:04:11,460 ....there you go 45 00:04:11,460 --> 00:04:16,079 so now the 11 songs from The Black Keys albums attack and release appear together 46 00:04:16,079 --> 00:04:19,890 as you can see right at the end of the list and you can check that you wanted 47 00:04:19,890 --> 00:04:28,140 to by typing..... 48 00:04:28,140 --> 00:04:35,880 .....and then we could do something like.... 49 00:04:35,880 --> 00:04:41,160 .... 50 00:04:43,230 --> 00:04:47,550 ....there you go you can see with a quick scan up to the list shows that the records 51 00:04:47,550 --> 00:04:51,750 are grouped by album ID the last column and in track order within an album the 52 00:04:51,750 --> 00:04:56,550 second column now having to run separate queries like that is a bit grubby though 53 00:04:56,550 --> 00:05:01,170 let's see how to relate the tables together so that we can get a list of 54 00:05:01,170 --> 00:05:05,450 songs that include the album the appear on as well as the artist that produce them 55 00:05:06,560 --> 00:05:11,960 now to do this we need to use the SQL join clause that used to join tables 56 00:05:11,960 --> 00:05:16,760 together now keeping data normalized so the tables only contain information 57 00:05:16,760 --> 00:05:21,940 that relates to a single thing song album or artist in our example is a 58 00:05:21,940 --> 00:05:26,930 fundamental part of relational databases and by doing that and then joining the 59 00:05:26,930 --> 00:05:31,280 tables back together you get a great deal of flexibility in how you can query 60 00:05:31,280 --> 00:05:36,160 and manipulate the data now remember that the songs table contains a column 61 00:05:36,160 --> 00:05:41,630 holding the album ID and the album table has an artist ID field and these are 62 00:05:41,630 --> 00:05:44,630 used to provide a link between the tables 63 00:05:45,170 --> 00:05:49,070 don't worry about how those ids got into the tables at this stage we just 64 00:05:49,070 --> 00:05:52,070 interested in using them to join the tables at the moment 65 00:05:53,120 --> 00:05:57,860 so you can see here on screen how the album column in the songs table provides 66 00:05:57,860 --> 00:06:03,260 a link to the album table the first 10 songs all belong to the album whose ID 67 00:06:03,260 --> 00:06:08,150 is one tales of the crown and the next set of songs belong to the masquerade 68 00:06:08,150 --> 00:06:11,420 ball 69 00:06:11,420 --> 00:06:16,790 the artist column in the album's links to the artist table so those first two 70 00:06:16,790 --> 00:06:21,860 albums are by axel rudi pell and the album crimes of passion is by pat 71 00:06:21,860 --> 00:06:27,830 benatar and night flight is by band called budgie alright so with that said let's 72 00:06:27,830 --> 00:06:32,960 actually join the tables in sql and see how this is going to look so I'm 73 00:06:32,960 --> 00:06:37,340 going to do I'm actually just going to do a . quit and then I'm just going to 74 00:06:37,340 --> 00:06:42,200 clear the screen and see notice how the up arrow is working from here it's just in 75 00:06:42,200 --> 00:06:47,090 sql lite three for some reason it's not working maybe it was weird characters but 76 00:06:47,090 --> 00:06:51,680 so I've gone back into the database again and just so starting off with a clean 77 00:06:51,680 --> 00:06:56,870 slate so let's let's now use this select statement and add a joint clause to link 78 00:06:56,870 --> 00:07:04,550 the songs and albums i'm going to do is type.... 79 00:07:04,550 --> 00:07:27,770 .... 80 00:07:27,770 --> 00:07:32,590 ....press enter their so the first thing to note is that have specified which table the 81 00:07:32,590 --> 00:07:37,180 columns are in when selecting them and probably what I should have done is explained that 82 00:07:37,180 --> 00:07:40,550 while that select statement was on screen because of course now i can't 83 00:07:40,550 --> 00:07:43,550 bring back or can i I can scroll up 84 00:07:45,450 --> 00:07:51,210 so what I do is I just type it again and again you shouldn't have this scenario 85 00:07:51,210 --> 00:07:55,290 should be able to do an up arrow and it should work but for some reason my Mac 86 00:07:55,290 --> 00:08:06,990 is not doing what i wanted to do so albums.... 87 00:08:06,990 --> 00:08:14,150 .... 88 00:08:14,150 --> 00:08:19,340 alright so leave that on before I press enter this time getting back to that statement 89 00:08:19,340 --> 00:08:22,800 the first thing to note is that i've specified which table the columns are in 90 00:08:22,800 --> 00:08:27,680 when selecting them so track and title are in the song table and you notice how 91 00:08:27,680 --> 00:08:33,289 I use songs . track and songs . title now name comes from the album's table so 92 00:08:33,289 --> 00:08:37,850 specified that as albums . name if there's no ambiguity you can actually 93 00:08:37,850 --> 00:08:42,289 leave off the table name so what I could have done I will just press this to see the 94 00:08:42,289 --> 00:08:49,910 results again so i could have also written this as select track title name from 95 00:08:49,910 --> 00:08:58,260 songs join album on song.albums 96 00:08:58,260 --> 00:09:04,160 ...so I could have done it that way if there's no ambiguity 97 00:09:04,160 --> 00:09:10,230 with the names but it is a good habit to always specify the table 98 00:09:10,230 --> 00:09:15,150 name especially in code now living in it off is a useful shortcut to save 99 00:09:15,150 --> 00:09:20,370 typing when working interactively like this but i'd say always prefix the field 100 00:09:20,370 --> 00:09:25,200 with the table name in your code now some albums have a sort of subtitle so 101 00:09:25,200 --> 00:09:29,130 if the table was modified to include a title column then that query will no 102 00:09:29,130 --> 00:09:33,330 longer work because it would know which table the title column should come from 103 00:09:33,330 --> 00:09:38,580 and note though that we can't leave the table name off when using the ID column 104 00:09:38,580 --> 00:09:42,960 so we just went back hear the end of it instead of putting albums.id if I 105 00:09:42,960 --> 00:09:48,780 just put _ID there and press semicolon press enter we get error no 106 00:09:48,780 --> 00:09:53,700 such song and and sorry no such column song . album now that was 107 00:09:53,700 --> 00:09:57,650 actually different message that was because i accidentally type song their 108 00:09:57,650 --> 00:09:58,750 so just going but I shoudl 109 00:09:58,750 --> 00:10:02,830 be able to copy and paste i might do it that way it save a bit of time so 110 00:10:02,830 --> 00:10:07,990 the original request was songs should be have been songs.album because of course songs is 111 00:10:07,990 --> 00:10:13,420 the name of the table was going to show you as if I just type it like that without 112 00:10:13,420 --> 00:10:19,870 actually putting the albums . before the ID and press enter now we get the error 113 00:10:19,870 --> 00:10:23,530 that i wanted to show you the first time error ambiguous column name 114 00:10:23,530 --> 00:10:28,450 _ID and that's because both tables have a column of that same name _ID 115 00:10:28,450 --> 00:10:31,150 and then sql lite doesn't know which one you mean 116 00:10:31,150 --> 00:10:37,570 so you need to specify it there and just going to copy that again paste it so i 117 00:10:37,570 --> 00:10:42,700 would go back and make that songs and make that album I should say . _ID 118 00:10:42,700 --> 00:10:50,380 and get our data back there are different types of joins the most common being an 119 00:10:50,380 --> 00:10:55,780 inner join and join as I have used here is really a shorthand for inner join what 120 00:10:55,780 --> 00:10:58,780 I'll do is I'll retrieve the full command that included the table names 121 00:10:58,780 --> 00:11:06,790 then used include the word inner so i'm going to type.... 122 00:11:06,790 --> 00:11:16,780 .... 123 00:11:16,780 --> 00:11:27,550 ....now 124 00:11:27,550 --> 00:11:31,060 keep in mind that not all database systems will allow you to leave off the 125 00:11:31,060 --> 00:11:35,650 word inner so it's worth always using it and i'll just run this to make sure it 126 00:11:35,650 --> 00:11:41,680 works now looking at the result of that query we can see that the song just walk 127 00:11:41,680 --> 00:11:47,020 in my shoes is from the album super lungs and permanent vacation is from an 128 00:11:47,020 --> 00:11:53,770 album of the same name and so on so just paste this code back in again so again 129 00:11:53,770 --> 00:11:57,730 this select statement follows the same pattern as we've been using up until now 130 00:11:57,730 --> 00:12:03,430 instead of select from songs we're doing select from songs inner join albums 131 00:12:03,430 --> 00:12:07,690 we then have to tell sql lite which columns are involved in the join which 132 00:12:07,690 --> 00:12:12,520 is what the on part does it says to relate the rows and songs 133 00:12:12,520 --> 00:12:18,670 to those in albums where the songs tables album column equals the album 134 00:12:18,670 --> 00:12:24,310 tables ID column and if you really want to we can actually tact the order by 135 00:12:24,310 --> 00:12:28,660 clause on the end of that if we want to sort the data so i could come to the end 136 00:12:28,660 --> 00:12:39,790 here and I could type..... 137 00:12:39,790 --> 00:12:47,530 .....that's actually 138 00:12:47,530 --> 00:12:51,430 returned a heck of a lot of results as you can see there but it actually 139 00:12:51,430 --> 00:12:56,080 went through and do it really quickly if I wanted to I could just scroll back up 140 00:12:56,080 --> 00:13:00,580 and have a look at some of the other data that has been returned but you can see 141 00:13:00,580 --> 00:13:04,660 there's a lot of data and sql lite has manipulated that and returned it very 142 00:13:04,660 --> 00:13:05,920 quickly 143 00:13:05,920 --> 00:13:09,760 alright so I'm going to finish the video here now we'll continue on working with 144 00:13:09,760 --> 00:13:11,410 sql lite in the next video