AdventureWorks Database: Null special offers will return MaxQty as zero
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.