Exam Marks to University Grade Letters — SQL Problem
The exam cell publishes letter grades, not marks. The university's bands are: 90 and above O, 80–89 A+, 70–79 A, 60–69 B+, 50–59 B, 40–49 C, and below 40 F.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #128
Problem statement
The exam cell publishes letter grades, not marks. The university's bands are: 90 and above O, 80–89 A+, 70–79 A, 60–69 B+, 50–59 B, 40–49 C, and below 40 F. A student who was absent has marks NULL and gets AB.
Return every result with the columns roll_no, subject_code and grade. Each band includes its lower bound (exactly 80 is A+). Order the rows by roll_no, then by subject_code.
Tables
Table: ExamResult
| Column | Type |
|---|---|
| roll_no | int |
| subject_code | varchar |
| marks | int |
Primary key: roll_no, subject_code.
marks is a whole number from 0 to 100, or NULL when the student was absent from the exam.
Examples
Example 1
ExamResult
| roll_no | subject_code | marks |
|---|---|---|
| 2101 | MA101 | 92 |
| 2101 | PH102 | 80 |
| 2102 | MA101 | 39 |
| 2102 | PH102 | NULL |
| 2103 | MA101 | 40 |
| 2103 | CS103 | 69 |
| 2104 | CS103 | 75 |
| 2104 | MA101 | 55 |
Output
| roll_no | subject_code | grade |
|---|---|---|
| 2101 | MA101 | O |
| 2101 | PH102 | A+ |
| 2102 | MA101 | F |
| 2102 | PH102 | AB |
| 2103 | CS103 | B+ |
| 2103 | MA101 | C |
| 2104 | CS103 | A |
| 2104 | MA101 | B |
How to solve Exam Marks to University Grade Letters
Mapping a number onto bands is what a searched CASE is for. CASE checks its WHEN conditions in order and returns the first one that is true, so listing the bands from the highest down lets each branch test only its lower bound: an 85 fails >= 90, passes >= 80, and stops there as A+.
NULL needs care. NULL >= 90 is unknown, not false, but CASE treats unknown like false and moves on — so without a branch of its own an absentee would reach ELSE 'F' and be failed rather than marked absent. Testing marks IS NULL first (or making AB the ELSE after every band, as the BETWEEN version does) fixes that.
Nested IF calls do the same job less readably. The query is a single scan with no grouping.
Reference solution (MySQL)
SELECT roll_no, subject_code,
CASE
WHEN marks IS NULL THEN 'AB'
WHEN marks >= 90 THEN 'O'
WHEN marks >= 80 THEN 'A+'
WHEN marks >= 70 THEN 'A'
WHEN marks >= 60 THEN 'B+'
WHEN marks >= 50 THEN 'B'
WHEN marks >= 40 THEN 'C'
ELSE 'F'
END AS grade
FROM ExamResult
ORDER BY roll_no, subject_codeAnother way
SELECT roll_no, subject_code, IF(marks IS NULL, 'AB', IF(marks >= 90, 'O', IF(marks >= 80, 'A+', IF(marks >= 70, 'A', IF(marks >= 60, 'B+', IF(marks >= 50, 'B', IF(marks >= 40, 'C', 'F'))))))) AS grade FROM ExamResult ORDER BY roll_no, subject_codeAnother way
SELECT roll_no, subject_code, CASE WHEN marks BETWEEN 90 AND 100 THEN 'O' WHEN marks BETWEEN 80 AND 89 THEN 'A+' WHEN marks BETWEEN 70 AND 79 THEN 'A' WHEN marks BETWEEN 60 AND 69 THEN 'B+' WHEN marks BETWEEN 50 AND 59 THEN 'B' WHEN marks BETWEEN 40 AND 49 THEN 'C' WHEN marks < 40 THEN 'F' ELSE 'AB' END AS grade FROM ExamResult ORDER BY 1, 2← Learner Sign-ups by E-mail Domain · Faculty Names Formatted for ID Cards →