How to add a string into a new column based on the value of another column? (Oracle SQL Developer)-CodePudding

I am needing to update a newly created row's values based on another row. In this case, I need to create a new column called honor_level and need to fill it with different strings depending on what the student's gpa is. It has to be done with SELECT and UNION statements, but I'm not sure if I should use the ALTER TABLE command to create the new column or if it can be made in the separate SELECT statements. The code below is mostly correct, I just need to figure out a way to assign the correct strings to the newly created column based on student GPA.

SELECT first_name, last_name, email, cGPA AS "1st Class Honor"
    FROM Student
    WHERE cGPA > 3.9;
UNION
SELECT first_name, last_name, email, cGPA AS "2nd Class Honor"
    FROM Student
    WHERE cGPA >= 3.7 AND cGPA < 3.9;
UNION
SELECT first_name, last_name, email, cGPA AS "3rd Class Honor"
    FROM Student
    WHERE cGPA >= 3.5 AND cGPA < 3.7;
UNION  
SELECT first_name, last_name, email, cGPA AS "On track to graduate"
    FROM Student
    WHERE cGPA < 3.5 AND cGPA > 2.0;

CodePudding user response：

If the honor level is always derived from the cgpa you could also look at using a derived column:

alter table student add honor_level varchar2(100) generated always as (       CASE
          WHEN cgpa > 3.9
          THEN
             '1st Class Honor'
          WHEN     cgpa >= 3.7
               AND cgpa < 3.9
          THEN
             '2nd Class Honor'
          WHEN     cgpa >= 3.5
               AND cgpa < 3.7
          THEN
             '3rd Class Honor'
          WHEN     cgpa < 3.5
               AND cgpa > 2
          THEN
             'On track to graduate'
       END) virtual;

No update needed - the value is always derived and the column cannot be inserted/updated.

CodePudding user response：

I'm not sure if I should use the ALTER TABLE command to create the new column or if it can be made in the separate SELECT statements

It depends on what result you really need.

If you just want to select these values, then select it is:

SELECT first_name,
       last_name,
       email,
       cgpa,
       CASE
          WHEN cgpa > 3.9
          THEN
             '1st Class Honor'
          WHEN     cgpa >= 3.7
               AND cgpa < 3.9
          THEN
             '2nd Class Honor'
          WHEN     cgpa >= 3.5
               AND cgpa < 3.7
          THEN
             '3rd Class Honor'
          WHEN     cgpa < 3.5
               AND cgpa > 2
          THEN
             'On track to graduate'
       END honor_level
  FROM student;

If you want to store values into a new column, then you'd have to add it first (if it doesn't exist), and then update its contents:

ALTER TABLE student
   ADD honor_level VARCHAR2 (30);

UPDATE student
   SET honor_level =
          CASE
             WHEN cgpa > 3.9
             THEN
                '1st Class Honor'
             WHEN     cgpa >= 3.7
                  AND cgpa < 3.9
             THEN
                '2nd Class Honor'
             WHEN     cgpa >= 3.5
                  AND cgpa < 3.7
             THEN
                '3rd Class Honor'
             WHEN     cgpa < 3.5
                  AND cgpa > 2
             THEN
                'On track to graduate'
          END;

Alternatively, you could create a view so that you wouldn't have to write the same code all over again (nor update honor_level when cgpa changes):

CREATE OR REPLACE VIEW v_student
AS
   SELECT first_name,
          last_name,
          email,
          cgpa,
          CASE
             WHEN cgpa > 3.9
             THEN
                '1st Class Honor'
             WHEN     cgpa >= 3.7
                  AND cgpa < 3.9
             THEN
                '2nd Class Honor'
             WHEN     cgpa >= 3.5
                  AND cgpa < 3.7
             THEN
                '3rd Class Honor'
             WHEN     cgpa < 3.5
                  AND cgpa > 2
             THEN
                'On track to graduate'
          END honor_level
     FROM student;