W3. SQL Case

Question 1

Q: What does the SQL CASE expression do?

Answer 1

A: It goes through conditions and returns a value when the first condition is met, similar to an if-then-else statement.

Question 2

Q: What happens if no conditions in a CASE expression are true and there is no ELSE?

Answer 2

A: It returns NULL.

Question 3

Q: Write the syntax for a CASE expression.

Answer 3

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE result
END;

Question 4

Q: Write a query to categorize Quantity as “greater than 30,” “equal to 30,” or “under 30” using CASE.

Answer 4

SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN ‘The quantity is greater than 30’
WHEN Quantity = 30 THEN ‘The quantity is 30’
ELSE ‘The quantity is under 30’
END AS QuantityText
FROM OrderDetails;

Question 5

Q: How can CASE be used in an ORDER BY clause?

Answer 5

A: CASE can be used to define the order based on conditions, such as ordering by a different column if a value is NULL.

Question 6

Q: Write a query to order customers by City, but if City is NULL, then order by Country.

Answer 6

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);