w3resource

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


Click to view Full table

Sample Solution:

SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
WHERE ProductLine = 'R' 
AND DaysToManufacture < 4
ORDER BY Name ASC;

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

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