170|SQL – Booking Barney’s Bulk Books Buyers

Challenges
, , , ,
1

BYU Student Author: @James_Gerstner
Reviewers: @Jimmy_Han, @Marta_Ellsworth
Estimated Time to Solve: 35 Minutes

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

Overview
Barney’s Bulk Books has been around since 1996, when it started selling books with names derived from classic and popular books and magazines (see if you can figure them all out!). Lately, management at Barney’s has wanted to improve data readability, so they’ve turned to you, their accountant, to help with this issue. You have the tables containing all the company’s data, and you’ve decided to use SQL to structure and interpret this data.

Specifically, you want to find:

  1. Which genres are more expensive (to your customers) than others
  2. A ranking of books’ popularity based on how many orders they have
  3. How many sets of Ketchup on a Fry sold in 2023

And, for a bonus from management:
The order of which supplier sells Barney’s the greatest number of sets, and for how much revenue each supplier is ultimately responsible.

Instructions

  1. Download the Microsoft Access database or the Excel document to import the data you will use for this challenge
  2. In Access (or a similar program), navigate to the SQL view by clicking SQL in the bottom right corner or using the dropdown for “View” in the home tab in the top left corner.
  3. Write an SQL Query (Query 1) to display the average price of each genre, ordered low to high. Column names should be: Genre, AvgPrice
  4. Write an SQL Query (Query 2) to display each book that has sold and how many times it has sold, in order of most sales (high to low). Column names should be: ProductName, Genre, SaleCount
  5. Write an SQL Query (Query 3) that will show how many sets of Ketchup on a Fry sold in 2023, as well as the date for each sale. Order the results by SaleDate, with the oldest sale first. Column names should be: ProductID, ProductName, NumSold, SaleDate

Challenge question:
Write an SQL Query (Query 4) that will display each supplier, the number of products they supply to you (including books with no sales), and the total revenue associated with those books. Order the results by the number of products (most to least), then the amount of revenue (most to least). Column names should be: SupplierName, NumProducts, TotalRevenue

Data Files

Suggestions and Hints
  • If you get an error that one of the fields in your SELECT statement “isn’t part of an aggregate function,” add the field into the GROUP BY line and see if that helps.
  • For query 4, nesting queries is probably the best way to solve this; write one query to find the number of products for each supplier, and a query that finds the total revenue per supplier. Then, “nest” one inside of the other using a JOIN.
  • Solutions for each of the queries can be found in the “Solution” section of this page, separated from one another, so you can check the queries one at a time if you’d like to or if you get stuck.

Solution

Query 1 Solution

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products AS p
GROUP BY Genre
ORDER BY AVG(Price) ASC;

Query 2 Solution

SELECT p.ProductName, p.Genre, COUNT(p.ProductID) AS SaleCount
FROM Products p INNER JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY COUNT(p.ProductID) DESC;

Query 3 Solution

SELECT p.ProductID, p.ProductName, SUM(Quantity) AS NumSold, s.SaleDate
FROM Sales s INNER JOIN Products p ON p.ProductID = s.ProductID
WHERE p.ProductName LIKE ‘Ketchup on a Fry’
AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;

Query 4 Solution

SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC;

Solution Image

Challenge170_Solution_Image

Challenge170_Solution.accdb
Solution Video: Challenge 170|SQL – Booking Barney’s Bulk Books Buyers

2

SELECT Genre, AVG(Price) AS AvgPrice
FROM Products AS p
GROUP BY Genre
ORDER BY AVG(Price) ASC;

SELECT p.ProductName, p.Genre, COUNT(p.ProductID) AS SaleCount
FROM Products p INNER JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY COUNT(p.ProductID) DESC;

SELECT p.ProductID, p.ProductName, SUM(Quantity) AS NumSold, s.SaleDate
FROM Sales s INNER JOIN Products p ON p.ProductID = s.ProductID
WHERE p.ProductName LIKE ‘Ketchup on a Fry’
AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;

SELECT su.SupplierName, sp.NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM ((Products AS p LEFT JOIN Sales AS s ON p.ProductID = s.ProductID)
LEFT JOIN Suppliers su ON p.SupplierID = su.supplierID)
LEFT JOIN

(SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts
FROM Products AS p LEFT JOIN Suppliers AS su ON p.SupplierID = su.SupplierID
GROUP BY su.SupplierName) AS sp ON sp.SupplierName = su.SupplierName

GROUP BY su.SupplierName, sp.NumProducts
ORDER BY sp.NumProducts DESC, SUM(p.Price * s.Quantity) DESC

1 Like
3

1-
SELECT Genre, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Genre
ORDER BY AvgPrice ASC;
2-
SELECT p.ProductName, p.Genre, COUNT(s.ProductID) AS SaleCount
FROM Products p
INNER JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName, p.Genre
ORDER BY SaleCount DESC;
3-
SELECT p.ProductID, p.ProductName, SUM(s.Quantity) AS NumSold, s.SaleDate
FROM Sales s
INNER JOIN Products p ON p.ProductID = s.ProductID
WHERE p.ProductName LIKE ‘Ketchup on a Fry’ AND YEAR(s.SaleDate) = 2023
GROUP BY p.ProductID, p.ProductName, s.SaleDate
ORDER BY s.SaleDate ASC;
4-
SELECT su.SupplierName, COUNT(p.ProductID) AS NumProducts, SUM(p.Price * s.Quantity) AS TotalRevenue
FROM Suppliers su
LEFT JOIN Products p ON p.SupplierID = su.SupplierID
LEFT JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY su.SupplierName
ORDER BY NumProducts DESC, TotalRevenue DESC;

1 Like
4