So, orders shipped outside the USA and Canada had NULL shipping_cost values. This is because we didn't specify the column value for other countries.
Northwinds' shipping cost for countries other than the US and Canada is $10.00. How can we show that in the report? Take a look:
SELECT
order_id,
customer_id,
ship_country,
CASE
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
ELSE 10.0
END AS shipping_cost
FROM orders
WHERE order_id BETWEEN 10720 AND 10730;
We enriched our CASE WHEN construction with an ELSE. The ELSE is executed when no other conditions are satisfied. Thanks to this, all other countries will get a column value of 10.0 instead of NULL.
We want to show the following basic customer information (from the customers table):
customer_idcompany_namecountrylanguageThe value of the language column will be decided by the following rules:
'German' for companies from Germany, Switzerland, and Austria.'English' for companies from the UK, Canada, the USA, and Ireland.'Other' for all other countries.Use the following template in the SELECT clause:
CASE WHEN country IN (...) THEN 'German' WHEN country IN (...) THEN 'English' ELSE 'Other' END
