Please submit your answer either in SQL code: The following questions are based
ID: 3699551 • Letter: P
Question
Please submit your answer either in SQL code:
The following questions are based on the sample database provided on W3schools.com (30 points in total, 3points/each except Q.9).
6. What is the total amount of money (TotalDue) spent on each order (orderID), and rounded the total by 2 decimal places?
7. What is the total amount of money (TotalAmount) spent by each customer (customerID, customerName)? Order the result by TotalAmount.
8. What is the Total quantity (TotalQuantitySoldOut) sold for each product (ProductID and ProductName)? And Order the result by TotalQuantity.
9. What insights can you discover from the above (or any other) questions? What actions would you suggest the business to take based on those insights in order to improve business performance? Make three examples. (6 points)
Explanation / Answer
6. SELECT o1.OrderID,ROUND(SUM(p.Price),2) as TotalAmountPerOrder FROM [Orders] o1
inner join [OrderDetails] od on od.OrderID = o1.OrderID
inner join [Products] p on p.ProductID = od.ProductID
group by o1.OrderID
7. SELECT c.CustomerID,c.CustomerName,ROUND(SUM(p.Price),2) as TotalAmount FROM [OrderDetails] od
join Orders o on o.OrderID = od.OrderID
join Products p on p.ProductID = od.ProductID
join Customers c on c.CustomerID = o.CustomerID
group by c.CustomerID
order by TotalAmount
8.SELECT p.ProductID,p.ProductName,SUM(od.Quantity) as TotalQuantity FROM [OrderDetails] od
join Products p on p.ProductID = od.ProductID
group by p.ProductID
order by TotalQuantity
9.bussiness needs to work on selling product "Laughing Lumberjack Lager".