Good job! We told you previously that there is also another type of
CASE WHEN which is searched
CASE WHEN. This is a slightly more complicated form, but don't worry – it's no rocket science. Take a look at the example:
WHEN score_math > 80 THEN 'amazing mathematician'
WHEN score_math > 60 THEN 'knows something about numbers indeed'
ELSE 'not good enough'
END AS mathematical_skills
Let's analyze what happened here. First of all, there is now no column name immediately after the keyword
CASE. Instead, we put entire logical conditions with column names after each
WHEN. Again, you can have as many
WHEN … THEN statements as you want and you can skip
ELSE if you like.
CASE WHEN took a single column and checked it against a number of values we provided. A searched
CASE WHEN is more general – you can put different column names in each
WHEN clause and you can use mathematical and logical operators. We'll show you that in a second.
In this case, those candidates who had a score greater than 80 will have 'amazing mathematician' printed, those with a score higher than 60 but lower than 80 will have 'knows something about numbers indeed' shown instead. Those who scored 60 or less will be presented with 'not good enough'.