Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
2. Student Course Averages (Easy)
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Problem Statement

Table: Students
Each row in this table represents a student enrolled in courses, providing their unique ID and name.

+---------------+---------+
| Column Name   | Type    | 
+---------------+---------+
| student_id    | int     | 
| student_name  | varchar |
+---------------+---------+
student_id serves as the primary key for this table.

Table: Courses
Contains details about the courses offered in the school, including the name of each course.

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| course_name  | varchar |
+--------------+---------+
course_name acts as the primary key for this table.

Table: Grades
Holds information about the grades obtained by students in various courses.

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| course_name  | varchar |
| grade        | int     |
+--------------+---------+

Problem Definition

Write a SQL query to calculate the average grade obtained by each student in each course they are enrolled in.

Return the result table sorted by student_id and course_name.

Example

Input:

Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+
Courses table:
+--------------+
| course_name  |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+
Grades table:
+------------+--------------+-------+
| student_id | course_name  | grade |
+------------+--------------+-------+
| 1          | Math         | 90    |
| 1          | Physics      | 85    |
| 1          | Programming  | 95    |
| 2          | Programming  | 88    |
| 1          | Physics      | 82    |
| 1          | Math         | 88    |
| 13         | Math         | 78    |
| 13         | Programming  | 92    |
| 13         | Physics      | 80    |
| 2          | Math         | 85    |
| 1          | Math         | 91    |
+------------+--------------+-------+

Output:

+------------+--------------+--------------+----------------+
| student_id | student_name | course_name  | average_grade  |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 89.67          |
| 1          | Alice        | Physics      | 83.50           |
| 1          | Alice        | Programming  | 93.50           |
| 2          | Bob          | Math         | 85.00          |
| 2          | Bob          | Physics      | null           |
| 2          | Bob          | Programming  | 88.00          |
| 6          | Alex         | Math         | null           |
| 6          | Alex         | Physics      | null           |
| 6          | Alex         | Programming  | null           |
| 13         | John         | Math         | 78.00          |
| 13         | John         | Physics      | 80.00          |
| 13         | John         | Programming  | 92.00          |
+------------+--------------+--------------+----------------+

In this example, Alice's average grade in Math is (90 + 88 + 91) / 3 = 89.67, in Physics it's (85 + 82) / 2 = 83.5, and in Programming it's (95) / 1 = 95. Bob's average grade in Math is (85) / 1 = 85.00, in Physics it's null since he has no grades in Physics, and in Programming it's (88) / 1 = 88.00. Alex had no grades, so his average grades are null for all subjects. John's average grade in Math is (78) / 1 = 78.00, in Physics it's (80) / 1 = 80.00, and in Programming it's (92) / 1 = 92.00.

Try It Yourself

MYSQL
MYSQL
. . . .

Solution

To solve this problem, we need to calculate the average grade obtained by each student in each course. We can achieve this using SQL queries to filter and aggregate the necessary data.

  1. Perform a Cross Join: Performing a cross join between the Students and Courses tables to generate all possible combinations of students and courses.
  2. Left Join with Grades Table: Perform a left join with the Grades table to incorporate the grade information for each student-course combination.
  3. Calculate Average Grade: Utilize the AVG() function and appropriate grouping to compute the average grade obtained by each student in each course.
  4. Handle Null Values: Use the COALESCE() function to handle cases where a student has no grades for a particular course.
  5. Order the Results: Arrange the result table by student_id and course_name for clarity.

SQL Query

The SQL query to accomplish the task is as follows:

SELECT 
    s.student_id,
    s.student_name,
    c.course_name,
    ROUND(COALESCE(AVG(g.grade), NULL), 2) AS average_grade
FROM 
    Students s
CROSS JOIN
    Courses c
LEFT JOIN 
    Grades g ON s.student_id = g.student_id AND c.course_name = g.course_name
GROUP BY 
    s.student_id, s.student_name, c.course_name
ORDER BY 
    s.student_id, c.course_name;

Step by Step Approach

Step 1: Perform a Cross Join

Start by performing a cross join between the Students and Courses tables to generate all possible combinations of students and courses.

SELECT 
    s.student_id,
    s.student_name,
    c.course_name
FROM 
    Students s
CROSS JOIN
    Courses c;

Output After Step 1:

+------------+--------------+--------------+
| student_id | student_name | course_name  |
+------------+--------------+--------------+
| 1          | Alice        | Math         |
| 1          | Alice        | Physics      |
| 1          | Alice        | Programming  |
| 2          | Bob          | Math         |
| 2          | Bob          | Physics      |
| 2          | Bob          | Programming  |
| 6          | Alex         | Math         |
| 6          | Alex         | Physics      |
| 6          | Alex         | Programming  |
| 13         | John         | Math         |
| 13         | John         | Physics      |
| 13         | John         | Programming  |
+------------+--------------+--------------+

