0:00
In the next set of SQL
0:01
queries, we'll see examples
0:03
of using sub queries in the from and the select clauses.
0:07
In the previous video, we
0:09
introduced sub queries but we only used them in the where clause.
0:12
In the condition of queries, we
0:13
wrote sub queries that generated
0:15
sets that we use for comparisons.
0:18
In the next set of queries,
0:19
we're going to see that we can
0:20
also use sub queries, select
0:22
expressions in the from
0:24
clause of queries as well as in the select clause.
0:27
If we use a sub query
0:29
in the front, what we're really
0:30
doing is running and that's the
0:31
select statement that's going to
0:32
generate one of the
0:33
tables that we use in the rest of the query.
0:36
If we use a sub query
0:38
in the select, then what
0:39
we're doing is writing a select
0:41
expression, a sub select expression,
0:43
that produces the value that comes out of the query.
0:45
So let's see this in action
0:47
and as usual we'll be using
0:48
our simple college admissions database
0:51
with a college, student and apply tables.
0:54
Our first query is going to
0:56
lead to demonstrating the use
0:57
of a sub query in the from
0:58
clause. You might remember
1:00
from a previous video that we
1:01
had the concept of scaling a
1:03
student's GPA based on the size of their high school.
1:06
Specifically, we took the GPA
1:08
and multiplied it by the size of high school divided by a thousand.
1:12
What we're going to do in
1:12
this query is we're going to
1:14
return all students where scaling
1:16
their GPA changes its value
1:17
by more than one, specifically either
1:19
the scale GPA minus the
1:21
GPA is greater than one
1:22
or the GPA minus the scale GPA is greater than one.
1:26
So let's run the query, and
1:27
we find a number of
1:29
students whose GPA is modified
1:31
by more than one by scaling it.
1:33
Now let's take a look at this query.
1:34
We have this expression that scales
1:36
the GPA and we actually
1:38
have to write the expression out three times.
1:40
Now remember, this could in fact be a much more complicated calculation.
1:43
A smart database system
1:45
might look at the query and
1:46
recognize that it can do
1:47
the computation once and use
1:49
it in all three places, but maybe
1:51
we're not using a smart database system.
1:53
And furthermore, for the
1:54
query itself, we might
1:57
like to simplify things and only write that expression once.
2:00
So, simplifying the where cause is actually fairly easy.
2:04
We can just use the absolute
2:05
value function that's built into most SQL implementation.
2:09
So we write absolute value
2:10
function and we apply
2:12
it to this computation here.
2:15
We delete the second clause
2:17
of the query, and we run the query.
2:19
And we should get exactly the same result, and we do.
2:22
So, that's good, but we
2:24
still have to use
2:26
the two computations here of the scale GPA.
2:29
So, what I'm going to do
2:29
now is I'm going to
2:31
put in the from clause
2:33
a sub-query and that sub-query
2:34
is going to among things compute the scale GPA.
2:37
When we put a
2:38
sub query in the from clause,
2:40
what we're doing is creating a
2:42
select from where statement that
2:43
produces a relation, but in
2:45
the rest of the query we can
2:46
treat that just like a
2:47
relation, so we can refer
2:48
to the scale GPA in the rest of the query.
2:51
Alright, so let me just do a little editing here.
2:55
So, I'm going to take this select
2:57
from here, this portion
2:59
of the query, like that, and
3:01
I'm going to turn it into its own
3:02
subquery, and put it in the from clause.
3:04
Now I'm going to
3:06
give it a name G and technically,
3:09
I might be able to leave
3:10
that out, but most SQL implementations require it.
3:13
This says compute the select
3:14
from where expression and call
3:16
the result G. And now
3:18
in the rest of the query,
3:20
anytime I refer to G,
3:21
I'm referring to the result of this sub-query in the from.
3:24
Specifically, I now have
3:26
scaled GPA and if
3:31
I want, I can put that
3:32
it comes from G, although, I don't need to since it's not ambiguous.
3:35
And I can use that scaled GPA in the rest of my query.
3:40
Now, I'm still missing a select.
3:41
So, I'll just put select star
3:43
here and I'll get all attributes
3:44
back and now, I have
3:46
a complete query where I
3:47
computed the scale GPA
3:48
in the from clause and used it elsewhere in the query.
3:51
I run this one and I
3:53
get again the exact same result.
3:56
We're going to see more examples of
3:57
using sub-queries in the from
3:59
clause in the later video on aggregation.
4:03
Our second major example is going
4:05
to lead to using a sub-query in the select clause.
4:08
Let's start by explaining what this query does.
4:11
What we're going to do is
4:12
we're going to find colleges
4:14
and we're going to pair those colleges
4:16
with the highest GPA among their applicants.
4:19
So this requires joining all three
4:21
of our relations: college, apply, and student.
4:23
We have here the join conditions.
4:25
We always need to remember to include those.
4:27
And we're going to return
4:29
those college names, states, and
4:31
GPAs where the GPA
4:33
is the highest (and I'm
4:34
using the greater than or equal
4:36
to all construct here) among
4:38
the GPAs of the
4:40
students who applied to that same college.
4:43
Okay?
4:44
You might want to pause
4:45
the video and just make sure
4:46
you understand what the query is doing.
4:48
But again and what it's going to
4:49
return is colleges with the highest GPA among their applicants.
4:53
Let's run the query, and we
4:55
see that most colleges,
4:57
all the colleges in fact, have
4:59
a applicant with a 3.9 GPA.
5:01
That happens to be the
5:02
highest in the database, and all
5:04
of the colleges have an applicant with that GPA.
5:06
Now we see some duplicates here.
5:08
If we don't like duplicates, we use
5:09
our usual trick of putting
5:12
distinct, and now we
5:14
have our four colleges in
5:15
our database and the highest GPA of their applicants.
5:20
Now let's see how we
5:21
can write this query using a sub-query in the select clause.
5:25
What a sub-query in a select
5:26
clause does is performs a
5:28
computation and as long
5:29
as that computation returns exactly one
5:31
value, that value is used in the result tuple.
5:35
So let me just start typing here a little bit.
5:37
What we're going to do here
5:38
is we're going to select
5:39
the college name and state
5:42
from the college.
5:43
Let me just pull this down here.
5:47
from the college, and then
5:48
we're going to use a modified
5:50
version of this query to
5:51
compute the highest GPA for that particular college.
5:55
So we're going to have college name,
5:56
state, and here comes the
5:58
sub-query in the from clause.
6:00
So let's just put
6:02
parentheses around there, okay?
6:04
And we're going to in
6:07
the sub-query just compute the
6:08
GPA, and it's going to be
6:09
the highest GPA for this college.
6:12
So we don't need to include college in our from list.
6:15
And now we have college here,
6:17
and the rest of this is the same query precisely.
6:20
The last thing we're going to
6:21
do is label this attribute as GPA.
6:25
And now we're all set.
6:26
You may want to pause
6:27
the video and look carefully to make
6:29
sure you understand how the query works.
6:31
But again, it's taking the...for each
6:33
college the name, the state, and then finding the highest GPA.
6:36
So let's go for it.
6:37
We run the query and indeed we get the results.
6:40
It happens to have sorted differently
6:42
then last time,but it is exactly the same result.
6:45
Now let's suppose that
6:47
instead of wanting to pair
6:48
every college with the highest
6:50
GPA of the applicants, we wanted
6:52
to instead pair the colleges
6:53
with the names of the applicants.
6:55
So we can do something similar.
6:56
We have the college, name, and state.
6:58
And then in the sub-query
7:00
in the from clause, we'll find
7:01
the names of the students who applied to that college.
7:05
So everything looks pretty much
7:06
the same except we don't
7:07
need this big sub-query, this big portion here.
7:13
We write it like this.
7:15
We'll call it the student name.
7:18
And that looks pretty similar.
7:19
Again, we've got the college,
7:21
name, state, and then all the students who applied to that college.
7:24
We run the query and we get an error.
7:26
The error was expected, and I'm using it to illustrate a point.
7:30
When you write a sub-query in
7:31
the select clause, it's critical
7:33
that that sub-query return exactly one
7:35
value, because the result
7:37
of that is being used to fill in just one cell of the result.
7:41
In our earlier example, we were
7:42
computing the highest GPA for
7:44
each college and there's exactly one of those.
7:46
In this case, we have several
7:48
students applying to colleges, so
7:50
we wouldn't know, when we get
7:51
a bunch of values in the
7:52
result, which one to put in the tuple that's being constructed.
7:56
And in fact, our result here says
7:57
subquery returns more than
7:59
one row, and that's the error.