w3resource

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


Click to view Full table

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.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