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


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.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv