1. What is the CASE statement in Oracle?
The CASE statement in Oracle SQL is a conditional expression that allows you to perform IF-THEN-ELSE logic in SQL queries. It enables you to return specific values based on the evaluation of conditions.
2. What is the difference between a Simple CASE and a Searched CASE?
- Simple CASE Expression: Compares a single expression to multiple possible values. It is used when you know the specific values you want to compare against.
- Example: CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 END
- Searched CASE Expression: Evaluates different conditions for each row. It allows for more complex logic because each condition can be any valid expression or comparison.
- Example: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 END
3. Can I use the CASE statement in the WHERE clause?
Yes, the CASE statement can be used in the WHERE clause, but it is not as common. You can use it for more complex conditions when you need to apply different logic for filtering results based on specific criteria.
Example:
SELECT * FROM products
WHERE
CASE
WHEN category_id = 1 THEN price > 100
WHEN category_id = 2 THEN price < 50
ELSE price > 75
END;
4. Can I use multiple CASE statements in a single query?
Yes, you can use multiple CASE statements in a single query, either in different columns or within different conditions. Each CASE expression can be used independently or nested within another CASE.
Example:
SELECT product_name,
CASE WHEN price < 100 THEN 'Affordable'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END AS price_category,
CASE WHEN quantity > 50 THEN 'In Stock'
ELSE 'Out of Stock'
END AS stock_status
FROM products;
5. Is it possible to use CASE in the ORDER BY clause?
Yes, you can use the CASE statement in the ORDER BY clause to conditionally sort the results. You can use it to apply different sorting rules based on specific conditions.
Example:
SELECT product_name, price
FROM products
ORDER BY
CASE
WHEN price < 100 THEN 1
WHEN price BETWEEN 100 AND 500 THEN 2
ELSE 3
END;
6. How does the ELSE clause work in the CASE statement?
The ELSE clause provides a default result when none of the conditions are true. If you omit the ELSE clause and no conditions match, the result will be NULL. Including an ELSE clause ensures that a value is returned for all rows.
Example:
SELECT product_name,
CASE
WHEN price < 50 THEN 'Low'
WHEN price BETWEEN 50 AND 100 THEN 'Medium'
ELSE 'High'
END AS price_range
FROM products;
7. Can I use NULL values in a CASE statement?
Yes, you can handle NULL values in a CASE statement. If you need to handle NULL specifically, you should use IS NULL or IS NOT NULL to test for NULL values.
Example:
SELECT employee_name,
CASE
WHEN salary IS NULL THEN 'Salary Not Available'
ELSE 'Salary Available'
END AS salary_status
FROM employees;
8. How do nested CASE statements work?
A nested CASE statement is when one CASE expression is placed inside another CASE expression. It allows you to evaluate more complex conditions.
Example:
SELECT product_name,
CASE
WHEN price < 100 THEN
CASE WHEN quantity > 50 THEN 'Affordable and Stocked' ELSE 'Affordable but Low Stock' END
WHEN price >= 100 THEN 'Expensive'
ELSE 'Unknown'
END AS product_status
FROM products;
9. What happens if no conditions match in a CASE statement?
If no conditions match in a CASE expression and there is no ELSE clause, the result will be NULL. If you want to avoid NULL, you should always include an ELSE clause.
Example:
SELECT product_name,
CASE
WHEN category_id = 1 THEN 'Electronics'
WHEN category_id = 2 THEN 'Furniture'
ELSE 'Other'
END AS category
FROM products;
10. Can I use CASE in an UPDATE statement?
Yes, you can use CASE in an UPDATE statement to conditionally update values based on different conditions.
Example:
UPDATE employees
SET salary =
CASE
WHEN department_id = 10 THEN salary * 1.10
WHEN department_id = 20 THEN salary * 1.15
ELSE salary
END
WHERE department_id IN (10, 20);
11. Can I use the CASE statement in a JOIN?
Yes, you can use the CASE statement in a JOIN condition to perform conditional logic while joining tables.
Example:
SELECT a.product_name, b.category_name
FROM products a
JOIN categories b
ON a.category_id = b.category_id
AND CASE
WHEN a.price > 100 THEN b.category_type = 'Premium'
ELSE b.category_type = 'Standard'
END;
12. Is the CASE statement case-sensitive?
No, the CASE statement itself is not case-sensitive. However, the expressions or values used within the CASE statement may be case-sensitive, depending on the column's collation or the comparison being made.
No comments:
Post a Comment