Problem 20: Categories, and the total products in each category
For this problem, we’d like to see the total number of products in each category. Sort the results by the total number of products, in descending order. We have a table called Shippers. Return all the fields from all the shippers.
Solution
select C.CategoryName, COUNT(P.ProductID) AS TotalProducts
from Products AS P JOIN Categories AS C ON P.CategoryID = C.CategoryID
group by C.CategoryName
ORDER BY COUNT(P.ProductID) DESC;
Problem 21: Total customers per country/city
In the Customers table, show the total number of customers per Country and City.
Solution
select Country, City, COUNT(CustomerID) AS TotalCustomers
from Customers
group by Country, City
ORDER BY COUNT(CustomerID) DESC;
Problem 22: Products that need reordering
What products do we have in our inventory that should be reordered? For now, just use the fields UnitsInStock and ReorderLevel, where UnitsInStock is less than the ReorderLevel, ignoring the fields UnitsOnOrder and Discontinued. Order the results by ProductID.
Solution
select ProductID, ProductName, UnitsInStock, ReorderLevel
from Products
where UnitsInStock < ReorderLevel
order by ProductID;
Problem 23: Products that need reordering, continued
Now we need to incorporate these fields— UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued—into our calculation. We’ll define “products that need reordering” with the following:
- UnitsInStock plus UnitsOnOrder are less than or equal to ReorderLevel
- The Discontinued flag is false (0)
Solution
select ProductID, ProductName, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
from Products
where UnitsInStock + UnitsOnOrder < ReorderLevel
and Discontinued = 0
order by ProductID;
Problem 24: Customer list by region
A salesperson for Northwind is going on a business trip to visit customers, and would like to see a list of all customers, sorted by region, alphabetically. However, he wants the customers with no region (null in the Region field) to be at the end, instead of at the top, where you’d normally find the null values. Within the same region, companies should be sorted by CustomerID.
Solution
Select T.CustomerID, T.CompanyName, T.Region
from
(Select CustomerID, CompanyName, Region,
Case when Region is null then 1 else 0 End As SortField
from Customers) AS T
order by T.SortField, T.Region, T.CustomerID;
Problem 25: High freight charges
Some of the countries we ship to have very high freight charges. We’d like to investigate some more shipping options for our customers, to be able to offer them lower freight charges. Return the three ship countries with the highest average freight overall, in descending order by average freight.
Solution
select Top 3 ShipCountry, AVG(Freight) AS AverageFreight
from Orders
group by ShipCountry
order by AVG(Freight) DESC;
Problem 26: High freight charges _ 2015
We’re continuing on the question above on high freight charges. Now, instead of using all the orders we have, we only want to see orders from the year 2015.
Solution
select Top 3 ShipCountry, AVG(Freight) AS AverageFreight
from Orders
where YEAR(OrderDate) = 2015
group by ShipCountry
order by AVG(Freight) DESC;
Problem 27: High freight charges with between
Another (incorrect) answer to the problem above is this:
Select Top 3 ShipCountry, AverageFreight = avg(freight)
From Orders Where OrderDate between '1/1/2015' and '12/31/2015'
Group By ShipCountry
Order By AverageFreight desc;
Notice when you run this, it gives Sweden as the ShipCountry with the third highest freight charges. However, this is wrong - it should be France. What is the OrderID of the order that the (incorrect) answer above is missing?
Solution
select OrderID, Freight, ShipCountry
from Orders
Where YEAR(OrderDate) = 2015
and
OrderID not in
(select OrderID
from Orders
where OrderDate between '1/1/2015' and '12/31/2015');
Problem 28: High freight charges _ last year
We’re continuing to work on high freight charges. We now want to get the three ship countries with the highest average freight charges. But instead of filtering for a particular year, we want to use the last 12 months of order data, using as the end date the last OrderDate in Orders.
Solution
select Top 3 ShipCountry, AVG(Freight) AS AverageFreight
from Orders
where OrderDate between
Dateadd(yy, -1, (Select Max(OrderDate) from Orders))
and
(Select Max(OrderDate) from Orders)
group by ShipCountry
order by AVG(Freight) DESC;
Problem 29: Inventory list
We’re doing inventory, and need to show information like the below, for all orders. Sort by OrderID and Product ID.
Solution
select O.EmployeeID, E.LastName, O.OrderID, P.ProductName, OD.Quantity
from Employees AS E, Orders AS O, Products AS P, OrderDetails as OD
where
O.EmployeeID = E.EmployeeID
and
OD.OrderID = O.OrderID
and
OD.ProductID = P.ProductID;
Problem 30: Customers with no orders
There are some customers who have never actually placed an order. Show these customers.
Solution
select CustomerID
from Customers
where
CustomerID not in (select DISTINCT(CustomerID) from Orders);
Problem 31: Customers with no orders
Customers with no orders for EmployeeID 4 One employee (Margaret Peacock, EmployeeID 4) has placed the most orders. However, there are some customers who’ve never placed an order with her. Show only those customers who have never placed an order with her.
Solution
select CustomerID
from Customers
where
CustomerID not in
(select DISTINCT(CustomerID)
from Orders where EmployeeID = 4);