BYU Student Author: @Sterling_Lane
Reviewers: @James_Gerstner, @Andrew_Wilson
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
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.
- Which baseball cards in TCTT’s collection are Ultra Rare rarity? Return all columns in the Cards table in your result.
- 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?
- 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
- 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
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;
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!
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;
Time to complete: 20 minutes
Difficulty: Intermediate
Notes: Enjoyed the challenge! And I learned that Microsoft Access doesn’t allow the LIMIT function.
- SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;
- SELECT Collections.CollectionName, Count(Cards.CardID) AS CardCount
FROM Collections LEFT JOIN Cards ON Collections.CollectionID = Cards.CollectionID
GROUP BY Collections.CollectionName
ORDER BY COUNT(Cards.CardID) DESC;
- SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity = “Rare” OR Rarity = “Ultra Rare”
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY COUNT(CardID) DESC;
- SELECT TOP 1 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards AS ca INNER JOIN Collections AS co ON co.CollectionID = ca.CollectionID)
INNER JOIN Sales AS s ON s.CardID = ca.CardID
WHERE YEAR(s.SaleDate) = 2022 AND MONTH(s.SaleDate) = 10
ORDER BY s.SalePrice DESC;
Difficulty: Intermediate
Time: 30 Minutes
1)
SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;
2)
SELECT Collections.CollectionName, COUNT(Cards.CardID) AS CardCount
FROM Collections
LEFT JOIN Cards ON Collections.CollectionID = Cards.CollectionID
GROUP BY Collections.CollectionName
ORDER BY COUNT(Cards.CardID) DESC;
3)
SELECT Team, Count(CardID) AS CardCount
FROM Cards
WHERE Rarity = “Rare” OR “Ultra Rare”
GROUP BY Team
HAVING Count(CardID)>= 10
ORDER BY Count(CardID) DESC;
4)
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 s.CardID = ca.CardID
WHERE YEAR (s.SaleDate) = 2022 AND MONTH (s.SaleDate) = 10
ORDER BY s.SalePrice DESC;
Time to complete: 20 min
Difficulty: Intermediate
Solution:
1.
SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;
2.
SELECT Collections.CollectionName, Count(Cards.CardID) AS CardCount
FROM Collections
LEFT JOIN Cards
ON Collections.CollectionID = Cards.CollectionID
GROUP BY Collections.CollectionName
ORDER BY COUNT(Cards.CardID) DESC;
3.
SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity = “Rare” OR Rarity = “Ultra Rare”
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY COUNT(CardID) DESC;
4.
SELECT TOP 1 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards AS ca
INNER JOIN Collections AS co
ON co.CollectionID = ca.CollectionID)
INNER JOIN Sales AS s
ON s.CardID = ca.CardID
WHERE YEAR(s.SaleDate) = 2022 AND MONTH(s.SaleDate) = 10
ORDER BY s.SalePrice DESC;
Time to complete: 30 minutes
Difficulty: Intermediate
Notes: Query 4 was a challenge due to not knowing that Microsoft Access does not allow the limit function, creating a syntax error.
Solutions: See Below
- SELECT *
from Cards
where Rarity like “Ultra Rare”
- SELECT co.CollectionName, count(c.CardID) as CardCount
from Cards as c
left join Collections as co on c.CollectionID=co.CollectionID
group by co.CollectionName
order by count(c.CardID) DESC
- SELECT c.team, count(c.CardID) as CardCount
from Cards as c
where Rarity in(“Ultra Rare”, “Rare”)
group by Team
having count(c.CardID) >=10
order by count(c.CardID) DESC
- SELECT TOP 1 c.PlayerName, c.Year, co.CollectionName, s.SalePrice, s.SaleDate
from (sales as S
inner join Cards as c on s.CardID=c.CardID)
inner join Collections as co on c.CollectionID=co.CollectionID
where year (s.SaleDate) = 2022 and month (s.SaleDate) = 10
order by s.SalePrice desc
Time to complete: 25 minutes
Difficulty: Medium
Solution:
- SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;
- SELECT co.CollectionName, COUNT(ca.CardID) AS CardCount
FROM Collections co
LEFT JOIN Cards ca ON co.CollectionID=ca.CollectionID
GROUP BY co.CollectionName
ORDER BY COUNT(ca.CardID) DESC;
- SELECT ca.Team, COUNT(ca.CardID) AS CardCount
FROM Cards ca
WHERE Rarity = “Rare” OR “Ultra Rare”
GROUP BY ca.Team
HAVING COUNT(ca.CardID) >= 10
ORDER BY COUNT(ca.CardID) DESC;
- SELECT TOP 1 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards ca INNER JOIN Collections co ON ca.CollectionID=co.CollectionID)
INNER JOIN Sales s ON ca.CardID=s.CardID
WHERE MONTH(s.SaleDate) = 10 AND YEAR(s.SaleDate) = 2022
ORDER BY s.SalePrice DESC;
Time to Complete: 15 mins
Difficulty Level: Intermediate
Solution Below:
SELECT * FROM Cards;
SELECT A.CollectionName, COUNT(B.CardID) AS CardCount
FROM Collections A
LEFT JOIN Cards B ON A.CollectionID = B.CollectionID
GROUP BY A.CollectionName
ORDER BY CardCount DESC;
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;
SELECT TOP 1 ca.playername, ca.year, co.collectionname, s.saleprice
FROM (Cards ca INNER JOIN Collections co ON ca.CollectionID = co.CollectionID)
INNER JOIN Sales s ON s.CardID = ca.CardID
WHERE YEAR (s.SaleDate) = 2022 AND MONTH (s.SaleDate) = 10
ORDER BY s.SalePrice DESC;
Time to complete: 30 minutes
Difficulty: HARD
Solution:
Q1:
SELECT *
FROM Cards
WHERE rarity = “ultra rare”
Q2:
SELECT co.CollectionName, COUNT(c.CardID) AS CardCount
FROM (Collections co LEFT JOIN Cards c ON c.collectionID = co.collectionid)
GROUP BY co.collectionname
ORDER BY COUNT(c.CardID) 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 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards AS ca INNER JOIN Collections AS co ON co.CollectionID = ca.CollectionID)
INNER JOIN Sales AS s ON s.CardID = ca.CardID
WHERE YEAR(s.SaleDate) = 2022 AND MONTH(s.SaleDate) = 10
ORDER BY s.SalePrice DESC;
Time to complete: 17 minutes
Rating: Difficult
Solution:
Q1:
SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”;
Q2:
SELECT COUNT(c.CardID) AS CardCount, cc.CollectionName
FROM Cards AS c RIGHT JOIN Collections AS cc ON c.CollectionID = cc.CollectionID
GROUP BY cc.CollectionName;
Q3:
SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity = “Rare” OR WHERE = “Ultra Rare”
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY COUNT(CardID) DESC;
Q4:
SELECT TOP 1 c.playername, c.year, co.collectionname, s.saleprice
FROM (Cards AS c INNER JOIN Collections AS co ON c.CollectionID = co.CollectionID) INNER JOIN Sales AS s ON s.CardID = c.CardID
WHERE YEAR (s.SaleDate) = 2022 AND MONTH (s.SaleDate) = 10
ORDER BY s.SalePrice DESC;
Time: 30 mins
Difficulty: Moderate
Solutions:
-
SELECT *
FROM Cards
WHERE Rarity = ‘Ultra Rare’;
-
SELECT co.CollectionName, COUNT (c.CardID) AS CardCount
FROM Collections AS co
LEFT JOIN Cards AS c ON co.collectionid = c.collectionid
GROUP BY co.CollectionName
ORDER BY COUNT (c.CardID) DESC
-
SELECT Team, COUNT (CardID) AS CardCount
FROM Cards
WHERE Rarity = ‘Rare’ OR ‘Ultra Rare’
GROUP BY Team
HAVING COUNT (CardID) >= 10
ORDER BY COUNT (CardID) DESC
-
SELECT TOP 1 ca.PlayerName, ca.Year, co.CollectionName, s.SalePrice
FROM (Cards ca INNER JOIN Collections co ON ca.CollectionID=co.CollectionID)
INNER JOIN Sales s ON ca.CardID=s.CardID
WHERE MONTH(s.SaleDate) = 10 AND YEAR(s.SaleDate) = 2022
ORDER BY s.SalePrice DESC;
Time to Complete: 30
Difficulty: medium
Solution:
-
SELECT *
FROM Cards
WHERE Rarity=“Ultra Rare”;
-
SELECT Collections.CollectionName, COUNT(Cards.CardID) AS CardCount
FROM Collections as Collections LEFT JOIN Cards as Cards ON Collections.CollectionID=Cards.CollectionID
group by Collections.CollectionName
ORDER BY Count(Cards.CardID) DESC;
-
SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity=‘Rare’ OR Rarity=‘Ultra Rare’
GROUP BY Team
HAVING COUNT(cardID) >= 10
ORDER BY COUNT(CardID) DESC;
-
SELECT Cards.PlayerName, Cards.Year, Collections.CollectionName, Sales.SalePrice
FROM (Cards INNER JOIN Collections ON Cards.CollectionID=Collections.CollectionID) INNER JOIN Sales ON Cards.CardID=Sales.CardID
WHERE YEAR(sales.saledate)=2022 AND MONTH(sales.saledate)=10
ORDER BY Sales.SalePrice DESC;
Difficulty: Beginner
Time: 20min
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 COUNT(Cards.CardID) DESC;
Q3:
SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity = ‘Rare’ OR ‘Ultra Rare’
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY COUNT(CardID) DESC;
Q4:
SELECT TOP 1 Cards.PlayerName, Cards.Year, Collections.CollectionName, Sales.SalePrice
FROM (Cards INNER JOIN Collections ON Collections.CollectionID = Cards.CollectionID)
INNER JOIN Sales ON Sales.CardID = Cards.CardID
WHERE YEAR(Sales.SaleDate) = 2022 AND MONTH (Sales.SaleDate) = 10
ORDER BY Sales.SalePrice DESC;
Time to Complete: 30 Minutes
Difficulty: Medium
Solutions:
1.
SELECT *
FROM Cards
WHERE Rarity = “Ultra Rare”
SELECT CollectionName, COUNT(CardID) AS CardCount
FROM Collections co
LEFT JOIN Cards ca ON co.CollectionID=ca.CollectionID
GROUP BY CollectionName
ORDER BY COUNT(CardID) DESC
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
SELECT TOP 1 PlayerName, Year, CollectionName, SalePrice
FROM ((Collections
INNER JOIN Cards ON Collections.CollectionID=Cards.CollectionID)
INNER JOIN Sales ON Cards.CardID=Sales.CardID)
WHERE YEAR(SaleDate)=2022 AND MONTH(SaleDate) = 10
ORDER BY SalePrice DESC
Time to complete: 25
Rating: Medium
Great problem to work through
- SELECT *
FROM Cards
WHERE Rarity = ‘Ultra Rare’;
- SELECT CO.CollectionName, COUNT(CA.CardID) AS CardCount
FROM Collections AS CO
LEFT JOIN Cards AS CA ON CA.CollectionID = CO.CollectionID
GROUP BY CO.CollectionName
ORDER BY COUNT(CA.CardID) DESC;
- SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity = ‘Rare’ OR Rarity = ‘Ultra Rare’
GROUP BY Team
HAVING COUNT(CardID) >= 10
ORDER BY COUNT(CardID) DESC;
- SELECT CA.PlayerName, CA.Year, CO.CollectionName, S.SalePrice
FROM (Collections AS CO
INNER JOIN Cards AS CA 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;
Q1:
SELECT *
FROM Cards
WHERE Rarity=“utlra Rare”
Q2:
SELECT collections.collectionname AS CollectionName, COUNT(cards.cardID) as CardCount
FROM Collections
LEFT JOIN Cards ON cards.collectionid=collections.collectionid
GROUP BY collections.collectionname
ORDER BY COUNT(cards.cardID) 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 cards.playername AS PlayerName, Cards.year AS Year, Collections.CollectionName AS CollectionName, Sales.SalePrice AS SalesPrice
FROM (Cards
INNER JOIN Collections ON Collections.collectionID=cards.collectionID)
INNER JOIN Sales ON sales.cardid=cards.cardID
WHERE YEAR (sales.saledate)=2022 AND MONTH (sales.saledate)=10
Time: 35 minutes
Rating: Intermediate
Notes: The 4th query was very difficult, but the others were easy
Solutions:
- SELECT *
FROM Cards
WHERE Rarity= “Ultra Rare”;
- SELECT Collections.CollectionName, COUNT(Cards.CardID) AS CardCount
FROM Collections
LEFT JOIN Cards ON Collections.CollectionID=Cards.CollectionID
GROUP BY Collections.CollectionName
ORDER BY COUNT(Cards.CardID) DESC;
- SELECT Team, COUNT(CardID) AS CardCount
FROM Cards
WHERE Rarity=“Rare” OR Rarity= “Ultra Rare”
GROUP BY Team
HAVING COUNT(CardID)>9
ORDER BY COUNT(CardID) DESC;
- SELECT TOP 1 c.PlayerName, c.Year, co.CollectionName, s.SalePrice
FROM (Sales AS s
INNER JOIN Cards AS c ON s.CardID=c.CardID)
INNER JOIN Collections AS co ON c.CollectionID=co.CollectionID
WHERE YEAR(s.SaleDate)= 2022 AND MONTH(s.SaleDate)=10
ORDER BY s.SalePrice DESC;
