Please write queries based on the following requirements using WideWorldImporter
ID: 3725767 • Letter: P
Question
Please write queries based on the following requirements using WideWorldImporters Database
which is already installed on the SQL Server you used at class.
5. List the top 10 buying groups with the largest order line quantity. Order the list by order
quantity in descending order and include only customers with NULL credit limit and
where the order date year is 2015. (Sales.Orders, Sales.OrderLines, Sales.Customers,
Sales.BuyingGroups)
Explanation / Answer
5) For this query 4 tables are joined on common attributes. Aggregate function COUNT is used to get the total quantity by BuyingGroupName (by using group by). Order by is used to result in descending order of quantity. TOP 10 is used to fetch the only top 10 buyersgroup.
Query
SELECT TOP 10 b.BuyingGroupName, COUNT(ol.Quantity) AS TheQty
FROM Sales.BuyingGroupName AS b
INNER JOIN Sales.Customers AS c
ON b.BuyingGroupID = c.BuyingGroupID
INNER JOIN Sales.Orders AS o
ON o.CustomerID = c.CustomerID
INNER JOIN Sales.OrderLines AS ol
ON ol.OrderID = o.OrderID
GROUP BY b.BuyingGroupName
ORDER BY COUNT(ol.Quantity) DESC;