0:00
In this final video, we'll
0:01
learn the modification statements of SQL.
0:03
There are statements for inserting
0:05
data, for deleting data
0:07
and for updating existing data.
0:10
For inserting data, there are two methods.
0:13
The first method allows us to
0:14
enter one tupple into
0:15
the database by specifying it's actual value.
0:18
So that's the command here.
0:19
We say insert into a
0:20
table, we specify the
0:22
values of a tuple and
0:23
the result of that command will
0:25
be to insert one new tuple
0:26
into the table with that value.
0:28
The other possibility is
0:30
to run a query over the
0:31
database as a select statement.
0:34
That select statement will produce
0:35
a set of tuples, and as
0:37
long as that set of tuples
0:38
has the same schema as
0:40
the table we could insert
0:41
all of the tuples into the table.
0:44
So those are the two methods of
0:45
inserting data and we'll see those shortly in our demo.
0:49
To delete data, we have a fairly simple command.
0:51
It says we delete from table
0:53
where a certain condition is true.
0:56
So this condition is similar to
0:57
the conditions that we see in the select statement.
1:00
And every tuple in the
1:01
table that satisfies this condition will be deleted.
1:05
Now this condition can be fairly complicated.
1:07
It can include sub-queries.
1:09
It can include aggregation over other
1:11
tables and so on, again to be seen in our demo.
1:15
Finally, we're interested in updating
1:16
existing data, and that's
1:18
done through a command similar to the delete command.
1:21
It similarly operates on one table.
1:24
It evaluates a condition over each tuple of the table.
1:28
And now when the condition is true, we don't delete the tuple.
1:31
Instead, we modify the tuple.
1:33
We take the attribute that's
1:34
specified here and we
1:36
reassign it to have
1:37
the value that's the result of the expression.
1:40
As we'll see in the demo, this condition here can be fairly complicated.
1:43
It can have sub-queries and so on.
1:46
And this expression can also be quite complicated.
1:48
It can involve queries over other
1:50
tables or the same table in the database.
1:53
Finally, I wanted to
1:55
mention that we can actually update
1:57
multiple attributes in a tuple.
1:59
So if we're updating a
2:00
table, again, exactly the same, a single table.
2:03
A condition identifies the
2:04
tuples to update, but now
2:06
we can update simultaneously any number
2:08
of attributes, each by evaluating
2:10
an expression and assigning the
2:12
result of that expression to the attribute.
2:14
As always, our demos
2:16
will use the simple college admissions
2:18
database with colleges, students and applications.
2:23
As usual, we have four colleges,
2:25
a bunch of students,
2:27
and a bunch of applications
2:29
for the students for the colleges. We'll
2:31
first see some insert commands, then
2:33
some delete commands, and finally some update commands.
2:36
As I mentioned, there's two different forms of insert command.
2:38
One that inserts a tupple
2:40
at a time by specifying the values
2:42
and another that uses subqueries to insert tuples.
2:45
So let's start with a simple one.
2:46
Let's add a new college, Carnegie Mellon, to our database.
2:49
We do that by saying we
2:50
want to insert into college, we
2:52
use the keyword values and we
2:53
simply list the values we
2:54
want to insert: the name
2:56
of the college, the state and the enrollment.
2:58
We run the query and we
3:00
go take a look now at the college relation.
3:04
Let's go to college.
3:06
Here we go and we see
3:07
now that Carnegie Mellon has been added to our database.
3:11
Now let's do little more complicated insert commands.
3:15
Now that Carnegie Mellon is in our database, let's have some students apply.
3:19
What we're going to do is have
3:20
those students who haven't
3:21
applied anywhere yet, apply to
3:23
Carnegie Mellon, to be a computer science major.
3:25
Let me take it step by step.
3:27
Let's start by finding those
3:29
students who haven't applied anywhere
3:30
so this is a review of what we saw earlier.
3:32
We're going to find those students
3:34
whose ID is not in
3:35
the sub-query that selects all of the IDs in the apply relation.
3:39
We run the query and we
3:40
discovered that we have four
3:41
students who haven't yet applied anymore.
3:43
The next thing that we're
3:44
going to do is turn this query
3:46
into something that constructs the
3:48
couples, that we want
3:49
to insert into the apply relations.
3:51
As a reminder, the apply
3:53
relation has the student's ID,
3:55
the name of the college, the
3:56
major that they're applying for and the decision.
3:59
So we'll construct the student's ID of course.
4:02
We want them to apply to Carnegie Mellon.
4:05
We want them to major in CS.
4:06
And for now let's say that
4:09
we don't know what the decision
4:10
is, so we'll put a null value in for the decision.
4:13
So let's run this query and
4:15
now we see for our four
4:17
students, we've constructed a
4:18
tuple, four tuples, that are
4:20
ready to be inserted into
4:22
the apply relation, they have the appropriate schema.
4:24
So now that we have that
4:26
query all ready to go
4:27
we say insert into apply
4:31
and we run the query
4:32
and we'll see that 4
4:34
tuples are inserted in the
4:35
apply relation, let's go take a look at the relation.
4:40
Let's try again, here we've
4:41
got apply, and now we
4:43
see we have our four
4:44
new tuples and as a reminder
4:46
in this user interface, a blank
4:47
cell is what's used as a null value.
4:51
Now, let's see a little more action for Carnegie Mellon.
4:54
Let's find students who have
4:55
applied for an EE
4:56
Major at other colleges and
4:58
have been turned down and let's
5:00
have them apply to Carnegie Mellon and let's accept them right away.
5:03
So again, I'm going to do this step by step.
5:05
The first thing we'll do is
5:06
we will find students who have
5:08
applied for EE at
5:10
another college so we'll change
5:11
this to N where the
5:13
major equals EE and
5:16
we want students who were
5:17
rejected so the decision equals no.
5:20
We have y/n for our decision.
5:22
And let's just check how
5:24
many students we have in that category.
5:26
Let's run the query here.
5:28
And now we see there's two students.
5:30
And if we went back and looked at
5:31
the apply relation, we would
5:32
find that indeed they'd applied to EE and been rejected.
5:35
Now let's turn this into
5:36
a query that constructs tuples that
5:38
we'd like to insert into the apply relation.
5:40
So we need the student ID.
5:41
We want them to apply to Carnegie Mellon.
5:46
Excuse my slow typing, as usual.
5:48
This time they're going
5:50
to apply to EE and we're
5:52
going to accept them right away.
5:54
So let's just check that query.
5:56
So now we've produced two tuples
5:58
that are ready to be inserted into the apply relation.
6:01
We say insert into apply
6:05
with the sub-query, we run
6:07
it, and we see that two
6:09
rows have been inserted into apply.
6:11
Let's go take a look at that.
6:12
And we need to refresh here.
6:15
And we look down and
6:16
we see that indeed, the two
6:17
students have applied to EE and they've been accepted.
6:22
Now let's explore the delete command.
6:24
What we're going to do in this
6:25
example is we're going to
6:26
find all students that have
6:28
applied to more than two different
6:29
majors, and we're going
6:31
to decide that they are unreliable
6:32
students and we are going to entirely delete them from the database.
6:35
So let's start by forming
6:37
the query that finds the students
6:39
who have applied to more than two majors.
6:41
Here it is.
6:42
You may want to remember the group I am having clause.
6:45
So, it says we go to the apply relation.
6:48
We form groups or partitions by SID's.
6:50
So, we're going to consider the
6:51
set of application for each student individually.
6:55
We're going to count how
6:56
many distinct majors there are in each group.
6:59
And if that number is greater
7:00
than 2, we're going to return
7:01
the student's ID and for now,
7:03
let's look at how many majors they applied for.
7:06
So we find in our database
7:07
that there are two students who have applied for three majors.
7:11
And, we don't like those students, so were just going to get rid of them.
7:14
Here's how we do it; we
7:16
say "delete from student" where
7:22
and as a reminder the
7:22
delete command is of the
7:24
form delete from the table
7:25
where and then you can
7:26
have a simple condition or
7:28
a complicated condition so this
7:29
is a sort of complicated condition.
7:32
We're going to see where their
7:33
SID is in and we're going to turn this into a sub-query.
7:37
We don't need to count distinct
7:38
here and let me just format this a little better.
7:42
Is in the set of
7:45
student ids who have applied to more
7:46
than one more than, more than two majors.
7:48
So we should be deleting students
7:50
three-four-five and eight-seven-six if all goes well.
7:53
Let's run the query.
7:54
We did delete two rows, let's
7:56
go back and take a look
7:57
at the students, and we
8:00
should find that three-four-five and
8:02
eight-seven-six are gone, and indeed they are.
8:04
Now, we've deleted them from
8:06
the students, but we haven't
8:07
deleted them from the apply
8:09
relation, so we can
8:10
write the same deletion statement
8:12
exactly as a matter of
8:14
fact and just delete from applied.
8:16
Now I want to mention that
8:18
not all database systems allow this particular deletion.
8:22
Some database systems don't allow
8:24
deletion commands where the sub-query
8:26
includes the same relation
8:28
that you're deleting from and it
8:30
can be a little tricky, but
8:32
some do, PostGRES does, that's
8:33
what we're running today, and so
8:35
we'll run the query and, this
8:38
time, eight rows were affected,
8:39
so we had eight applications for
8:41
students, who had applied to
8:43
more than two distinct majors, and again, it will be those same students.
8:46
Or we can go back
8:47
and check if we want to
8:48
look in the apply relation we'll see
8:51
that 876 and 345 are
8:54
now gone from the apply relation as well.
8:56
Now going back to our
8:58
query as I mentioned
9:00
some database systems don't support
9:02
this form of deletion, and
9:03
so it's a bit more complicated to
9:05
do this exact deletion in those systems.
9:07
The way to do it would typically
9:08
be to create a temporary table,
9:11
put in the results of this
9:12
sub-query and then delete
9:14
from apply, where the student
9:15
ID is present, in that temporary table.
9:17
You can give that a try
9:18
on SQL light, or MySQL, and
9:20
see if you get the same results, you ought to.
9:24
Let's see one more deletion.
9:25
In this example, we have
9:26
decided that any college that doesn't
9:28
have a CS applicant is
9:30
probably not worth keeping in the database and we'll delete it.
9:32
We'll start by doing the select command
9:34
and then we'll translate it to a delete.
9:36
So this select command finds
9:38
colleges where their college
9:39
name is not among the
9:41
college names in the apply
9:42
relation, where the major is CS.
9:44
In other words, this finds all colleges where nobody has applied for CS.
9:48
We run the query and we
9:49
discovered that nobody's applied for CS at Cornell.
9:52
If we want to now delete the tupple.
9:55
What we do, it's very simple transformation here.
9:57
We just say "delete from college"
10:00
and then the rest is the same
10:01
where the college name is not among
10:02
those where someone has applied to CS.
10:05
We run the query, it's successful,
10:07
and if we go back and
10:08
now we look at our college
10:10
relation, we will see
10:11
that Cornell is indeed gone.
10:14
Finally, let's take a look at some update commands.
10:17
In this first command we're going
10:19
to find students whose GPA
10:20
is less than three point
10:21
six and who have applied to Carnegie Mellon.
10:24
We're going to accept those students,
10:26
but we're going to turn them into economics majors.
10:29
Let's start by finding the
10:30
students who have applied to
10:31
Carnegie Mellon, with a GPA
10:33
of less than three point six, here
10:34
the query, again, this is
10:35
a review, from previous videos, we
10:38
find in the apply relation where
10:40
college is Carnegie Mellon, and
10:41
where the student ID is among
10:43
those students, whose GPA is less than three point six.
10:47
We run the query and find that there are two such applications.
10:50
So now what we want to
10:51
do is update the apply
10:53
relation and we're going to
10:54
accept these students, but for
10:56
an economics major, so we
10:57
change the select to an
10:59
update and this is,
11:00
were going to update the apply
11:01
relation, and we're going to update
11:03
every tuple that satisfies the
11:04
conditions, we happen to know
11:05
it's these two tuples, and we're
11:07
going to set the decision
11:11
for the students to be equal
11:12
to yes and we're
11:14
going to set the major to be equal to economics.
11:19
We run the query, it succeeded
11:22
and we go now, and
11:23
we look at the apply
11:25
relation, and we find
11:27
these two students have now
11:28
applied to economics and they've been accepted.
11:32
Here's a more complicated and highly motivated update command.
11:36
In this command we're going to
11:38
find the student who has
11:39
the highest GPA and has
11:41
applied to major in EE
11:43
and we're going to change
11:44
them from an EE major to
11:45
a CSE major, a computer
11:47
science and engineering, rather than
11:49
just having them waste their time on electrical engineering.
11:52
All right so let's take a
11:53
look at this query which is
11:54
going to find the applications
11:56
that we're going modify. This query
11:58
is an excellent review from previous
11:59
videos because it has
12:01
triply nested sub-queries.
12:04
So, again, our goal is to
12:05
find students who have applied to Major in EE.
12:07
So here are students who have
12:08
applied to major in EE, but
12:09
we want them to have the
12:11
highest GPA of anybody who's applied to major in EE.
12:14
So in the innermost we
12:16
find the students who have
12:17
applied to EE, their student
12:18
ID's, then moving out
12:20
one level, we find the
12:22
GPA's of all the students
12:23
who have applied to major in EE.
12:25
Now we find all students
12:27
whose GPA is at least
12:29
as high as the highest EE
12:31
major, EE applicant, and
12:33
finally we find all students
12:35
who have applied to EE and have that highest GPA.
12:38
Whew!
12:39
OK, let's run the query and see who this is.
12:42
We discover it is student 123.
12:45
And that student has applied three
12:46
times, actually, to EE, to
12:48
Stanford, Cornell, and Carnegie Mellon.
12:50
So now that we
12:52
have identified those students, transforming
12:54
this command to do the update
12:56
is actually relatively simple.
12:57
We update apply and instead
12:59
of having the major be EE then
13:01
we have the major be CSEE that
13:05
major equals, sorry, CSE.
13:07
That's it, we run the query.
13:10
And, it succeeded, and we
13:12
go back and we look
13:13
at the apply relation, and we
13:16
refresh and here we
13:17
see that student 123 has
13:19
now applied to CSE rather
13:21
than EE at those three universities.
13:24
Let's see a couple more
13:25
updates that are a little
13:26
silly but demonstrate some features.
13:29
In this example, we're going
13:30
to give every student the highest
13:32
GPA and the smallest high school in the database.
13:35
So, what it's demonstrating is that,
13:36
in the set command, the right-hand
13:38
side of the equals can itself be a sub-query.
13:40
Here we're updating every student.
13:42
There's no where clause, so that
13:43
means every tuple is going
13:45
to satisfy the condition, and for
13:46
each student, we're going to set
13:47
their their GPA to the result
13:49
of finding the maximum GPA in
13:51
the database and we're going
13:52
to set their size high school
13:53
to the result of finding the minimum size high school.
13:56
We run the query, and we
13:58
go take a look at the
13:59
student relation, and we
14:00
will see now that every student
14:02
has a GPA of 3.9
14:04
and every student has a size
14:05
high school of two hundred, what
14:07
were formerly the largest and smallest values in the database.
14:10
Our very last query says
14:12
we're in a great mood today,
14:13
let's go to our apply relation, and let's accept everybody.
14:16
Very simple query, just says
14:18
"update apply" there's no where
14:19
conditions, so every tuple satisfies the query.
14:22
We set the decision equal "Y".
14:24
Here we go, make a lot
14:25
of students happy, let's take
14:27
a look at the apply relation, and
14:28
we will see now that everybody has been accepted.