BYU Student Author: @Marta_Ellsworth
Reviewers: @Sterling_Lane, @Andrew_Wilson
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
Welcome, adventurer, to the Guild of Gold, a legendary group of treasure hunters, artifact seekers, and wealth wizards. Our guild specializes in uncovering and managing treasures across the realms. Our vaults are filled with the spoils of a thousand quests, and our ledger, maintained in the mystical database of Avaritia, holds the secrets to our riches. Your quest, should you choose to accept it, involves deciphering the Ledger of Avaritia to ensure the prosperity of our guild continues to grow.
Instructions
Please complete each of the following queries to complete your quest. You will need to download the Microsoft Access database, which already has the tables loaded, and the relationships between them have been established.
- Chronicles of the Vault Keepers:
a. Unveil all unique quest leaders logged in our vault. Display a list of their names.
b. Expected Columns: QuestLeader
- Sagas of the Glittering Hoards:
a. Summarize the total value of each type of treasure hoard. Order the treasures by their total value, from the richest to the least.
b. Expected Columns: TypeName, TotalValue
- The Five Legendary Treasures:
a. Identify the top 5 quests that have amassed the greatest wealth. List the quest names and the total loot acquired.
b. Expected Columns: QuestName, TotalLoot
- Wealth of the Realm:
a. Highlight the average treasure value for each quest leader, for each quest they lead, sorted from highest average to lowest. Round the average to two decimals.
b. Expected Columns: QuestLeader, QuestName, AvgTreasureValue
- The Diverse Bounty:
a. How many times has the guild found each treasure type on a quest. Order it by the treasure found least to most.
b. Expected Columns: TypeName, CountFound
Data Files
Suggestions and Hints
- Finding the top 5 in Access is different in Access than other database systems, Access uses TOP in the SELECT statement.
- Feel free to use ChatGPT to convert your SQL queries into Access language.
Solution
Time: 12 mins
Difficulty: Easy
Solution:
1.
SELECT DISTINCT QuestLeader
FROM Quests;
2.
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
3.
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
4.
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
5.
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);
Time: 20 mins
difficulty: intermediate
SELECT DISTINCT QuestLeader
FROM Quests
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID)
Time: 25 Min
Difficulty: Medium/Hard
Solutions:
-
SELECT DISTINCT QuestLeader
FROM Quests;
-
SELECT TypeName, SUM(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON tr.TypeID = t.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
-
SELECT TOP 5 q.QuestName, SUM(t.TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY q.QuestName
ORDER BY SUM(t.TreasureValue) DESC;
-
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
-
SELECT TypeName, COUNT(T.TypeID) AS CountFound
FROM Treasures AS T INNER JOIN Treasure_Types AS TT ON T.TypeID = TT.TypeID
GROUP BY TypeName
ORDER BY COUNT(T.TypeID);
Time: 25 minutes
Rating: intermediate
Comments: the order of the joins is weird to me
Q1: SELECT Distinct QuestLeader
from Quests
Q2: SELECT Treasure_Types.TypeName, sum(Treasures.TreasureValue) as TotalTreasureValue
from Treasures
inner join Treasure_Types on Treasures.TypeID = Treasure_Types.TypeID
group by Treasure_Types.TypeName
order by sum(Treasures.TreasureValue) desc
Q3: SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests INNER JOIN Treasures ON Treasures.QuestID = Quests.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
Q4: SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests INNER JOIN Treasures ON Treasures.QuestID = Quests.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
Q5: SELECT TypeName, Count(Treasures.TypeID) AS CountFound
FROM Treasures INNER JOIN Treasure_Types ON Treasures.TypeID = Treasures.Types.TypeID
GROUP BY TypeName
ORDER BY Count(Treasures.TypeID);
Time to complete: 30 minutes
Difficulty: Advanced- I cannot get even the solutions to run, and the data was missing headers.
Solutions:
- SELECT DISTINCT QuestLeader
FROM Quests;
- SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
- SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
- SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
- SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);
Time: 30 minutes
Difficulty: Intermediate (I had some technical difficulties)
1: SELECT DISTINCT QuestLeader
FROM Quests;
2: SELECT Treasure_Types.TypeName, SUM (Treasures.TreasureValue) AS TotalValue
FROM Treasures
INNER JOIN Treasure_Types ON Treasures.TypeID = Treasure_Types.TypeID
GROUP BY Treasure_Types.TypeName
ORDER BY SUM (Treasures.TreasureValue) DESC;
3: SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
4: SELECT q.QuestLeader, q.QuestName, Round(AVG(t.TreasureValue),2) AS AvgTreasureValue
FROM Treasures AS t
INNER JOIN Quests AS q ON t.QuestID = q.QuestID
GROUP BY q.QuestLeader, q.QuestName, AvgTreasureValue
ORDER BY AVG(TreasureValue) DESC;
5: SELECT TypeName, COUNT(t.TypeID) AS CountFound
FROM Treasure_Types tt
INNER JOIN Treasures t ON tt.TypeID = t.TypeID
GROUP BY TypeName
ORDER BY COUNT(t.TypeID);
Time to Complete: 20 min
Difficulty: Easy/Medium
-
SELECT DISTINCT QuestLeader
FROM Quests;
-
SELECT tt.TypeName, Sum(t.TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY tt.TypeName
ORDER BY Sum(t.TreasureValue) DESC;
-
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON q.QuestID = t.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
-
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON q.QuestID = t.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
-
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);
Time to complete: 30 minutes
Difficulty: moderate
Solution:
-
SELECT DISTINCT QuestLeader
FROM Quests;
-
SELECT TypeName,
SUM(TreasureValue) AS TotalValue
FROM (Treasure
INNER JOIN Treasure_Types ON Treasures.TypeID=Treasure.TypeID)
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
-
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests
INNER JOIN Treasures ON treasures.QuestID = quests.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
-
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests INNER JOIN Treasures ON treasures.QuestID = quests.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
-
SELECT TypeName, COUNT(Treasures.TypeID) AS CountFound
FROM Treasures
INNER JOIN Treasure_Types ON Treasures.TypeID = Treasure_Types.TypeID
GROUP BY TypeName
ORDER BY COUNT(Treasures.TypeID);
Time: 12 minutes
Difficulty: Beginner
-
SELECT DISTINCT QuestLeader
FROM Quests;
-
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
-
SELECT TOP 5 QuestName, SUM(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY SUM(TreasureValue) DESC;
-
SELECT QuestLeader, QuestName, ROUND(AVG(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY AVG(TreasureValue) DESC;
-
SELECT TypeName, COUNT(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY COUNT(t.TypeID);
Time to complete: 30 minutes
Difficulty: Intermediate
SELECT DISTINCT QuestLeader
FROM Quests;
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
SELECT QuestLeader, QuestName, ROUND(AVG(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY AVG(TreasureValue) DESC;
SELECT TypeName, Count(t.TypeID)
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Count (t.TypeID)
ttc 20
ranking medium
Solutions:
q1
SELECT DISTINCT Q.questleader as QuestLeaders
FROM Quests as Q
q2
SELECT tt.TypeName, Sum(TreasureValue) as TotalValue
FROM treasures as t
inner join treasure_types as tt on tt.typeID=t.typeid
GROUP by tt.typename
order by Sum(TreasureValue) desc;
q3
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
q4
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC
q5
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID)
Time: 20
Difficulty Intermediate
Solution:
SELECT DISTINCT QuestLeader
FROM Quests;
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID)
Time to complete: 30 minutes
Difficulty: Intermediate
Q1:
SELECT DISTINCT QuestLeader
FROM Quests;
Q2:
SELECT tt.TypeName, SUM(t.TreasureValue) AS TotalValue
FROM Treasure_Types tt INNER JOIN Treasures t ON tt.TypeId = t.TypeId
GROUP BY tt.TypeName
ORDER BY SUM(t.TreasureValue) DESC;
Q3:
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
Q4:
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
Q5:
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);
Time to complete: 25 Minutes
Difficulty: Intermediate
Solution:
1
SELECT DISTINCT QuestLeader
FROM Quests;
2
SELECT TypeName,
SUM(TreasureValue) AS TotalValue
FROM (Treasure INNER JOIN Treasure_Types ON Treasures.TypeID=Treasure.TypeID)
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
3
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests INNER JOIN Treasures ON treasures.QuestID = quests.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
4
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests INNER JOIN Treasures ON treasures.QuestID = quests.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
5
SELECT TypeName, COUNT(Treasures.TypeID) AS CountFound
FROM Treasures INNER JOIN Treasure_Types ON Treasures.TypeID = Treasure_Types.TypeID
GROUP BY TypeName
ORDER BY COUNT(Treasures.TypeID);
Time to complete: 30 Minutes
Difficulty: Intermediate
See Solution Below
1:
SELECT Distinct QuestLeader
From Quests;
2:
SELECT TypeName, Sum(TreasureValue) as TotalValue
From Treasures as t inner join Treasure_Types as tt on t.TypeID = tt.TypeID
Group By Typename
order by Sum(TreasureValue) DESC;
3:
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
4:
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
5:
SELECT TypeName, Count(t.TypeID) as CountFound
From Treasures as t inner join Treasure_Types as tt on t.TypeID = tt.TypeID
Group By TypeName
Order By Count(t.TypeID);