Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 5
In the previous blog, we answered some basic questions ranging from 15 to 19. I hope you found your answers in the provided information. We’ll be answering intermediate questions in this blog, so keep reading and you’ll find answers as you go. You can view the solution and description for any question by clicking on it.
Contents:
20. Categories, and the total products in each category.
21. Total customers per country/city
22. Products that need reordering
23. Products that need reordering, continued
24. Customer list by region.
25. High freight charges.
26. High freight charges — 2015.
27. High freight charges with between.
28. High freight charges — last year
29. Inventory list
30. Customers with no orders
31. Customers with no orders for EmployeeID 4
Problem 20: 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.
Description: To solve this problem, we need to combine a join, and a group by.A good way to start is by creating a query that shows the CategoryName and all ProductIDs associated with it, without grouping. Then, add the Group by.
Problem 21: In the Customers table, show the total number of customers per Country and City.
Description: Just as you can have multiple fields in a Select clause, you can also have multiple fields in a Group By clause.
Problem 22: 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.
Description: We want to show all fields where the UnitsInStock is less than the ReorderLevel. So in the Where clause, we have used the following:UnitsInStock < ReorderLevel
Problem 23: 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)
Description: For the first part of the Where clause, you should have something like this:UnitsInStock + UnitsOnOrder <= ReorderLevel
Problem 24: 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.
Description: We won’t be able to sort directly on the Region field here. We’ll need to sort on the Region field, and also on a computed field that we have createed, which will give you a secondary sort for when Region is null First, without ordering, create a computed field that has a value which will sort the way we want. In this case, we can create a field with the Case statement, which allows you do to if/then logic. We want a field that is 1 when Region is null.
Problem 25: 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.
Description: We’ll be using the Orders table, and using the Freight and ShipCountry fields.
Problem 26: 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 1994.
Description: We need to add a Where clause to the query from the previous problem. The field to filter on is OrderDate.
Problem 27: Another (incorrect) answer to the problem above is this:
Note: Both Problem Statements 26 and 27 are the same.
Problem 28: We’re continuing to work on high freight charges. We now want to get the three shipping 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.
Description: First, get the last OrderDate in Orders. Write a simple select statement to get the highest value in the OrderDate field using the Max aggregate function.You should have something like this: Select Max(OrderDate) from Orders Now you need to get the date 1 year before the last order date.
Problem 29: We’re doing inventory, and need to show information like the below, for all orders. Sort by OrderID and Product ID.
Description: You’ll need to do a join between 4 tables, displaying only those fields that are necessary.In first join we have added the employees & orders tables and in second join products & oreder details are joined.
Problem 30: There are some customers who have never actually placed an order. Show these customers.
Description: One way of doing this is to use a left join,also known as a left outer join. We have added the where clause in that simply write the null keyword because we want that customers who never actually placed an order.
Problem 31: 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.
Description: Building on the previous problem, you might think you need to do something like this:
Select Customers.CustomerID,Orders.CustomerID From Customers left join Orders on Orders.CustomerID = Customers.CustomerID Where Orders.CustomerID is null and Orders.EmployeeID = 4…adding this filter in the where clause: and Orders.EmployeeID = 4However, this returns no records. Note that with outer joins, the filters on the where clause are applied after the join.
