Populate a column based on another column

Question

I'm trying to make a table for Exams where it looks like:

ExamID | Name | Points | Grade

Where Points is equal to some value from 0 to 100. Now I want to automatically set the value of Grade based on the value of Points. IF > 90 Grade=A...

What is the basic syntax for an sql query to do this? I'll adjust it to my needs afterwards anyway.

Tried playing around with the CASE syntax, but couldn't do it. I'm guessing I should use the UPDATE query, not the INSERT query.

Answer 1

You could use a computed column. Once your table is created without the Grade column, you could add a computed column like this (this should work at least on SQL Server, but you might need to find the differences on other DBMS's sql):

ALTER TABLE Exams
  ADD Grade AS
    CASE
      WHEN (Points > 90) THEN 'A'
      WHEN (Points > 70) THEN 'B'
      WHEN (Points > 50) THEN 'C'
      ELSE 'D'
    END

Depending on your DBMS you might want to use a Trigger for both insertion and updating instead, but the syntax for triggers is largely dependent on the system you are using.

If the Grade is only calculated and you don't really need to store it, you could use a View instead, or directly create it when querying the table and just not have a Grade column at all.