Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
The COALESCE() function
The GREATEST() & LEAST() functions
The NULLIF() function
14. Practice NULLIF() – 2
Summary

Instruction

Okay! One more exercise on NULLIF() and we'll move on to the summary.

Exercise

Mr. Adams wants to launch a new promotion: Each customer who orders a product and picks it up (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 just pays the difference (i.e., the absolute value of price - shipping_cost).

A customer has $1,000.00 and wants to know how many of each product she could buy. Show each product name with the quantity column (which calculates the number of products she can buy for $1,000). Get rid of the decimal part. If you get a price of 0.00, show NULL instead.

Stuck? Here's a hint!

Use the FLOOR() function to get rid of the unwanted decimal part.
Use the ABS() function to get the absolute value.