Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 6
In the previous blog, we solved intermediate problems. In this blog, we will be solving advanced-level questions from the book. You can read the blog and solve the problems as you go.
Contents:
32. High-value customers
33. High-value customers - total orders
34. High-value customers - with discount
35. Month-end orders
36. Orders with many line items
37. Orders - random assortment
38. Orders - accidental double-entry
39. Orders - accidental double-entry details
40. Orders - accidental double-entry details, derived table
Problem 32: We want to send all of our high-value customers a special VIP gift. We're defining high-value customers as those who've made at least 1 order with a total value (not including the discount) equal to $10,000 or more. We only want to consider orders made in the year 2016.
Description: First, let’s get the necessary fields for all orders made in the year 1994. Don’t bother grouping yet, just work on the Where clause. You’ll need the CustomerID, CompanyName from Customers; OrderID from Orders; and Quantity and unit price from OrderDetails. Order by the total amount of the order, in descending order.
Problem 33: The manager has changed his mind. Instead of requiring that customers have at least one individual order totaling $10,000 or more, he wants to define high-value customers as those who have orders totaling $15,000 or more in 2016. How would you change the answer to the problem above?
Description: This query is almost identical to the one above, but there are just a few lines you need to delete or comment out, to the group at a different level.
Problem 34: Change the above query to use the discount when calculating high-value customers. Order by the total amount which includes the discount.
Description: To start out, just use the OrderDetails table. In this, you need to figure out how the Discount works.
Problem 35: At the end of the month, salespeople are likely to try much harder to get orders, to meet their month-end quotas. Show all orders made on the last day of the month. Order by EmployeeID and OrderID.
Description: Here we have used the last_day() function.
Problem 36: The Northwind mobile app developers are testing an app that customers will use to show orders. In order to make sure that even the largest orders will show up correctly on the app, they'd like some samples of orders that have lots of individual line items. Show the 10 orders with the most line items, in order of total line items.
Description: Using Orders and OrderDetails, you’ll use Group by and count() functionality.
Problem 37: The Northwind mobile app developers would now like to just get a random assortment of orders for beta testing on their app. Show a random set of 2% of all orders.
Description: Note that in the below SQL, the RandomValue field returns the same random value for each row. We have just used the limit key word.
Problem 38: Janet Leverling, one of the salespeople, has come to you with a request. She thinks that she accidentally double- entered a line item on an order, with a different ProductID, but the same quantity. She remembers that the quantity was 60 or more. Show all the OrderIDs with line items that match this, in order of OrderID.
Description: We might start out with something like this: Select OrderID, ProductID, Quantity From OrderDetails Where Quantity >= 60 However, this will only give us the orders where at least one order detail has a quantity of 60 or more. We need to show orders with more than one order detail with a quantity of 60 or more. Also, the same value for quantity needs to be there more than once.
Problem 39: Based on the previous question, we now want to show details of the order, for orders that match the above criteria.
Description: There are many ways of doing this, including CTE (common table expression) and derived tables. I suggest using a CTE and a subquery. This is an example of a simple CTE in Northwind. It returns orders made by the oldest employee: with OldestEmployee as ( Select top 1 EmployeeID from Employees order by BirthDate ) Select OrderID, OrderDate from Orders where EmployeeID in (Select EmployeeID from OldestEmployee)
Problem 40: Here’s another way of getting the same results as in the previous problem, using a derived table instead of a CTE. However, there’s a bug in this SQL. It returns 20 rows instead of 16. Correct the SQL.
Problem SQL: Select OrderDetails.OrderID, ProductID
, UnitPrice, Quantity, Discount From OrderDetails Join ( Select OrderID
From OrderDetails Where Quantity >= 60
Group By OrderID, Quantity Having Count(*) > 1) PotentialProblemOrders on PotentialProblemOrders.OrderID = OrderDetails.OrderID
Order by OrderID, ProductID.
Description: Here's another way of getting the same results as in the previous problem, using a derived table instead of a CTE. However, there's a bug in this SQL. It returns 20 rows instead of 16. Correct the SQL.
Problem SQL: Select OrderDetails.OrderID,ProductID, UnitPrice, Quantity , Discount From OrderDetails Join ( Select OrderID From OrderDetails Where Quantity >= 60 Group By OrderID, Quantity Having Count(*) > 1 ) PotentialProblemOrders on PotentialProblemOrders.OrderID = OrderDetails.OrderID Order by OrderID, ProductID There are 2 rows for OrderID 10263 because there are 2 sets of rows that have the same, identical quantity, that's 60 or above. When you do a join to a table that has duplicates, you will get duplicates in the output as well, unless you take steps to avoid it.
