0% completed
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
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.
- Perform a Cross Join: Performing a cross join between the
Students
andCourses
tables to generate all possible combinations of students and courses. - Left Join with Grades Table: Perform a left join with the
Grades
table to incorporate the grade information for each student-course combination. - Calculate Average Grade: Utilize the
AVG()
function and appropriate grouping to compute the average grade obtained by each student in each course. - Handle Null Values: Use the
COALESCE()
function to handle cases where a student has no grades for a particular course. - Order the Results: Arrange the result table by
student_id
andcourse_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 | +------------+--------------+--------------+----------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible