Skip to content
Tutorials Center
My learning

Topics

Lesson 110 of 125 ยท Projects

Project: School Report Cards

Build school reports with SQL. Turn grades into credit-weighted GPAs, rank students with window functions, pivot grades into report cards and flag at-risk students.

Schools run on reports: report cards for families, class rankings for awards, course statistics for department heads and, most importantly, early warnings about students who are starting to struggle. Each of these is a SQL query over the same few tables, and together they make a great final project, because they need almost everything you've learned.

In this project you'll work with the school sample database: 36 students, 10 courses, 7 teachers and three terms of grades. You'll convert percentage grades into grade points, compute credit-weighted GPAs, rank students, pivot grades into report cards, build an at-risk list and finish with a course report for department heads.

You'll use CASE, aggregates, GROUP BY, CTEs, ranking functions, LAG, pivoting, NULL handling and COALESCE.

Stage 1: explore the school

Every grade lives in enrollments, one row per student, course and term, with a grade from 0 to 100. Start by summarizing it per term, and pay attention to missing grades:

SQL Enrollments per term ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Autumn 2024 and Spring 2025 are complete: every enrollment has a grade. Autumn 2025 has 24 enrollments without a grade yet, so that term is in progress. That shapes every decision that follows. A NULL grade means "not graded yet", not zero, so it must never drag down an average. Luckily AVG and COUNT(grade) already skip NULLs, as explained in Working with NULL.

33 different students appear in each term, but the school has 36. Who's missing, and is any course empty?

