To answer your initial question:
You should first start with the object you want to get data about, in this case Employees
:
SELECT LastName
FROM Employees -- Gets the LastName of every employee
Now we need a way to pull in Orders
information, since you want "the employee with the largest order quantity". The way we associate an Order
record with an Employee
record is with a JOIN
operator. In this case an INNER JOIN
is sufficient because INNER JOIN
returns rows only where there is a match between both tables. (This will filter out Employees
who have no Orders
which is what we want since you're looking for largest order quantity.)
SELECT Employees.LastName -- Specifying the Table name or using an Alias is good practice to avoid errors when the same column exists in two tables that are part of the same query (not the case here but it's best practice to do for readability too).
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
ON Employees.EmployeeId = Orders.EmployeeId
Now we need to do the same thing to bring in the OrderDetails
which is where your Quantity
data lives. Again an INNER JOIN
should be appropriate here, like so:
SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order.
ON Orders.OrderID = OrderDetails.OrderID
Notice my comments in the query. Since the first INNER JOIN of the query returns a row for every Order of each Employee, and the second INNER JOIN returns a row for every OrderDetail of every Order, this whole query now returns one row per OrderDetail of every Order for each Employee, so your row count will be Employees x Orders x OrderDetails essentially.
Now that we have all rows for Employee Order Quantities with the above query, we want to order the data by largest Quantity
descending and then filter out everything but the first record. We can accomplish that with an ORDER BY
clause and a LIMIT
(TOP
if you're using Microsoft SQL Server) like so:
SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order.
ON Orders.OrderID = OrderDetails.OrderID
ORDER BY OrderDetails.Quantity DESC
LIMIT 1;
The final note here is more of a question on what your OrderDetails
table represents (as a few other users have hinted at)? Is there always only one row in OrderDetails
per Order
like your example data shows or can have an Order
relate to multiple rows in the OrderDetails
table?
If an Order
can relate to multiple OrderDetails
then you need to do one more operation called a GROUP BY
which will allow us to condense multiple OrderDetail
rows that belong to the same Order
per Employee
and then aggregate their Quantities
by summing them like so:
SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order.
ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Employees.LastName, Orders.OrderID -- This condense the rows back down to just one row per Order per Employee.
ORDER BY SUM(OrderDetails.Quantity) DESC
LIMIT 1;
Note the above gets you the LastName
of the Employee
who had the largest Order
Quantity
on a single Order
. For example if EmployeeA has an Order with Quantity = 10 and EmployeeB has two different Orders, one with Quantity = 7 and the other with Quantity = 5, the above query will get you EmployeeA's LastName
. But if you want to condense all Quantities
between different Orders
for the same Employee
into one row to compare, such that in my previous example you'd want EmployeeB (because the sum of his two Orders' Quantities = 12 which is greater than 10 in EmployeeA's only Order) then you can loosen the GROUP BY
clause to group on just the Employee.LastName
field like so:
SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order.
ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Employees.LastName -- This condense the rows down to just one row per Employee's LastName.
ORDER BY SUM(OrderDetails.Quantity) DESC
LIMIT 1;
Now let's use what we learned above to answer your second question in the comments on "What product was ordered the most by customers in Germany?":
-- Gets the Product with the highest Quantity on a single OrderDetail
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
ORDER BY OrderDetails.Quantity DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity
Or:
-- Gets the Product with the highest Quantity on a single Order
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
GROUP BY Products.ProductID, Products.ProductName, Orders.OrderID -- Condenses all the OrderDetails rows to a single Order per Product and sums the Quantities for a total per Product per Order
ORDER BY SUM(OrderDetails.Quantity) DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity
Or:
-- Gets the Product with the highest Quantity across all Orders
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
GROUP BY Products.ProductID, Products.ProductName -- Condenses all the OrderDetails and Orders rows to a single row per Product and sums the Quantities for a total per Product
ORDER BY SUM(OrderDetails.Quantity) DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity