IT452 Unit 6 Set Operations and Data Modification

IT452 Unit 6 Set Operations and Data Modification

1. List the contact name, contact title, address, city, region, postal code, and country of all customer and supplier contacts. Sort the results by country, then by region, then by city. Use UNION ALL. 120 rows returned

2. List the city, region, and country that have both a customer and a supplier. Use INTERSECT. 4 rows returned

3. Create a table in the tempdb database using the following CREATE TABLE statement:
USE tempdb; CREATE TABLE dbo.DOGS ( DogID int IDENTITY NOT NULL ,Name varchar(20) NOT NULL ,BirthDay date ,Alive char(1) NOT NULL — Either ‘Y’ or ‘N’ ); GO
Write one INSERT statement with one VALUES clause to insert information about the following dogs:
Name Birthday Alive
Samantha 1993-03-17 Passed away Feb 2009 (Alive = ‘N’)
Misty 1993-06-20 No longer alive
Henry the 1/8th 2003-10-21 Alive
(Note: a small dog) Inka 2006-09-18 Alive
Result: (4 row(s) affected) Paste your INSERT statement into your Assignment document.

4. Use SELECT … INTO … syntax to create a Vendor table (name the table Vendor) in the tempdb database that has the exact structure and content as the purchasing.vendor table. Validate that the new vendor table was created in the tempdb (using SQL Query) and that all rows were inserted. Paste both queries into your project document. (104 rows returned)

5. Write a DELETE statement that removes the vendors from the tempdb.vendor table that have the PreferredVendorStatus flag turned on. Result: (11 row(s) affected)

6. The tempdb.vendor table is no longer needed. Please truncate it. Verify the table has been emptied using a SQL Query.

7. Run the following code to create the tempdb.dbo.DimProducts table:
USE tempdb;
CREATE TABLE [dbo].[DimProducts]( [dimProdID] [int] NOT NULL,
[ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
[UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
[EndDate] [date] NOT NULL, CONSTRAINT [PK_Products] PRIMARY KEY
CLUSTERED ( [dimProdID] ASC ) );
GO
Use bcp to import data into the tempdb.dbo.DimProducts table from the DimProducts.txt file. The file may be obtained from Doc Sharing.
You should get a message that 77 rows were imported. Put a copy of your bcp command into your Assignment document.

8. Run the following code to create the tempdb.dbo.ProductStage table:
USE tempdb;
CREATE TABLE [dbo].[ProductStage]( [dimProdID] [int] NOT NULL,
[ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
[UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
[EndDate] [date] NOT NULL );
GO
Use BULK INSERT to import data into the tempdb.dbo.ProductStage table from the ProductStage.csv file. That file may be obtained from Doc Sharing.
You should get a message that 27 rows were imported. Put a copy of your BULK INSERT command into your Assignment document.

9. Write a query that determines if there are any product names in the tempdb.dbo.ProductStage table that are not in the tempdb.dbo.DimProducts table. Use EXCEPT. Put a copy of your query into your Assignment document. In addition, put the product name(s) you returned from this query into your Assignment document.

10. Write a MERGE statement that modifies the tempdb.dbo.DimProducts table based on the contents of the tempdb.dbo.ProductStage table. When the dimProdID values match, update the target table based on what is different in the source row. [Examine the data in both tables to determine what row(s) will need updating.] When the dimProdID values do not match, insert the source row. Paste your MERGE query into your Assignment document. It should return a message that 27 row(s) were affected. In addition, report how many rows.