AdventureWorks Database: Change the display of product line categories with comment
SQL Query - AdventureWorks: Exercise-128 with Solution
128. From the following table write a query in SQL to change the display of product line categories to make them more understandable. Return ProductNumber, category, and name of the product. Sort the result set in ascending order on ProductNumber.
Sample table: production.Productproductid|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:
SELECT ProductNumber,
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
end "Category",
Name
FROM Production.Product
ORDER BY ProductNumber;
Sample Output:
productnumber|Category |name | -------------+----------------+--------------------------------+ AR-5381 |Not for sale |Adjustable Race | BA-8327 |Not for sale |Bearing Ball | BB-7421 |Not for sale |LL Bottom Bracket | BB-8107 |Not for sale |ML Bottom Bracket | BB-9108 |Not for sale |HL Bottom Bracket | BC-M005 |Mountain |Mountain Bottle Cage | BC-R205 |Road |Road Bottle Cage | BE-2349 |Not for sale |BB Ball Bearing | BE-2908 |Not for sale |Headset Ball Bearings | BK-M18B-40 |Mountain |Mountain-500 Black, 40 | BK-M18B-42 |Mountain |Mountain-500 Black, 42 | BK-M18B-44 |Mountain |Mountain-500 Black, 44 | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Display the list price as a comment based on the price range.
Next: Evaluate whether the values in two columns are the same.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics