1 00:00:00,720 --> 00:00:03,460 In this demo, we'll be learning some more features of the SQL language. 2 00:00:04,300 --> 00:00:05,500 Specifically, we'll be learning about 3 00:00:05,850 --> 00:00:07,770 table variables and about set operators. 4 00:00:09,910 --> 00:00:11,380 We already learned the basic select statement. 5 00:00:12,020 --> 00:00:13,150 which can be quite powerful for 6 00:00:13,250 --> 00:00:14,380 writing queries but we'll learn 7 00:00:14,640 --> 00:00:15,940 some constructs in these demos 8 00:00:16,400 --> 00:00:17,900 that will give us even more expressive power. 9 00:00:18,690 --> 00:00:20,390 The first construct is table variables. 10 00:00:21,580 --> 00:00:24,220 Table variables are in the FROM clause, and they actually serve two uses. 11 00:00:24,790 --> 00:00:27,540 One is simply to make queries more readable, as we'll see. 12 00:00:28,160 --> 00:00:29,610 But a second purpose is to 13 00:00:29,780 --> 00:00:32,290 rename relations that are used in the FROM clause, 14 00:00:32,960 --> 00:00:35,400 particularly when we have two instances of the same relation. 15 00:00:35,990 --> 00:00:37,380 This is exactly what we 16 00:00:37,530 --> 00:00:39,280 needed in the relational algebra when 17 00:00:39,450 --> 00:00:40,420 we wrote joins that included 18 00:00:40,870 --> 00:00:41,990 two instances of the same relation. 19 00:00:43,090 --> 00:00:44,360 The second construct we'll be 20 00:00:44,490 --> 00:00:45,350 learning, actually a set of constructs, 21 00:00:46,350 --> 00:00:47,550 in this video, are the set operators. 22 00:00:48,640 --> 00:00:49,460 And we'll be learning the same 23 00:00:49,650 --> 00:00:50,700 three set operators we had 24 00:00:50,900 --> 00:00:52,090 in relational algebra: the union 25 00:00:52,390 --> 00:00:53,820 operator, the intersect operator, 26 00:00:54,690 --> 00:00:57,270 and the except operator which is the minus operator. 27 00:00:58,550 --> 00:00:59,830 We'll be doing a demo and 28 00:01:00,050 --> 00:01:00,860 the demo will use the 29 00:01:01,050 --> 00:01:02,650 same college admissions database that 30 00:01:02,760 --> 00:01:04,240 we've been using in previous demos where 31 00:01:04,360 --> 00:01:05,620 we have tables about college 32 00:01:05,950 --> 00:01:09,290 information, student information and students applying to colleges. 33 00:01:10,010 --> 00:01:10,840 Let's move to the demo. 34 00:01:11,990 --> 00:01:12,910 Let's start with a big 35 00:01:13,120 --> 00:01:15,100 join query that we'll use to introduce table variables. 36 00:01:16,110 --> 00:01:17,400 This query involves all three relations. 37 00:01:18,180 --> 00:01:19,480 It joins the three relations on 38 00:01:19,590 --> 00:01:21,860 their shared attributes and then it selects a bunch of information. 39 00:01:23,120 --> 00:01:24,640 So here we see the result of that query. 40 00:01:25,570 --> 00:01:26,400 So the main point of this 41 00:01:26,490 --> 00:01:27,630 query is not the result but 42 00:01:27,780 --> 00:01:30,360 just to show you how table variables are used in the FROM clause. 43 00:01:31,130 --> 00:01:34,080 We can add two, each of our relation names, a variable. 44 00:01:34,580 --> 00:01:36,120 We'll use S for student 45 00:01:36,270 --> 00:01:37,870 and C for college and A for apply. 46 00:01:38,890 --> 00:01:40,000 And then everywhere else in the 47 00:01:40,100 --> 00:01:41,400 query, instead of writing the 48 00:01:41,610 --> 00:01:43,890 full relation name we can just use the variable. 49 00:01:44,960 --> 00:01:46,150 In this case we're not changing the 50 00:01:46,580 --> 00:01:47,710 expressiveness, we're not changing the 51 00:01:47,890 --> 00:01:48,890 outcome of the query, we're really 52 00:01:49,210 --> 00:01:50,200 just making it a bit 53 00:01:50,330 --> 00:01:51,730 more readable and we can do 54 00:01:51,830 --> 00:01:53,620 the same thing here in this left clause. 55 00:01:54,170 --> 00:01:56,990 We'll take S and A and so on. 56 00:01:57,980 --> 00:02:00,590 Then we'll run the query and we'll get exactly the same result, no change. 57 00:02:01,210 --> 00:02:02,420 Now let's look at 58 00:02:02,480 --> 00:02:04,410 where table variables are actually useful. 59 00:02:04,930 --> 00:02:05,910 What we want to get 60 00:02:06,030 --> 00:02:07,310 in this query is all pairs 61 00:02:07,690 --> 00:02:08,670 of students who have the same 62 00:02:09,080 --> 00:02:10,540 GPA. This is kind of 63 00:02:10,600 --> 00:02:11,760 similar to the relational algebra 64 00:02:12,170 --> 00:02:13,000 query we did where we found 65 00:02:13,350 --> 00:02:15,250 all pairs of colleges that are in the same state. 66 00:02:16,040 --> 00:02:16,990 In order to do that we 67 00:02:17,100 --> 00:02:19,350 need to have two instances of the student relation. 68 00:02:20,160 --> 00:02:23,140 So we'll call one instance, S1, and we'll call the other instance S2. 69 00:02:23,270 --> 00:02:24,950 And the FROM will do the 70 00:02:25,040 --> 00:02:26,280 cross-product of those two, 71 00:02:26,470 --> 00:02:27,450 so it will consider every 72 00:02:27,470 --> 00:02:30,400 every possible pair of students from the student relation. 73 00:02:30,910 --> 00:02:32,360 From all those pairs, 74 00:02:32,800 --> 00:02:33,920 we'll take the pairs where the 75 00:02:34,010 --> 00:02:34,780 student had the same GPA 76 00:02:35,580 --> 00:02:36,620 and will return the ID, 77 00:02:36,670 --> 00:02:38,820 name, and GPA for each of the two students. 78 00:02:39,220 --> 00:02:40,300 So let's go ahead and 79 00:02:40,400 --> 00:02:43,400 execute the query; and here we can see the result. 80 00:02:44,530 --> 00:02:46,960 Now, this result is exactly what we wrote. 81 00:02:47,260 --> 00:02:48,660 It literally is every pair 82 00:02:49,100 --> 00:02:49,830 of students that have the same 83 00:02:50,080 --> 00:02:51,780 GPA, but it might not be what we intended. 84 00:02:53,700 --> 00:02:55,130 Amy and Amy, the same student. 85 00:02:55,620 --> 00:02:56,660 Well Amy has the same GPA 86 00:02:57,220 --> 00:02:58,620 as herself, but more likely 87 00:02:59,150 --> 00:03:01,610 we just wanted different students who had the same GPA. 88 00:03:02,460 --> 00:03:03,350 So to do that we'll add 89 00:03:03,630 --> 00:03:05,990 an and that says these are two different students. 90 00:03:07,140 --> 00:03:08,310 The SIDs of the students are different. 91 00:03:12,410 --> 00:03:13,880 Now, let's run the query and see what happens. 92 00:03:15,660 --> 00:03:16,610 Now, we see that we no 93 00:03:16,840 --> 00:03:18,000 longer have Amy and Amy, 94 00:03:18,480 --> 00:03:20,390 and every student is paired with a different student. 95 00:03:20,720 --> 00:03:21,790 We do have two Amy's here, 96 00:03:21,960 --> 00:03:23,400 but don't be alarmed, this Amy 97 00:03:23,740 --> 00:03:25,640 is 123 and this Amy is 654. 98 00:03:25,950 --> 00:03:27,480 So things are looking 99 00:03:27,800 --> 00:03:29,720 quite a bit better, but there's 100 00:03:29,870 --> 00:03:30,860 still one thing that we 101 00:03:31,020 --> 00:03:32,020 might not want in the result 102 00:03:32,200 --> 00:03:33,350 of the query which is that 103 00:03:33,450 --> 00:03:34,550 we have Amy paired with 104 00:03:34,700 --> 00:03:35,850 Doris and then we 105 00:03:36,000 --> 00:03:37,410 have Doris paired with Amy. 106 00:03:37,920 --> 00:03:39,730 So we're actually getting every pair 107 00:03:39,850 --> 00:03:41,660 of students twice in the two different orders. 108 00:03:42,030 --> 00:03:44,230 As it turns out, that's very easy to fix. 109 00:03:44,570 --> 00:03:47,220 We only need to erase one character to make that work. 110 00:03:48,240 --> 00:03:49,510 Maybe you can think about what that character is. 111 00:03:50,830 --> 00:03:51,090 Here it is. 112 00:03:51,960 --> 00:03:54,760 Instead of looking at not equals, we'll just make it less than. 113 00:03:55,420 --> 00:03:56,430 And then we'll get every pair of 114 00:03:56,670 --> 00:03:58,060 students only once because we'll 115 00:03:58,200 --> 00:03:59,270 always be listing the one with 116 00:03:59,410 --> 00:04:01,210 the smaller SID first and 117 00:04:01,320 --> 00:04:02,820 finally we get the answer 118 00:04:03,090 --> 00:04:04,930 that we probably intended in the first place. 119 00:04:06,140 --> 00:04:08,890 Now let's take a look at the set operators and we'll start with union. 120 00:04:09,830 --> 00:04:11,330 Just like in our relational algebra video 121 00:04:11,820 --> 00:04:13,230 let's use the union operator to 122 00:04:13,520 --> 00:04:14,720 generate a list that includes names 123 00:04:15,070 --> 00:04:16,890 of colleges together with names of students. 124 00:04:17,710 --> 00:04:18,660 So here's the query that will 125 00:04:18,820 --> 00:04:19,860 do it for us and we 126 00:04:19,940 --> 00:04:22,500 go ahead and execute the query and we see our result. 127 00:04:23,800 --> 00:04:25,180 Now I left the 128 00:04:25,270 --> 00:04:26,440 schema as having the 129 00:04:26,670 --> 00:04:27,540 C name in the first part 130 00:04:27,790 --> 00:04:29,230 of the union and the S name in the second. 131 00:04:29,870 --> 00:04:30,700 SQL allowed me to do 132 00:04:30,840 --> 00:04:33,050 that and it chose to use the C name to label the result. 133 00:04:34,020 --> 00:04:35,580 If I want to unify the 134 00:04:36,140 --> 00:04:36,980 schemas of the two sides 135 00:04:37,300 --> 00:04:38,410 of the union and give a 136 00:04:38,460 --> 00:04:40,380 new label for the result, I 137 00:04:40,720 --> 00:04:41,870 use the "as" as we saw earlier for 138 00:04:42,000 --> 00:04:43,760 re-naming attributes in the result of queries. 139 00:04:44,380 --> 00:04:45,760 So I'll add "as name" to 140 00:04:45,950 --> 00:04:46,630 both sides of the union, 141 00:04:47,420 --> 00:04:49,920 run the query, and now I see name in the result. 142 00:04:51,050 --> 00:04:52,140 Now one thing you might have 143 00:04:52,410 --> 00:04:54,400 noticed is that this result is actually sorted. 144 00:04:55,050 --> 00:04:56,030 We didn't ask for it 145 00:04:56,200 --> 00:04:57,200 to be sorted, but for some 146 00:04:57,350 --> 00:04:58,920 reason the system sorted it for us. 147 00:04:59,400 --> 00:05:00,970 And I can actually explain why that happened. 148 00:05:01,810 --> 00:05:03,040 I'll also mention that if I 149 00:05:03,170 --> 00:05:05,760 ran this same query on another system, it might not come out sorted. 150 00:05:06,090 --> 00:05:08,270 In fact, it will not come out sorted because I tried it. 151 00:05:09,100 --> 00:05:09,440 Here's the deal. 152 00:05:10,200 --> 00:05:11,900 The union operator in SQL 153 00:05:12,410 --> 00:05:15,150 by default eliminates duplicates in its results. 154 00:05:15,700 --> 00:05:16,690 So if we have two Amy's, 155 00:05:17,230 --> 00:05:19,780 which in fact we do, we only get one Amy in our result. 156 00:05:20,230 --> 00:05:22,880 And similarly, for Craig, we have two of those as well. 157 00:05:23,910 --> 00:05:24,860 So that's the default, and 158 00:05:25,070 --> 00:05:26,150 it so happens the system I'm 159 00:05:26,300 --> 00:05:27,340 using today, which is called 160 00:05:27,650 --> 00:05:31,000 SQL Lite, eliminates duplicates gets by sorting the result. 161 00:05:31,520 --> 00:05:32,890 So, it sorts the result, looks for 162 00:05:33,250 --> 00:05:34,350 adjacent pairs that are the 163 00:05:34,430 --> 00:05:35,650 same and eliminates all but 164 00:05:35,800 --> 00:05:37,340 one of those, and then it gives us the answer. 165 00:05:37,810 --> 00:05:39,260 But again, I want to 166 00:05:39,590 --> 00:05:40,840 emphasize that's not something one 167 00:05:40,940 --> 00:05:42,220 can count on when one runs 168 00:05:42,470 --> 00:05:43,500 the same query on a different 169 00:05:43,820 --> 00:05:46,220 system or even on the same system on a different day. 170 00:05:47,400 --> 00:05:48,390 Now, if we want 171 00:05:48,690 --> 00:05:49,880 to have the duplicates in our 172 00:05:50,020 --> 00:05:52,240 result, that's something we can do quite easily. 173 00:05:52,670 --> 00:05:53,970 We add to union the 174 00:05:54,040 --> 00:05:55,420 word all that will turn 175 00:05:55,880 --> 00:05:57,260 the set operator into what's 176 00:05:57,600 --> 00:06:00,160 technically a multi-set operator that retains duplicates. 177 00:06:01,130 --> 00:06:01,810 We run the query. 178 00:06:02,060 --> 00:06:04,340 Well, the first thing we notice is it's not sorted anymore. 179 00:06:05,130 --> 00:06:06,800 That's because it didn't need to eliminate the duplicates. 180 00:06:07,730 --> 00:06:10,590 But if we look closely, we'll also see that the duplicates are now there. 181 00:06:10,800 --> 00:06:12,060 We have two Amys, for example, 182 00:06:12,590 --> 00:06:13,980 and we have two Craigs as well. 183 00:06:15,520 --> 00:06:16,770 If we want this result to 184 00:06:16,860 --> 00:06:18,070 be sorted and to guarantee 185 00:06:18,350 --> 00:06:20,740 that the other one's sorted, we would add an order by clause. 186 00:06:21,380 --> 00:06:22,650 So we can just say order by name. 187 00:06:23,890 --> 00:06:25,350 We run the query and now 188 00:06:25,600 --> 00:06:27,100 we have the result in sorted order. 189 00:06:28,510 --> 00:06:30,630 Our next query demonstrates the intersect operator. 190 00:06:31,610 --> 00:06:32,550 This query is going to 191 00:06:32,630 --> 00:06:33,660 get the IDs of all 192 00:06:34,160 --> 00:06:35,210 students who have applied to both 193 00:06:35,650 --> 00:06:37,790 CS for a major and EE for a major. 194 00:06:38,130 --> 00:06:38,890 So, very simple query. 195 00:06:39,530 --> 00:06:40,350 We get the IDs of students 196 00:06:40,820 --> 00:06:42,270 who applied to CS, the 197 00:06:42,340 --> 00:06:43,740 IDs of students who applied to EE, 198 00:06:44,370 --> 00:06:45,900 and then we perform the intersect operator 199 00:06:46,370 --> 00:06:47,450 on the result of those two queries. 200 00:06:48,370 --> 00:06:49,460 We execute it, and we find that there are indeed two students who applied to CS and EE. Some database systems don't support the intersect operator. They don't lose any expressive power. We just have to write our queries in different ways. So, this next query is computing exactly the same thing. The sIDs of students who have applied to both CS and EE, but this time we're doing it by doing two instances of the apply relation. One of these self joins, so we have to use table variables again, so we take every pair of apply tuples, we look at cases where it's the same student, and in one case, they're applying for CS, in the other case they're applying for EE, and we'll return the sID of those students. So we run the query and we get sort of the same answer, but not exactly, because we have a whole bunch of duplicates now that we didn't get when we did it with an intersect operator. Now, where did those duplicates come from? Let's take a look at the apply relation itself. Here, we see that student 123 applied to 201 00:06:49,740 --> 00:06:51,020 that there are indeed two students 202 00:06:51,580 --> 00:06:53,370 who applied to CS and EE. 203 00:06:55,530 --> 00:06:58,030 Some database systems don't support the intersect operator. 204 00:06:58,550 --> 00:07:00,300 They don't lose any expressive power. 205 00:07:00,620 --> 00:07:02,300 We just have to write our queries in different ways. 206 00:07:03,340 --> 00:07:04,400 So this next query is computing 207 00:07:04,780 --> 00:07:06,030 exactly the same thing, the 208 00:07:06,270 --> 00:07:07,400 SIDs of students who have 209 00:07:07,500 --> 00:07:08,640 applied to both CS and 210 00:07:08,890 --> 00:07:09,990 EE, but this time 211 00:07:10,130 --> 00:07:11,580 we're doing it by doing two 212 00:07:11,840 --> 00:07:13,190 instances of the apply relation, 213 00:07:14,060 --> 00:07:16,770 one of these self joins, so we have to use table variables again. 214 00:07:17,600 --> 00:07:18,660 So we take every pair 215 00:07:19,060 --> 00:07:20,460 of apply tuples, we look 216 00:07:20,650 --> 00:07:21,550 at cases where it's the same 217 00:07:21,940 --> 00:07:24,280 student, and in one case, they're applying for CS. 218 00:07:24,870 --> 00:07:26,110 In the other case, they're applying for 219 00:07:26,320 --> 00:07:28,630 EE, and we'll return the SID of those students. 220 00:07:29,650 --> 00:07:30,960 So we run the query and we 221 00:07:31,360 --> 00:07:32,660 get sort of the same answer, 222 00:07:32,970 --> 00:07:34,310 but not exactly because we have 223 00:07:34,420 --> 00:07:35,640 a whole bunch of duplicates now 224 00:07:35,850 --> 00:07:37,920 that we didn't get when we did it with an intersect operator. 225 00:07:39,130 --> 00:07:41,080 Now where did those duplicates come from? 226 00:07:41,910 --> 00:07:42,900 Let's take a look at the 227 00:07:42,990 --> 00:07:46,490 apply relation itself. 228 00:07:47,380 --> 00:07:48,100 Here we see that student 229 00:07:48,190 --> 00:07:49,390 123 applied to CS and to 230 00:07:49,560 --> 00:07:51,920 EE and to CS again and to EE again. 231 00:07:52,190 --> 00:07:53,250 And we're gonna get all 232 00:07:53,530 --> 00:07:55,540 pairs of 123 tuples, 233 00:07:56,440 --> 00:07:57,740 where one pair pair of 234 00:07:57,830 --> 00:07:59,350 the tuples is CS and the other is EE. 235 00:08:00,010 --> 00:08:02,810 So, we'll get CS with EE, CS with EE and so on. 236 00:08:04,120 --> 00:08:05,250 Going back to our query result. 237 00:08:05,710 --> 00:08:06,990 Here it is. 238 00:08:07,640 --> 00:08:10,600 We can see that we got the four 123's when we ran the query. 239 00:08:11,390 --> 00:08:12,400 Well, that's easy to get rid of. 240 00:08:12,570 --> 00:08:14,550 We just write select distinct and 241 00:08:14,690 --> 00:08:15,840 that will get rid of duplicates and 242 00:08:16,170 --> 00:08:17,860 now we're back to our original query result. 243 00:08:19,290 --> 00:08:20,290 Now instead of finding students 244 00:08:20,660 --> 00:08:21,850 who applied to both CS and 245 00:08:22,280 --> 00:08:23,520 EE, let's find students 246 00:08:23,880 --> 00:08:25,870 who applied to CS but did not apply to EE. 247 00:08:26,290 --> 00:08:27,890 For that we need the difference operator. 248 00:08:28,430 --> 00:08:29,870 It's called difference in relational Algebra. 249 00:08:30,300 --> 00:08:31,130 Sometimes, it's called minus. 250 00:08:31,870 --> 00:08:32,790 The word that's used in the 251 00:08:32,980 --> 00:08:33,970 SQL standard is the word except. 252 00:08:34,920 --> 00:08:35,730 So here's our query. 253 00:08:35,990 --> 00:08:37,150 We find the student IDs who 254 00:08:37,240 --> 00:08:38,340 applied to CS and then 255 00:08:38,390 --> 00:08:39,770 we take away from those the 256 00:08:40,020 --> 00:08:41,530 IDs of students who applied to EE. 257 00:08:42,330 --> 00:08:43,410 We run the query and we 258 00:08:43,670 --> 00:08:44,600 find that there are three students 259 00:08:44,830 --> 00:08:46,600 who applied to CS and not to EE. 260 00:08:48,270 --> 00:08:49,530 Some database systems don't support 261 00:08:50,020 --> 00:08:51,560 the except operator either and 262 00:08:51,930 --> 00:08:53,280 here, things get a little tricky. 263 00:08:53,720 --> 00:08:54,690 So, let's try to rewrite 264 00:08:55,330 --> 00:08:57,510 that query without using the except operator. 265 00:08:58,540 --> 00:08:59,680 So as a reminder, we want to 266 00:08:59,860 --> 00:09:01,530 find students who applied to 267 00:09:01,670 --> 00:09:03,070 CS but did not apply to EE. 268 00:09:03,710 --> 00:09:05,250 So, here's my attempt at writing that query. 269 00:09:05,760 --> 00:09:06,750 I again do a self join 270 00:09:07,560 --> 00:09:09,540 of apply with apply, and I 271 00:09:09,640 --> 00:09:11,380 find all pairs where it's 272 00:09:11,510 --> 00:09:12,600 the same student we're talking about 273 00:09:13,430 --> 00:09:14,800 and the major in one 274 00:09:14,960 --> 00:09:17,640 of the tuples of CS and the major in the other one is not EE. 275 00:09:17,800 --> 00:09:19,310 Well, it looks pretty good. 276 00:09:19,510 --> 00:09:19,990 Let's see what happens. 277 00:09:21,030 --> 00:09:22,110 Whoa, we got a lot of results. 278 00:09:22,860 --> 00:09:24,100 Okay, well that's probably just that 279 00:09:24,450 --> 00:09:25,900 problem with duplicates again, so 280 00:09:26,100 --> 00:09:29,320 let's just add distinct and go for it. 281 00:09:31,120 --> 00:09:32,620 It still seems like a lot of results. 282 00:09:33,520 --> 00:09:34,330 Let's go back to our previous 283 00:09:34,740 --> 00:09:35,920 query that uses except and 284 00:09:36,090 --> 00:09:37,870 then we found that there were three students in the result 285 00:09:38,810 --> 00:09:41,300 where here we're still getting five in the result. 286 00:09:41,650 --> 00:09:43,310 Well, if we think about 287 00:09:43,540 --> 00:09:44,630 exactly what we wrote, 288 00:09:44,860 --> 00:09:46,040 what we wrote is finding 289 00:09:46,520 --> 00:09:47,590 all pairs of apply 290 00:09:48,010 --> 00:09:49,070 records where it's the same student 291 00:09:49,500 --> 00:09:51,090 and they applied to CS in 292 00:09:51,110 --> 00:09:52,410 one of the pairs and they didn't 293 00:09:52,800 --> 00:09:54,010 apply to EE in the other. 294 00:09:54,330 --> 00:09:56,870 So, it could be, for example, biology or geology. 295 00:09:58,400 --> 00:09:59,400 But the problem is that 296 00:09:59,660 --> 00:10:01,130 when we consider these pairs, that 297 00:10:01,460 --> 00:10:02,390 doesn't mean there's not another 298 00:10:02,840 --> 00:10:04,010 pair with the same student 299 00:10:04,690 --> 00:10:06,020 where they applied to CS and EE. 300 00:10:06,700 --> 00:10:08,140 All this is actually finding is 301 00:10:08,260 --> 00:10:09,470 students who applied to CS 302 00:10:10,230 --> 00:10:11,730 and also applied to another 303 00:10:12,170 --> 00:10:13,480 major that's not EE. 304 00:10:13,770 --> 00:10:16,190 So, that's quite different from the query we're shooting for. 305 00:10:16,890 --> 00:10:18,190 And actually, the interesting thing 306 00:10:18,770 --> 00:10:20,130 is that with the constructs we've 307 00:10:20,400 --> 00:10:21,710 seen so far in SQL, it's 308 00:10:21,910 --> 00:10:23,310 not possible to write 309 00:10:23,600 --> 00:10:26,960 the query we had earlier without using the except operator. 310 00:10:27,620 --> 00:10:28,660 But in later videos we will 311 00:10:28,790 --> 00:10:31,920 see additional constructs in SQL that do allow us to write that query.