easy Student Course Averages
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
-- TODO: Write your user queries hereSolution
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
StudentsandCoursestables to generate all possible combinations of students and courses. - Left Join with Grades Table: Perform a left join with the
Gradestable 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_idandcourse_namefor 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 | +------------+--------------+--------------+----------------+
🤖 Don't fully get this? Learn it with Claude
Stuck on Student Course Averages? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.
Progressively stronger hints — you still solve it.
I'm working on the problem **Student Course Averages** (Databases). Give me a HINT LADDER: start with the tiniest nudge, then wait. Only reveal the next, stronger hint when I ask. Do NOT show the full solution unless I type 'show solution'. Keep me doing the thinking. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
See the technique, not just code.
Explain the optimal approach to **Student Course Averages** with a VISUAL walkthrough: trace it on a small concrete example using ASCII art / a step-by-step diagram, narrate what changes each step, then give time & space complexity with a one-line derivation. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Catch bugs, edge cases, sub-optimality.
I'll paste my solution to **Student Course Averages**. Review it for correctness, missed edge cases, and time/space complexity, then coach me toward the optimal — don't just rewrite it. Ask me to paste my code now. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Lock in recognition with look-alikes.
Give me 2 problems that use the SAME underlying pattern as **Student Course Averages**. For each, let me attempt first, then review my answer and name the trigger signal that reveals the pattern. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.