w3resource

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.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 --

Click to view Full table

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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