SQL Practice Problems - Intermediate

Solutions to intermediate problems in the book - SQL Practice Problems.
SQL
Published

December 8, 2019

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:

  1. UnitsInStock plus UnitsOnOrder are less than or equal to ReorderLevel
  2. 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);
Back to top