SQL SQL Tutorial SQL Database



SQL Case

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. One of the most powerful features of SQL is the ability to use the CASE statement. The CASE statement allows you to perform conditional logic in your SQL queries, making it a valuable tool for data analysis and reporting.

Brief Explanation of SQL Case

The CASE statement in SQL is similar to the switch statement in other programming languages. It allows you to evaluate a set of conditions and return a value based on the first condition that is true. The basic syntax of the CASE statement is as follows:


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

The CASE statement starts with the keyword CASE, followed by one or more WHEN clauses that specify the conditions to be evaluated. Each WHEN clause consists of a condition and a result value. If the condition is true, the result value is returned. If none of the conditions are true, the ELSE clause specifies a default result value to be returned.

Here is an example of a simple CASE statement:


SELECT
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM
    grades;

In this example, the CASE statement evaluates the grade of each student and returns a letter grade based on the following scale:

  • A: 90-100
  • B: 80-89
  • C: 70-79
  • D: 60-69
  • F: below 60

The result of the query will be a table with a column called letter_grade that contains the letter grade for each student.

Code Examples in <pre> Tags

Here are some more examples of how the CASE statement can be used in SQL:

Example 1: Counting the Number of Students in Each Grade


SELECT
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade,
    COUNT(*) AS num_students
FROM
    grades
GROUP BY
    letter_grade;

This query uses the CASE statement to group students by their letter grade and count the number of students in each grade.

Example 2: Calculating a Discount Based on Order Total


SELECT
    order_id,
    order_total,
    CASE
        WHEN order_total >= 1000 THEN order_total * 0.1
        WHEN order_total >= 500 THEN order_total * 0.05
        ELSE 0
    END AS discount
FROM
    orders;

This query uses the CASE statement to calculate a discount based on the total amount of an order. If the order total is greater than or equal to $1000, a 10% discount is applied. If the order total is between $500 and $999.99, a 5% discount is applied. Otherwise, no discount is applied.

Example 3: Converting a Boolean Value to a String


SELECT
    product_name,
    in_stock,
    CASE
        WHEN in_stock = 1 THEN 'Yes'
        ELSE 'No'
    END AS in_stock_string
FROM
    products;

This query uses the CASE statement to convert a boolean value (1 or 0) to a string ('Yes' or 'No'). The result of the query will be a table with a column called in_stock_string that contains the string representation of the in_stock column.

References



Share via:

Activity