11. CASE WHEN Statement

Question 1

SQL evaluates all WHEN clauses inside the CASE…END clause.
(T/F)

Answer 1

False.

If the first WHEN clause evaluates to True, then SQL skips other WHEN clauses below it.

CASE…END clause can have many WHEN clauses and final ELSE clause at the end.

Question 2

Deriving new columns based on information from existing data is achieved by _ .

Answer 2

CASE…END clause in the SELECT clause

Question 3

Create report showing total sales for each of the following categories:
1. High (sales above 50)
2. Medium (sales from 21 to 50)
3. Low (20 or less)

Sort the results from highest to lowest category.

ORDERS: orderid (int), sales (int)

Answer 3

select category, sum(sales) as totalsales
from (
select orderid, sales,
CASE
when sales > 50 then 'High'
when sales > 20 then 'Medium'
else 'Low'
 END as category
from Orders
) t
group by category
order by totalsales DESC

Note: You cannot apply group by on a column that does not exist yet. Hence subquery.

Question 4

Find average scores of customers and treat NULLs as 0.
Also display id and name of each customer.

customers: id, name, score

Answer 4

select 
  id,
  name,
  avg(
    case
      when score is null then 0
      else score
    end
  ) over() as cleanavg
from CUSTOMERS;

Better: AVG(COALESCE(score,0)) OVER() AS cleanavg

Question 5

Count how many times each customer has made an order of amount greater than 30.

Orders: orderId, customerId, sales

Answer 5

select
    customerId,
   sum(case
      when sales > 30 then 1
      else 0
    end) as insight
from Orders
group by customerId;