Advanced SQL - Inner Join, Group By & Order By

 

Inner Join, Group By & Order By

Addition to the post, "Student Information", we will do some advanced tasks with the database:

Query the database for tuples of the form (StudentName, N, GPA) where N is the number of courses that the specified student is in and 'GPA' is the grade point average of the specified student according to the following point system.

                                                A    = 4.0    B  = 3.0      C  = 2.0     D   = 1.0

                                                A-  = 3.7     B- = 2.7     C- = 1.7     D-  = 0.7

                                                A+ = 4.0     B+ = 3.4    C+ = 2.4    D+ = 1.4

Order the results from greatest GPA to least.

To accomplish the tasks, we will use Inner Join, Group By, and Order By with each explanation.

1. Inner Join:

There are two types of join: Inner Join and Left Outer Join. I have more explanation about Left Outer Join int the post titled, "Advanced SQL - Lefter Outer Join & Wild Cards". 

But, basically, the most standing difference between them shows after you join them. Inner Join creates a combined table without showing NULL values. On the other hand, Left Outer Join shows NULL values.

Thus, when you combine StudentInfo table and MajorInfo table on MajorID using Inner Join, you see below:

2. Group By:

You can group rows from a table on the values in one or more columns using Group By. It's usually used in conjunction such as COUNT, SUM, AVG, and MAX. Let's say we want to count how many students shows up in StudentGrades table using Group By. Then we can see this result:

3. Order By:

When you query a result and you want them to be in order, we use Order By. For example, we want to order the list above by the number of courses each student is in, then alphabetically by student name. After using Order By, this is the result that shows up:

You can see that the order is slightly changed.

4. Application:

Finally it's the time to apply those concepts into our code.

First thing first. The final values we want are the student names, how many courses each students are in, and the average GPA of each student. So, those values go after SELECT. Next, we use WHEN clause for CASE.  It's the same concept as CASE and SWITCH in C++. Since we are finding all these values from a inner joined table of studentGrades and studentInfo, we will put them after FROM. Because we want to count the number of courses, we use Group By. Lastly, we will use order by because we want to order GPA in descending order:

After running the code, this is the result we can find:

*GitHub: https://github.com/KwakSukyoung/coding/blob/master/ACME/SQL2/sql2.py