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.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 applying a CASE statement to categorize products by ProductLine
SELECT
-- Selecting the ProductNumber column
ProductNumber,
-- Applying a CASE statement to categorize products by ProductLine
CASE ProductLine
-- If ProductLine is 'R', label the product as 'Road'
WHEN 'R' THEN 'Road'
-- If ProductLine is 'M', label the product as 'Mountain'
WHEN 'M' THEN 'Mountain'
-- If ProductLine is 'T', label the product as 'Touring'
WHEN 'T' THEN 'Touring'
-- If ProductLine is 'S', label the product as 'Other sale items'
WHEN 'S' THEN 'Other sale items'
-- Default condition: If ProductLine is none of the above, label the product as 'Not for sale'
ELSE 'Not for sale'
END "Category",
-- Selecting the Name column
Name
-- 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 categorizes products by their ProductLine.
- The CASE statement categorizes products into different categories based on their ProductLine values.
- If ProductLine is 'R', the product is labeled as 'Road'.
- If ProductLine is 'M', the product is labeled as 'Mountain'.
- If ProductLine is 'T', the product is labeled as 'Touring'.
- If ProductLine is 'S', the product is labeled as 'Other sale items'.
- If ProductLine is none of the specified values, the product is labeled as 'Not for sale'.
- The result set includes columns for ProductNumber, the categorized Category, and Name of the products.
- The result set is ordered 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.
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-128.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics