Common Table Expressions with Aggregations

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10
Video Transcription
00:00
I welcome back to module six. This is less than 6.5 c t. With aggregations common table expressions with aggregation queries. They're great for complicated queries. They help separate concerns among the table expression
00:13
and in this lesson, going to rewrite a previous query we wrote using derived tables, except we're going to use common table expressions instead. Now, with that said, let's go ahead and head over to the development environment.
00:27
Welcome back to the development environment. I have that previous query that we wrote that which uses a bunch of derived tables. This is the query we want to rewrite using common table expressions. So let's go ahead and get started.
00:39
What I had and hit enter a few times to get below that query
00:43
and we'll start the table expression, syntax with
00:47
se.
00:49
Start with teachers with teacher
00:52
courses.
00:55
As
00:58
so I'm gonna take this piece out here
01:03
and paste it in there.
01:07
I'm going to a comma on do student
01:11
courses
01:15
as
01:19
on I'm gonna take this part,
01:23
paste it in here.
01:26
So by doing this, we're actually gonna have gonna loot, gonna lose some of those joints that we had to use and between the tables, which will just again make it a little bit easier to work with, because we'll be less
01:38
stuff going on in it.
01:41
So what else do we have here? So we got the students table teachers. Let's get the grades of the students.
01:47
Actually, let's merge these two, uh, common table expressions together first. So
01:53
well, say, teacher
01:56
underscores student,
01:57
of course
02:00
courses as
02:09
and here we're going to join the two previous table expressions. So if select star
02:16
from's
02:17
teacher courses alias that with a T. C.
02:23
Well, say join
02:25
student
02:28
courses
02:29
S C.
02:31
The key word on there.
02:36
Well, say TC
02:38
that school
02:43
I d equals S C that school I d.
02:46
That's part of that composite primary key. So that's why we constantly keep that in there.
02:51
And
02:53
TC that course I d
02:57
equals s c dot course I d.
03:05
And we're gonna limit the columns a little bit so we don't get the same stuff back
03:09
school. I d
03:14
se course I d
03:17
on a
03:21
teacher. I d
03:25
and CSC dot student I d.
03:31
Let's go out and run this and make sure that it's working the way we expect it to.
03:38
Well, that's gonna be wrong. So well, that's going to fix that.
03:40
We had a group by just to make sure I don't not getting any duplicates.
03:52
So select star from
03:57
teacher
03:59
student
04:00
courses
04:03
Highlight that and run it.
04:05
So we have a at school i d one course i d 20 Teacher, 51 taught student 42. Okay,
04:14
that's what I expect.
04:15
And I'm sure you guys picked up on this, but
04:17
this is derived from the user i D. And though it's called student I d here, that's simply because we a liest the user. I d. As student I d. On this common table expression here. And the reason we are doing that is, of course, to keep the two ideas separate.
04:36
We have a user that can be a teacher. We have a user that could be student,
04:40
and when we drive a table by the common table expression, we want to make sure that we know what When we were talking about the teacher, I d versus the student i D. Based on what role the student is playing in that pivot table that is called user underscore courses.
04:55
So this is working so far
04:57
So let's go and go ahead and tie in the grades table. So comma
05:02
se student grades
05:06
as
05:10
brackets in there,
05:13
and I'm gonna go ahead and
05:18
cut that out.
05:20
Put that here.
05:25
Okay, so now we should be able to tie these grades in.
05:30
You enjoin
05:35
student grades
05:39
s t
05:42
on
05:43
Have that over.
05:45
So we're gonna Well, let me give this nail is so given an alias of T s c.
05:51
So TSC dot school i d
05:57
it was SG dot school already.
06:01
Be careful with those relationships that you're forming between your tables there because obviously, if you get those incorrect in some capacity, like forgetting the school, I d. You're gonna get weird looking results.
06:15
Okay? So school I d and
06:19
school idea and student I d. Okay, so we need TSC. That student I d
06:27
equals.
06:30
Has she got student? I d.
06:33
Let's go ahead and run this portion of it. Now. I think I have enough out of this area. I'm just gonna get rid of it
06:40
so I don't have to go through the trouble of avoiding it.
06:45
Okay, so I got their names and their grades,
06:50
their schools.
06:53
So, in course teacher taught teacher 37 Top student 33 of course. 15.
07:00
And that student currently has a 92 average.
07:04
We'll see you.
07:06
Okay.
07:09
All right. We're getting somewhere. So TSC that school i d
07:15
and TSC dot
07:18
teacher i d
07:24
And we're gonna go ahead and group everything together here so that we don't have to go and implement another table expression
07:30
right now at the very bottom of this table expression chain. I feel like this is pretty easy to work with, So I'm gonna go ahead and do my grouping down here.
07:40
So a teacher, I d And we need to
07:43
count
07:45
the seed out. Student I d.
07:47
I'll tell us how many
07:50
high performing students
07:53
to each teacher in each school that we have.
07:55
So I need a group by these, of course.
08:01
Group by.
08:05
Go ahead and run this
08:07
on. These are the numbers I pretty much expected to see so we can see that at school. I d three teacher 80 37 has to hyper performance students
08:16
and the rest just have one.
08:20
Okay,
08:22
so we've successfully converted that previously derived table query to a table expression equivalent and let's walk through what's going on here real fast. So we're starting our table expression chain up here at the top,
08:35
and at first we describe a table as teacher underscore courses,
08:41
and this is the query that makes up that table.
08:43
Then we have a student courses, and this is the query that makes up that table.
08:48
So it's great to think of these common table expressions as tables when you're working with them as you go through the query
08:56
on. Then we joined the are two common table expressions above to create a
09:01
teacher student course table,
09:05
which would show which teachers taught which students in which course.
09:11
Then we brought in the student grades.
09:13
Now, in this query, we again use the fact that somebody had grades to just make the assumption that they are a student so Onley students would have grades, I would think, and we labeled that common table expression student grades
09:28
and then at the very bottom. We achieved our goal and we tied together the student, the teacher, student courses and student grades, and we did a group by with account to expose how many high performance students
09:41
are associated with each teacher,
09:43
so that completes this lesson, this portion of the lesson. So let's head over to the summary and finish less now.
09:50
And that brings us to the 6.5 summary on this lesson. We implemented the previous aggregation quarry that had a lot of Dr tables in it with table expressions.
10:01
Table expressions are generally easier to read, which in turn makes them easier to work with.
10:07
So that completes this lesson, and I want to see in the next thank you.
Up Next
Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By