Project · SQL and Databases
Project: a college results report
The brief
The exam cell of an engineering college has first-semester marks in one table. Build the full results report: absentees, subject grades, totals and percentages, backlogs, subject and branch analysis, and each student's final class.
Why it matters
Every college exam cell, coaching institute and school board produces this report each semester, and data analyst interviews love it. It uses only one table, but every beginner skill: filters, CASE, NULL handling, GROUP BY and HAVING.
Skills you practise
- WHERE and IS NULL
- CASE grades
- COALESCE
- GROUP BY and ORDER BY
- HAVING
- conditional aggregation
- ROUND and percentages
What you will have at the end
A results report in seven SQL files (absentees, grades, totals, backlogs, subject and branch analysis, final class) that an exam cell could run every semester.
How it is marked (rubric, 100 points)
| Correct resultsAll seven checks pass, and absent students are never graded F or counted as having sat the paper. | 45 |
|---|---|
| NULL handled on purposeIS NULL, COALESCE and COUNT(column) are used where they change the answer, and you can say where. | 20 |
| Readable SQLOne clause per line, clear aliases (total, percent, backlogs) and a comment on each file. | 15 |
| Report write-upA short note for the principal: who needs a re-exam, which subject was hardest and which branch did best, with the numbers. | 20 |
0/7 steps done
Step 1 of 7
The absentee list
01-absent.sql lists the roll number, name and subject of every exam a student missed (marks is NULL).
The results table has one row per student per subject: roll_no, name, branch, subject and marks out of 100. When a student was absent, marks is empty (NULL). The exam cell sends the absentee list to heads of department first. Remember that NULL is never equal to anything, so use IS NULL.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.