Question

SQL help (all parts are related to each other so please answer them all, thank you!)

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

A. Find the EmployeeID who has the highest order amount(e.g. the total dollar amount for an order)in July 1996
B. Find the top three countries where consumers purchased the most(e.g. the total dollar amount for all orders placed for goods originated from a country)in all periods

Answer 1

a)

we just need to find the sum of order amount of each employee per order and sort the table in descending order and take the first row

Below is the query

SELECT o.EmployeeID FROM OrderDetails od, Orders o,Products p WHERE od.OrderID = o.OrderID AND od.productID = p.ProductID AND o.OrderDate like '1996-07%' GROUP BY o.EmployeeID ORDER BY sum(od.Quantity*p.price) DESC limit 1

b)

Select c.Country from Customers c, Orders o, OrderDetails od, Products p where c.CustomerID = o.CustomerID and od.productID = p.ProductID and o.OrderID = od.OrderId GROUP BY c.Country ORDER BY sum(od.Quantity*p.price) DESC limit 3