A grade for every mark
Medium · School database · CASE, JOIN, NULL values
Give every enrollment a grade from its marks:
Afor 90 and aboveBfor 75 to 89Cfor 60 to 74Dbelow 60Pendingwhen there are no marks yet (NULL)
Return the student's name, the course name, marks and the grade, in that order. Any row order.
Expected result
| student | course | marks | grade |
|---|---|---|---|
| Aarav Sharma | Physics | 88 | B |
| Aarav Sharma | Mathematics | 88 | B |
| Priya Nair | Chemistry | 91 | A |
| Priya Nair | English | 86 | B |
| Rohan Gupta | Physics | 72 | C |
| Rohan Gupta | Mathematics | 67 | C |
| Ananya Iyer | Chemistry | 85 | B |
| Ananya Iyer | English | 92 | A |
| Kabir Singh | Mathematics | 58 | D |
| Kabir Singh | Physical Education | 90 | A |
| Meera Patel | Chemistry | 68 | C |
| Meera Patel | Biology | 75 | B |
| Arjun Reddy | Physics | 97 | A |
| Arjun Reddy | Mathematics | 82 | B |
| Sara Khan | English | 78 | B |
| Sara Khan | Economics | 73 | C |
| Vikram Rao | Physics | 64 | C |
| Vikram Rao | Mathematics | 95 | A |
| Vikram Rao | Computer Science | 98 | A |
| Fatima Sheikh | English | 78 | B |
| Fatima Sheikh | Biology | 93 | A |
| Amara Okafor | Physics | 81 | B |
| Lucas Martin | Computer Science | 66 | C |
| Tanvi Bose | Computer Science | 87 | B |
| Tanvi Bose | Economics | 91 | A |
| Ishaan Verma | Biology | 74 | C |
| Ishaan Verma | Mathematics | 95 | A |
| Diya Menon | Biology | 84 | B |
| Diya Menon | Economics | 80 | B |
| Aditya Joshi | Mathematics | 71 | C |
First 30 of 40 rows.
The school tables
courses: 10 rows, first 5 shown
| id | name | teacher_id | credits |
|---|---|---|---|
| 1 | Physics | 1 | 4 |
| 2 | Chemistry | 2 | 4 |
| 3 | Mathematics | 3 | 5 |
| 4 | English | 4 | 3 |
| 5 | Biology | 5 | 4 |
enrollments: 40 rows, first 5 shown
| id | student_id | course_id | marks | enrolled_on |
|---|---|---|---|---|
| 1 | 1 | 1 | 88 | 2025-04-07 |
| 2 | 1 | 3 | 88 | 2025-04-07 |
| 3 | 2 | 2 | 91 | 2025-04-08 |
| 4 | 2 | 4 | 86 | 2025-04-08 |
| 5 | 3 | 1 | 72 | 2025-04-09 |
students: 24 rows, first 5 shown
| id | name | gender | class | section | city | dob | |
|---|---|---|---|---|---|---|---|
| 1 | Aarav Sharma | M | 12 | A | Delhi | 2008-05-14 | aarav.sharma@example.com |
| 2 | Priya Nair | F | 12 | A | Kochi | 2008-09-02 | priya.nair@example.com |
| 3 | Rohan Gupta | M | 12 | B | Delhi | 2008-01-23 | NULL |
| 4 | Ananya Iyer | F | 12 | B | Chennai | 2008-11-30 | ananya.iyer@example.com |
| 5 | Kabir Singh | M | 12 | C | Chandigarh | 2007-12-19 | kabir.singh@example.com |
teachers: 8 rows, first 5 shown
| id | name | subject | city | joined_on |
|---|---|---|---|---|
| 1 | Sunita Mehra | Physics | Delhi | 2012-06-01 |
| 2 | Rajesh Kumar | Chemistry | Delhi | 2015-07-15 |
| 3 | Lakshmi Krishnan | Mathematics | Chennai | 2010-04-01 |
| 4 | David Thomas | English | Kochi | 2018-06-20 |
| 5 | Farah Qureshi | Biology | Mumbai | 2016-08-01 |
-- Write your SQL here