9 hours 41 minutes
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
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.
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.
What I had and hit enter a few times to get below that query
and we'll start the table expression, syntax with
Start with teachers with teacher
so I'm gonna take this piece out here
and paste it in there.
I'm going to a comma on do student
on I'm gonna take this part,
paste it in here.
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
stuff going on in it.
So what else do we have here? So we got the students table teachers. Let's get the grades of the students.
Actually, let's merge these two, uh, common table expressions together first. So
well, say, teacher
and here we're going to join the two previous table expressions. So if select star
teacher courses alias that with a T. C.
Well, say join
The key word on there.
Well, say TC
I d equals S C that school I d.
That's part of that composite primary key. So that's why we constantly keep that in there.
TC that course I d
equals s c dot course I d.
And we're gonna limit the columns a little bit so we don't get the same stuff back
school. I d
se course I d
teacher. I d
and CSC dot student I d.
Let's go out and run this and make sure that it's working the way we expect it to.
Well, that's gonna be wrong. So well, that's going to fix that.
We had a group by just to make sure I don't not getting any duplicates.
So select star from
Highlight that and run it.
So we have a at school i d one course i d 20 Teacher, 51 taught student 42. Okay,
that's what I expect.
And I'm sure you guys picked up on this, but
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.
We have a user that can be a teacher. We have a user that could be student,
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.
So this is working so far
So let's go and go ahead and tie in the grades table. So comma
se student grades
brackets in there,
and I'm gonna go ahead and
cut that out.
Put that here.
Okay, so now we should be able to tie these grades in.
Have that over.
So we're gonna Well, let me give this nail is so given an alias of T s c.
So TSC dot school i d
it was SG dot school already.
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.
Okay? So school I d and
school idea and student I d. Okay, so we need TSC. That student I d
Has she got student? I d.
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
so I don't have to go through the trouble of avoiding it.
Okay, so I got their names and their grades,
So, in course teacher taught teacher 37 Top student 33 of course. 15.
And that student currently has a 92 average.
We'll see you.
All right. We're getting somewhere. So TSC that school i d
and TSC dot
teacher i d
And we're gonna go ahead and group everything together here so that we don't have to go and implement another table expression
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.
So a teacher, I d And we need to
the seed out. Student I d.
I'll tell us how many
high performing students
to each teacher in each school that we have.
So I need a group by these, of course.
Go ahead and run this
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
and the rest just have one.
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,
and at first we describe a table as teacher underscore courses,
and this is the query that makes up that table.
Then we have a student courses, and this is the query that makes up that table.
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
on. Then we joined the are two common table expressions above to create a
teacher student course table,
which would show which teachers taught which students in which course.
Then we brought in the student grades.
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
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
are associated with each teacher,
so that completes this lesson, this portion of the lesson. So let's head over to the summary and finish less now.
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.
Table expressions are generally easier to read, which in turn makes them easier to work with.
So that completes this lesson, and I want to see in the next thank you.