Introduction to Oracle case Functions

1. Create a test table:

Copy codeThe Code is as follows:
Drop sequence student_sequence;
Create sequence student_sequence start with 10000 increment by 1;

Drop table students;
Create table students (
Id NUMBER (5) primary key,
First_name VARCHAR2 (20 ),
Last_name VARCHAR2 (20 ),
Major VARCHAR2 (30 ),
Current_credits NUMBER (3 ),
Grade varchar2 (2 ));

Insert into students (id, first_name, last_name, major, current_credits, grade)
VALUES (student_sequence.NEXTVAL, 'Scott ', 'Smith', 'computer Science ', 98, null );

Insert into students (id, first_name, last_name, major, current_credits, grade)
VALUES (student_sequence.NEXTVAL, 'Margaret ', 'mason', 'History', 88, null );

Insert into students (id, first_name, last_name, major, current_credits, grade)
VALUES (student_sequence.NEXTVAL, 'joanne ', 'junebug', 'computer Science ', 75, null );

Insert into students (id, first_name, last_name, major, current_credits, grade)
VALUES (student_sequence.NEXTVAL, 'manish', 'murgratroid', 'Economists ', 66, null );

Commit;

2. view the corresponding data

Copy codeThe Code is as follows:
SQL> select * from students;

ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS GR
-------------------------------------------------------------------------------------------------
10000 Scott Smith Computer Science 98
10001 Margaret Mason History 88
10002 Joanne Junebug Computer Science 75
10003 Manish Murgratroid Economics 66

3. Update statement

Copy codeThe Code is as follows:
Update students
Set grade = (
Select grade from
(
Select id,
Case when current_credits> 90 then 'A'
When current_credits> 80 then 'B'
When current_credits> 70 then 'C'
Else 'D' end grade
From students
)
Where a. id = students. id
)
/

4. updated results

Copy codeThe Code is as follows:
SQL> select * from students;

ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS GR
-------------------------------------------------------------------------------------------------
10000 Scott Smith Computer Science 98
10001 Margaret Mason History 88 B
10002 Joanne Junebug Computer Science 75 c
10003 Manish Murgratroid Economics 66 d