class exercise-5

docx

School

Conestoga College *

*We aren’t endorsed by this school

Course

PROG8400

Subject

Information Systems

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by SuperMoon13224

Class Exercise Mar-13 Relational Database Design (PROG8401-5) 1. Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database.

This view should return these columns from the Customers table: CustomerID, EmailAddress, LastName and FirstName. This view should return columns with the following names created from the Addresses table: BillLine1, BillLine2, BillCity, BillState, BillZip, ShipLine1, ShipLine2, ShipCity, ShipState, and ShipZip. Use the BillingAddressID and ShippingAddressID columns in the Customers table to determine which addresses are billing addresses and which are shipping addresses. Ans : 2. Write a SELECT statement that returns these columns from the CustomerAddresses view that you created in exercise 1: CustomerID, LastName, FirstName, BillLine1.

Ans : 3. Write an UPDATE statement that updates the CustomerAddresses view you created in exercise 1 so it sets the first line of the shipping address to “1990 Westwood Blvd.” for the customer with an ID of 8.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Ans : 4. Create a view named OrderItemProducts that returns columns from the Orders, OrderItems, and Products tables. This view should return these columns from the Orders table: OrderID, OrderDate, TaxAmount, and ShipDate.

This view should return these columns from the OrderItems table: ItemPrice, DiscountAmount, FinalPrice (the discount amount subtracted from the item price), Quantity, and ItemTotal (the quantity times the final price). This view should return the ProductName column from the Products table. Ans : 5. Create a view named ProductSummary that uses the view you created in exercise 4 to return some summary information about each product.

Each row should include these columns: ProductName, OrderCount (the number of times the product has been ordered), and OrderTotal (the total sales for the product). Ans : 6. Write a SELECT statement that uses the view that you created in exercise 5 to get the total sales for each of the five best selling products. Sort the results by the total sales in descending order.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Ans :

class exercise-5

Related Documents