w3resource

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:

SELECT Description, DiscountPct, MinQty, coalesce(MaxQty, 0.00) AS "Max Quantity" 
FROM Sales.SpecialOffer;

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.