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_age_categ: Find the top category ordered by customers of different age groups. This represents the hit category for each age group and helps in targeted advertising. Top category can be calculated by comparing the number of product units (total quantity sold) of each category ordered. Consider bins of size 10 for different ages. For example, a customer of age 45 will fall in bin {>40, <= 50}. The number of bins depends on the age groups of the customers available. If the lowest age available is 32 and largest is 67, then there will be 4 bins as {>30, <=40}, {>40, <=50}, {>50, <=60} and {>60, <=70}. The bin size is fixed but the number of bins depends on the range of age available.

output example:

HIT CATEGORIES
>30, <=40 >40, <=50 >50, <=60 >60, <=70

Electronics Clothing Home Appliances Clothing

Explanation / Answer

Follow the below mentioned quiries in order.....

1.CREATE TABLE FIRST_TABLE AS SELECT AGE,CATEGORY

FROM Customer as c,Product as p,Orders as o,OrderItems as or

WHERE c.CustomerId=o.CustomerId and o.OrderId=or.OrderId and p.ProductId=o.ProductId;

2.CREATE TABLE RESULT SELECT COUNT(*) `count`,CATEGORY
10*FLOOR(AGE/10) `from`,
10*FLOOR(AGE/10)+9 `to`
FROM FIRST_TABLE
GROUP BY FLOOR(age/10),CATEGORY

3.SELECT result.from,result.to, MAX(count),CATEGORY
FROM result
GROUP BY result.from

Note: beware of nomenclature . It may vary from ur machine...

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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