[SQL] HackerRank: The Report

Question

Grade the students and don’t display the name of the student who grades are below 8, use "NULL" as their name instead

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true
(What a mean teacher, you don’t wanna know the name below 8 but need a full list?)

Solution

1. Draft version

SELECT 
case 
    when marks >= 70 then  name
    else null
end as new_name,  
case 
    when marks <=9  then 1
    when marks >9 and marks <=19 then 2
    when marks >19 and marks <=29 then 3
    when marks >29 and marks <=39 then 4
    when marks >39 and marks <=49 then 5
    when marks >49 and marks <=59 then 6
    when marks >59 and marks <=69 then 7
    when marks >69 and marks <=79 then 8
    when marks >79 and marks <=89 then 9
    when marks >89 and marks <=100 then 10
    else null
end as grade,
marks
FROM Students

I simply wrote down all the cases, followed by the grade table. This code looks a bit inefficient, but anyway, I made it.

2. Final version

SELECT 
CASE 
    WHEN marks >= 70 then  name
    else null
END AS new_name,  
CASE 
    WHEN marks <=99 then ROUND(marks/10 +1,0)
    else 10
END AS grade,
marks
FROM Students

On second thought, this case was simple (only 1 to 10), but what if I need to make a more detailed grading system? So, I tried again.

» Problem was that if students get 100 marks, then they would get a grdde of 11 (Which is out of range)

» So I just adjusted query to return 10, if the marks exceed 100.

Today I’ve Learned

• CASE syntax is placed in SELECT (it’s your new column that displayed on result)

• Multiple condition are allowed

• Data type can be changed (for example : you can grade ‘A’ instead of ‘10’ (INT TO CHAR)

SELECT
    CASE
        WHEN(Condition 1) THEN A
        WHEN(Condition 2) THEN B
        ELSE C
    END AS NewColumnName
FROM TABLE;

Any comments about query or typo is welcomed :)