SQL Interview Question and Answers | What is the Purpose of SQL CASE Statement
The SQL CASE statement is a powerful and versatile tool used to perform conditional logic within SQL queries. Its primary purpose is to allow you to define conditional expressions and produce different results based on those conditions. The CASE statement is often used for the following purposes:
1. *Conditional Data Selection:*
You can use the CASE statement to conditionally select data based on certain criteria. For example, you might want to retrieve specific data depending on the value of a column. Here's an example:
```sql
SELECT
customer_name,
CASE
WHEN total_purchase greter than symbol 1000 THEN 'High Value'
WHEN total_purchase greter than symbol 500 THEN 'Medium Value'
ELSE 'Low Value'
END AS customer_category
FROM customers;
```
In this query, the CASE statement categorizes customers based on their total purchases.
2. *Conditional Calculations:*
The CASE statement is frequently used to perform conditional calculations. You can use it to create calculated columns based on conditions. For example:
```sql
SELECT
product_name,
unit_price,
CASE
WHEN unit_price greter than symbol 10 THEN 'Expensive'
ELSE 'Affordable'
END AS price_category
FROM products;
```
In this example, the CASE statement categorizes products as "Expensive" or "Affordable" based on their unit price.
3. *Sorting and Ranking:*
You can use the CASE statement in the ORDER BY clause to create custom sorting logic. For instance, you can sort records in a specific order or prioritize certain values:
```sql
SELECT
product_name,
unit_price
FROM products
ORDER BY
CASE
WHEN product_name = 'Special Product' THEN 1
ELSE 2
END,
unit_price;
```
In this query, "Special Product" is sorted first, followed by other products sorted by unit price.
4. *Data Transformation:*
The CASE statement can be used to transform data values. You can replace values with new values or categories based on specific conditions. This is useful for data cleaning and standardization:
```sql
UPDATE orders
SET order_status = CASE
WHEN order_status = 'Processing' THEN 'In Progress'
WHEN order_status = 'Shipped' THEN 'Delivered'
ELSE order_status
END;
```
In this example, the CASE statement updates order statuses to standardize them.
5. *Pivoting Data:*
In some cases, the CASE statement is used to pivot data, transforming rows into columns. This is often used for creating crosstab reports:
```sql
SELECT
department,
SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
-- ...
FROM sales_data
GROUP BY department;
```
This query pivots sales data to show monthly totals for each department.
The SQL CASE statement provides a way to perform conditional operations and produce different results within a single query. It is a valuable tool for data analysis, report generation, and data transformation in SQL.
#sql
