Question

Report on the top Customer Names and their Total Order Quantity, where Total Order Quantity is greater than 100, order results by Total Order Quantity in Descending order. Hint: Join Order Details, Order and Customers. Sum the Quantity ordered in Quantity Details for all the Orders for every Customer, but only for sum of quantity > 100.

Answer 1

The student's question involves creating an SQL query to report top Customer Names and their Total Order Quantity from a database, where the sum of the quantities is greater than 100, and results are ordered in descending order.

Step-by-step explanation:

The question pertains to extracting business insights from a database through the use of SQL queries. The student is tasked with joining three tables: Order Details, Orders, and Customers to find the top Customer Names based on their Total Order Quantity. The key here is to calculate the sum of quantities ordered by each customer from the Order Details table, and to filter the results to only include customers with a Total Order Quantity greater than 100. The results must then be ordered in Descending order based on the Total Order Quantity.

An exemplified SQL query might look like this:

SELECT Customers.Name, SUM(Order Details.Quantity) AS TotalOrderQuantity
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Order Details ON Orders.OrderID = Order Details.OrderID
GROUP BY Customers.Name
HAVING SUM(Order Details.Quantity) > 100
ORDER BY TotalOrderQuantity DESC;

This query will compile the necessary information to meet the criteria set by the student's question. The HAVING clause is used to filter grouped records that satisfy the condition of having a sum of quantities over 100, and the ORDER BY clause ensures that the output is sorted in descending order based on Total Order Quantity.