0

I have a student table, one of its column contains the grade obtained by student(eg, A,B,C...). I need to assign a number to every grade (eg, A->10, B->8, C->6 ...)

I am new to sql and I am not getting any idea on how to do it. I tried one method which I found online:

select letter_grade, case when 'A' then 4 end as lettergrade from students;

But its not working

shubham
  • 33
  • 1
  • 9
  • Possible duplicate of [Using IF ELSE in Oracle](https://stackoverflow.com/questions/14386881/using-if-else-in-oracle) – Jan Oct 25 '18 at 04:57

2 Answers2

4

You are on the right track:

SELECT
    letter_grade,
    CASE letter_grade WHEN 'A' THEN 10
                      WHEN 'B' THEN 8
                      WHEN 'C' THEN 6
                      WHEN 'D' THEN 4
                      ELSE 0 END AS number_grade
FROM students;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

An Oracle solution (which will not work in other SQL dialects, because it uses an Oracle proprietary function) is to use decode(). This function compares the first argument to the second, forth, sixth etc. and when it finds a match, it returns the argument immediately following the match. If no match is found, it returns the default entered as the last argument.

select student_id, letter_grade,
       decode(letter_grade, 'A', 10, 'B', 8, 'C', 6, 'D', 4, 0) as numeric_grade
from   students