Databases MSSQL Server Exam - 27 Jun 2020. Problem 9. Past Expenses

-- Databases MSSQL Server Exam - 27 Jun 2020
-- Problem 9. Past Expenses

SELECT Jobs.JobId JobId, ISNULL(SUM (OrderParts.Quantity*Parts.Price),0)[Total cost] FROM Jobs
LEFT JOIN Orders ON Orders.JobId = Jobs.JobId
LEFT JOIN OrderParts ON Orders.OrderId = OrderParts.OrderId
LEFT JOIN Parts ON Parts.PartId = OrderParts.PartId
WHERE Jobs.Status = 'Finished'
GROUP BY Jobs.JobId
ORDER BY [Total cost] DESC, Jobs.JobId