Project #78645 - Database Optimization

 8 SQL statements

**Site for Adventureworks:  It has to be AdventureWorksLT2012

http://msftdbprodsamples.codeplex.com/releases/view/93587

 

You are hired by AdventureWorks as a Database administrator. You are required to integrate some Database script solutions into their existing database (AdventureWorksLT2012) to modify data from within the database.

Using the AdventureWorksLT2012 database, write a SQL DML statement that will modify the data. Base it on the following requirements:

Query #1: Write a script to create a new Product Category called ‘Shoes’. This category belongs under the category Clothing. Only ParentProductCategoryID and Name are required. The system will assign values for the other fields. Use a subquery to find the value for ParentProductCategoryID.

Query #2: Provide a SELECT statement to show that the new Product Category Shoes was inserted.

Query #3: Add the following two products in the data base for your new Shoes category. Insert other appropriate column values as necessary. Use a subquery to find the ProductCategoryID for Shoes.

 

Query #4: Provide a SELECT statement to show only the newly inserted products using a subquery to find the ProductCategoryID for Shoes.
 

Name

Product Category

Product Number

Standard Cost

List Price

Sell Start Date

Men’s Black Size 10

Shoes

SH-1000

30.99

55.99

Today’s date

Woman’s White Size 38

Shoes

SH-2000

28.99

50.99

Today’s date

Query #5: Utilizing a subquery, provide a script to display all product fields for products in the Mountain Frames Product category that are silver. Look at the List Price.

Query #6: Utilizing a subquery, increase the list price by 5% for products in the Mountain Frames Product category that are silver. Rerun Query #5 and verify that the price has changed.

Query #7: Utilizing a subquery, change the unit price discount in the Sales Order Details table on all orders of products of an ordered quantity of more than 15 to that of the largest discount ever given on any product.

Query #8: You have decided against the new Fast-Food category. Provide SQL scripts to delete the Fast-Food category as well as any products assigned to that category. Use a subquery to delete the products.

Subject Computer
Due By (Pacific Time) 08/14/2015 12:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy