CASE statement allows us to create different outputs (usually in the
SELECT statement). It is SQL’s way of handling if-then logic.
Suppose we want to condense the ratings in
movies to three levels:
- If the rating is above 8, then it is Fantastic.
- If the rating is above 6, then it is Poorly Received.
- Else, Avoid at All Costs.
SELECT name, CASE WHEN imdb_rating > 8 THEN 'Fantastic' WHEN imdb_rating > 6 THEN 'Poorly Received' ELSE 'Avoid at All Costs' END FROM movies;
WHENtests a condition and the following
THENgives us the string if the condition is true.
ELSEgives us the string if all the above conditions are false.
CASEstatement must end with
In the result, you have to scroll right because the column name is very long. To shorten it, we can rename the column to ‘Review’ using
SELECT name, CASE WHEN imdb_rating > 8 THEN 'Fantastic' WHEN imdb_rating > 6 THEN 'Poorly Received' ELSE 'Avoid at All Costs' END AS 'Review' FROM movies;
Let’s try one on your own.
name column and use a
CASE statement to create the second column that is:
- ‘Chill’ if
genre = 'romance'
- ‘Chill’ if
genre = 'comedy'
- ‘Intense’ in all other cases
Optional: Rename the whole
CASE statement to ‘Mood’ using
Give it your best shot! Check hint for the answer.