AdventureWorks Database: List all the products that are red or blue
37. From the following table write a query in SQL to list all the products that are Red or Blue in color. Return name, color and listprice.Sorts this result by the column listprice.
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 where Color is 'Red'
SELECT Name, Color, ListPrice
-- From the Production schema's Product table
FROM Production.Product
-- Filtering the results to include only rows where Color is 'Red'
WHERE Color = 'Red'
-- UNION ALL combines the results of two SELECT statements, including duplicates
UNION ALL
-- Selecting specific columns from the Product table where Color is 'Blue'
SELECT Name, Color, ListPrice
-- From the Production schema's Product table
FROM Production.Product
-- Filtering the results to include only rows where Color is 'Blue'
WHERE Color = 'Blue'
-- Ordering the combined results by ListPrice in ascending order
ORDER BY ListPrice ASC;
Explanation:
- The SQL query retrieves data from the Product table within the Production schema.
- It selects three columns: Name, Color, and ListPrice.
- Two SELECT statements are used, each filtering the results based on a specific color (Red and Blue).
- The UNION ALL operator combines the results of both SELECT statements, including duplicates.
- As a result, the combined result set will contain products that are either red or blue.
- The ORDER BY clause specifies the sorting criteria for the combined result set, ordering by ListPrice in ascending order.
- This will arrange the products from lowest to highest list price, regardless of their color.
Sample Output:
name |color|listprice| ---------------------------+-----+---------+ Sport-100 Helmet, Blue |Blue | 34.99| Sport-100 Helmet, Red |Red | 34.99| Classic Vest, S |Blue | 63.5| Classic Vest, L |Blue | 63.5| Classic Vest, M |Blue | 63.5| LL Touring Frame - Blue, 54|Blue | 333.42| LL Touring Frame - Blue, 50|Blue | 333.42| LL Touring Frame - Blue, 44|Blue | 333.42| LL Touring Frame - Blue, 62|Blue | 333.42| LL Touring Frame - Blue, 58|Blue | 333.42| LL Road Frame - Red, 58 |Red | 337.22| LL Road Frame - Red, 60 |Red | 337.22| LL Road Frame - Red, 62 |Red | 337.22| LL Road Frame - Red, 44 |Red | 337.22| LL Road Frame - Red, 48 |Red | 337.22| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Fetch rows from the middle of a sorted table.
Next: Retrieve name and associated salesorders.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.