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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics