Routine for grades conversion

grades_compatibilization.sql

-- Defines a continuous range
UPDATE yulie
SET grades_continuous = cast(grades AS FLOAT)
WHERE grades ~ '^-?[0-9]+\.?[0-9]*$'

UPDATE yulie
SET grades_continuous = (CASE   -- Values are the median of ranges
              WHEN grades = 'A' OR grades = 'a' THEN 95
              WHEN grades = 'B' OR grades = 'b' THEN 85
              WHEN grades = 'C' OR grades = 'c' THEN 75
              WHEN grades = 'D' OR grades = 'd' THEN 65
              WHEN grades = 'F' OR grades = 'f' THEN 30
              END)
WHERE grades !~ '^-?[0-9]+\.?[0-9]*$'

-- Defnes a categorical range
UPDATE yulie
SET grades_letters = upper(grades)
WHERE grades !~ '^-?[0-9]+\.?[0-9]*$'

UPDATE yulie
SET grades_letters = (CASE   -- Values are the median of ranges
              WHEN cast(grades AS FLOAT) >= 90  THEN 'A'
              WHEN cast(grades AS FLOAT) >= 80 AND cast(grades AS FLOAT) <= 89 THEN 'B'
              WHEN cast(grades AS FLOAT) >= 70 AND cast(grades AS FLOAT) <= 79 THEN 'C'
              WHEN cast(grades AS FLOAT) >= 60 AND cast(grades AS FLOAT) <= 69 THEN 'D'
              WHEN cast(grades AS FLOAT) <= 59 THEN 'F'
              END)
WHERE grades ~ '^-?[0-9]+\.?[0-9]*$'