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.
2: Provide a SELECT statement to show that the new Product Category Shoes was inserted.
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.
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
|
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.
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.
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.
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