MySQL + SQL · Lesson 63

Report Card Query Using SQL

Goal

Generate a report card showing each student's total, percentage and grade across subjects.

The Query

SELECT s.name,
       SUM(m.score) AS total,
       ROUND(AVG(m.score), 1) AS percentage,
       CASE WHEN AVG(m.score) >= 90 THEN 'A+'
            WHEN AVG(m.score) >= 75 THEN 'A'
            WHEN AVG(m.score) >= 33 THEN 'Pass'
            ELSE 'Fail' END AS grade
FROM students s JOIN marks m ON s.roll_no = m.roll_no
GROUP BY s.name;

Summary

JOIN + GROUP BY totals each student; AVG gives percentage.
CASE turns the percentage into a grade in the same query.