AdventureWorks Database: Find the product name that has a long sleeve logo jersey model
196. From the following tables write a query in SQL to retrieve one instance of each product name whose product model is a long sleeve logo jersey, and the ProductModelID numbers match between the tables.
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 table: Production.ProductModel
productmodelid|name |catalogdescription|instructions|rowguid |modifieddate |
--------------+---------------------------+------------------+------------+------------------------------------+-----------------------+
1|Classic Vest | | |29321d47-1e4c-4aac-887c-19634328c25e|2013-04-30 00:00:00.000|
2|Cycling Cap | | |474fb654-3c96-4cb9-82df-2152eeffbdb0|2011-05-01 00:00:00.000|
3|Full-Finger Gloves | | |a75483fe-3c47-4aa4-93cf-664b51192987|2012-04-30 00:00:00.000|
4|Half-Finger Gloves | | |14b56f2a-d4aa-40a4-b9a2-984f165ed702|2012-04-30 00:00:00.000|
5|HL Mountain Frame | | |fdd5407b-c2db-49d1-a86b-c13a2e3582a2|2011-05-01 00:00:00.000|
6|HL Road Frame | | |4d332ecc-48b3-4e04-b7e7-227f3ac2a7ec|2008-03-31 00:00:00.000|
7|HL Touring Frame | |[XML] |d60ed2a5-c100-4c54-89a1-531404c4a20f|2015-04-15 16:34:28.980|
8|LL Mountain Frame | | |65bf3f6d-bcf2-4db6-8515-fc5c57423037|2012-10-19 09:56:38.273|
9|LL Road Frame | | |ddc67a2f-024a-4446-9b54-3c679baba708|2011-05-01 00:00:00.000|
10|LL Touring Frame | |[XML] |66c63844-2a24-473c-96d5-d3b3fd57d834|2015-04-15 16:34:28.980|
11|Long-Sleeve Logo Jersey | | |20efe3f1-a2f8-4dde-b74b-18265f61f863|2011-05-01 00:00:00.000|
12|Men's Bib-Shorts | | |219e2f87-26a9-483b-b968-04578e943096|2012-04-30 00:00:00.000|
-- more --
Sample Solution:
-- Selecting distinct product names
SELECT DISTINCT Name
-- From the Production.Product table aliased as 'p'
FROM Production.Product AS p
-- Checking for the existence of a subquery result
WHERE EXISTS
(SELECT *
-- From the Production.ProductModel table aliased as 'pm'
FROM Production.ProductModel AS pm
-- Matching condition: ProductModelID and Name criteria
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name LIKE 'Long-Sleeve Logo Jersey%');
Explanation:
- This SQL query retrieves distinct product names from the Product table that meet specific criteria related to a product model.
- 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 DISTINCT clause ensures that only unique product names are returned in the result set.
- The FROM clause specifies the source table as Production.Product aliased as 'p'.
- The WHERE clause filters records based on the existence of a subquery result.
- The subquery checks for the existence of records in the ProductModel table where the ProductModelID matches and the Name starts with 'Long-Sleeve Logo Jersey'.
Or
-- Selecting distinct product names
SELECT DISTINCT Name
-- From the Production.Product table aliased as 'p'
FROM Production.Product p
-- Filtering records based on ProductModelID matching a subquery result
WHERE ProductModelID IN
(SELECT ProductModelID
-- From the Production.ProductModel table aliased as 'pm'
FROM Production.ProductModel AS pm
-- Matching condition: ProductModelID and Name criteria
WHERE p.ProductModelID = pm.ProductModelID
AND Name LIKE 'Long-Sleeve Logo Jersey%');
Explanation:
- This SQL query retrieves distinct product names from the Product table that meet specific criteria related to a product model.
- 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 DISTINCT clause ensures that only unique product names are returned in the result set.
- The FROM clause specifies the source table as Production.Product aliased as 'p'.
- The WHERE clause filters records based on the ProductModelID matching a subquery result.
- The subquery selects ProductModelID from the ProductModel table, where both the ProductModelID matches and the name starts with 'Long-Sleeve Logo Jersey'.
Sample Output:
name | ---------------------------+ Long-Sleeve Logo Jersey, L | Long-Sleeve Logo Jersey, M | Long-Sleeve Logo Jersey, S | Long-Sleeve Logo Jersey, XL|
Go to:
PREV : Calculate the revenue for each product in each sales order.
NEXT : Retrieve the name of each employee whose bonus in the SalesPerson table is 5000.
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.