AdventureWorks Database: Include the contents of the ProductModelID and Name columns
SQL Query - AdventureWorks: Exercise-200 with Solution
200. Create a table public.gloves from Production.ProductModel for the ProductModelID 3 and 4.
From the following table write a query in SQL to include the contents of the ProductModelID
and Name columns of both the tables.
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:
Create Gloves table
-- Creating a new table named Gloves in the public schema with columns ProductModelID and Name
SELECT ProductModelID, Name
-- Populating the newly created table with data from the ProductModel table
INTO public.Gloves
-- Selecting records where ProductModelID is either 3 or 4
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
Explanation:
- This SQL query creates a new table named Gloves in the public schema and populates it with data from the ProductModel table.
- 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 clause specifies the columns (ProductModelID and Name) to be selected from the ProductModel table.
- The INTO clause creates a new table named Gloves in the public schema based on the specified column structure.
- The FROM clause specifies the source table as Production.ProductModel from which data will be retrieved.
- The WHERE clause filters records where the ProductModelID is either 3 or 4.
-- Selecting ProductModelID and Name from the ProductModel table
SELECT ProductModelID, Name
-- Filtering records where ProductModelID is not 3 or 4
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
-- Combining the result set with another query result using UNION
UNION
-- Selecting ProductModelID and Name from the Gloves table
SELECT ProductModelID, Name
-- From the Gloves table in the public schema
FROM public.Gloves
-- Ordering the combined result set by Name
ORDER BY Name;
Explanation:
- This SQL query retrieves ProductModelID and Name from both the ProductModel table and the Gloves table and combines them into a single result set, ordered by name.
- 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 first SELECT statement retrieves ProductModelID and Name from the ProductModel table where ProductModelID is not 3 or 4.
- The second SELECT statement retrieves ProductModelID and Name from the Gloves table in the public schema.
- The UNION operator combines the result sets of both SELECT statements into a single result set, eliminating duplicates.
- The ORDER BY clause arranges the combined result set by the Name column in ascending order.
Sample Output:
productmodelid|name |
--------------+---------------------------+
122|All-Purpose Bike Stand |
119|Bike Wash |
115|Cable Lock |
98|Chain |
1|Classic Vest |
2|Cycling Cap |
121|Fender Set - Mountain |
102|Front Brakes |
103|Front Derailleur |
3|Full-Finger Gloves |
4|Half-Finger Gloves |
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find the names of employees who have sold a particular product.
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://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-200.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics