AdventureWorks Database: Null special offers will return MaxQty as zero

SQL Query - AdventureWorks: Exercise-185 with Solution

185. From the following table write a query in SQL to find all special offers. When the maximum quantity for a special offer is NULL, return MaxQty as zero.

Sample table: Sales.SpecialOffer

specialofferid|description                       |discountpct|type                |category   |startdate              |enddate                |minqty|maxqty|rowguid                             |modifieddate           |
--------------+----------------------------------+-----------+--------------------+-----------+-----------------------+-----------------------+------+------+------------------------------------+-----------------------+
             1|No Discount                       |          0|No Discount         |No Discount|2011-05-01 00:00:00.000|2014-11-30 00:00:00.000|     0|      |0290c4f5-191f-4337-ab6b-0a2dde03cbf9|2011-04-01 00:00:00.000|
             2|Volume Discount 11 to 14          |       0.02|Volume Discount     |Reseller   |2011-05-31 00:00:00.000|2014-05-30 00:00:00.000|    11|    14|d7542ee7-15db-4541-985c-5cc27aef26d6|2011-05-01 00:00:00.000|
             3|Volume Discount 15 to 24          |       0.05|Volume Discount     |Reseller   |2011-05-31 00:00:00.000|2014-05-30 00:00:00.000|    15|    24|4bdbcc01-8cf7-40a9-b643-40ec5b717491|2011-05-01 00:00:00.000|
             4|Volume Discount 25 to 40          |        0.1|Volume Discount     |Reseller   |2011-05-31 00:00:00.000|2014-05-30 00:00:00.000|    25|    40|504b5e85-8f3f-4ebc-9e1d-c1bc5dea9aa8|2011-05-01 00:00:00.000|
             5|Volume Discount 41 to 60          |       0.15|Volume Discount     |Reseller   |2011-05-31 00:00:00.000|2014-05-30 00:00:00.000|    41|    60|677e1d9d-944f-4e81-90e8-47eb0a82d48c|2011-05-01 00:00:00.000|
             6|Volume Discount over 60           |        0.2|Volume Discount     |Reseller   |2011-05-31 00:00:00.000|2014-05-30 00:00:00.000|    61|      |8157f569-4e8d-46b6-9347-5d0f726a9439|2011-05-01 00:00:00.000|
             7|Mountain-100 Clearance Sale       |       0.35|Discontinued Product|Reseller   |2012-04-13 00:00:00.000|2012-05-29 00:00:00.000|     0|      |7df15bf5-6c05-47e7-80a4-22bd1ce59a72|2012-03-14 00:00:00.000|
			 -- more --

Click to view Full table

Sample Solution:

-- Selecting columns Description, DiscountPct, MinQty, and MaxQty (if not null) from the Sales.SpecialOffer table
SELECT Description, DiscountPct, MinQty, COALESCE(MaxQty, 0.00) AS "Max Quantity" 
-- From the Sales.SpecialOffer table
FROM Sales.SpecialOffer;

Explanation:

• This SQL query retrieves data from the SpecialOffer table in the Sales schema.
• Comments are added to explain each part of the query for better understanding and maintenance.
• Here's a breakdown of what the query does:
• It selects the Description, DiscountPct, MinQty, and MaxQty columns from the SpecialOffer table.
• The COALESCE function is used to handle NULL values in the MaxQty column. If MaxQty is NULL, it replaces it with 0.00.
• The result set includes the Description, DiscountPct, MinQty, and Max Quantity (with possible substitution for NULLs) columns.

Sample Output:

description                       |discountpct|minqty|Max Quantity|
----------------------------------+-----------+------+------------+
No Discount                       |          0|     0|        0.00|
Volume Discount 11 to 14          |       0.02|    11|          14|
Volume Discount 15 to 24          |       0.05|    15|          24|
Volume Discount 25 to 40          |        0.1|    25|          40|
Volume Discount 41 to 60          |       0.15|    41|          60|
Volume Discount over 60           |        0.2|    61|        0.00|
Mountain-100 Clearance Sale       |       0.35|     0|        0.00|
Sport Helmet Discount-2002        |        0.1|     0|        0.00|
Road-650 Overstock                |        0.3|     0|        0.00|
Mountain Tire Sale                |        0.5|     0|        0.00|
Sport Helmet Discount-2003        |       0.15|     0|        0.00|
LL Road Frame Sale                |       0.35|     0|        0.00|
Touring-3000 Promotion            |       0.15|     0|        0.00|
Touring-1000 Promotion            |        0.2|     0|        0.00|
Half-Price Pedal Sale             |        0.5|     0|        0.00|
Mountain-500 Silver Clearance Sale|        0.4|     0|        0.00|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Repeat the 0 character four times before productnumber.
Next: Find all products that have NULL in the weight column.