Build a report listing all products and the customers who have purchased them. Y

Question

Build a report listing all products and the customers who have purchased them. You may need to build a query to organize the data to support this report. For each product, show only the product name. Show the customerID, first name, and last name for each customer. Also show the time the product was ordered and the quantity ordered at that time. Order the products alphabetically by product name, then by lastname and firstname. Set the page orientation of the report to landscape—this will give you a bit more room to fit the fields required for this report. Save your report and name it “Purchases by Product” (without the quotes). When your report is complete, it should resemble the following.

Explanation / Answer

The question has two parts. First one is to write a query and second is to develop the reports.As the reporting platform is not mentioned( the tool in which report needs to be developed), I assume this question is for writing the query.

Assumptions made for the Query-
1. The customer table name is "Customer"
2. The Product table name is Product.
3. The Order details table name is Orderdetails.
4. The orderdetails table and customer table can be joined with customerId column. The product and orderdetails table can be joined by productId table.

Query-
Select ProductID,productName , customerID, FirstName, LastName, Purchasetime, quantity
from orderdetails od
inner join product p
on od.productId=p.productId
inner join customer c
on od.customerID=c.customerID
order by productName, FirstName, LastName asc;

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

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