AdventureWorks Database: Include the contents of the ProductModelID and Name columns

Last update on January 27 2026 12:22:49 (UTC/GMT +8 hours)

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

Click to view Full table

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

Go to:

PREV : Find the names of employees who have sold a particular product.

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.