1 00:00:00,141 --> 00:00:02,661 (bubby upbeat music) 2 00:00:02,661 --> 00:00:05,530 (keyboard clicking) 3 00:00:05,530 --> 00:00:07,010 Okay, so let's look a bit more at 4 00:00:07,010 --> 00:00:10,320 querying the data, including how we can make sure 5 00:00:10,320 --> 00:00:12,360 that we get the data back in a sensible order 6 00:00:12,360 --> 00:00:15,261 if, as I mentioned, ordering a row is undefined 7 00:00:15,261 --> 00:00:17,970 in a relational data base. 8 00:00:17,970 --> 00:00:19,970 So when we display all the artists' records, 9 00:00:19,970 --> 00:00:22,710 they actually come out in the same order each time. 10 00:00:22,710 --> 00:00:25,480 So I can do that again, select star 11 00:00:25,480 --> 00:00:29,180 from artists, so I get the same, 12 00:00:29,180 --> 00:00:31,900 every time we do it we get the same order. 13 00:00:31,900 --> 00:00:34,400 And that's because we have a primary key. 14 00:00:34,400 --> 00:00:36,900 So the records will automatically be selected 15 00:00:36,900 --> 00:00:39,750 based on the ordering of the primary key. 16 00:00:39,750 --> 00:00:41,870 Now note that the actual order of the records in 17 00:00:41,870 --> 00:00:44,990 the data base is undefined and if we didn't have 18 00:00:44,990 --> 00:00:48,800 a primary key, they'd be coming out in an undefined order. 19 00:00:48,800 --> 00:00:51,480 Now we can actually specify a different order in our 20 00:00:51,480 --> 00:00:54,910 select statement by using an order by clause. 21 00:00:54,910 --> 00:00:59,910 So it can talk select star from artists, order by, name. 22 00:01:03,120 --> 00:01:05,363 Semicolon on the end as always. 23 00:01:06,420 --> 00:01:08,670 And you can see now that the records 24 00:01:08,670 --> 00:01:11,230 have a appeared in alphabetical order. 25 00:01:11,230 --> 00:01:13,780 And I can do exactly the same for the albums, 26 00:01:13,780 --> 00:01:18,567 select star from albums, order by, name. 27 00:01:23,010 --> 00:01:24,440 You notice right down at the bottom, 28 00:01:24,440 --> 00:01:26,727 the two Black Beards Tea Party albums, 29 00:01:26,727 --> 00:01:30,640 "Heavens to Betsy" and "Whip Jamboree" are out of order, 30 00:01:30,640 --> 00:01:33,370 that's because they start with lower case letters. 31 00:01:33,370 --> 00:01:35,700 Now you can actually ignore case by using the 32 00:01:35,700 --> 00:01:37,740 collate no case clause. 33 00:01:37,740 --> 00:01:41,403 So what you can do is select star from albums, 34 00:01:42,980 --> 00:01:47,980 order by name, collate no case. 35 00:01:51,240 --> 00:01:53,891 And you can see there the ID four thirty about 36 00:01:53,891 --> 00:01:56,730 80 percent towards the bottom of the screen, 37 00:01:56,730 --> 00:01:58,340 which is "Whip Jamboree" and that appears 38 00:01:58,340 --> 00:02:01,000 with the other albums beginning with W. 39 00:02:01,000 --> 00:02:02,780 In other words, it's now ignoring case when 40 00:02:02,780 --> 00:02:05,070 it's actually returning results. 41 00:02:05,070 --> 00:02:08,130 Now it also possible to specify ascending or 42 00:02:08,130 --> 00:02:12,380 descending order using the keywords asc or desc, 43 00:02:12,380 --> 00:02:15,030 or A S C or D E S C respectively, which stands 44 00:02:15,030 --> 00:02:17,220 for ascending order, descending order. 45 00:02:17,220 --> 00:02:21,253 So it can do, select star from albums, 46 00:02:22,550 --> 00:02:27,435 order by name, collate no case, now we can talk 47 00:02:27,435 --> 00:02:29,823 D E S C for descending order. 48 00:02:31,516 --> 00:02:33,170 And that's fine, but what if we want to group 49 00:02:33,170 --> 00:02:35,490 albums together so that all the albums 50 00:02:35,490 --> 00:02:38,030 by each artist appear together? 51 00:02:38,030 --> 00:02:39,370 Well the order by clause can actually 52 00:02:39,370 --> 00:02:41,470 contain more than one column. 53 00:02:41,470 --> 00:02:45,673 So if we do something like select star from albums, 54 00:02:48,630 --> 00:02:53,630 order by artist, comma name, collate no case. 55 00:02:58,380 --> 00:03:00,380 And what that does, it sorts first by 56 00:03:00,380 --> 00:03:03,700 artist ID, and then by album name. 57 00:03:03,700 --> 00:03:07,600 So all the Deep Purple albums, artist 196, 58 00:03:07,600 --> 00:03:08,920 and you can see a group of them here with 59 00:03:08,920 --> 00:03:13,000 the number 196 at the end of the list of data, 60 00:03:13,000 --> 00:03:15,000 now appear together near the end of the list. 61 00:03:15,000 --> 00:03:18,483 You can see, so I started with Burn, this one up here, 62 00:03:19,658 --> 00:03:21,990 starting with Burn there, and ending right down 63 00:03:21,990 --> 00:03:24,640 here with Who Do We Think We Are remastered addition. 64 00:03:26,650 --> 00:03:28,563 Okay, time for another mini challenge. 65 00:03:31,680 --> 00:03:34,310 The challenge is to list all the songs so that 66 00:03:34,310 --> 00:03:38,650 songs from the same album appear together in track order. 67 00:03:38,650 --> 00:03:40,010 So that's the challenge, have a go at 68 00:03:40,010 --> 00:03:42,780 doing that by typing in the SQL code 69 00:03:42,780 --> 00:03:44,390 that's necessary to achieve that. 70 00:03:44,390 --> 00:03:45,870 Pause the video and when you're ready 71 00:03:45,870 --> 00:03:48,010 to see me type it in, start the video again. 72 00:03:48,010 --> 00:03:50,613 So pause the video and I'll see you when you get back. 73 00:03:52,730 --> 00:03:54,720 Alright to achieve that, what we need to do 74 00:03:54,720 --> 00:03:56,910 to list all the songs so that songs from the 75 00:03:56,910 --> 00:03:59,470 same album appear together in track order, 76 00:03:59,470 --> 00:04:04,470 we talk select star, from songs, 77 00:04:04,510 --> 00:04:08,893 and order by album, comma track. 78 00:04:09,850 --> 00:04:12,810 Semicolon on the end, and there you go. 79 00:04:12,810 --> 00:04:15,180 So now the 11 songs from the Black Keys album 80 00:04:15,180 --> 00:04:17,990 Attack and Release, appear together as you can see 81 00:04:17,990 --> 00:04:19,670 right at the end of the list. 82 00:04:19,670 --> 00:04:21,260 And you can check that if you wanted to 83 00:04:21,260 --> 00:04:26,087 by typing something like select star from albums, 84 00:04:27,740 --> 00:04:31,223 where underscore ID equals four three nine, 85 00:04:33,130 --> 00:04:36,120 and then we can do something like, select star 86 00:04:36,120 --> 00:04:41,120 from artists, where underscore ID equals one three three. 87 00:04:45,076 --> 00:04:46,577 And there you go. 88 00:04:46,577 --> 00:04:48,250 You can see with a quick scan up the list shows 89 00:04:48,250 --> 00:04:50,209 the records are grouped by album ID, the last column 90 00:04:50,209 --> 00:04:53,713 and in track order within an album, the second column. 91 00:04:54,690 --> 00:04:56,730 Now having to run separate queries like that 92 00:04:56,730 --> 00:04:58,889 is a bit grubby though, so let's see how to 93 00:04:58,889 --> 00:05:01,830 relate the tables together so that we can 94 00:05:01,830 --> 00:05:03,760 get a list of songs that include the album 95 00:05:03,760 --> 00:05:06,633 they appear on, as well as the artist that produced them. 96 00:05:09,660 --> 00:05:13,010 Now to do this, we need to use the S Q L JOIN clause, 97 00:05:13,010 --> 00:05:15,710 that's used to join tables together. 98 00:05:15,710 --> 00:05:18,450 Now, keeping data normalised so that tables 99 00:05:18,450 --> 00:05:20,480 only contain information that relates to 100 00:05:20,480 --> 00:05:23,120 a single theme, song, album, or artist 101 00:05:23,120 --> 00:05:26,160 in our example, is a fundamental part of relational 102 00:05:26,160 --> 00:05:29,380 data bases, and by doing that and then joining 103 00:05:29,380 --> 00:05:31,770 the tables back together, you get a great deal 104 00:05:31,770 --> 00:05:34,140 of flexibility in how you can query and 105 00:05:34,140 --> 00:05:36,290 manipulate the data. 106 00:05:36,290 --> 00:05:38,360 Now remember that the songs table contains 107 00:05:38,360 --> 00:05:41,260 a column holding the album ID, and the album 108 00:05:41,260 --> 00:05:43,740 table has an artist ID field. 109 00:05:43,740 --> 00:05:46,743 And these are used to provide a link between the tables. 110 00:05:47,760 --> 00:05:49,780 Now don't worry about how those IDs got into 111 00:05:49,780 --> 00:05:52,130 the tables at this stage, we're just interested 112 00:05:52,130 --> 00:05:54,623 in using them to join the tables at the moment. 113 00:05:58,110 --> 00:05:59,870 So you can see here on screen how the 114 00:05:59,870 --> 00:06:02,172 album column in the song's table provides 115 00:06:02,172 --> 00:06:05,270 the link to the album table. 116 00:06:05,270 --> 00:06:07,440 The first ten songs all belong to the album 117 00:06:07,440 --> 00:06:09,132 who's ID is one, Tales of the Crown, 118 00:06:09,132 --> 00:06:12,090 and the next set of songs belong to 119 00:06:12,090 --> 00:06:13,573 The Masquerade Ball. 120 00:06:16,620 --> 00:06:19,730 The artist column in albums links to the artist 121 00:06:19,730 --> 00:06:23,833 table, so those first two albums are by Axel Rudi Pell, 122 00:06:25,722 --> 00:06:27,600 and the album Crimes of Passion is by Pat Benatar 123 00:06:27,600 --> 00:06:30,263 and Nightflight is by a band called Budgie. 124 00:06:32,490 --> 00:06:34,370 Alright, so with that said, let's actually 125 00:06:34,370 --> 00:06:38,880 join the tables in SQL and see how this is going to look. 126 00:06:38,880 --> 00:06:40,220 So what I'm going to do, I'm actually just going 127 00:06:40,220 --> 00:06:43,610 to do a dot quit, and then I'm just going to 128 00:06:43,610 --> 00:06:47,030 clear the screen and notice how the up arrow 129 00:06:47,030 --> 00:06:48,980 is working for me hear, it's just in SQL 130 00:06:48,980 --> 00:06:51,240 light three for some reason, it's not working, 131 00:06:51,240 --> 00:06:53,130 there's weird characters. 132 00:06:53,130 --> 00:06:56,310 But I've gone back into the data base again, 133 00:06:56,310 --> 00:06:58,710 and just sort of starting off with a clear slate. 134 00:06:58,710 --> 00:07:01,760 So let's now use the select statement and add 135 00:07:01,760 --> 00:07:04,890 a JOIN clause to link the songs and albums. 136 00:07:04,890 --> 00:07:07,830 So what I'll do is, talk select, space, 137 00:07:07,830 --> 00:07:12,830 songs dot track, comma, songs dot title, 138 00:07:13,290 --> 00:07:17,883 comma, albums dot name from songs, 139 00:07:19,160 --> 00:07:23,850 and here's the JOIN clause albums on songs 140 00:07:27,160 --> 00:07:32,160 dot album, equal albums dot underscore ID. 141 00:07:33,770 --> 00:07:34,670 Press enter there. 142 00:07:35,840 --> 00:07:37,300 So the first thing to notice that I've 143 00:07:37,300 --> 00:07:39,170 specified which table the columns 144 00:07:39,170 --> 00:07:41,620 are in when selecting them. 145 00:07:41,620 --> 00:07:42,880 And probably what I should have done is 146 00:07:42,880 --> 00:07:45,130 explained that while that select stamp 147 00:07:45,130 --> 00:07:46,720 was on screen because of course now I can't 148 00:07:46,720 --> 00:07:49,243 bring it back, or can I, I can scroll up. 149 00:07:53,440 --> 00:07:55,650 So what I'll do, is I'll just type it in again. 150 00:07:55,650 --> 00:07:57,380 And again, you shouldn't have this scenario, 151 00:07:57,380 --> 00:07:58,660 you should be able to go up arrow and it should 152 00:07:58,660 --> 00:08:02,160 work, but for some reason my MAC is not doing 153 00:08:02,160 --> 00:08:02,993 what I want it to do. 154 00:08:02,993 --> 00:08:07,870 So albums, dot name, from songs, JOIN albums on 155 00:08:14,110 --> 00:08:19,110 songs dot album, equals albums dot underscore ID. 156 00:08:19,860 --> 00:08:21,602 Alright, so I'll leave that on before 157 00:08:21,602 --> 00:08:22,802 I press enter this time. 158 00:08:24,230 --> 00:08:26,080 So getting back to that statement, the first 159 00:08:26,080 --> 00:08:27,770 thing to notice is that I've specified which 160 00:08:27,770 --> 00:08:30,700 table the columns are in when selecting them. 161 00:08:30,700 --> 00:08:33,390 So track and title are in the songs table, 162 00:08:33,390 --> 00:08:35,240 and you notice how I use songs dot track, 163 00:08:35,240 --> 00:08:37,169 and songs dot title. 164 00:08:37,169 --> 00:08:39,500 Now name comes from the albums table, so I've 165 00:08:39,500 --> 00:08:42,179 specified that as albums dot name. 166 00:08:42,179 --> 00:08:44,169 Now if there's no ambiguity, you can actually 167 00:08:44,169 --> 00:08:46,640 leave off the table name, so what I could have 168 00:08:46,640 --> 00:08:49,460 done, I'll just press this to see the results again. 169 00:08:49,460 --> 00:08:51,450 So I could have also written this as select 170 00:08:51,450 --> 00:08:56,450 track title name from songs, JOIN albums on someday 171 00:09:02,332 --> 00:09:05,670 albums, someday album I should say, 172 00:09:05,670 --> 00:09:09,110 equals albums dot underscore ID. 173 00:09:09,110 --> 00:09:10,250 So I could have done it that way if there's no 174 00:09:10,250 --> 00:09:14,710 ambiguity with the names, but it is a good habit 175 00:09:14,710 --> 00:09:18,950 to always specify the table name, especially in code. 176 00:09:18,950 --> 00:09:21,110 The leaving it off is a useful short cut to 177 00:09:21,110 --> 00:09:24,360 save typing when working interactively like this, 178 00:09:24,360 --> 00:09:26,780 but I'd say always prefix the fields with 179 00:09:26,780 --> 00:09:29,250 a table name in your code. 180 00:09:29,250 --> 00:09:31,210 Now some albums have a sort of subtitle, 181 00:09:31,210 --> 00:09:33,680 so if the table is modified to include a title 182 00:09:33,680 --> 00:09:36,460 column, then that query would no longer work 183 00:09:36,460 --> 00:09:38,180 because it wouldn't know which table the 184 00:09:38,180 --> 00:09:39,743 title column should come from. 185 00:09:41,010 --> 00:09:43,200 And note though, we can't leave the table 186 00:09:43,200 --> 00:09:45,360 name off when using the ID column. 187 00:09:45,360 --> 00:09:47,690 So we just went back here at the end of it, 188 00:09:47,690 --> 00:09:49,840 instead of putting albums then ID, if I just put 189 00:09:49,840 --> 00:09:52,562 underscore ID there, semicolon, and then press 190 00:09:52,562 --> 00:09:57,562 enter, we get error, no such column, song dot album. 191 00:09:59,510 --> 00:10:00,700 Now that was actually a different message, 192 00:10:00,700 --> 00:10:03,487 that was because I accidentally typed song in there 193 00:10:03,487 --> 00:10:05,473 so I'm going to, should be able to copy and paste, 194 00:10:05,473 --> 00:10:08,780 I'll do it that way, that might save a bit of time. 195 00:10:08,780 --> 00:10:12,390 So, the original request was songs, should have been 196 00:10:12,390 --> 00:10:13,710 songs, not album, because of course 197 00:10:13,710 --> 00:10:15,573 songs is the name of the table. 198 00:10:16,820 --> 00:10:18,480 What I was going to show you was if I just 199 00:10:18,480 --> 00:10:20,570 type like that without actually putting the 200 00:10:20,570 --> 00:10:25,270 albums dot before the ID, and press enter, 201 00:10:25,270 --> 00:10:27,690 now we get the error that I wanted to show the first time. 202 00:10:27,690 --> 00:10:31,111 Error, ambiguous column name, underscore ID. 203 00:10:31,111 --> 00:10:33,230 And that's because both tables have a column 204 00:10:33,230 --> 00:10:36,020 of that same name underscore ID, and SQL light 205 00:10:36,020 --> 00:10:37,430 doesn't know which one you mean. 206 00:10:37,430 --> 00:10:39,143 So you need to specify it there and I'm 207 00:10:39,143 --> 00:10:40,763 just going to copy that again. 208 00:10:42,810 --> 00:10:46,500 Paste it, so I'll go back and make that songs 209 00:10:46,500 --> 00:10:49,343 and make that albums I should say, dot underscore ID. 210 00:10:50,820 --> 00:10:52,070 And we get our data back. 211 00:10:53,450 --> 00:10:55,430 Now there are different types of JOINs, 212 00:10:55,430 --> 00:10:58,030 the most common being an inner JOIN and 213 00:10:58,030 --> 00:11:00,060 JOINers I've used here is really a short hand 214 00:11:00,060 --> 00:11:01,800 I've used for inner JOIN. 215 00:11:01,800 --> 00:11:02,920 What I will do is I will retrieve the 216 00:11:02,920 --> 00:11:04,990 full command that included the table names, 217 00:11:04,990 --> 00:11:07,350 then use, then include the word inner. 218 00:11:07,350 --> 00:11:12,350 So I'm going to type select songs dot track, 219 00:11:12,560 --> 00:11:17,560 songs dot title, albums dot name, from songs, 220 00:11:19,540 --> 00:11:22,670 inner JOIN, inner space JOIN that should be, 221 00:11:22,670 --> 00:11:27,670 on, sorry inner JOIN space albums on songs dot 222 00:11:28,360 --> 00:11:32,423 album, equals albums dot underscore ID. 223 00:11:33,530 --> 00:11:35,560 Now keep in mind that not all data base 224 00:11:35,560 --> 00:11:38,190 systems will allow you to leave off the work inner, 225 00:11:38,190 --> 00:11:39,710 so it's worth always using it. 226 00:11:39,710 --> 00:11:42,493 And I'll just run this to make sure it works. 227 00:11:43,600 --> 00:11:46,400 Now looking at the result of that query, 228 00:11:46,400 --> 00:11:48,930 we can see that the song "Just Walk in My Shoes", 229 00:11:48,930 --> 00:11:50,957 is from the album Super Lungs and 230 00:11:50,957 --> 00:11:53,810 "Permanent Vacation", is from an album of the 231 00:11:54,779 --> 00:11:56,490 same name and so on. 232 00:11:56,490 --> 00:11:58,690 So we'll just paste this code back in again. 233 00:11:59,540 --> 00:12:01,410 So again, this select statement follows the 234 00:12:01,410 --> 00:12:04,640 same pattern as we've been using up until now. 235 00:12:04,640 --> 00:12:06,670 Instead of select from songs, we're doing 236 00:12:06,670 --> 00:12:09,270 select from songs inner JOINed albums. 237 00:12:09,270 --> 00:12:12,300 We then have to tell SQL light which columns 238 00:12:12,300 --> 00:12:13,710 are involved in the JOIN, 239 00:12:13,710 --> 00:12:16,031 which is what the on part does. 240 00:12:16,031 --> 00:12:19,150 It says to relate the rows in songs 241 00:12:19,150 --> 00:12:22,250 to those in albums where the song's table's 242 00:12:22,250 --> 00:12:26,693 album column equals the album table's ID column. 243 00:12:27,540 --> 00:12:29,530 And if you really wanted to, we can actually 244 00:12:29,530 --> 00:12:31,860 tack an order by clause on the end of that 245 00:12:31,860 --> 00:12:33,910 if you want to sort the data. 246 00:12:33,910 --> 00:12:35,460 So if you come to the end here, 247 00:12:37,010 --> 00:12:40,453 and I could then type order by, 248 00:12:42,530 --> 00:12:46,880 and name of the table, albums dot and then the 249 00:12:46,880 --> 00:12:51,003 column we want, name then songs dot track, semicolon. 250 00:12:53,180 --> 00:12:55,108 That's actually returned a heck of a lot of 251 00:12:55,108 --> 00:12:57,480 results as you can see there, but it 252 00:12:57,480 --> 00:12:59,533 actually went through and did it really quickly. 253 00:13:00,750 --> 00:13:03,123 But if I wanted to, I could just scroll back up, 254 00:13:04,260 --> 00:13:05,720 and have a look at some of the other 255 00:13:05,720 --> 00:13:06,553 data that's been returned. 256 00:13:06,553 --> 00:13:07,386 But you can see that there's a lot of data and 257 00:13:07,386 --> 00:13:09,800 SQL light has manipulated that and 258 00:13:09,800 --> 00:13:12,090 returned it very quickly. 259 00:13:12,090 --> 00:13:13,370 Alright, so I'm going to finish the video 260 00:13:13,370 --> 00:13:15,890 here now, we'll continue on working 261 00:13:15,890 --> 00:13:17,813 with SQL light in the next video.