Hey guys, in this video, we will be looking at our max and min aggregate functions. For this exercise, we start off with the activity to bring back the highest and lowest grade, or grades for each course. And so, as usual, we start off with our basic select star. And then we can assess and see what it is that we're interested in. And that would be the course and the grid. So process of elimination, we just say course, ID, and very so now we're bringing back each course and the grades that have been awarded for these courses.
And I can include a where clause to say where greed is not know. So that I can eliminate those rules that have low grade. So essentially, this sighs should see us bringing back one rule per course. And in this one rule, we should have the highest grade and the lowest grade for any particular course. So let's take for instance course, with ID one, the highest grade awarded so far is 100. And the lowest one is 35.
And so we want one frontals. That's is the course, highest period and lowest grade. As I'm sure we have grasped by know, anytime that we're going to be doing an aggregate or some mathematical function like this, we need or GROUP BY clause. So we see course ID. And because we're going to be doing an aggregate function on greed, then we don't need to include greed here in the group by so if I say Max, then that is the aggregate function that brings back the most or the highest value. One start grouping has occurred.
So if I run this, then we will see that course ID one is bringing about 100 course it to 9095 and 59. But of course, that's not all we've satisfied the requirement to bring about the highest now we need to bring about the lowest and to bring back the lowest is simply using the function mean. So mean grade. And so when it groups it by force, ID is going to say, Okay, what are all the grades Give me back the highest value and giving back the lowest value. Also, you may realize a trend that most of these aggregate functions only work with numbers because, you know, you can find the max name and the min name. But then of course, like count actually is not biased towards any data type.
Because Cohen's is literally just keeping track and presenting a number of the number of occurrences. Alright, so when we have marks In these, remember to only use number values there. And so when we run this query, we see that we have 135 9064. And so this is a nice clean way to bring back a report on each course and this highest scoring or the highest score and the lowest score. So you can go ahead and play around with min and max and as always see how fun