On January 21th at 14:15 UTC , progression through exercises will be unavailable for 10 minutes due to a planned maintenance break.
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'
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 1, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.4727097451686859, "perplexity": 4437.265513402197}, "config": {"markdown_headings": true, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.18, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2021-04/segments/1610704799741.85/warc/CC-MAIN-20210126104721-20210126134721-00533.warc.gz"}