AdventureWorks Database: Find all Silver colored bicycles with a standard price under $400
SQL Query - AdventureWorks: Exercise-156 with Solution
156. From the following tables, write a query in SQL to find all silver-colored bicycles with a standard cost not greater than $400. Return the columns ProductID, Name, Color, and StandardCost. Filter the results to include only those products where the ProductNumber starts with 'BK-'.
Sample table: Production.Productproductid|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 product ID, name, color, and standard cost from the Product table
SELECT
-- Selecting the ProductID column from the Product table
ProductID,
-- Selecting the Name column from the Product table
Name,
-- Selecting the Color column from the Product table
Color,
-- Selecting the StandardCost column from the Product table
StandardCost
-- From the Product table
FROM
Production.Product
-- Filtering records to include only those where the ProductNumber starts with 'BK-', Color is 'Silver', and StandardCost is not greater than $400
WHERE
ProductNumber LIKE 'BK-%'
AND Color = 'Silver'
AND NOT StandardCost > 400;
Explanation:
- This SQL code retrieves product IDs, names, colors, and standard costs of products meeting specific criteria.
- The SELECT statement specifies the columns to be included in the result set.
- The FROM clause indicates the table from which data is being retrieved, which is the Production.Product table.
- The WHERE clause filters records to include only those meeting the specified conditions:
- ProductNumber starts with 'BK-'
- Color is 'Silver'
- StandardCost is not greater than $400.
Sample Output:
productid|name |color |standardcost| ---------+-----------------------+------+------------+ 984|Mountain-500 Silver, 40|Silver| 308.2179| 985|Mountain-500 Silver, 42|Silver| 308.2179| 986|Mountain-500 Silver, 44|Silver| 308.2179| 987|Mountain-500 Silver, 48|Silver| 308.2179| 988|Mountain-500 Silver, 52|Silver| 308.2179|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Identify people whose first name 'Gail' with area codes except 415.
Next: Identify Quality Assurance personnel who work evenings or nights.
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-156.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics