Hey guys, in this video we will look at SQL some function. The scenario that we're presented with today is to bring about the total number of credits that each student is doing. Now to get to this information, we will have to hop through a few tables starting with our enrollments table, which is tracking each student and what class they're doing. And then the classes table contains what course is associated with that class. And then by extension, our courses table has the details of that course and the number of credits, so we'll have to skip through a few tables to get this kind of information. But then the ultimate result should be that for any one students, we should see the total number of credits that they have, considering what they have been rostered for the semester.

So based on this data that I'm working with, we can expect that Students number seven and 21, at least should have more than three credits. With our sum function, we will actually see the end result of that. So I'm going to start off and use the school dB. And then I'm going to select, I'll just start with star from enrollments. All right. And then having done that we see okay, we're getting back all the enrollments.

And then we probably want to know the names of for students. So I'm going to enter join the students table firstly. And there we're bringing back all of the students that are currently taking classes. And then we probably want the details for the class to get the details for the courses. All right, I know that's an inner join the cast is on to the enrollment, Steve, that'd be even more details, but then I'm still lacking the details for courses. So I'm going to do one more INNER JOIN where I'm going to join our Courses table.

And I'll call it co on to our classes table. And now we're selecting all of the data possible across all four tables having inner joined an inner join. So these two are inner join the two enrollments and then courses is directly inter joined onto classes. And so the end result should be that we see one record for students, six tests six, and that should be six credits, because that's three plus three, and there was another student, or we weren't that's what essentially, we should only be seeing the maximum number of credits per student as opposed to seeing the drill of 33323. So let us refine our results set by process of elimination, what is it that we expect to see? I want to see the students name So I'm going to do a con cuts.

And I'm going to take the s dot last name and concatenate that with a comma on to s dot first on the score name. And I'm going to call this one student name. And then after that, I want to see the number of credits. So I'm going to see some and what am i assuming I'm summing the number of credits, column value, so that would be in my courses table. So that's c dot number of credits. And so I'm going to execute this and I have an error here.

Because I should have said SQL apologies. So I'm going to execute that. And we see that we're only getting back one student with some. So that's wrong. And that is wrong because we left off the fundamental part of our query, which is our group by. And what we're going to group by is, or are the columns last name and first name, because remember, whatever appears in our aggregate function, that column does not get grouped.

So we're going to just group by s dot last name, comma s dot first name. And then this will actually just aggregate everybody and show us who is doing which course how many credits based on the classes that they have been enrolled in. And so there are times you may have to jump through a number of fields to get to the results, but that is just the point of how SQL plays nicely All of these keywords on statements, they play nicely together, and they help you to formulate a query that gives you the results that you need in the moment. So I hope you enjoyed this lecture. Please feel free to play around with that and have fun