w3resource

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

Click to view Full table

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|

Click to view Full table

Sample Solution:

SELECT DISTINCT Name
FROM Production.Product AS p 
WHERE EXISTS
    (SELECT *
     FROM Production.ProductModel AS pm 
     WHERE p.ProductModelID = pm.ProductModelID
           AND pm.Name LIKE 'Long-Sleeve Logo Jersey%');

Or

SELECT DISTINCT Name
FROM Production.Product p
WHERE ProductModelID IN
    (SELECT ProductModelID 
     FROM Production.ProductModel AS pm
     WHERE p.ProductModelID = pm.ProductModelID
        AND Name LIKE '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.



Follow us on Facebook and Twitter for latest update.