Q:

SQL query on Northwind multiple tables

From Northwind database I want to get total revenue generated by emplyee sales
Employee -> Orders -> "Order Details"
I am not sure if my solution gives the right data (it was partly guessing)
SELECT 
    Employees.FirstName, Employees.LastName,
    SUM(CONVERT(MONEY, ("Order Details".UnitPrice * Quantity * (1 - Discount) / 100)) * 100) AS ExtendedPrice
FROM 
    ((Orders
INNER JOIN 
    "Order Details" ON Orders.OrderID = "Order Details".OrderID)
INNER JOIN 
    Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY 
    LastName, FirstName;

Northwind database structure can be found here
Thank you in advance. It would be great to have a nice explanation as well

A:

Chris, your effort is pretty good first effort, so there are a few things to change on this.

You don't need to divide by 100 and then multiply by 100. The discount is already a %. Your operation just truncates the numbers. I would avoid to this too early in a process as it introduces rounding errors. It is better to keep numbers raw and keep their precision as best you can for as long as you can. It is OK to display numbers as money in the GUI though i.e. to 2 decimals but not in intermediate calculations due to error introduced by truncating.
Table names and field names with spaces should be handled using [] rather than quotes. That makes it easier to find misspelling so use [Order Details]
When grouping and summing, make sure you use the keys. So name is not a key, so use EmployeeID if you are trying to group individual employees, this is because in real datasets you may have 2 employees with the same name and their sales will be grouped together incorrectly using your code.
Try this course/book, it is a good intro to querying databases. https://www.microsoft.com/en-au/learning/exam-70-461.aspx
The reason how this works? Select syntax has Select [fieldlist] from [table] inner join [jointable] on [join fields] group by [grouping fields]. fieldlist can be a calculation as well as actual field names to display. "inner join" means you want only those orders, order details, employees where there is actual matching data - Correct in your scenario. [table] and [jointable] is the actual tables that contain your data in a relational sense. 
There is obvisouly a lot here to learn in one go. I would work through some of the different SQL Server querying courses that you can google.

Here's a revised version of the code:
SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Sum([Order Details].UnitPrice * Quantity * (1 - Discount)) AS ExtendedPrice
FROM Orders
INNER JOIN  [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN  Employees ON Orders.EmployeeID = Employees.EmployeeID
group by Employees.EmployeeID, Employees.FirstName, Employees.LastName
order by Employees.FirstName, Employees.LastName;

