Write PL/SQL query for: Customer(CustomerId, FirstName, LastName, Address, Phone

Question

Write PL/SQL query for:

Customer(CustomerId, FirstName, LastName, Address, PhoneNo, Age)

Supplier(SupplierId, FirstName, LastName, Address, PhoneNo)

Product(ProductId, ProductName, Category, SupplierId, ProductDescription)

Inventory(ProductId, TotalStock, LastUpdatedOn)

Orders(OrderId, CustomerId, OrderDate, Status)

OrderItems(OrderId, ProductId, Quantity, UnitPrice, Discount)

Pro_category_info: Generate a report to display the details about each category. These details include the number of units of that category ordered, the average unit price of the products in that category, the average discounted amounts given. Notice that the discounts given in the data is in percentage while the report wants the average discount in whole amount. The average prices and average discounts should be formatted to 2 decimal places. Order the result by the total units sold in descending order. The computation of the averages should take the quantity into account.

CATEGORY REPORT
CATEGORY TOTAL_UNITS AVG_PRICE AVG_DISCOUNT

Clothing 130 98.00 6.45

Electronics 32 459.00 45.00

Explanation / Answer

Hey following is the query required to generate your report:

It is a bit complicated so try to understand it step by step. Cast is used to convert it to 2 decimal place.

Select Category,

Sum(Quantity) as TOTAL_UNITS,

Cast(Sum(Quantity*UnitPrice)/Sum(Quantity) as Decimal(10,2)) as AVG_PRICE,  
Cast(Sum(Discount*Quantity*UnitPrice)/(100* Sum(Quantity)) as Decimal(10,2)) as AVG_DISCOUNT

from OrderItems, Product where OrderItems.ProductId=Product.ProductId

group by Category order by SUM(Quantity) desc;

Kindly let me know if you have any doubts!

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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