Revision
Comparisons with NULL
Functions with NULL
COALESCE to the rescue
NULLIF
22. NULLIF – practice 2
Revision and practice

Instruction

Nice. Ok, one more exercise on NULLIF and we'll go on to another section.

Exercise

Mr Amund wants to release a new promotion.

The promotion is as follows: every customer who orders a product and picks it up on their own (i.e. no shipping required) can buy it at a special price: the initial price MINUS the shipping_cost! If the shipping_cost is greater than the price itself, then the customer still pays the difference (i.e. the absolute value of price – shipping_cost).

Our customer has 1000.00 USD again and wants to know how many products of each kind they could buy. Show each product name with the column quantity which calculates the amount of products. Get rid of the decimal part. If you get a price of 0.00, show NULL instead.

Stuck? Here's a hint!

Use floor(x) to get rid of the unwanted decimal part. Use abs(x) to get the absolute value.

Console

Code editor

Result

TableConsole