Explain the usage of the CASE statement in SQL for conditional expressions.

Question

Aryan Kumar · Answer

Sure. The CASE statement in SQL is a conditional statement that allows you to evaluate a condition and return a different value depending on the result of the evaluation. The CASE statement can be used to perform a variety of tasks, such as:

Formatting data based on different conditions.
Calculating different values for different groups of data.
Determining the next action to take based on the results of a logical expression.

The CASE statement has the following syntax:

SQL

CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
[ELSE valueN]
END;

The condition1 is the first condition that will be evaluated. If the condition is true, then the value1 will be returned. If the condition is false, then the condition2 will be evaluated. If the condition2 is true, then the value2 will be returned. This process will continue until one of the conditions is true, or until the ELSE clause is reached. If the ELSE clause is reached, then the valueN will be returned.

The valueN can be any expression, including a literal value, a column name, or a function call. The valueN can also be a nested CASE statement.

Here is an example of how the CASE statement can be used to format data based on different conditions:

SQL

SELECT customer_name,
CASE
WHEN status = 'Active' THEN 'Active'
WHEN status = 'Inactive' THEN 'Inactive'
ELSE 'Unknown'
END AS status
FROM customers;

This query will select the customer_name and status columns from the customers table. The CASE statement will be used to format the status column based on the value of the status column. If the value of the status column is Active, then the CASE statement will return the value Active. If the value of the status column is Inactive, then the CASE statement will return the value Inactive. If the value of the status column is not Active or Inactive, then the CASE statement will return the value Unknown.

Here is an example of how the CASE statement can be used to calculate different values for different groups of data:

SQL

SELECT department,
CASE
WHEN department = 'Sales' THEN salary * 1.1
WHEN department = 'Marketing' THEN salary * 1.2
ELSE salary
END AS bonus
FROM employees;

This query will select the department and bonus columns from the employees table. The CASE statement will be used to calculate the bonus column based on the value of the department column. If the value of the department column is Sales, then the CASE statement will return the value of the salary column multiplied by 1.1. If the value of the department column is Marketing, then the CASE statement will return the value of the salary column multiplied by 1.2. If the value of the department column is not Sales or Marketing, then the CASE statement will return the value of the salary column.

The CASE statement is a powerful tool that can be used to perform a variety of tasks in SQL. It is a versatile statement that can be used in a variety of ways to analyze data.

forum

Explain the usage of the CASE statement in SQL for conditional expressions.

Can you answer this question?

1 Answers