easy Student Course Averages

Average grade per student per course — only courses they actually took

Students, Courses, Grades(student_id, course_name, grade). Report each student's average grade in each course they're enrolled in, sorted by student_id, then course_name. The earlier solution CROSS JOINed every student against every course, producing slots for courses a student never took (a NULL average) — but there is no enrollment table, so "enrolled in" can only mean "has a grade row". The straightforward grouped join is both correct and simpler:

SELECT s.student_id, s.student_name, g.course_name,
       ROUND(AVG(g.grade), 2) AS average_grade
FROM Students s
JOIN Grades g ON g.student_id = s.student_id
GROUP BY s.student_id, s.student_name, g.course_name
ORDER BY s.student_id, g.course_name;

Traced on the sample

student	course	grades	AVG (2dp)
1 Alice	Math	90, 88, 91	89.67
1 Alice	Physics	85, 82	83.50
1 Alice	Programming	95	95.00
2 Bob	Math	85	85.00
2 Bob	Programming	88	88.00
13 John	Math/Physics/Prog	78 / 80 / 92	78.00 / 80.00 / 92.00

Student 6 (Alex) has no grades, so they correctly do not appear. Note Alice/Programming is 95.00 (a single grade) — averaging one value returns that value.

Pitfalls

• Don't materialize every student×course with CROSS JOIN unless an enrollment table tells you who's enrolled — otherwise you invent rows for courses nobody took.
• Use an INNER JOIN so students with no grades drop out (the expected output excludes them).

Takeaways

• Group by (student, course) over Grades and AVG — no CROSS JOIN needed.
• "Enrolled in" = has a grade row, since no enrollment table exists.

---

Re-authored for correctness for this guide (the prior CROSS JOIN produced courses students never took, and an incorrect Programming average). See also: Aggregate Functions, INNER JOIN, GROUP BY.

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.

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.

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.

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.