I need help with two brief SQL Questions(summary query) Schema Question Administ
ID: 3750039 • Letter: I
Question
I need help with two brief SQL Questions(summary query)
Schema
Question
Administrators AdminlD EmailAddress Password FirstName LastName Orderltems ItemID OrderID ProductiD temPrice DiscountAmount Products ProductiD CategoryID ProductName Description ListPrice DiscountPercent DateAdded Orders Addresses OrderlD CustomerlD OrderDate ShipAmount TaxAmount ShipDate ShipAddressID AddresSID CustomeriD Line1 Line2 Customers CustomerID EmailAddress Password FirstName State ZipCode ShippingAddressID BillingAddressID Categories CategoryID CategoryName CardNumber CardExpires BillingAddressIDExplanation / Answer
Answer:
1).select count(OrderID) as "Total number of Orders",sum(ShipAmount) as "Total Shipping Amount" from Orders. /*This query will list down the total count of orders placed and total amount of shipping Amount*/
Expected Output:
Total Number of Orders Total Shipping Amount
1232 $1345.23
2)select cust.EmailAdress,count(orde.OrderID) as "Total Orders",((ordi.Itemprice-ordi.DiscountAmount)*ordi.Quantity) as "Total Purchase Amount"
from Customers cust
join Orders orde on orde.CustomerID = cust.CustomerID
join OrderItems ordi on ordi.OrderID = orde.OrderID
group by cust.EmailAddress
/*This query will list down all the Mail address, Total orders and total purchase amount of the Customer*/
Expected Output:
EmailAddress Total Orders Total Purchase Amount
Sophie@**.com 20 $324.89
Marlie@**.com 30 $5689..98
Let me know incase if you have any queries, Thank you and All the best.