Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Welcome back to Module six. This is less than 6.3 aggregation queries. Now aggregation queries are simply queries to include the aggregation functions in some form or capacity.
00:10
Let's go in and walk through these bullet points. Aggregation queries gather relevant and interesting data from the data trees, much like many other
00:17
queries, do their the basis of sequel reporting. So there's are a lot of applications like Crystal reports, Power Bi I and a dozen others where you can write sequel code. You can take that code copy and paste it into the application, and that application will automatically render some nifty reports and charts for you, which allows you to avoid having to write
00:37
complex application code that would generate those same charts and reports,
00:41
because without those types of applications, that would be a requirement.
00:45
So what? That said, we're going to walk through the fallen query. We're going to generate a query that will be the teachers with student count. We want to exclude students with a low average score, so we want students with 90 or higher, and we want to count the number of high performance students each teacher has
01:02
now, with that said, Let's go ahead and head over to the development environment.
01:06
Welcome back to the development environment. There are a couple fixes to the database files we need to make to really optimize the experience with writing this query.
01:14
What that said, Let's take a look at these files and the grade factory,
01:19
which is in the Factories folder.
01:22
I have changed the minimum from 0 to 65 which means the great will have a minimum score 65 a maximum of 100 which makes it more likely that a grade or a student will have a higher average score
01:36
in the database. Cedar file. I have implemented a high number of user courses and a high number of grades 400 to be precise, and I've done that just again to make it more likely that I generate a student that has that has a higher average.
01:51
Then, in the user model class, which isn't in the APP folder,
01:56
we need to fix the random not enrolled course function.
01:59
The UC dot course I d underscore. I d was originally you see dot i d what? You need to switch it. To
02:07
course, underscore. I D, and that will help you avoid an air you might have seen where the database cedarwood try to insert a user into a course in which they were already enrolled.
02:16
Changing this to the course underscore I d. Will fix that issue.
02:22
And once you've implemented those changes, be sure to rerun your database cedar. Otherwise, those changes would be reflected in your database.
02:29
Let's go ahead and take a look at the query requirements and work through the query we need to write.
02:35
So we need teachers with student count. We want to include students with a lower average score, so we want students with a nine year higher
02:42
count the number of high performance students each teacher has.
02:46
So a good way to approach these problems is to break it apart and look at the different data points that you need. We're going to need the teachers.
02:53
We're gonna need the students with the grades
02:55
and we're gonna need teachers who have taught specific students. So this will look like a pivot table in between the grades and students and the teachers table
03:06
so that we can connect that relationship together.
03:08
So let's go ahead and generate the grades because I think that would be a little bit easier.
03:15
So let's select star
03:17
from users
03:21
when alias that with the U
03:23
And then I'm gonna join
03:29
grades on Alias that with a G
03:32
put on right there.
03:36
Tab on G. That school idea
03:42
you dot school i d.
03:44
We got to get these connections right. Otherwise, will emerge data that we don't intend emerge
03:49
Angina user idea
03:52
equals you died I d.
03:55
Let's go out and run. That's what that looks like.
04:00
All right,
04:01
let's go ahead and minimize the data that we're bringing back a little bit. So you died.
04:05
I
04:06
you dot i d
04:10
And let's bring back a name you dot
04:14
just We'll just do the first name
04:16
on then g dot grade score
04:20
and let's put an average behind that
04:24
or let's put that in an average
04:29
as average,
04:33
then we need a group by these,
04:36
but we actually want to keep the school idea in there, too. So let's do that. You school idea.
04:42
It's part of our composite primary key.
04:46
So
04:50
the group by
04:56
All right, so we got some students with some averages,
05:00
unless use are having claws.
05:04
This is how you do a filter with an aggregate function.
05:10
So having
05:12
there's no e in that
05:19
greater than
05:20
for equal to 90.
05:24
Let's go ahead and run that
05:28
oh
05:30
greater than or equal to. Not evil too.
05:33
So there are high performing students,
05:36
so we could put that into brackets like so
05:40
which kind of makes it a derive table from a query or a sub query?
05:45
If I select star from it
05:47
and I going to give it on Alias with HS for High Student,
05:53
I run this.
05:56
I need the key word from
06:00
I run this.
06:03
Okay,
06:05
so now we need the students. So let's start working on that.
06:11
Get rid of the selection. Gonna leave this down here as its own table so that I can connect it up later.
06:16
So let's let's get the teachers. It's good. The teachers with the courses that they thought so Select star
06:25
from
06:29
user courses,
06:30
you see,
06:35
actually, we're gonna join
06:39
during user types
06:41
U t
06:43
on.
06:44
You see that?
06:46
Use the type I d equals u t dot i d
06:50
We're gonna add in a filter where u t dot title
06:56
people's teacher
06:58
so you could avoid adding the user types table if you memorized the i d of the types that were on that table. However, as a query gets more complicated, it's very useful to include the titles so that when you go back and you have to work with the query, you immediately know what you're getting. For example, we know what teacher means intuitively,
07:16
but we may not know what one is.
07:20
Okay, so we have our teachers. It's going to run this query, Gonna highlight it and hit control. Inner NDB vor.
07:29
So this is all my teachers.
07:31
So let's go ahead and minimize this query a little bit. So you see that school I d. We're gonna need that.
07:39
See that
07:41
user? I D.
07:43
And
07:46
that course idea
07:46
And let's group by that.
07:56
All right, let's run that.
07:59
So now we have a list of the schools with the user and the course they taught.
08:05
So let's go to put this in brackets and will make this another table,
08:11
and we'll go ahead and alias this with
08:16
Let's see him
08:18
t c for teacher course.
08:22
But we don't have the bridge we need across
08:26
to get from teacher course to the grades
08:30
so Let's get a student course table as well.
08:35
So it's almost gonna be exactly like this with one big difference
08:39
when alias that within SC
08:41
and change this to students.
08:50
So let's select star
08:54
from
08:56
teachers
08:58
T. C.
09:01
We're gonna join
09:05
this table SC
09:09
on
09:13
See that
09:13
school? I d
09:18
equals t See that school idea
09:24
and a seed? Of course I d
09:31
equals t See that course I d.
09:37
Let's go ahead and run this to see what this looks like.
09:41
So what do we have here we have at school? One
09:46
user I D 51 which is probably the teacher. We're gonna label that so that it's more clear. In course, 20
09:54
taught student 42
09:56
in course. 20. So let's go ahead and minimize what we're getting back a bit.
10:01
So let's say SC, that school i d.
10:05
So let's do t c dot school idea.
10:11
That's the school,
10:13
and we'll say TC, that user i d.
10:18
As a teacher, i d.
10:24
And then we'll say TC that
10:28
actually it's to SC that
10:33
user i d. As a student, I d.
10:39
Was that everything we need? Let's go in and run this query, see what it looks like
10:43
So we got school ideas at school. One teacher, 51 taught 40 student, 42 in some course.
10:58
All right,
11:01
so now we just need to connect this
11:05
to our grades table.
11:07
I wonder if our group by would do anything.
11:11
I don't think we're getting any duplicates, but we'll be able to see
11:16
if we are
11:20
by grouping by and seeing if our record count changes. But I don't think it's going. Teoh.
11:26
Yes, you've got 100 32
11:28
group by
11:31
131. So okay, there was one duplicate in there,
11:33
so we'll leave that group by statement statement in there
11:39
because we don't want any duplicates.
11:43
So let's turn this into another derive table
11:48
with the drive table or sub query within. A sub query within a submarine will say
11:52
Call this one teacher student.
11:58
Oh, well, just alias it with, uh,
12:01
ts for teacher student.
12:03
What's going to select star from it?
12:09
Let's run this. Make sure it's still working.
12:11
Okay, it is still working.
12:15
Now we have our grades down here, so let's join this.
12:20
So
12:22
I need the key word. Join here
12:28
on
12:30
a chest, a school idea. We wanna make sure we have them at the same school
12:33
equals siesta school idea.
12:39
And
12:43
I see a chest that
12:46
I d. Which is the user I d
12:50
equals.
12:54
He s got
12:56
student I d.
13:03
So let's make sure we get some data back from here.
13:09
All right?
13:09
So at school, i d three teacher 18 taught student 1991
13:16
whose first name is Fritz and has an average of 96 74
13:22
at the same school teacher i d 37 taught student i d 91
13:26
the same data, which is what we would expect to see.
13:33
So
13:33
this is actually getting closer to what we want, but it's not quite there yet,
13:39
so let's select what we want. Want
13:43
t c dot school idea
13:48
on. We want t see
13:50
that teacher I d
13:54
on. Then we want
13:58
you see that student I d
14:03
on. Then we want, um, close. The last one hs
14:09
h s that
14:11
first name
14:15
and then hs dot
14:16
average.
14:20
All right.
14:24
And that
14:26
All right, I got that wrong. So I think it's TS, isn't it
14:31
t s? Yes.
14:33
Yes. Normally, we didn't go over table expressions yet, so I'm not using table expressions. But normally I would use table expressions on a
14:41
query like this because it keeps things cleaner and more segregated, so you can more easily tell where things are.
14:50
All right, so now we wanted to count the number of high performance students that a teacher had.
14:56
So we're going to go one more layer up
15:03
and we're going to call this
15:07
Ah, teacher student High score.
15:11
So select star
15:13
from
15:16
it's good and highlight this and make sure this runs
15:20
and it still does.
15:22
So now this is where we're going to
15:28
do our accounts.
15:33
We're gonna get rid of this. Gonna put the count function around the student i d.
15:39
And then we're going to group by
15:43
these columns,
15:48
which is thescore idea in the teacher idea. Let's go in and run this
15:52
starting into a pretty gnarly quarry.
15:58
See what happened. I don't call him
16:00
just that school. I d Did I do the same thing I did before?
16:07
Yes, I did. It's TSH.
16:15
Now let's run that.
16:19
And here we go. We got our teacher. I ds with our student counts.
16:25
So at this point, if I needed to, if this was data that was interesting, and I needed expected to use it in other places. I would probably make this view so that I would not have to deal with writing this or embedding it in other queries.
16:38
So now we have a teacher I d. With account of students there high performance.
16:42
She's here 37 wins because he has too high performance students.
16:48
So that completes this query. Let's head over to the summary. I know this lesson is getting long,
16:53
and that brings us to the 6.3 summary. So what did we do in this lesson we implemented in the aggregation query? And that query contained the following elements. It had a having clause, had a group by a clause and, of course, had an aggregate function or two.
17:10
So that completes this lesson, and I hope to see you 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

Instructor Profile Image
Kitt Parker
Instructor