AdventureWorks Database: Display the list price as a comment based on the price range
SQL Query - AdventureWorks: Exercise-127 with Solution
127. From the following table write a query in SQL to display the list price as a text comment based on the price range for a product. Return ProductNumber, Name, and listprice. Sort the result set on ProductNumber in ascending order.
Sample table: production.Product
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample Solution:
-- Selecting specific columns from the Product table and adding a calculated column for price range
SELECT
-- Selecting the ProductNumber column
ProductNumber,
-- Selecting the Name column
Name,
-- Selecting the listprice column
listprice,
-- Applying a CASE statement to categorize products into price ranges
CASE
-- Condition: ListPrice is 0
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
-- Condition: ListPrice is less than 50
WHEN ListPrice < 50 THEN 'Under $50'
-- Condition: ListPrice is between 50 and 249.99
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
-- Condition: ListPrice is between 250 and 999.99
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
-- Default condition: ListPrice is 1000 or more
ELSE 'Over $1000'
END "Price Range"
-- Selecting data from the Product table
FROM Production.Product
-- Ordering the result set by ProductNumber
ORDER BY ProductNumber ;
Explanation:
- This SQL code selects specific columns from the Product table and adds a calculated column for price range.
- The CASE statement categorizes products into different price ranges based on their ListPrice.
- If ListPrice is 0, the product is labeled as 'Mfg item - not for resale'.
- If ListPrice is less than 50, the product is labeled as 'Under $50'.
- If ListPrice is between 50 and 249.99, the product is labeled as 'Under $250'.
- If ListPrice is between 250 and 999.99, the product is labeled as 'Under $1000'.
- If ListPrice is 1000 or more, the product is labeled as 'Over $1000'.
- The result set is ordered by ProductNumber.
Sample Output:
productnumber|name |listprice|Price Range | -------------+--------------------------------+---------+-------------------------+ AR-5381 |Adjustable Race | 0|Mfg item - not for resale| BA-8327 |Bearing Ball | 0|Mfg item - not for resale| BB-7421 |LL Bottom Bracket | 53.99|Under $250 | BB-8107 |ML Bottom Bracket | 101.24|Under $250 | BB-9108 |HL Bottom Bracket | 121.49|Under $250 | BC-M005 |Mountain Bottle Cage | 9.99|Under $50 | BC-R205 |Road Bottle Cage | 8.99|Under $50 | BE-2349 |BB Ball Bearing | 0|Mfg item - not for resale| BE-2908 |Headset Ball Bearings | 0|Mfg item - not for resale| BK-M18B-40 |Mountain-500 Black, 40 | 539.99|Under $1000 | BK-M18B-42 |Mountain-500 Black, 42 | 539.99|Under $1000 | BK-M18B-44 |Mountain-500 Black, 44 | 539.99|Under $1000 | BK-M18B-48 |Mountain-500 Black, 48 | 539.99|Under $1000 | BK-M18B-52 |Mountain-500 Black, 52 | 539.99|Under $1000 | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Sort the BusinessEntityID in descending or ascending order.
Next: Change the display of product line categories with comment.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-127.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics