Explain the purpose of the CASE statement in SQL and provide an example.

Question

Aryan Kumar · Answer

Sure. The CASE statement in SQL is used to evaluate a condition and return a value based on the outcome of the evaluation. The CASE statement can be used to perform simple if-then-else logic, or it can be used to perform more complex conditional logic.

The syntax for the CASE statement is:

SQL

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

condition1, condition2, conditionN are the conditions that you want to evaluate.
result1, result2, resultN are the values that you want to return if the corresponding condition is true.
result is the value that you want to return if none of the conditions are true.

For example, the following query will return the grade of a student based on their score:

SQL

SELECT
  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 students;

The CASE statement will first evaluate the score column. If the score is greater than or equal to 90, then the CASE statement will return the value A. If the score is greater than or equal to 80, but less than 90, then the CASE statement will return the value B. And so on.

If the score is less than 60, then none of the conditions will be true. In this case, the CASE statement will return the value F.

The CASE statement is a powerful tool that can be used to perform conditional logic in SQL. It can be used to simplify your queries and make them more readable.

Here are some other examples of how the CASE statement can be used:

To return a different value based on the day of the week.
To return a different value based on the value of a column.
To return a different value based on the user input.

The CASE statement is a versatile tool that can be used in a variety of ways. If you are working with conditional logic in your SQL queries, then the CASE statement is a tool that you should definitely know about.

forum

Explain the purpose of the CASE statement in SQL and provide an example.

Can you answer this question?

1 Answers