Step 2: Left Join with Grades Table

Perform a left join with the Grades table to incorporate the grade information for each student-course combination.

SELECT 
    s.student_id,
    s.student_name,
    c.course_name,
    g.grade
FROM 
    Students s
CROSS JOIN
    Courses c
LEFT JOIN 
    Grades g ON s.student_id = g.student_id AND c.course_name = g.course_name;

Output After Step 2:

+------------+--------------+--------------+-------+
| student_id | student_name | course_name  | grade |
+------------+--------------+--------------+-------+
| 1          | Alice        | Math         | 90    |
| 1          | Alice        | Physics      | 85    |
| 1          | Alice        | Programming  | 95    |
| 2          | Bob          | Math         | 85    |
| 2          | Bob          | Physics      | NULL  |
| 2          | Bob          | Programming  | 88    |
| 6          | Alex         | Math         | NULL  |
| 6          | Alex         | Physics      | NULL  |
| 6          | Alex         | Programming  | NULL  |
| 13         | John         | Math         | 78    |
| 13         | John         | Physics      | 80    |
| 13         | John         | Programming  | 92    |
+------------+--------------+--------------+-------+

Step 3: Calculate Average Grade

Calculate the average grade for each student-course combination using the AVG() function.

SELECT 
    s.student_id,
    s.student_name,
    c.course_name,
    AVG(g.grade) AS average_grade
FROM 
    Students s
CROSS JOIN
    Courses c
LEFT JOIN 
    Grades g ON s.student_id = g.student_id AND c.course_name = g.course_name
GROUP BY 
    s.student_id, s.student_name, c.course_name;

Output After Step 3:

+------------+--------------+--------------+----------------+
| student_id | student_name | course_name  | average_grade  |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 89.67          |
| 1          | Alice        | Physics      | 83.50          |
| 1          | Alice        | Programming  | 93.50          |
| 2          | Bob          | Math         | 85.00          |
| 2          | Bob          | Physics      | null           |
| 2          | Bob          | Programming  | 88.00          |
| 6          | Alex         | Math         | null           |
| 6          | Alex         | Physics      | null           |
| 6          | Alex         | Programming  | null           |
| 13         | John         | Math         | 78.00          |
| 13         | John         | Physics      | 80.00          |
| 13         | John         | Programming  | 92.00          |
+------------+--------------+--------------+----------------+

Step 4: Handle Null Values

Initiate the process by selecting the student_id, student_name, and course_name from the respective tables, along with the average grade obtained.

SELECT 
    s.student_id,
    s.student_name,
    c.course_name,
    ROUND(COALESCE(AVG(g.grade), NULL), 2) AS average_grade
FROM 
    Students s
CROSS JOIN
    Courses c
LEFT JOIN 
    Grades g ON s.student_id = g.student_id AND c.course_name = g.course_name

Output After Step 4:

+------------+--------------+--------------+----------------+
| student_id | student_name | course_name  | average_grade  |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 89.67          |
| 1          | Alice        | Physics      | 83.50          |
| 1          | Alice        | Programming  | 93.50          |
| 2          | Bob          | Math         | 85.00          |
| 2          | Bob          | Physics      | null           |
| 2          | Bob          | Programming  | 88.00          |
| 6          | Alex         | Math         | null           |
| 6          | Alex         | Physics      | null           |
| 6          | Alex         | Programming  | null           |
| 13         | John         | Math         | 78.00          |
| 13         | John         | Physics      | 80.00          |
| 13         | John         | Programming  | 92.00          |
+------------+--------------+--------------+----------------+

Step 5: Order the Results

Finally, arrange the output by student_id and course_name for better readability. We achieve this by adding an ORDER BY clause at the end of the query.

SELECT 
    s.student_id,
    s.student_name,
    c.course_name,
    COALESCE(AVG(g.grade), NULL) AS average_grade
FROM 
    Students s
CROSS JOIN
    Courses c
LEFT JOIN 
    Grades g ON s.student_id = g.student_id AND c.course_name = g.course_name
ORDER BY 
    s.student_id, c.course_name;

Final Output:

+------------+--------------+--------------+----------------+
| student_id | student_name | course_name  | average_grade  |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 89.67          |
| 1          | Alice        | Physics      | 83.50          |
| 1          | Alice        | Programming  | 93.50          |
| 2          | Bob          | Math         | 85.00          |
| 2          | Bob          | Physics      | null           |
| 2          | Bob          | Programming  | 88.00          |
| 6          | Alex         | Math         | null           |
| 6          | Alex         | Physics      | null           |
| 6          | Alex         | Programming  | null           |
| 13         | John         | Math         | 78.00          |
| 13         | John         | Physics      | 80.00          |
| 13         | John         | Programming  | 92.00          |
+------------+--------------+--------------+----------------+

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible