Case Statement in SQL - Javagyansite

In this article, we will explore “Case Statement in SQL” with examples. Let’s get started.

Table of Contents

Syntax of Simple Case Statement in SQL

The simple CASE statement compares an expression to a set of values and returns a result when a match is found. It has the following syntax:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE else_result
END

Example : Simple Case Statement in SQL

Let’s take an example of creating a table for customer data and insert query, and then we will see how to use the CASE statement to calculate the customer rating based on their grade.

First, let’s create a table for customer data:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    grade CHAR(1)
);

Next, let’s insert some sample data into the customers table:

INSERT INTO customers (customer_id, customer_name, grade)
VALUES
    (1, 'Alice', 'A'),
    (2, 'Bob', 'B'),
    (3, 'Charlie', 'C'),
    (4, 'David', 'B'),
    (5, 'Eve', 'A');

Now, you have a table named customers with sample data. To calculate the customer rating based on their grade using the CASE statement, you can use the following query:

SELECT
    customer_name,
    CASE grade
        WHEN 'A' THEN 'Excellent'
        WHEN 'B' THEN 'Good'
        WHEN 'C' THEN 'Fair'
        ELSE 'Unknown'
    END AS customer_rating
FROM customers;

Running this query will give you the following result:

+---------------+----------------+
| customer_name | customer_rating|
+---------------+----------------+
| Alice         | Excellent      |
| Bob           | Good           |
| Charlie       | Fair           |
| David         | Good           |
| Eve           | Excellent      |
+---------------+----------------+

Searched CASE statement in SQL

The Searched CASE statement in SQL is a conditional expression that allows you to evaluate multiple conditions and return a result based on the first condition that evaluates to true. It’s a more flexible form of the CASE statement compared to the Simple CASE statement, which compares a single expression to a set of values. The Searched CASE statement is often used when you need to perform complex conditional logic in your SQL queries.

Here’s the syntax of the Searched CASE statement:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END

Here’s an example of how to use the Searched CASE statement in SQL using the below table student_stores.

-- Create the student_scores table
CREATE TABLE student_scores (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255),
    score INT
);

-- Insert sample data into the student_scores table
INSERT INTO student_scores (student_id, student_name, score)
VALUES
    (1, 'Alice', 92),
    (2, 'Bob', 78),
    (3, 'Charlie', 88),
    (4, 'David', 65),
    (5, 'Eve', 45);

SELECT
    student_name,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM student_scores;

In this example, the Searched CASE statement evaluates different conditions based on the score column in the student_scores table and returns the corresponding grade. If none of the conditions are met, it returns ‘F’.

Here’s what the result look like:

+--------------+-------+
| student_name | grade |
+--------------+-------+
| Alice        | A     |
| Bob          | C     |
| Charlie      | B     |
| David        | D     |
| Eve          | F     |
+--------------+-------+

Conclusion: Case Statement in SQL

This article has provided a comprehensive overview of the “Case Statement in SQL” with practical examples. It covered the syntax and usage of both the Simple CASE and Searched CASE statements. The Simple CASE statement allows you to compare an expression to a set of values and return results based on matches, while the Searched CASE statement handles complex conditions by evaluating multiple criteria.

Through examples, we demonstrated how to use these statements to calculate customer ratings based on grades and assign grades to students based on their scores. These concepts are essential for data transformation, categorization, and conditional logic in SQL queries, making them valuable tools for database developers and analysts.

Related Articles :

Syntax of Simple Case Statement in SQL

Example : Simple Case Statement in SQL

Searched CASE statement in SQL

Conclusion: Case Statement in SQL

Related posts:

Leave a Reply Cancel reply