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
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises