w3resource

AdventureWorks Database: Retrieve products that first two digits of listprice 33

SQL Query - AdventureWorks: Exercise-80 with Solution

80. From the following table write a query in SQL to retrieve the name of the products. Product, that have 33 as the first two digits of 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 --

Click to view Full table

Sample Solution:

-- Selecting the substring of product names up to 30 characters and the list price
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice  

-- From the Production schema's Product table
FROM Production.Product  

-- Filtering the results to include only rows where the list price starts with '33'
WHERE CAST(ListPrice AS char(2)) LIKE '33%';

Explanation:

  • The SQL query retrieves data from the Product table within the Production schema.
  • It selects the substring of product names up to the first 30 characters and the list price.
  • The SUBSTRING() function is used to extract the first 30 characters of the product name to ensure it doesn't exceed the specified length.
  • The LIKE operator is used in combination with the CAST() function to filter the results based on the list price.
  • The CAST() function converts the 'ListPrice' column to a character data type (specifically, to the first 2 characters) for pattern matching.
  • The WHERE clause filters the rows to include only those where the list price starts with '33'.

Sample Output:

productname                  |listprice|
-----------------------------+---------+
LL Road Frame - Black, 58    |   337.22|
LL Road Frame - Black, 60    |   337.22|
LL Road Frame - Black, 62    |   337.22|
LL Road Frame - Red, 44      |   337.22|
LL Road Frame - Red, 48      |   337.22|
LL Road Frame - Red, 52      |   337.22|
LL Road Frame - Red, 58      |   337.22|
LL Road Frame - Red, 60      |   337.22|
LL Road Frame - Red, 62      |   337.22|
LL Road Frame - Black, 44    |   337.22|
LL Road Frame - Black, 48    |   337.22|
LL Road Frame - Black, 52    |   337.22|
Mountain-100 Silver, 38      |  3399.99|
Mountain-100 Silver, 42      |  3399.99|
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Show resulting expression that is too small to display.
Next: Round the value of SalesYTD dividing by CommissionPCT.

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.