1 00:00:00,150 --> 00:00:01,690 In this final video, we'll 2 00:00:01,860 --> 00:00:03,440 learn the modification statements of sequel. 3 00:00:03,990 --> 00:00:05,090 There are statements for inserting 4 00:00:05,590 --> 00:00:06,890 data, for deleting data 5 00:00:07,610 --> 00:00:08,850 and for updating existing data. 6 00:00:10,570 --> 00:00:12,080 For inserting data, there are two methods. 7 00:00:13,030 --> 00:00:14,350 The first method allows us to 8 00:00:14,480 --> 00:00:15,730 enter one tupple into 9 00:00:15,810 --> 00:00:17,810 the database by specifying it's actual value. 10 00:00:18,180 --> 00:00:19,370 So that's the command here. 11 00:00:19,620 --> 00:00:20,610 We say insert into a 12 00:00:20,680 --> 00:00:22,250 table, we specify the 13 00:00:22,330 --> 00:00:23,830 values of a tuple and 14 00:00:23,910 --> 00:00:24,910 the result of that command will 15 00:00:25,040 --> 00:00:26,170 be to insert one new tuple 16 00:00:26,590 --> 00:00:28,010 into the table with that value. 17 00:00:28,300 --> 00:00:30,390 The other possibility is 18 00:00:30,520 --> 00:00:31,600 to run a query over the 19 00:00:31,710 --> 00:00:33,290 database as a select statement. 20 00:00:34,110 --> 00:00:35,290 That select statement will produce 21 00:00:35,620 --> 00:00:37,170 a set of tuples, and as 22 00:00:37,290 --> 00:00:38,240 long as that set of tuples 23 00:00:38,580 --> 00:00:40,170 has the same schema as 24 00:00:40,340 --> 00:00:41,530 the table we could insert 25 00:00:41,920 --> 00:00:43,200 all of the tuples into the table. 26 00:00:44,060 --> 00:00:45,070 So those are the two methods of 27 00:00:45,240 --> 00:00:47,800 inserting data and we'll see those shortly in our demo. 28 00:00:49,060 --> 00:00:51,110 To delete data, we have a fairly simple command. 29 00:00:51,950 --> 00:00:53,400 It says we delete from table 30 00:00:53,910 --> 00:00:55,520 where a certain condition is true. 31 00:00:56,170 --> 00:00:57,380 So this condition is similar to 32 00:00:57,550 --> 00:00:59,200 the conditions that we see in the select statement. 33 00:01:00,100 --> 00:01:01,360 And every tuple in the 34 00:01:01,450 --> 00:01:04,170 table that satisfies this condition will be deleted. 35 00:01:05,100 --> 00:01:06,500 Now this condition can be fairly complicated. 36 00:01:07,200 --> 00:01:08,490 It can include sub-queries. 37 00:01:09,260 --> 00:01:11,290 It can include aggregation over other 38 00:01:11,550 --> 00:01:14,050 tables and so on, again to be seen in our demo. 39 00:01:15,180 --> 00:01:16,360 Finally, we're interested in updating 40 00:01:16,830 --> 00:01:18,350 existing data, and that's 41 00:01:18,540 --> 00:01:21,400 done through a command similar to the delete command. 42 00:01:21,890 --> 00:01:23,600 It similarly operates on one table. 43 00:01:24,650 --> 00:01:27,480 It evaluates a condition over each tuple of the table. 44 00:01:28,090 --> 00:01:30,950 And now when the condition is true, we don't delete the tuple. 45 00:01:31,640 --> 00:01:33,010 Instead, we modify the tuple. 46 00:01:33,710 --> 00:01:34,780 We take the attribute that's 47 00:01:34,970 --> 00:01:36,170 specified here and we 48 00:01:36,630 --> 00:01:37,620 reassign it to have 49 00:01:37,780 --> 00:01:39,560 the value that's the result of the expression. 50 00:01:40,630 --> 00:01:43,110 As we'll see in the demo, this condition here can be fairly complicated. 51 00:01:43,850 --> 00:01:45,320 It can have sub-queries and so on. 52 00:01:46,280 --> 00:01:47,960 And this expression can also be quite complicated. 53 00:01:48,680 --> 00:01:50,160 It can involve queries over other 54 00:01:50,390 --> 00:01:52,370 tables or the same table in the database. 55 00:01:53,780 --> 00:01:55,040 Finally, I wanted to 56 00:01:55,140 --> 00:01:56,820 mention that we can actually update 57 00:01:57,180 --> 00:01:58,960 multiple attributes in a tuple. 58 00:01:59,230 --> 00:02:00,830 So if we're updating a 59 00:02:00,870 --> 00:02:02,710 table, again, exactly the same, a single table. 60 00:02:03,220 --> 00:02:04,540 A condition identifies the 61 00:02:04,610 --> 00:02:05,920 tuples to update, but now 62 00:02:06,150 --> 00:02:08,340 we can update simultaneously any number 63 00:02:08,720 --> 00:02:09,920 of attributes, each by evaluating 64 00:02:10,860 --> 00:02:12,370 an expression and assigning the 65 00:02:12,450 --> 00:02:14,310 result of that expression to the attribute. 66 00:02:14,600 --> 00:02:16,260 As always, our demos 67 00:02:16,610 --> 00:02:17,970 will use the simple college admissions 68 00:02:18,380 --> 00:02:21,250 database with colleges, students and applications. 69 00:02:23,610 --> 00:02:24,910 As usual, we have four colleges, 70 00:02:25,450 --> 00:02:26,700 a bunch of students, 71 00:02:27,170 --> 00:02:28,430 and a bunch of applications 72 00:02:29,160 --> 00:02:30,990 for the students for the colleges. We'll 73 00:02:31,260 --> 00:02:33,010 first see some insert commands, then 74 00:02:33,120 --> 00:02:34,950 some delete commands, and finally some update commands. 75 00:02:36,280 --> 00:02:38,200 As I mentioned, there's two different forms of insert command. 76 00:02:38,570 --> 00:02:40,030 One that inserts a tupple 77 00:02:40,530 --> 00:02:41,650 at a time by specifying the values 78 00:02:42,470 --> 00:02:44,610 and another that uses subqueries to insert tuples. 79 00:02:45,200 --> 00:02:46,270 So let's start with a simple one. 80 00:02:46,800 --> 00:02:48,940 Let's add a new college, Carnegie Mellon, to our database. 81 00:02:49,810 --> 00:02:50,820 We do that by saying we 82 00:02:50,920 --> 00:02:52,170 want to insert into college, we 83 00:02:52,370 --> 00:02:53,670 use the keyword values and we 84 00:02:53,820 --> 00:02:54,900 simply list the values we 85 00:02:54,980 --> 00:02:56,030 want to insert: the name 86 00:02:56,270 --> 00:02:58,150 of the college, the state and the enrollment. 87 00:02:58,850 --> 00:03:00,310 We run the query and we 88 00:03:00,430 --> 00:03:02,620 go take a look now at the college relation. 89 00:03:04,390 --> 00:03:04,980 Let's go to college. 90 00:03:06,810 --> 00:03:07,810 Here we go and we see 91 00:03:07,990 --> 00:03:10,120 now that Carnegie Mellon has been added to our database. 92 00:03:11,620 --> 00:03:13,590 Now let's do little more complicated insert commands. 93 00:03:15,490 --> 00:03:18,310 Now that Carnegie Mellon is in our database, let's have some students apply. 94 00:03:19,270 --> 00:03:20,070 What we're going to do is have 95 00:03:20,360 --> 00:03:21,560 those students who haven't 96 00:03:21,800 --> 00:03:23,300 applied anywhere yet, apply to 97 00:03:23,400 --> 00:03:25,410 Carnegie Mellon, to be a computer science major. 98 00:03:25,750 --> 00:03:26,960 Let me take it step by step. 99 00:03:27,630 --> 00:03:28,760 Let's start by finding those 100 00:03:29,000 --> 00:03:30,220 students who haven't applied anywhere 101 00:03:30,650 --> 00:03:32,200 so this is a review of what we saw earlier. 102 00:03:32,990 --> 00:03:33,850 We're going to find those students 103 00:03:34,260 --> 00:03:35,240 whose ID is not in 104 00:03:35,330 --> 00:03:38,090 the sub-query that selects all of the IDs in the apply relation. 105 00:03:39,150 --> 00:03:40,070 We run the query and we 106 00:03:40,180 --> 00:03:41,070 discovered that we have four 107 00:03:41,320 --> 00:03:42,760 students who haven't yet applied anymore. 108 00:03:43,180 --> 00:03:44,430 The next thing that we're 109 00:03:44,620 --> 00:03:46,000 going to do is turn this query 110 00:03:46,700 --> 00:03:48,110 into something that constructs the 111 00:03:48,210 --> 00:03:49,280 couples, that we want 112 00:03:49,570 --> 00:03:50,870 to insert into the apply relations. 113 00:03:51,460 --> 00:03:52,640 As a reminder, the apply 114 00:03:53,000 --> 00:03:54,650 relation has the student's ID, 115 00:03:55,220 --> 00:03:56,020 the name of the college, the 116 00:03:56,610 --> 00:03:58,520 major that they're applying for and the decision. 117 00:03:59,690 --> 00:04:01,770 So we'll construct the student's ID of course. 118 00:04:02,010 --> 00:04:03,750 We want them to apply to Carnegie Mellon. 119 00:04:05,390 --> 00:04:06,190 We want them to major in CS. 120 00:04:06,400 --> 00:04:09,170 And for now let's say that 121 00:04:09,300 --> 00:04:10,110 we don't know what the decision 122 00:04:10,610 --> 00:04:12,900 is, so we'll put a null value in for the decision. 123 00:04:13,960 --> 00:04:15,490 So let's run this query and 124 00:04:15,700 --> 00:04:16,800 now we see for our four 125 00:04:17,040 --> 00:04:18,190 students, we've constructed a 126 00:04:18,810 --> 00:04:20,510 tuple, four tuples, that are 127 00:04:20,620 --> 00:04:21,790 ready to be inserted into 128 00:04:22,000 --> 00:04:24,030 the apply relation, they have the appropriate schema. 129 00:04:24,990 --> 00:04:25,860 So now that we have that 130 00:04:26,140 --> 00:04:27,010 query all ready to go 131 00:04:27,360 --> 00:04:28,760 we say insert into apply 132 00:04:31,210 --> 00:04:32,100 and we run the query 133 00:04:32,670 --> 00:04:33,760 and we'll see that 4 134 00:04:34,150 --> 00:04:35,250 tuples are inserted in the 135 00:04:35,420 --> 00:04:37,380 apply relation, let's go take a look at the relation. 136 00:04:40,200 --> 00:04:41,520 Let's try again, here we've 137 00:04:41,720 --> 00:04:43,100 got apply, and now we 138 00:04:43,270 --> 00:04:44,270 see we have our four 139 00:04:44,460 --> 00:04:45,600 new tuples and as a reminder 140 00:04:46,070 --> 00:04:47,220 in this user interface, a blank 141 00:04:47,580 --> 00:04:49,500 cell is what's used as a null value. 142 00:04:51,230 --> 00:04:53,280 Now, let's see a little more action for Carnegie Mellon. 143 00:04:54,100 --> 00:04:55,240 Let's find students who have 144 00:04:55,360 --> 00:04:56,700 applied for an EE 145 00:04:56,760 --> 00:04:58,450 Major at other colleges and 146 00:04:58,540 --> 00:05:00,010 have been turned down and let's 147 00:05:00,330 --> 00:05:02,710 have them apply to Carnegie Mellon and let's accept them right away. 148 00:05:03,170 --> 00:05:04,990 So again, I'm going to do this step by step. 149 00:05:05,300 --> 00:05:06,590 The first thing we'll do is 150 00:05:06,830 --> 00:05:08,250 we will find students who have 151 00:05:08,430 --> 00:05:09,960 applied for EE at 152 00:05:10,060 --> 00:05:11,540 another college so we'll change 153 00:05:11,640 --> 00:05:13,810 this to N where the 154 00:05:13,930 --> 00:05:16,300 major equals EE and 155 00:05:16,460 --> 00:05:17,470 we want students who were 156 00:05:17,650 --> 00:05:20,050 rejected so the decision equals no. 157 00:05:20,790 --> 00:05:21,870 We have y/n for our decision. 158 00:05:22,690 --> 00:05:24,270 And let's just check how 159 00:05:24,400 --> 00:05:26,070 many students we have in that category. 160 00:05:26,740 --> 00:05:27,650 Let's run the query here. 161 00:05:28,100 --> 00:05:29,590 And now we see there's two students. 162 00:05:30,160 --> 00:05:31,010 And if we went back and looked at 163 00:05:31,080 --> 00:05:32,210 the apply relation, we would 164 00:05:32,400 --> 00:05:34,160 find that indeed they'd applied to EE and been rejected. 165 00:05:35,350 --> 00:05:36,490 Now let's turn this into 166 00:05:36,780 --> 00:05:38,290 a query that constructs tuples that 167 00:05:38,390 --> 00:05:39,950 we'd like to insert into the apply relation. 168 00:05:40,540 --> 00:05:41,660 So we need the student ID. 169 00:05:41,810 --> 00:05:44,340 We want them to apply to Carnegie Mellon. 170 00:05:46,190 --> 00:05:47,710 Excuse my slow typing, as usual. 171 00:05:48,470 --> 00:05:49,890 This time they're going 172 00:05:50,040 --> 00:05:51,880 to apply to EE and we're 173 00:05:52,220 --> 00:05:53,660 going to accept them right away. 174 00:05:54,400 --> 00:05:55,660 So let's just check that query. 175 00:05:56,420 --> 00:05:57,500 So now we've produced two tuples 176 00:05:58,270 --> 00:06:00,220 that are ready to be inserted into the apply relation. 177 00:06:01,390 --> 00:06:03,290 We say insert into apply 178 00:06:05,760 --> 00:06:07,430 with the sub-query, we run 179 00:06:07,860 --> 00:06:08,760 it, and we see that two 180 00:06:09,090 --> 00:06:10,260 rows have been inserted into apply. 181 00:06:11,090 --> 00:06:12,100 Let's go take a look at that. 182 00:06:12,880 --> 00:06:14,660 And we need to refresh here. 183 00:06:15,000 --> 00:06:16,120 And we look down and 184 00:06:16,230 --> 00:06:17,490 we see that indeed, the two 185 00:06:17,730 --> 00:06:20,280 students have applied to EE and they've been accepted. 186 00:06:22,220 --> 00:06:23,590 Now let's explore the delete command. 187 00:06:24,870 --> 00:06:25,540 What we're going to do in this 188 00:06:25,690 --> 00:06:26,680 example is we're going to 189 00:06:26,790 --> 00:06:28,090 find all students that have 190 00:06:28,330 --> 00:06:29,510 applied to more than two different 191 00:06:29,820 --> 00:06:31,010 majors, and we're going 192 00:06:31,190 --> 00:06:32,010 to decide that they are unreliable 193 00:06:32,630 --> 00:06:34,880 students and we are going to entirely delete them from the database. 194 00:06:35,970 --> 00:06:37,200 So let's start by forming 195 00:06:37,490 --> 00:06:38,850 the query that finds the students 196 00:06:39,210 --> 00:06:40,670 who have applied to more than two majors. 197 00:06:41,620 --> 00:06:42,210 Here it is. 198 00:06:42,830 --> 00:06:45,060 You may want to remember the group I am having clause. 199 00:06:45,940 --> 00:06:47,200 So, it says we go to the apply relation. 200 00:06:48,010 --> 00:06:50,360 We form groups or partitions by SID's. 201 00:06:50,700 --> 00:06:51,670 So, we're going to consider the 202 00:06:51,900 --> 00:06:54,190 set of application for each student individually. 203 00:06:55,460 --> 00:06:56,280 We're going to count how 204 00:06:56,450 --> 00:06:58,610 many distinct majors there are in each group. 205 00:06:59,480 --> 00:07:00,380 And if that number is greater 206 00:07:00,660 --> 00:07:01,380 than 2, we're going to return 207 00:07:01,790 --> 00:07:02,790 the student's ID and for now, 208 00:07:03,460 --> 00:07:05,370 let's look at how many majors they applied for. 209 00:07:06,160 --> 00:07:07,210 So we find in our database 210 00:07:07,560 --> 00:07:10,220 that there are two students who have applied for three majors. 211 00:07:11,330 --> 00:07:14,070 And, we don't like those students, so were just going to get rid of them. 212 00:07:14,760 --> 00:07:16,160 Here's how we do it; we 213 00:07:16,460 --> 00:07:20,910 say "delete from student" where 214 00:07:22,030 --> 00:07:22,890 and as a reminder the 215 00:07:22,940 --> 00:07:24,080 delete command is of the 216 00:07:24,260 --> 00:07:25,170 form delete from the table 217 00:07:25,650 --> 00:07:26,410 where and then you can 218 00:07:26,600 --> 00:07:28,220 have a simple condition or 219 00:07:28,300 --> 00:07:29,580 a complicated condition so this 220 00:07:29,720 --> 00:07:31,260 is a sort of complicated condition. 221 00:07:32,090 --> 00:07:33,070 We're going to see where their 222 00:07:33,440 --> 00:07:36,850 SID is in and we're going to turn this into a sub-query. 223 00:07:37,340 --> 00:07:38,310 We don't need to count distinct 224 00:07:38,780 --> 00:07:41,800 here and let me just format this a little better. 225 00:07:42,460 --> 00:07:44,960 Is in the set of 226 00:07:45,040 --> 00:07:46,010 student ids who have applied to more 227 00:07:46,220 --> 00:07:47,530 than one more than, more than two majors. 228 00:07:48,330 --> 00:07:49,610 So we should be deleting students 229 00:07:50,090 --> 00:07:52,690 three-four-five and eight-seven-six if all goes well. 230 00:07:53,470 --> 00:07:54,080 Let's run the query. 231 00:07:54,910 --> 00:07:56,640 We did delete two rows, let's 232 00:07:56,860 --> 00:07:57,830 go back and take a look 233 00:07:57,960 --> 00:07:59,860 at the students, and we 234 00:08:00,030 --> 00:08:02,220 should find that three-four-five and 235 00:08:02,430 --> 00:08:04,280 eight-seven-six are gone, and indeed they are. 236 00:08:04,920 --> 00:08:06,220 Now, we've deleted them from 237 00:08:06,320 --> 00:08:07,400 the students, but we haven't 238 00:08:07,920 --> 00:08:08,940 deleted them from the apply 239 00:08:09,360 --> 00:08:10,700 relation, so we can 240 00:08:10,940 --> 00:08:12,520 write the same deletion statement 241 00:08:12,940 --> 00:08:14,060 exactly as a matter of 242 00:08:14,250 --> 00:08:15,740 fact and just delete from applied. 243 00:08:16,790 --> 00:08:18,050 Now I want to mention that 244 00:08:18,240 --> 00:08:21,160 not all database systems allow this particular deletion. 245 00:08:22,220 --> 00:08:24,290 Some database systems don't allow 246 00:08:24,910 --> 00:08:26,420 deletion commands where the sub-query 247 00:08:26,840 --> 00:08:28,520 includes the same relation 248 00:08:28,960 --> 00:08:30,270 that you're deleting from and it 249 00:08:30,560 --> 00:08:31,980 can be a little tricky, but 250 00:08:32,190 --> 00:08:33,590 some do, PostGRES does, that's 251 00:08:33,800 --> 00:08:35,230 what we're running today, and so 252 00:08:35,330 --> 00:08:37,870 we'll run the query and, this 253 00:08:38,020 --> 00:08:38,960 time, eight rows were affected, 254 00:08:39,760 --> 00:08:41,520 so we had eight applications for 255 00:08:41,960 --> 00:08:43,100 students, who had applied to 256 00:08:43,350 --> 00:08:45,930 more than two distinct majors, and again, it will be those same students. 257 00:08:46,240 --> 00:08:47,150 Or we can go back 258 00:08:47,450 --> 00:08:48,380 and check if we want to 259 00:08:48,480 --> 00:08:49,940 look in the apply relation we'll see 260 00:08:51,570 --> 00:08:53,060 that 876 and 345 are 261 00:08:54,070 --> 00:08:55,860 now gone from the apply relation as well. 262 00:08:56,180 --> 00:08:58,020 Now going back to our 263 00:08:58,250 --> 00:08:59,680 query as I mentioned 264 00:09:00,130 --> 00:09:01,450 some database systems don't support 265 00:09:02,020 --> 00:09:03,640 this form of deletion, and 266 00:09:03,760 --> 00:09:05,020 so it's a bit more complicated to 267 00:09:05,120 --> 00:09:06,610 do this exact deletion in those systems. 268 00:09:07,490 --> 00:09:08,450 The way to do it would typically 269 00:09:08,910 --> 00:09:10,290 be to create a temporary table, 270 00:09:11,060 --> 00:09:12,090 put in the results of this 271 00:09:12,350 --> 00:09:13,720 sub-query and then delete 272 00:09:14,200 --> 00:09:15,120 from apply, where the student 273 00:09:15,560 --> 00:09:17,270 ID is present, in that temporary table. 274 00:09:17,760 --> 00:09:18,660 You can give that a try 275 00:09:18,910 --> 00:09:20,560 on SQL light, or MySQL, and 276 00:09:20,680 --> 00:09:22,320 see if you get the same results, you ought to. 277 00:09:24,730 --> 00:09:25,160 Let's see one more deletion. 278 00:09:25,430 --> 00:09:26,640 In this example, we have 279 00:09:26,890 --> 00:09:28,130 decided that any college that doesn't 280 00:09:28,420 --> 00:09:30,050 have a CS applicant is 281 00:09:30,180 --> 00:09:32,410 probably not worth keeping in the database and we'll delete it. 282 00:09:32,990 --> 00:09:33,890 We'll start by doing the select command 283 00:09:34,380 --> 00:09:36,010 and then we'll translate it to a delete. 284 00:09:36,750 --> 00:09:37,910 So this select command finds 285 00:09:38,220 --> 00:09:39,380 colleges where their college 286 00:09:39,850 --> 00:09:41,040 name is not among the 287 00:09:41,100 --> 00:09:42,200 college names in the apply 288 00:09:42,560 --> 00:09:44,260 relation, where the major is CS. 289 00:09:44,730 --> 00:09:47,730 In other words, this finds all colleges where nobody has applied for CS. 290 00:09:48,470 --> 00:09:49,550 We run the query and we 291 00:09:49,610 --> 00:09:51,780 discovered that nobody's applied for CS at Cornell. 292 00:09:52,830 --> 00:09:54,420 If we want to now delete the tupple. 293 00:09:55,090 --> 00:09:57,400 What we do, it's very simple transformation here. 294 00:09:57,750 --> 00:09:59,620 We just say "delete from college" 295 00:10:00,640 --> 00:10:01,450 and then the rest is the same 296 00:10:01,780 --> 00:10:02,720 where the college name is not among 297 00:10:02,970 --> 00:10:04,300 those where someone has applied to CS. 298 00:10:05,010 --> 00:10:06,520 We run the query, it's successful, 299 00:10:07,420 --> 00:10:08,460 and if we go back and 300 00:10:08,700 --> 00:10:09,870 now we look at our college 301 00:10:10,440 --> 00:10:11,740 relation, we will see 302 00:10:11,930 --> 00:10:13,240 that Cornell is indeed gone. 303 00:10:14,790 --> 00:10:16,450 Finally, let's take a look at some update commands. 304 00:10:17,590 --> 00:10:18,950 In this first command we're going 305 00:10:19,030 --> 00:10:20,360 to find students whose GPA 306 00:10:20,660 --> 00:10:21,660 is less than three point 307 00:10:21,660 --> 00:10:23,590 six and who have applied to Carnegie Mellon. 308 00:10:24,440 --> 00:10:25,670 We're going to accept those students, 309 00:10:26,670 --> 00:10:28,400 but we're going to turn them into economics majors. 310 00:10:29,380 --> 00:10:30,500 Let's start by finding the 311 00:10:30,600 --> 00:10:31,580 students who have applied to 312 00:10:31,690 --> 00:10:32,520 Carnegie Mellon, with a GPA 313 00:10:33,000 --> 00:10:34,300 of less than three point six, here 314 00:10:34,600 --> 00:10:35,630 the query, again, this is 315 00:10:35,770 --> 00:10:37,990 a review, from previous videos, we 316 00:10:38,370 --> 00:10:39,550 find in the apply relation where 317 00:10:40,140 --> 00:10:41,300 college is Carnegie Mellon, and 318 00:10:41,530 --> 00:10:42,690 where the student ID is among 319 00:10:43,120 --> 00:10:45,730 those students, whose GPA is less than three point six. 320 00:10:47,040 --> 00:10:49,550 We run the query and find that there are two such applications. 321 00:10:50,830 --> 00:10:51,710 So now what we want to 322 00:10:51,800 --> 00:10:53,190 do is update the apply 323 00:10:53,550 --> 00:10:54,340 relation and we're going to 324 00:10:54,490 --> 00:10:55,640 accept these students, but for 325 00:10:56,150 --> 00:10:57,690 an economics major, so we 326 00:10:57,870 --> 00:10:58,930 change the select to an 327 00:10:59,060 --> 00:11:00,410 update and this is, 328 00:11:00,500 --> 00:11:01,670 were going to update the apply 329 00:11:01,880 --> 00:11:02,860 relation, and we're going to update 330 00:11:03,280 --> 00:11:04,610 every tuple that satisfies the 331 00:11:04,680 --> 00:11:05,800 conditions, we happen to know 332 00:11:05,900 --> 00:11:07,490 it's these two tuples, and we're 333 00:11:07,630 --> 00:11:10,040 going to set the decision 334 00:11:11,020 --> 00:11:12,160 for the students to be equal 335 00:11:12,570 --> 00:11:14,280 to yes and we're 336 00:11:14,470 --> 00:11:16,810 going to set the major to be equal to economics. 337 00:11:19,530 --> 00:11:21,660 We run the query, it succeeded 338 00:11:22,310 --> 00:11:23,680 and we go now, and 339 00:11:23,780 --> 00:11:24,800 we look at the apply 340 00:11:25,170 --> 00:11:27,130 relation, and we find 341 00:11:27,580 --> 00:11:28,750 these two students have now 342 00:11:28,940 --> 00:11:30,710 applied to economics and they've been accepted. 343 00:11:32,470 --> 00:11:35,420 Here's a more complicated and highly motivated update command. 344 00:11:36,830 --> 00:11:38,010 In this command we're going to 345 00:11:38,220 --> 00:11:39,640 find the student who has 346 00:11:39,930 --> 00:11:41,440 the highest GPA and has 347 00:11:41,600 --> 00:11:42,670 applied to major in EE 348 00:11:43,130 --> 00:11:44,220 and we're going to change 349 00:11:44,460 --> 00:11:45,690 them from an EE major to 350 00:11:45,870 --> 00:11:47,300 a CSE major, a computer 351 00:11:47,700 --> 00:11:49,260 science and engineering, rather than 352 00:11:49,420 --> 00:11:51,090 just having them waste their time on electrical engineering. 353 00:11:52,230 --> 00:11:53,040 All right so let's take a 354 00:11:53,190 --> 00:11:54,360 look at this query which is 355 00:11:54,420 --> 00:11:55,760 going to find the applications 356 00:11:56,010 --> 00:11:57,860 that we're going modify. This query 357 00:11:58,200 --> 00:11:59,480 is an excellent review from previous 358 00:11:59,920 --> 00:12:01,030 videos because it has 359 00:12:01,940 --> 00:12:02,910 triply nested sub-queries. 360 00:12:04,080 --> 00:12:04,960 So, again, our goal is to 361 00:12:05,010 --> 00:12:07,110 find students who have applied to Major in EE. 362 00:12:07,310 --> 00:12:08,220 So here are students who have 363 00:12:08,300 --> 00:12:09,790 applied to major in EE, but 364 00:12:09,900 --> 00:12:10,840 we want them to have the 365 00:12:11,100 --> 00:12:14,080 highest GPA of anybody who's applied to major in EE. 366 00:12:14,450 --> 00:12:15,700 So in the innermost we 367 00:12:16,300 --> 00:12:17,060 find the students who have 368 00:12:17,210 --> 00:12:18,320 applied to EE, their student 369 00:12:18,740 --> 00:12:20,790 ID's, then moving out 370 00:12:20,980 --> 00:12:21,950 one level, we find the 371 00:12:22,390 --> 00:12:23,390 GPA's of all the students 372 00:12:23,850 --> 00:12:25,210 who have applied to major in EE. 373 00:12:25,910 --> 00:12:27,250 Now we find all students 374 00:12:27,810 --> 00:12:29,030 whose GPA is at least 375 00:12:29,510 --> 00:12:31,010 as high as the highest EE 376 00:12:31,310 --> 00:12:33,540 major, EE applicant, and 377 00:12:33,740 --> 00:12:34,780 finally we find all students 378 00:12:35,170 --> 00:12:37,260 who have applied to EE and have that highest GPA. 379 00:12:38,000 --> 00:12:38,000 Whew! 380 00:12:39,590 --> 00:12:41,910 OK, let's run the query and see who this is. 381 00:12:42,780 --> 00:12:45,010 We discover it is student 123. 382 00:12:45,340 --> 00:12:46,700 And that student has applied three 383 00:12:46,980 --> 00:12:48,180 times, actually, to EE, to 384 00:12:48,300 --> 00:12:49,810 Stanford, Cornell, and Carnegie Mellon. 385 00:12:50,230 --> 00:12:51,710 So now that we 386 00:12:52,280 --> 00:12:54,060 have identified those students, transforming 387 00:12:54,770 --> 00:12:55,730 this command to do the update 388 00:12:56,090 --> 00:12:57,250 is actually relatively simple. 389 00:12:57,990 --> 00:12:59,580 We update apply and instead 390 00:12:59,970 --> 00:13:01,650 of having the major be EE then 391 00:13:01,870 --> 00:13:05,000 we have the major be CSEE that 392 00:13:05,220 --> 00:13:06,560 major equals, sorry, CSE. 393 00:13:07,950 --> 00:13:09,590 That's it, we run the query. 394 00:13:10,880 --> 00:13:12,400 And, it succeeded, and we 395 00:13:12,540 --> 00:13:13,400 go back and we look 396 00:13:13,550 --> 00:13:15,700 at the apply relation, and we 397 00:13:16,050 --> 00:13:17,400 refresh and here we 398 00:13:17,650 --> 00:13:19,330 see that student 123 has 399 00:13:19,540 --> 00:13:21,240 now applied to CSE rather 400 00:13:21,630 --> 00:13:23,230 than EE at those three universities. 401 00:13:24,800 --> 00:13:25,770 Let's see a couple more 402 00:13:25,890 --> 00:13:26,700 updates that are a little 403 00:13:26,860 --> 00:13:28,410 silly but demonstrate some features. 404 00:13:29,160 --> 00:13:30,330 In this example, we're going 405 00:13:30,540 --> 00:13:32,020 to give every student the highest 406 00:13:32,400 --> 00:13:34,400 GPA and the smallest high school in the database. 407 00:13:35,260 --> 00:13:36,430 So, what it's demonstrating is that, 408 00:13:36,580 --> 00:13:37,870 in the set command, the right-hand 409 00:13:38,130 --> 00:13:39,710 side of the equals can itself be a sub-query. 410 00:13:40,440 --> 00:13:42,020 Here we're updating every student. 411 00:13:42,390 --> 00:13:43,720 There's no where clause, so that 412 00:13:43,790 --> 00:13:44,920 means every tuple is going 413 00:13:45,010 --> 00:13:46,740 to satisfy the condition, and for 414 00:13:46,830 --> 00:13:47,640 each student, we're going to set 415 00:13:47,990 --> 00:13:48,870 their their GPA to the result 416 00:13:49,680 --> 00:13:51,070 of finding the maximum GPA in 417 00:13:51,300 --> 00:13:52,310 the database and we're going 418 00:13:52,390 --> 00:13:53,330 to set their size high school 419 00:13:53,610 --> 00:13:55,960 to the result of finding the minimum size high school. 420 00:13:56,810 --> 00:13:58,010 We run the query, and we 421 00:13:58,290 --> 00:13:59,090 go take a look at the 422 00:13:59,150 --> 00:14:00,560 student relation, and we 423 00:14:00,830 --> 00:14:02,180 will see now that every student 424 00:14:02,540 --> 00:14:04,320 has a GPA of 3.9 425 00:14:04,480 --> 00:14:05,560 and every student has a size 426 00:14:05,710 --> 00:14:06,980 high school of two hundred, what 427 00:14:07,300 --> 00:14:09,390 were formerly the largest and smallest values in the database. 428 00:14:10,750 --> 00:14:12,010 Our very last query says 429 00:14:12,260 --> 00:14:13,190 we're in a great mood today, 430 00:14:13,880 --> 00:14:16,260 let's go to our apply relation, and let's accept everybody. 431 00:14:16,960 --> 00:14:18,210 Very simple query, just says 432 00:14:18,390 --> 00:14:19,610 "update apply" there's no where 433 00:14:19,880 --> 00:14:21,660 conditions, so every tuple satisfies the query. 434 00:14:22,050 --> 00:14:23,290 We set the decision equal "Y". 435 00:14:24,230 --> 00:14:25,200 Here we go, make a lot 436 00:14:25,340 --> 00:14:26,790 of students happy, let's take 437 00:14:27,050 --> 00:14:28,540 a look at the apply relation, and 438 00:14:28,830 --> 00:14:31,560 we will see now that everybody has been accepted.