How to Use CASE Expression?

For performing the operations, We should have one table on which operations will be performed. If you don’t know How to Create a table in SQL Server then refer this.

We have created a table called studentsMarkList which contains studentId, studentName, studentName and marksObtained as Columns. Also, we have inserted some data into the studentsMarkList table.

After Inserting some data our table studentsMarkList Looks Like:

Let’s see how to assign R grade for students of particular studentId of 19104067 and 19104068 using the simple CASE expression

SELECT 
    studentName,
    CASE 
        WHEN studentId = 19104067 THEN 'R'
        WHEN studentId = 19104068 THEN 'R'
    END AS 'gradeObtained'
FROM  
    studentsMarkList;

Output:

Explanation: In this query, it will return a result set with columns ‘studentName‘ and ‘gradeObtained‘, where ‘gradeObtained‘ contains ‘R‘ for students with ‘studentId‘ 19104067 or 19104068 and NULL for all other students.

Let’s see the use of Searched CASE to assign the grades of the students based on the marks with the following 90-100 -> A, 80-90->B,70-80->C,60-70->D,50-60->E, 40-50->F , <40 -> R

SELECT *, CASE 
WHEN marksObtained>=91 AND marksObtained<=100 THEN 'A'
WHEN marksObtained>=81 AND marksObtained<=90 THEN  'B'
WHEN marksObtained>=71 AND marksObtained<=80 THEN 'C'
WHEN marksObtained>=61 AND marksObtained<=70 THEN 'D'
WHEN marksObtained>=51 AND marksObtained<=60 THEN 'E'
WHEN marksObtained>=41 AND marksObtained<=50 THEN 'F'
ELSE  'R' 
END AS gradeObtained
FROM  studentsMarkList

Output:

Explanation: This Query is based on Searched Case Expression which return a result set with all columns from ‘studentsMarkList‘ and an additional column ‘gradeObtained‘ indicating the grade based on the specified conditions.

Let’ see the CASE expression with the UPDATE statement to update the marks of students from studentIds till 19104065:

UPDATE studentsMarkList
SET marksObtained = (
        CASE
            WHEN studentId < 19104065 THEN marksObtained+1
            ELSE marksObtained
        END
        ) 

Output:

Explanation: In the query we have used the UPDATE statement to update the marks of all the students for the studentIds <19104065 by 1 using CASE expression.

The CASE expression is used to show another column which can be evaluated based on the conditions depending on the existing columns. The CASE expression consists of WHEN and THEN statements. WHEN is used when the condition satisfies and THEN executes the operation or assignment of value of that column based on the evaluation of the expression. The CASE statement can contain multiple WHEN and THEN statements based on the conditions. The THEN can also contain the ELSE statement if the condition in THEN is not satisfied then the ELSE statement will be assigned.