Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Simple CASE WHEN
Searched CASE WHEN
9. Searched CASE WHEN with various conditions
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Summary

## Instruction

All right. As we mentioned previously, you can use various conditions in a single CASE WHEN clause. As usual, here comes an example:

SELECT
candidate_id,
CASE
WHEN pay_date < '2015-06-01' THEN 'database error'
WHEN pay_date BETWEEN '2015-06-01'
AND '2015-06-05' THEN 'accepted'
WHEN pay_date = '2015-06-06'
THEN 'conditionally accepted'
ELSE 'not accepted'
END AS payment_status
FROM application;


The situation is as follows: each candidate was supposed to pay an application fee between June 1 and June 5. If the system provides an earlier date, this must be clearly a mistake, because the bank account was only opened on June 1 (hence the status 'database error'). Those who indeed paid between June 1 and June 5 got a status 'accepted'. The university also agreed to accept those who paid a day later ('conditionally accepted'). There was, however, no mercy for those who paid even later – they did not qualify. Poor them.

Note that we could use the less than sign, the BETWEEN construction and the equality sign (=) - all in a single CASE clause. In other words, you can basically use all the operators and functions you've learned so far. This is the magic of searched CASE WHENs.

## Exercise

For each candidate, show the first_name and last_name and a third column called overall_result: if the sum of score_math and score_language is 150 or more, show 'excellent'. If it's between 100 and 149, show 'good'. Otherwise, show 'poor'.

### Stuck? Here's a hint!

Use the following piece of code and keep going:

CASE
WHEN score_math + score_language >= 150 THEN 'excellent'