How do you use the CASE statement in SQL?

The CASE statement in SQL is used to perform conditions in a query, similar to if-else statements in other programming languages.

The CASE statement in SQL is a way of setting up if-then-else logic within an SQL statement. It's a powerful tool that allows you to manipulate and return different data based on certain conditions. It can be used in any statement or clause that allows a valid expression such as SELECT, UPDATE, DELETE, or SET.

The basic syntax of a CASE statement is as follows:

```
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END;
```

In this structure, each condition is evaluated in the order listed. If condition1 is true, then result1 is returned. If condition1 is not true, then condition2 is evaluated. If condition2 is true, then result2 is returned. This continues until a true condition is found or all conditions have been evaluated. If no conditions are true, the statement after ELSE is returned. If no ELSE is provided and no conditions are true, then NULL is returned.

Here's an example of a CASE statement in a SELECT query:

```
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;
```

In this example, the CASE statement is used to create a new column in the result set called 'QuantityText'. This column contains a text description of the quantity in each row. If the quantity is greater than 30, 'The quantity is greater than 30' is returned. If the quantity is exactly 30, 'The quantity is 30' is returned. For all other quantities, 'The quantity is under 30' is returned.

Remember, the CASE statement is quite flexible and can be used in many different ways to help you manipulate and return data based on specific conditions.