Understanding SQL Case Statements

SQL is a powerful language used for managing and manipulating relational databases. One of the most useful features of SQL is the case statement. The case statement allows you to perform conditional logic in your SQL queries, making it easier to handle different scenarios and produce the desired results.

Syntax of SQL Case Statement

The basic syntax of a case statement in SQL is as follows:


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

The case statement evaluates each condition in the order they are specified. When a condition is met, the corresponding result is returned. If none of the conditions are met, the result specified in the ELSE clause is returned. This allows you to handle multiple scenarios and define the desired outcome for each case.

Examples of SQL Case Statements

Let’s explore some examples to see how the case statement can be used in SQL queries:

Example 1: Categorizing Products

Suppose you have a table called products with columns product_id, product_name, and price. You want to categorize the products based on their prices into three categories: “Low”, “Medium”, and “High”. Here’s how you can achieve this using a case statement:


SELECT 
    product_id,
    product_name,
    price,
    CASE
        WHEN price < 50 THEN 'Low'
        WHEN price >= 50 AND price < 100 THEN 'Medium'
        ELSE 'High'
    END AS price_category
FROM
    products;

In this example, the case statement checks the value of the price column for each product and assigns it to the corresponding category based on the specified conditions. The result is an additional column called price_category that indicates the category for each product.

Example 2: Converting Grades

Let’s say you have a table called students with columns student_id, student_name, and score. You want to convert the scores into letter grades based on the following criteria: 90 and above – “A”, 80-89 – “B”, 70-79 – “C”, 60-69 – “D”, below 60 – “F”. Here’s how you can use a case statement to achieve this:


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

In this example, the case statement evaluates the value of the score column for each student and assigns the corresponding letter grade based on the specified conditions. The result is an additional column called grade that indicates the grade for each student.

Conclusion

The SQL case statement is a powerful tool that allows you to perform conditional logic in your queries. It enables you to handle different scenarios and produce the desired results based on specified conditions. By using the case statement, you can categorize data, convert values, and perform various other conditional operations in your SQL queries.

Remember, the case statement follows a specific syntax and evaluates conditions in the order they are specified. It is a versatile feature that can greatly enhance the functionality and flexibility of your SQL queries.

SQL Tutorial: Understanding Case Statements.