Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in MS SQL Server

Introduction
The ISNULL() function
The COALESCE() function
The NULLIF() function
15. NULLIF() – practice 2
Summary

Instruction

Okay! 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: each customer who orders a product and picks it up on his own (i.e. no shipping required) can buy it at a special price: the initial Price minus the ShippingCost! If the ShippingCost is greater than the price itself, then the customer still pays the difference (i.e., the absolute value of Price - ShippingCost).

Our customer has 1000.00 USD and he wants to know how many products of each kind he 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() to get rid of the unwanted decimal part. Use ABS() to get the absolute value.