CASE: if / else inside a query

Every programming language has if/else. In SQL it's the CASE statement: it lets a query produce different output values depending on conditions, which is perfect for turning numbers into labels.

The class example: box-office verdicts

Compute each Bollywood movie's profit (worldwide − budget, in ₹ crore) and label it:

SELECT title,
       worldwide - budget AS profit,
       CASE
           WHEN worldwide - budget > 100 THEN 'Superhit'
           WHEN worldwide - budget > 25  AND worldwide - budget <= 100 THEN 'Hit'
           WHEN worldwide - budget >= 0  AND worldwide - budget <= 25  THEN 'Average'
           ELSE 'Flop'
       END AS verdict
FROM movies;

Anatomy of CASE

• Starts with CASE, ends with END; give the result a name with AS.
• Each WHEN condition THEN value is an "if / else-if". They are checked top to bottom, and the first true one wins.
• ELSE catches everything else. Without it, unmatched rows get NULL.
• The whole CASE is just an expression, so it sits in the SELECT list like any column.

SELECT title,
       CASE
           WHEN worldwide - budget > 100 THEN 'Superhit'
           WHEN worldwide - budget > 25  THEN 'Hit'
           WHEN worldwide - budget >= 0  THEN 'Average'
           ELSE 'Flop'
       END AS verdict
FROM movies;

Going further (my additions)

SELECT CASE
           WHEN worldwide - budget > 100 THEN 'Superhit'
           WHEN worldwide - budget > 25  THEN 'Hit'
           WHEN worldwide - budget >= 0  THEN 'Average'
           ELSE 'Flop'
       END AS verdict,
       COUNT(*) AS movies
FROM movies
GROUP BY verdict
ORDER BY movies DESC;

SELECT pclass,
       SUM(CASE WHEN sex = 'female' THEN 1 ELSE 0 END) AS women,
       SUM(CASE WHEN sex = 'male'   THEN 1 ELSE 0 END) AS men
FROM train
GROUP BY pclass;

SELECT name,
       CASE embarked
           WHEN 'S' THEN 'Southampton'
           WHEN 'C' THEN 'Cherbourg'
           WHEN 'Q' THEN 'Queenstown'
           ELSE 'Unknown'
       END AS port
FROM train;

-- Drama first, then Action, then everything else
SELECT title, genre FROM movies
ORDER BY CASE genre WHEN 'Drama' THEN 1 WHEN 'Action' THEN 2 ELSE 3 END, title;

You've also seen CASE in Practice 1, Q7 (Titanic family size).

SQL lab

Try next: survival rate per age group (wrap the CASE in a GROUP BY), or label movies by runtime as "short / standard / epic".

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.