AdventureWorks Database: Return with a product line of R and a days to manufacture less than 4
SQL Query - AdventureWorks: Exercise-193 with Solution
193. From the following table write a query in SQL to return only the rows for Product that have a product line of R and that have days to manufacture that is less than 4. Sort the result set in ascending order on name.
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 columns Name, ProductNumber, and renaming ListPrice as Price
SELECT Name, ProductNumber, ListPrice AS Price
-- From the Production.Product table
FROM Production.Product
-- Filtering records where ProductLine is 'R' and DaysToManufacture is less than 4
WHERE ProductLine = 'R'
AND DaysToManufacture < 4
-- Ordering the result set by Name in ascending order
ORDER BY Name ASC;
Explanation:
- This SQL query retrieves data from the Product table in the Production schema.
- Comments are added to explain each part of the query for better understanding and maintenance.
- Here's a breakdown of what the query does:
- The SELECT clause specifies the columns to be retrieved: Name, ProductNumber, and ListPrice (renamed as Price).
- The FROM clause specifies the source table as Production.Product.
- The WHERE clause filters records where ProductLine is 'R' and DaysToManufacture is less than 4.
- The ORDER BY clause arranges the result set by the Name column in ascending order.
Sample Output:
name |productnumber|price | ----------------------------+-------------+-------+ Headlights - Dual-Beam |LT-H902 | 34.99| Headlights - Weatherproof |LT-H903 | 44.99| HL Road Frame - Black, 44 |FR-R92B-44 | 1431.5| HL Road Frame - Black, 48 |FR-R92B-48 | 1431.5| HL Road Frame - Black, 52 |FR-R92B-52 | 1431.5| HL Road Frame - Black, 58 |FR-R92B-58 | 1431.5| HL Road Frame - Black, 62 |FR-R92B-62 | 1431.5| HL Road Frame - Red, 44 |FR-R92R-44 | 1431.5| HL Road Frame - Red, 48 |FR-R92R-48 | 1431.5| HL Road Frame - Red, 52 |FR-R92R-52 | 1431.5| HL Road Frame - Red, 56 |FR-R92R-56 | 1431.5| HL Road Frame - Red, 58 |FR-R92R-58 | 1431.5| HL Road Frame - Red, 62 |FR-R92R-62 | 1431.5| HL Road Front Wheel |FW-R820 | 330.06| HL Road Handlebars |HB-R956 | 120.27| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Form one long string to display the last and first name.
Next: Return total sales and the discounts for each product.
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://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-193.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics