NOTE: Written in SQL Express. Using \"MyGuitarShop database\". Orders Table: PK.

Question

NOTE: Written in SQL Express. Using "MyGuitarShop database".

Orders Table:

PK. OrderId, FK CustomerID

OrderItems Table:

PK. ItemID, FK OrderID, FK ProductID, Columns: ItemPrice, DiscountAmount, Quantity.

1. Write a script that uses three variables to store (1) the count of distinct orders made in the Orders table (2) the total amount of sales (sales means the price of the item with discount multiplied by the quantity) made in the OrderItems table and (3) the average sales calculated using the first two variables ($843.3). Don’t include TaxAmount or shipping costs.

Explanation / Answer

DECLARE
order_count INT;
sales_price NUMBER(20,2);
BEGIN

SELECT COUNT(distinct OrderID) INTO order_count
FROM Orders;

SELECT SUM((ItemPrice - DiscountAmount) * DiscountAmount) INTO sales_price
FROM OrderItems;

END;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.