Exam Marks to University Grade Letters

Easy
128.

Exam Marks to University Grade Letters

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

ColumnType
roll_noint
subject_codevarchar
marksint

Primary key (together): roll_no, subject_code.

marks is a whole number from 0 to 100, or NULL when the student was absent from the exam.

Example

The rows must come back in the order shown.

ExamResult

roll_nosubject_codemarks
2101MA10192
2101PH10280
2102MA10139
2102PH102NULL
2103MA10140
2103CS10369
2104CS10375
2104MA10155

Output

roll_nosubject_codegrade
2101MA101O
2101PH102A+
2102MA101F
2102PH102AB
2103CS103B+
2103MA101C
2104CS103A
2104MA101B
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
roll_noint
subject_codevarchar
marksint

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_nosubject_codemarks
2101MA10192
2101PH10280
2102MA10139
2102PH102NULL
2103MA10140
2103CS10369
2104CS10375
2104MA10155

Output

roll_nosubject_codegrade
2101MA101O
2101PH102A+
2102MA101F
2102PH102AB
2103CS103B+
2103MA101C
2104CS103A
2104MA101B

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_code

Another 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_code

Another 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 →