w3resource

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

Click to view Full table

Sample Solution:

SELECT   ProductNumber, Name, listprice, 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'  
         WHEN ListPrice < 50 THEN 'Under $50'  
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'  
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'  
         ELSE 'Over $1000'  
      END "Price Range" 
FROM Production.Product  
ORDER 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.



Follow us on Facebook and Twitter for latest update.