Relational Online 4

profilemandrak_ingles

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.

  • 9 years ago
  • 15
Answer(1)

Purchase the answer to view it

blurred-text
NOT RATED
  • attachment
    solution_4.zip
Bids(0)