﻿ AdventureWorks: Null special offers will return MaxQty as zero

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|
```

Practice Online

Contribute your code and comments through Disqus.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

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