WEBVTT 1 00:00:01.740 --> 00:00:06.990 ok so let's look a bit more at querying the data including how we can make sure 2 00:00:06.990 --> 00:00:11.519 that we get the data back in a sensible order if as I mentioned ordering of rows 3 00:00:11.519 --> 00:00:16.830 is undefined in a relational database so when we display all the artists records 4 00:00:16.830 --> 00:00:21.929 actually come out in the same order each time i can do that again select.... 5 00:00:21.929 --> 00:00:29.070 .....so we get the same every time we do we get the same order and that's 6 00:00:29.070 --> 00:00:33.690 because we have a primary key so the records will automatically be selected 7 00:00:33.690 --> 00:00:38.610 based on the ordering of the primary key note that the actual order of the record 8 00:00:38.610 --> 00:00:43.500 in the database is undefined and if we didn't have a primary key that will be coming 9 00:00:43.500 --> 00:00:48.210 out in an undefined order now we can actually specify a different order in 10 00:00:48.210 --> 00:00:54.000 our select statement by using an order by clause so we can type..... 11 00:00:54.000 --> 00:01:04.680 ....you can see now that the 12 00:01:04.680 --> 00:01:09.600 records have appeared in alphabetical order and I can do exactly the same for 13 00:01:09.600 --> 00:01:21.179 the album's.....and you notice right down the bottom 14 00:01:21.179 --> 00:01:23.820 the two black beards Tea Party albums 15 00:01:23.820 --> 00:01:26.939 heavens to Betsy and whip jamboree are out of order 16 00:01:27.479 --> 00:01:31.859 that's because they start with lowercase letters now you can actually ignore case 17 00:01:31.859 --> 00:01:40.289 by using the collate no case clause so we can do..... 18 00:01:40.289 --> 00:01:52.229 ....and you can see there are the ID 430 about 80% towards 19 00:01:52.229 --> 00:01:55.350 the bottom of the screen which is whipped jamboree in that appears with 20 00:01:55.350 --> 00:01:59.310 the other albums beginning with W in other words instead now ignoring case 21 00:01:59.310 --> 00:02:04.499 when it's actually returning results now it is also possible to specify ascending 22 00:02:04.499 --> 00:02:10.800 or descending order using the keys keywords asc desc or ASC or desc 23 00:02:10.800 --> 00:02:13.630 respectively which stands for ascending or descending order 24 00:02:13.630 --> 00:02:20.710 so we can do select.... 25 00:02:21.490 --> 00:02:24.670 .... 26 00:02:24.670 --> 00:02:30.610 .....that's fine but what if we want to group albums together 27 00:02:30.610 --> 00:02:35.830 so that all the albums by each artist appear together well the order by clause 28 00:02:35.830 --> 00:02:39.280 can actually contain more than one column so we can do something like 29 00:02:39.280 --> 00:02:49.240 .... 30 00:02:49.870 --> 00:02:59.560 ....and what that does it sorts first by artist ID and then by album 31 00:02:59.560 --> 00:03:05.320 name so all the deep purple albums artists 196 you can see a group of them 32 00:03:05.320 --> 00:03:10.870 here with the number 196 at the end of the list of data they appear together near the 33 00:03:10.870 --> 00:03:15.040 end of the list you can see it also starting with burn this one up here 34 00:03:15.040 --> 00:03:20.020 starting burn their and ending right down here with who do we think we are 35 00:03:20.020 --> 00:03:23.120 remastered edition 36 00:03:23.120 --> 00:03:26.050 ok time for another mini challenge 37 00:03:26.050 --> 00:03:31.270 the challenges is to list all the songs so that songs from the same album appear 38 00:03:31.270 --> 00:03:35.580 together in track order so that's the challenge have a go at doing that by 39 00:03:35.580 --> 00:03:39.790 type in the sql code that necessary to achieve that pause the 40 00:03:39.790 --> 00:03:43.470 video and when you're ready to see me type it in start the video again so pause 41 00:03:43.470 --> 00:03:49.440 the video and I'll see you when you get back alright so to achieve that what we 42 00:03:49.440 --> 00:03:53.380 need to do to list all the songs so that song from the same album appear together 43 00:03:53.380 --> 00:04:05.110 in track order we type.... 44 00:04:05.110 --> 00:04:07.660 ....there you go 45 00:04:07.660 --> 00:04:12.280 so now the 11 songs from The Black Keys albums attack and release appear together 46 00:04:12.280 --> 00:04:16.090 as you can see right at the end of the list and you can check that you wanted 47 00:04:16.090 --> 00:04:24.340 to by typing..... 48 00:04:24.340 --> 00:04:32.080 .....and then we could do something like.... 49 00:04:32.080 --> 00:04:37.360 .... 50 00:04:39.430 --> 00:04:43.750 ....there you go you can see with a quick scan up to the list shows that the records 51 00:04:43.750 --> 00:04:47.950 are grouped by album ID the last column and in track order within an album the 52 00:04:47.950 --> 00:04:52.750 second column now having to run separate queries like that is a bit grubby though 53 00:04:52.750 --> 00:04:57.370 let's see how to relate the tables together so that we can get a list of 54 00:04:57.370 --> 00:05:01.650 songs that include the album the appear on as well as the artist that produce them 55 00:05:02.760 --> 00:05:08.160 now to do this we need to use the SQL join clause that used to join tables 56 00:05:08.160 --> 00:05:12.960 together now keeping data normalized so the tables only contain information 57 00:05:12.960 --> 00:05:18.140 that relates to a single thing song album or artist in our example is a 58 00:05:18.140 --> 00:05:23.130 fundamental part of relational databases and by doing that and then joining the 59 00:05:23.130 --> 00:05:27.480 tables back together you get a great deal of flexibility in how you can query 60 00:05:27.480 --> 00:05:32.360 and manipulate the data now remember that the songs table contains a column 61 00:05:32.360 --> 00:05:37.830 holding the album ID and the album table has an artist ID field and these are 62 00:05:37.830 --> 00:05:40.830 used to provide a link between the tables 63 00:05:41.370 --> 00:05:45.270 don't worry about how those ids got into the tables at this stage we just 64 00:05:45.270 --> 00:05:48.270 interested in using them to join the tables at the moment 65 00:05:49.320 --> 00:05:54.060 so you can see here on screen how the album column in the songs table provides 66 00:05:54.060 --> 00:05:59.460 a link to the album table the first 10 songs all belong to the album whose ID 67 00:05:59.460 --> 00:06:04.350 is one tales of the crown and the next set of songs belong to the masquerade 68 00:06:04.350 --> 00:06:07.620 ball 69 00:06:07.620 --> 00:06:12.990 the artist column in the album's links to the artist table so those first two 70 00:06:12.990 --> 00:06:18.060 albums are by axel rudi pell and the album crimes of passion is by pat 71 00:06:18.060 --> 00:06:24.030 benatar and night flight is by band called budgie alright so with that said let's 72 00:06:24.030 --> 00:06:29.160 actually join the tables in sql and see how this is going to look so I'm 73 00:06:29.160 --> 00:06:33.540 going to do I'm actually just going to do a . quit and then I'm just going to 74 00:06:33.540 --> 00:06:38.400 clear the screen and see notice how the up arrow is working from here it's just in 75 00:06:38.400 --> 00:06:43.290 sql lite three for some reason it's not working maybe it was weird characters but 76 00:06:43.290 --> 00:06:47.880 so I've gone back into the database again and just so starting off with a clean 77 00:06:47.880 --> 00:06:53.070 slate so let's let's now use this select statement and add a joint clause to link 78 00:06:53.070 --> 00:07:00.750 the songs and albums i'm going to do is type.... 79 00:07:00.750 --> 00:07:23.970 .... 80 00:07:23.970 --> 00:07:28.790 ....press enter their so the first thing to note is that have specified which table the 81 00:07:28.790 --> 00:07:33.380 columns are in when selecting them and probably what I should have done is explained that 82 00:07:33.380 --> 00:07:36.750 while that select statement was on screen because of course now i can't 83 00:07:36.750 --> 00:07:39.750 bring back or can i I can scroll up 84 00:07:41.650 --> 00:07:47.410 so what I do is I just type it again and again you shouldn't have this scenario 85 00:07:47.410 --> 00:07:51.490 should be able to do an up arrow and it should work but for some reason my Mac 86 00:07:51.490 --> 00:08:03.190 is not doing what i wanted to do so albums.... 87 00:08:03.190 --> 00:08:10.350 .... 88 00:08:10.350 --> 00:08:15.540 alright so leave that on before I press enter this time getting back to that statement 89 00:08:15.540 --> 00:08:19.000 the first thing to note is that i've specified which table the columns are in 90 00:08:19.000 --> 00:08:23.880 when selecting them so track and title are in the song table and you notice how 91 00:08:23.880 --> 00:08:29.490 I use songs . track and songs . title now name comes from the album's table so 92 00:08:29.490 --> 00:08:34.050 specified that as albums . name if there's no ambiguity you can actually 93 00:08:34.050 --> 00:08:38.490 leave off the table name so what I could have done I will just press this to see the 94 00:08:38.490 --> 00:08:46.110 results again so i could have also written this as select track title name from 95 00:08:46.110 --> 00:08:54.460 songs join album on song.albums 96 00:08:54.460 --> 00:09:00.360 ...so I could have done it that way if there's no ambiguity 97 00:09:00.360 --> 00:09:06.430 with the names but it is a good habit to always specify the table 98 00:09:06.430 --> 00:09:11.350 name especially in code now living in it off is a useful shortcut to save 99 00:09:11.350 --> 00:09:16.570 typing when working interactively like this but i'd say always prefix the field 100 00:09:16.570 --> 00:09:21.400 with the table name in your code now some albums have a sort of subtitle so 101 00:09:21.400 --> 00:09:25.330 if the table was modified to include a title column then that query will no 102 00:09:25.330 --> 00:09:29.530 longer work because it would know which table the title column should come from 103 00:09:29.530 --> 00:09:34.780 and note though that we can't leave the table name off when using the ID column 104 00:09:34.780 --> 00:09:39.160 so we just went back hear the end of it instead of putting albums.id if I 105 00:09:39.160 --> 00:09:44.980 just put _ID there and press semicolon press enter we get error no 106 00:09:44.980 --> 00:09:49.900 such song and and sorry no such column song . album now that was 107 00:09:49.900 --> 00:09:53.850 actually different message that was because i accidentally type song their 108 00:09:53.850 --> 00:09:54.950 so just going but I shoudl 109 00:09:54.950 --> 00:09:59.030 be able to copy and paste i might do it that way it save a bit of time so 110 00:09:59.030 --> 00:10:04.190 the original request was songs should be have been songs.album because of course songs is 111 00:10:04.190 --> 00:10:09.620 the name of the table was going to show you as if I just type it like that without 112 00:10:09.620 --> 00:10:16.070 actually putting the albums . before the ID and press enter now we get the error 113 00:10:16.070 --> 00:10:19.730 that i wanted to show you the first time error ambiguous column name 114 00:10:19.730 --> 00:10:24.650 _ID and that's because both tables have a column of that same name _ID 115 00:10:24.650 --> 00:10:27.350 and then sql lite doesn't know which one you mean 116 00:10:27.350 --> 00:10:33.770 so you need to specify it there and just going to copy that again paste it so i 117 00:10:33.770 --> 00:10:38.900 would go back and make that songs and make that album I should say . _ID 118 00:10:38.900 --> 00:10:46.580 and get our data back there are different types of joins the most common being an 119 00:10:46.580 --> 00:10:51.980 inner join and join as I have used here is really a shorthand for inner join what 120 00:10:51.980 --> 00:10:54.980 I'll do is I'll retrieve the full command that included the table names 121 00:10:54.980 --> 00:11:02.990 then used include the word inner so i'm going to type.... 122 00:11:02.990 --> 00:11:12.980 .... 123 00:11:12.980 --> 00:11:23.750 ....now 124 00:11:23.750 --> 00:11:27.260 keep in mind that not all database systems will allow you to leave off the 125 00:11:27.260 --> 00:11:31.850 word inner so it's worth always using it and i'll just run this to make sure it 126 00:11:31.850 --> 00:11:37.880 works now looking at the result of that query we can see that the song just walk 127 00:11:37.880 --> 00:11:43.220 in my shoes is from the album super lungs and permanent vacation is from an 128 00:11:43.220 --> 00:11:49.970 album of the same name and so on so just paste this code back in again so again 129 00:11:49.970 --> 00:11:53.930 this select statement follows the same pattern as we've been using up until now 130 00:11:53.930 --> 00:11:59.630 instead of select from songs we're doing select from songs inner join albums 131 00:11:59.630 --> 00:12:03.890 we then have to tell sql lite which columns are involved in the join which 132 00:12:03.890 --> 00:12:08.720 is what the on part does it says to relate the rows and songs 133 00:12:08.720 --> 00:12:14.870 to those in albums where the songs tables album column equals the album 134 00:12:14.870 --> 00:12:20.510 tables ID column and if you really want to we can actually tact the order by 135 00:12:20.510 --> 00:12:24.860 clause on the end of that if we want to sort the data so i could come to the end 136 00:12:24.860 --> 00:12:35.990 here and I could type..... 137 00:12:35.990 --> 00:12:43.730 .....that's actually 138 00:12:43.730 --> 00:12:47.630 returned a heck of a lot of results as you can see there but it actually 139 00:12:47.630 --> 00:12:52.280 went through and do it really quickly if I wanted to I could just scroll back up 140 00:12:52.280 --> 00:12:56.780 and have a look at some of the other data that has been returned but you can see 141 00:12:56.780 --> 00:13:00.860 there's a lot of data and sql lite has manipulated that and returned it very 142 00:13:00.860 --> 00:13:02.120 quickly 143 00:13:02.120 --> 00:13:05.960 alright so I'm going to finish the video here now we'll continue on working with 144 00:13:05.960 --> 00:13:07.610 sql lite in the next video