SQL Students and courses with no enrollments ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Three students have no enrollments at all, and CS300, the Machine Learning Seminar, has neither students nor a teacher. Reports need a policy for such rows: the GPA list will leave out students with no grades (there's nothing to average), while a roster report should show them. NOT EXISTS is the clearest way to ask "which rows have no match?"

If you compare each enrollment's term with the student's enrolled_on date, you'll find 91 grades from terms that ended before the student supposedly enrolled. Like the sign-up dates in the sales project, that column can't be trusted as-is. These reports rely only on enrollments and terms, so the problem doesn't affect them, but it belongs in your notes for the school office.

Stage 2: grade points and GPA

The school reports a GPA (grade point average) on a 4-point scale. Each percentage grade becomes a letter and a number of points:

Grade Letter Points
90โ€“100 A 4
80โ€“89 B 3
70โ€“79 C 2
60โ€“69 D 1
below 60 F 0

A grade below 60 is a fail and earns no credits. And because a 5-credit course is more work than a 2-credit one, the GPA is weighted by credits: multiply each course's points by its credits, add them up, and divide by the total credits.

Put the mapping in one CTE, graded, so every later query uses exactly the same scale:

SQL Letters, points and GPA ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Quinn Novak leads with a GPA of 2.95, followed by Elif Garcia (2.88) and Uma Dubois (2.78). The scale is demanding: an 85 is only a B.

Two details make the query correct:

  • WHERE e.grade IS NOT NULL removes in-progress courses before the weighting. Without it, the ELSE 0.0 branch would turn every missing grade into an F, because NULL >= 90 and all the other tests are unknown, not true. That's a classic CASE trap: ELSE catches NULL too.
  • 4.0, not 4: the points are real numbers, so the division keeps its decimals. With integer points, SQLite would do integer division and round every GPA down to a whole number.

Check one GPA by hand

Formulas deserve a manual check. Pick a student with few courses and compare SQL's arithmetic with your own:

SQL Stefan Bauer's transcript ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Stefan has five graded courses with 20 credits in total. Only two passed: MATH101 (71, a C: 2 ร— 4 = 8 points) and PHYS101 (80, a B: 3 ร— 4 = 12 points). So his GPA is (8 + 12) / 20 = 1.0, and his earned credits are 4 + 4 = 8. Change the student_id and the LIMIT in the previous query to check other students the same way.

Stage 3: rankings

Awards need a class rank. Ranking functions number rows in order, and they differ only in how they treat ties:

SQL Class rank with ties ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The LIMIT 6 OFFSET 12 shows ranks 13 to 18, where the interesting part is. Kofi Tanaka and Pablo Moreau both have a GPA of 1.94:

  • ROW_NUMBER() gives them 15 and 16 anyway, in no meaningful order. Fine for paging, unfair for awards.
  • RANK() gives both 15 and skips 16, so the next student is 17th. This is how sports and most schools rank.
  • DENSE_RANK() gives both 15 and continues with 16, leaving no gap.

The GPA is rounded to two decimals before ranking, on purpose. Unrounded, Pablo is ahead (1.9394 versus 1.9375), a difference no report card would show and no student could understand. Decide what counts as a tie, then rank on exactly that value.

Rank within a homeroom

Each student belongs to a homeroom (A, B or C), and teachers want a rank inside their own group. PARTITION BY restarts the numbering for each homeroom:

SQL Top 3 per homeroom ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

This is the top-N per group pattern: rank inside a CTE, then filter in the outer query. You can't put WHERE homeroom_rank <= 3 in the same SELECT that computes the rank, because WHERE runs before window functions.

The window function here ranks aggregated rows: GROUP BY runs first, then the window's ORDER BY uses the same SUM(...) / SUM(...) expression. Students with no homeroom (NULL) form their own partition, shown as (none) thanks to COALESCE. PARTITION BY treats all NULLs as one group, the same way GROUP BY does.

Stage 4: report cards

A report card shows one student's grades with courses down the side and terms across the top. Data in enrollments runs the other way, one row per course and term, so this is a pivot: one MAX(CASE ...) column per term. Here is Nico Adeyemi's:

SQL One student's report card ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Each cell combines the number and the letter ('88 B'). Three different kinds of "empty" are kept apart: a course taken but not graded yet shows in progress; a course not taken that term shows an empty string; and a real grade shows the grade. Mixing those up is the most common report-card bug.

The pivot also reveals a story. Nico took PHYS101 twice and failed both times (51, then 42), and he repeated ART101 and raised his grade from 57 to 71. MAX works here because each student has at most one enrollment per course per term (that's the table's primary key), so each cell has exactly one value to "aggregate".

GPA by term

For counselors, the most useful pivot puts each student's term GPAs side by side, so trends jump out. ROUND(... , 2) is applied per term inside the pivot:

SQL Term GPAs side by side ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The conditional SUM(CASE WHEN ... THEN ... END) has no ELSE, so rows from other terms give NULL and are ignored. A student with no grades in a term gets NULL / NULL, which is NULL: an honest blank instead of a misleading 0. Compare two rows. Bilal Kaya's Autumn 2025 cell is NULL because none of his grades for that term have been entered. Olga Cruz's Autumn 2024 GPA is a real 0: every course she took that term ended below 60. They look similar at a glance and mean very different things, which is why the CASE has no ELSE 0.

The last column is labeled "so far" on purpose. Chen Kaya, Jonas Novak and Pablo Santos show 0 there, but each has only one Autumn 2025 grade entered, so one bad result is the whole "GPA". Remove WHERE s.homeroom = 'A' to see everyone.

Stage 5: at-risk students

The most valuable report is the one that helps a student before it's too late. Turn the counselors' rules into three yes/no flags, add them up into a risk score, and list everyone with two or more:

  • Low GPA: the GPA over the completed terms (Autumn 2024 and Spring 2025) is below 1.5.
  • Falling: the Spring 2025 GPA is at least 1.0 lower than the Autumn 2024 GPA.
  • Failing now: at least one grade below 60 has already been entered for Autumn 2025.
SQL At-risk list ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The list has nine students. Omar Esposito is the only one with all three flags: his GPA fell from 2.55 in Autumn 2024 to 0.64 in Spring 2025, and he already has a failing grade this term. Others, like Mateo Eriksen, still have a decent overall GPA but are sliding fast, which is exactly the kind of student a GPA-only list would miss.

There's a lot in this query, so take it piece by piece:

  • term_gpa computes a GPA per student and term, and LAG puts the previous term's GPA on the same row. As in Stage 3, window functions run after GROUP BY, so they can wrap aggregates.
  • flags uses FILTER (WHERE ...) to compute different aggregates over different subsets in one pass. It's standard SQL, supported by SQLite and PostgreSQL; other databases use CASE inside the aggregate.
  • COALESCE(..., 0) protects the flags from NULL. In this data every graded student has grades in both completed terms, but next year's newcomers won't. A student who joins in Spring has no previous GPA, so t.gpa - t.previous_gpa <= -1.0 is NULL. Without COALESCE, NULL + 1 + 1 is NULL, and a student with two real warning signs would silently drop out of the list. Write the query for the data you'll have, not just the data you have today.
  • The flags are comparisons, which SQLite evaluates to 1 or 0, so adding them counts how many are true. In databases with a real boolean type, wrap each flag in CASE WHEN ... THEN 1 ELSE 0 END.

The score is repeated in WHERE because a column alias can't be used there. To avoid the repetition, move the scoring into one more CTE and filter in the outer query, as in Stage 3.

An at-risk list is a tool for offering help, not a label. Rules like these are guesses that someone should review, so show why each student is on the list (the flag columns), not just a score, and keep the thresholds easy to adjust.

Stage 6: the course report

Department heads look at the same data from the courses' side: how full is each class, how did students do, and who teaches it? Here's the Spring 2025 report, the latest complete term:

SQL Course report for Spring 2025 ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Two LEFT JOINs keep courses with no teacher and courses with no Spring enrollments. The term filter e.term_id = 2 sits in the ON clause on purpose: in WHERE, it would remove the courses that had no enrollments that term, as explained in LEFT JOIN.

AVG(e.grade >= 60) is a pass rate. Each comparison is 1 or 0, and AVG ignores the NULL from courses with no rows, so CS300 gets NULL in every grade column, not a misleading 0%. CS210 (Databases) is the fullest class at 17 of 20 seats, worth knowing when planning next year.

Putting it together

Look at how far one table of grades took you:

  • Data checks first: a term in progress, three students and one course with no enrollments, and an enrolled_on column that contradicts the grades.
  • One definition of GPA in a graded CTE: letter and points from CASE, NULL grades removed before weighting, decimals preserved.
  • Rankings with RANK() for fair ties and PARTITION BY for per-homeroom lists.
  • Report cards as pivots that keep "in progress", "not taken" and real grades apart.
  • An at-risk list that combines level, trend and current failures, with COALESCE protecting every flag from NULL.
  • A course report where LEFT JOIN plus a filter in ON keeps empty courses visible.

Ideas to extend it

  • Honor roll: list students with a term GPA of 3.5 or more and no grade below 70 in that term, for each completed term.
  • Retakes policy: many schools count only the best attempt at a course. Use ROW_NUMBER() OVER (PARTITION BY student_id, course_id ORDER BY grade DESC) to keep one attempt per course, then recompute GPAs.
  • Percentiles: add PERCENT_RANK() or CUME_DIST() to the class rank and print "top 10%" labels, as in Percentiles, Medians and Statistics.
  • Teacher dashboard: one row per teacher with the number of students taught, average grade and pass rate across all their courses and terms.
  • Grade distribution: count Aโ€“F letters per course and pivot them into columns.
  • Save the definitions: create views for graded and the term GPAs so every report uses the same scale, and change the scale in one place.
  • Plus and minus grades: extend the mapping to B+ (87โ€“89) and so on, ideally as a small grade_scale table you JOIN with BETWEEN instead of a long CASE.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the graded CTE filter with WHERE e.grade IS NOT NULL?

    Every WHEN test on a NULL grade is unknown, so the ELSE branch runs. In-progress courses would count as failures and pull GPAs down.
  2. 2. Two students both have a GPA of 1.94 at positions 15 and 16. What does RANK() give the next student?

    RANK gives tied rows the same rank and skips the positions they used, so the next student is 17th. DENSE_RANK would give 16.
  3. 3. In the report card, why is MAX(CASE WHEN term_id = 1 THEN cell END) safe to use for a text value?

    The primary key (student_id, course_id, term_id) guarantees at most one non-NULL value per cell, so the aggregate simply returns it.
  4. 4. In the at-risk query, what happens to a student with no previous term if you remove the COALESCE around the "falling" flag?

    Their LAG value is NULL, so the comparison is NULL, and NULL plus any number is NULL. A NULL condition in WHERE filters the row out.
  5. 5. Why is e.term_id = 2 placed in the ON clause of the course report's LEFT JOIN?

    In WHERE, the filter would run after the join and remove the NULL-filled rows of empty courses, turning the LEFT JOIN into an inner join.

Summary

  • Explore first: find terms in progress, rows with no matches and columns that contradict each other.
  • Define grade points and GPA once in a CTE; drop NULL grades before weighting and use decimal points to avoid integer division.
  • Credit-weighted GPA is SUM(points * credits) / SUM(credits); check one student by hand.
  • RANK() handles ties fairly; PARTITION BY ranks within groups, and top-N per group needs an outer query.
  • Pivot with MAX(CASE ...) and keep "in progress", "not taken" and real values distinct.
  • Combine flags into a risk score with COALESCE, because one NULL would hide a student.

Next: SQL Cheat Sheet, a one-page reference of everything you've learned in this course.