168|SQL – Trading Card Treasure Trove

Challenges
, , , ,
1

BYU Student Author: @Sterling_Lane
Reviewers: @James_Gerstner, @Andrew_Wilson
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

Overview
You are a data analyst at a firm consulting for “Trading Card Treasure Trove” (TCTT), a local shop specializing in sports memorabilia. Because baseball’s popularity has been exploding in the last few years, TCTT has been focusing on baseball cards. The shop has recently digitized its inventory, sales, and collection records to better understand its business dynamics, optimize its inventory, and enhance its marketing strategies. The shop owner, Derek J., is passionate about baseball and wants to leverage this new database to make informed decisions, especially regarding expanding collections, pricing cards, and identifying customers. Your task is to answer some questions for Derek J. using basic SQL querying to help get the discussions started on how the company can generate future profitability. Considerations for these decisions include card rarity, team skill/prestige, and timing of sales.

Instructions
Download the starting Access database to record your queries. This table contains all three tables as well as four queries preloaded for you to record your SQL code corresponding to the numbers below. For now, each query has a default “Select *” statement as a placeholder. You will update each query as you answer the questions below. Double clicking each query will open up the “Datasheet View” by default. Clicking the “SQL View” button at the bottom right of the program will bring up the code editor.

  1. Which baseball cards in TCTT’s collection are Ultra Rare rarity? Return all columns in the Cards table in your result.
  2. How many cards does TCTT have in each Baseball Card Collection? Make sure to include all collections even if there are no cards in that collection. Return two columns: CollectionName and CardCount. Order the results by CardCount from highest to lowest. Are there any collections with zero cards?
  3. Which baseball teams have the most player cards that are “Rare” or “Ultra Rare”? Only return baseball teams that have at least 10 cards that fit this criteria. Order the results by CardCount from highest to lowest. Return two columns in your query output: Team and CardCount
  4. Which baseball card had the highest sale price in October 2022 (the World Series)? Return only one row with the following columns: PlayerName, Year, CollectionName, SalePrice.

Data Files

Suggestions and Hints
  • An asterisk (*) is an easy way within a SQL SELECT statement to select all columns of a given table.
  • Using a LEFT JOIN may be helpful for Query 2 if you want to test to see if any collections don’t have any cards in them. An INNER JOIN will get rid of all null rows if they exist.
  • Any time you have an aggregation (like SUM or COUNT) in the SELECT statement, you need to GROUP BY all the non-aggregated columns in order for the query to run properly.
  • To limit the number of rows in a query output, consider using the TOP statement at the beginning of your SELECT statement.

Solution

Challenge168_Solution.accdb
Solution Video: Challenge 168|SQL – Trading Card Treasure Trove

2

Time to Complete: 25 Minutes
Rating: Beginning
The Hardest part was just trying to get used to using Access Database
Q1:

SELECT *
FROM Cards
WHERE Rarity = 'Ultra Rare';

Q2:

SELECT Collections.CollectionName, COUNT(Cards.CardID) AS CardCount
FROM Collections
LEFT JOIN Cards
ON Collections.CollectionID = Cards.CollectionID
GROUP BY Collections.CollectionName
ORDER BY CardCount DESC;

Q3:

SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity IN ('Rare', 'Ultra Rare')
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY COUNT(CardID) DESC;

Q4:

SELECT TOP 1 PlayerName, Year, SalePrice
FROM Sales
INNER JOIN Cards ON Sales.CardID = Cards.CardID
WHERE FORMAT(SaleDate, 'MM/DD/YYYY') BETWEEN '10/01/2022' AND '10/31/2022'
ORDER BY SalePrice DESC;
3

QUERY 1
image

QUERY 2

image
image882×151 4.79 KB

QUERY 3
image

QUERY 4

image
image1361×132 6.66 KB

Thank you!

4

Time to Complete: 35 Minutes
Rating: Beginning
Hardest Part was navigating through the tools on Access Database.

Q1:

SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;

Q2:

SELECT co.CollectionName, COUNT(ca.CardID) AS CardCount
FROM Collections AS co LEFT JOIN Cards AS ca ON co.CollectionID = ca.CollectionID
GROUP BY co.CollectionName
ORDER BY COUNT(ca.CardID)DESC;

Q3:

SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity <> “Common”
GROUP BY Team
HAVING COUNT(CardID) >=10
ORDER BY 2 DESC;

Q4:

SELECT TOP 1 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards AS ca INNER JOIN Collections AS co ON ca.CollectionID = co.CollectionID) INNER JOIN Sales AS s ON ca.CardID = s.CardID
WHERE YEAR(s.SaleDate) = 2022 AND MONTH(s.SaleDate) = 10
ORDER BY 4 DESC;

Thank you!

5

Time to Complete: 30 Minutes
Rating: Beginner
Hardest Part: figuring out how to join the tables on Query 4

Q1: SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;

Q2: SELECT co.CollectionName, COUNT(ca.CardID) AS CardCount
FROM Collections AS co LEFT JOIN Cards AS ca ON co.CollectionID = ca.CollectionID
GROUP BY co.CollectionName
ORDER BY COUNT(ca.CardID)DESC;

Q3: SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity <> “Common”
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY 2 DESC;

Q4: SELECT TOP 1 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards AS ca INNER JOIN Collections AS co ON ca.CollectionID = co.CollectionID) INNER JOIN Sales AS s ON ca.CardID = s.CardID
WHERE YEAR(s.SaleDate) = 2022 AND MONTH(s.SaleDate) = 10
ORDER BY 4 DESC;