AdventureWorks Database: Find the product name that has a long sleeve logo jersey model
SQL Query - AdventureWorks: Exercise-196 with Solution
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|
13|Men's Sports Shorts | | |45fe0d77-6645-473c-a116-1232baea8d43|2012-04-30 00:00:00.000|
14|ML Mountain Frame | | |0d48c51d-7603-4010-9265-0491805bb010|2012-04-30 00:00:00.000|
15|ML Mountain Frame-W | | |aa77697c-6d1c-48f1-845c-3cb089498715|2012-04-30 00:00:00.000|
16|ML Road Frame | | |3494e8ff-7daf-4860-abf6-97842048e272|2011-05-01 00:00:00.000|
17|ML Road Frame-W | | |ca18ecfd-2023-4fa7-a556-0321153bca34|2012-04-30 00:00:00.000|
18|Mountain Bike Socks | | |36b1a76a-dff3-4a55-86f9-65eb1cb18d7b|2011-05-01 00:00:00.000|
19|Mountain-100 |[XML] | |fca0665b-b956-489a-a5ec-6f0b4aa14d02|2011-05-01 00:00:00.000|
20|Mountain-200 | | |3b78edff-2aa9-4ac1-8c3d-94090b5f53a9|2012-04-30 00:00:00.000|
21|Mountain-300 | | |ecddd0d7-2db2-464d-b2da-89bffc6276aa|2012-04-30 00:00:00.000|
22|Mountain-400-W | | |6d2fcce4-ffce-4662-a3f8-5d18f0eedcd8|2013-04-30 00:00:00.000|
23|Mountain-500 |[XML] | |866dbad3-5999-4329-beac-d826d959d9a1|2012-10-19 09:56:38.273|
24|Racing Socks | | |bd8ba6f8-7e16-4fa3-b3b3-2036dd4a2ae0|2013-04-30 00:00:00.000|
25|Road-150 |[XML] | |94ffb702-0cbc-4e3f-b840-c51f0d11c8f6|2011-05-01 00:00:00.000|
26|Road-250 | | |3770c5e3-8dc9-43c7-b735-7aff21645d96|2012-04-30 00:00:00.000|
27|Road-350-W | | |dfe49035-7720-4ff4-b28b-16250ee46259|2013-04-30 00:00:00.000|
28|Road-450 |[XML] | |8456bb94-b4dd-4a47-a76b-d0e54ab4285d|2011-05-01 00:00:00.000|
29|Road-550-W | | |f85f84f2-9ce0-4ecc-9c29-e78021ffc877|2012-04-30 00:00:00.000|
30|Road-650 | | |42e1c597-6dd9-4071-b1a5-1dc5cdcbdbca|2011-05-01 00:00:00.000|
31|Road-750 | | |2bf795f4-2666-4691-af14-d490c7334a8a|2012-10-19 09:56:38.273|
32|Short-Sleeve Classic Jersey| | |6beccf2d-eacd-496b-995b-d692567565cd|2013-04-30 00:00:00.000|
33|Sport-100 | | |47f7c450-d16a-4cea-be6e-2d6c8c8f81ee|2011-05-01 00:00:00.000|
34|Touring-1000 |[XML] | |52e7f2c1-dbff-4518-927d-c7d46f9ed32e|2012-10-19 09:56:38.273|
35|Touring-2000 |[XML] | |aa10d9e6-e33f-4da8-ace1-992fcd6bb171|2012-10-19 09:56:38.273|
36|Touring-3000 | | |f5a6ec78-4451-45db-955f-db197de8b059|2012-10-19 09:56:38.273|
37|Women's Mountain Shorts | | |a08dd61a-6155-4051-9a11-223232ea51cd|2013-04-30 00:00:00.000|
38|Women's Tights | | |a96ff80d-d52a-432f-9701-731bef16efcc|2012-04-30 00:00:00.000|
39|Mountain-400 | | |37d261a7-00cf-4880-ac1a-533b6b4365b0|2011-05-01 00:00:00.000|
40|Road-550 | | |30450264-4ab8-45e0-8bb5-4d407ea2950a|2012-04-30 00:00:00.000|
41|Road-350 | | |d71bd21c-239e-4c2b-98a3-101962d6b2d3|2012-10-19 09:56:38.273|
42|LL Mountain Front Wheel | | |aa977b32-acd8-4c53-a560-88a02ac1954d|2012-10-19 09:56:38.273|
43|Touring Rear Wheel | |[XML] |e878fcaa-61cc-4014-988a-51f52643f7aa|2015-04-15 16:34:28.997|
44|Touring Front Wheel | |[XML] |6da78798-3793-4b8e-829e-dba9d140b1d4|2015-04-15 16:34:28.997|
45|ML Mountain Front Wheel | | |c3ff3f93-60a8-4957-b076-b7d0984ee70f|2012-04-30 00:00:00.000|
46|HL Mountain Front Wheel | | |cca597fb-195f-4ec5-bf5c-15b98d176f4c|2012-04-30 00:00:00.000|
47|LL Touring Handlebars | |[XML] |84138622-1ea6-489f-9c98-6e3924cfbac0|2015-04-15 16:34:29.010|
48|HL Touring Handlebars | |[XML] |9da82e49-80ad-4918-9a54-31f4b0c8eabb|2015-04-15 16:34:29.027|
49|LL Road Front Wheel | | |90f759c1-2073-4d9f-854b-c6b6f3bf9162|2012-04-30 00:00:00.000|
50|ML Road Front Wheel | | |980c8cd0-4903-41f2-9ffc-773c7fe4c254|2012-04-30 00:00:00.000|
51|HL Road Front Wheel | | |02a562e2-4dfa-4778-bbac-bbddcecf99b0|2012-04-30 00:00:00.000|
52|LL Mountain Handlebars | | |699c2ac5-5406-46d2-863d-dcfb23fc7943|2012-04-30 00:00:00.000|
53|Touring Pedal | |[XML] |b98a3207-56fc-405c-a040-3c7a90cc7890|2015-04-15 16:34:29.027|
54|ML Mountain Handlebars | | |c9fcc804-2cd7-4b8a-b186-9c409cc19df9|2012-04-30 00:00:00.000|
55|HL Mountain Handlebars | | |782c991b-a660-4561-a3f4-9bbd74259747|2012-04-30 00:00:00.000|
56|LL Road Handlebars | | |a7e65199-84a8-437e-ad55-360c1df1d788|2012-04-30 00:00:00.000|
57|ML Road Handlebars | | |02200aa0-c369-4d77-a67c-75973efda81b|2012-04-30 00:00:00.000|
58|HL Road Handlebars | | |2489ddc5-1c89-4dec-af22-b0112ccec467|2012-04-30 00:00:00.000|
59|LL Headset | | |39afbba9-0f6c-44ee-b5e1-32fa93f897e6|2012-04-30 00:00:00.000|
60|ML Headset | | |6ba9f3b6-e08b-4ac2-a725-b41114c2a283|2012-04-30 00:00:00.000|
61|HL Headset | | |e196d02e-9bf6-4c67-b772-ed9f86ccf44c|2012-04-30 00:00:00.000|
62|LL Mountain Pedal | | |8123f7e2-a5f4-4047-b69d-e74313dfebce|2013-04-30 00:00:00.000|
63|ML Mountain Pedal | | |be9cdc56-f4ab-40f1-b338-2e08e0627abd|2013-04-30 00:00:00.000|
64|HL Mountain Pedal | | |8da73708-8dae-44ae-ac6c-6e37022c1ffe|2013-04-30 00:00:00.000|
65|ML Touring Seat/Saddle | | |63a2199f-f5b5-49bd-bcfc-bec1d1d16d8b|2013-04-30 00:00:00.000|
66|LL Touring Seat/Saddle | |[XML] |4a17c43a-1a55-41bb-bc97-612f47cedeb3|2015-04-15 16:34:29.043|
67|HL Touring Seat/Saddle | |[XML] |059a2000-7549-4b49-8e0c-2de6b2771ef4|2015-04-15 16:34:29.043|
68|LL Road Pedal | | |218b016c-7454-4193-b518-21955c783d72|2013-04-30 00:00:00.000|
69|ML Road Pedal | | |3cdf61d6-6209-436f-b235-82e8f159208b|2013-04-30 00:00:00.000|
70|HL Road Pedal | | |35677b42-72ca-4d9e-a966-dd874b83ef45|2013-04-30 00:00:00.000|
71|LL Mountain Seat/Saddle 1 | | |a166af4c-87bb-41aa-8496-d76b26008fb3|2013-04-30 00:00:00.000|
72|ML Mountain Seat/Saddle 1 | | |baa9405b-68ca-4c18-bc9c-1c4acf49bafc|2013-04-30 00:00:00.000|
73|HL Mountain Seat/Saddle 1 | | |bbe4918f-198d-43c7-9f4b-79bc2aa08f2b|2013-04-30 00:00:00.000|
74|LL Road Seat/Saddle 2 | | |24e3e7d4-4053-4035-9d69-f451642f0c1e|2013-04-30 00:00:00.000|
75|ML Road Seat/Saddle 1 | | |394ed69c-2cc4-4a85-9080-8534112b66fe|2013-04-30 00:00:00.000|
76|HL Road Seat/Saddle 1 | | |b83ab7ae-ba3f-40df-8296-361915a3a60c|2013-04-30 00:00:00.000|
77|ML Road Rear Wheel | | |15702f98-bd92-4fe8-86bc-52f5fd049d3d|2012-04-30 00:00:00.000|
78|HL Road Rear Wheel | | |438cbcfa-05ff-4a29-ad95-ecf41dcb83d5|2012-04-30 00:00:00.000|
79|LL Mountain Seat/Saddle 2 | | |8bbeb399-5a87-4e40-9f52-462fb54f2183|2013-04-30 00:00:00.000|
80|ML Mountain Seat/Saddle 2 | | |5cefbb6e-3b7e-414f-ac1b-8f6df741fb21|2013-04-30 00:00:00.000|
81|HL Mountain Seat/Saddle 2 | | |98726f80-e9b9-4141-9cf5-bd2ef07dce25|2013-04-30 00:00:00.000|
82|LL Road Seat/Saddle 1 | | |00ce9171-8944-4d49-ba37-485c1d122f5c|2013-04-30 00:00:00.000|
83|ML Road Seat/Saddle 2 | | |feeb8440-446e-4df8-9482-d529c4fc5e8f|2013-04-30 00:00:00.000|
84|HL Road Seat/Saddle 2 | | |0d3a6ad7-6891-4de9-b14f-e1a841eb220c|2013-04-30 00:00:00.000|
85|LL Mountain Tire | | |e3cdc5dd-27c3-4891-9d5e-0d46d1b8457f|2013-04-30 00:00:00.000|
86|ML Mountain Tire | | |0434f63a-a361-4d0b-a9fc-8ac2a866ce85|2013-04-30 00:00:00.000|
87|HL Mountain Tire | | |ce1b1064-6679-4212-8f56-2b2617ec56a5|2013-04-30 00:00:00.000|
88|LL Road Tire | | |e7b00dff-8136-4947-b503-994584cc89e7|2013-04-30 00:00:00.000|
89|ML Road Tire | | |d566eb0f-6945-43d8-bc40-bb3d2f4ef7ed|2013-04-30 00:00:00.000|
90|HL Road Tire | | |a4b205df-955a-494e-8428-1898aea76f24|2013-04-30 00:00:00.000|
91|Touring Tire | | |3bcc63d6-9340-4b93-b5f2-73fa90758bf5|2013-04-30 00:00:00.000|
92|Mountain Tire Tube | | |8cfbe7f2-eec3-4ba6-8187-c8a3614f1f0b|2013-04-30 00:00:00.000|
93|Road Tire Tube | | |2771d2d2-2e35-4c12-966e-ce9070df6d53|2013-04-30 00:00:00.000|
94|Touring Tire Tube | | |deeea9bc-3c8c-4e73-b6b0-64c81a5d99e3|2013-04-30 00:00:00.000|
95|LL Bottom Bracket | | |217e7475-d3f4-46fa-836a-d9e53103e71b|2013-04-30 00:00:00.000|
96|ML Bottom Bracket | | |09caa74e-f47b-4fca-b206-9d3e46df9751|2013-04-30 00:00:00.000|
97|HL Bottom Bracket | | |816360e1-3dee-4568-bf2f-9828243d887b|2013-04-30 00:00:00.000|
98|Chain | | |aca920b2-d0f9-49f3-b879-573202b08c2f|2013-04-30 00:00:00.000|
99|LL Crankset | | |5b59f032-9b73-4d90-b252-eafd6a871ff1|2013-04-30 00:00:00.000|
100|ML Crankset | | |68c6cb29-d94a-40c5-aaad-90aa6e7c5ea1|2013-04-30 00:00:00.000|
101|HL Crankset | | |809668a3-d492-41fb-a196-cfe092a12aa2|2013-04-30 00:00:00.000|
102|Front Brakes | | |1099a23a-c9ed-41b1-8cc1-e2c1c54a10c8|2013-04-30 00:00:00.000|
103|Front Derailleur | | |10e0c8fd-ca13-437b-8e22-51853ae160a7|2013-04-30 00:00:00.000|
104|LL Fork | | |0481d7e1-4970-4efa-a560-020f6579918d|2012-04-30 00:00:00.000|
105|ML Fork | | |5f115aa4-0553-4478-84b3-5dcf3abe0d08|2012-04-30 00:00:00.000|
106|HL Fork | | |7706a8fd-9513-40bc-95e8-301b55b67db2|2012-04-30 00:00:00.000|
107|Hydration Pack | | |cfeef30f-f059-4447-92a8-47001e69f3db|2013-04-30 00:00:00.000|
108|Taillight | | |dba643d4-4cf2-4507-b947-e817d8c5792b|2012-04-30 00:00:00.000|
109|Headlights - Dual-Beam | | |7b17ebf1-cb73-4934-9689-1dc26cf22d9c|2012-04-30 00:00:00.000|
110|Headlights - Weatherproof | | |1fadb88f-af88-4e94-bb1e-6158c48e6b40|2012-04-30 00:00:00.000|
111|Water Bottle | | |3688268a-260c-48bf-bf71-fff350d4d3d5|2013-04-30 00:00:00.000|
112|Mountain Bottle Cage | | |2194e65b-9c13-46e1-a655-3ebff8a96719|2013-04-30 00:00:00.000|
113|Road Bottle Cage | | |9416c2dd-55d8-469d-8edf-ef447c511897|2013-04-30 00:00:00.000|
114|Patch kit | | |7c738101-c01e-45a2-a0e0-b28aeba1dc40|2013-04-30 00:00:00.000|
115|Cable Lock | | |e7e17f11-a7fd-4c3c-b701-68f0ae26143e|2012-04-30 00:00:00.000|
116|Minipump | | |90cef1a7-d817-403e-814c-40e305eeefef|2012-04-30 00:00:00.000|
117|Mountain Pump | | |b35598f6-b413-4138-8081-5dc7d4c64b64|2012-04-30 00:00:00.000|
118|Hitch Rack - 4-Bike | | |f570e0d1-e978-4ff2-b5b1-08f01ab60219|2013-04-30 00:00:00.000|
119|Bike Wash | | |90b1b93d-ebc8-44a2-ac08-cdd1d20ca39c|2013-04-30 00:00:00.000|
120|Touring-Panniers | | |f06999a1-3aa7-4e85-b8cb-049eb2c391fa|2012-04-30 00:00:00.000|
121|Fender Set - Mountain | | |c88d1136-a8bb-46bb-94aa-8c1854f813cc|2013-04-30 00:00:00.000|
122|All-Purpose Bike Stand | | |6eab8607-d927-40e1-af30-d8a2a953050c|2013-04-30 00:00:00.000|
123|LL Mountain Rear Wheel | | |29521f66-2926-471f-867b-668b0b9ec2b0|2012-04-30 00:00:00.000|
124|ML Mountain Rear Wheel | | |d968d774-778e-4399-a3c5-375176418229|2012-04-30 00:00:00.000|
125|HL Mountain Rear Wheel | | |95450545-adf7-48f3-899e-964de8920dc6|2012-04-30 00:00:00.000|
126|LL Road Rear Wheel | | |95946bd4-c6d9-4344-8066-317d8957ea21|2012-04-30 00:00:00.000|
127|Rear Derailleur | | |f9327e5d-f8b6-40c5-bfa9-63f886bdfc24|2013-04-30 00:00:00.000|
128|Rear Brakes | | |71d47afd-da3a-43f1-83ad-69c71f96ef33|2013-04-30 00:00:00.000|
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|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: 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.
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-196.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics