166|SQL – The Guild of Gold

Sam_Herbert · February 27, 2025, 6:03pm

Time: 25 minutes
Difficulty: Hard/Medium

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);

Alexandria_White · February 27, 2025, 6:06pm

Time to complete: 25 mins
difficulty: medium
Solutions:

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);

Ryan_Deng · February 27, 2025, 6:07pm

Q1:
SELECT DISTINCT QuestLeader AS [QuestLeader]
FROM Quests;

Q2:

SELECT tt.TypeName AS [TypeName],
SUM(t.Value) AS [TotalValue]
FROM Treasures AS t
INNER JOIN TreasureTypes AS tt
ON t.TypeID = tt.TypeID
GROUP BY tt.TypeName
ORDER BY SUM(t.Value) DESC;

Q3:

SELECT TOP 5 q.QuestName AS [QuestName],
SUM(t.Value) AS [TotalLoot]
FROM Quests AS q
INNER JOIN Treasures AS t
ON q.QuestID = t.QuestID
GROUP BY q.QuestName
ORDER BY SUM(t.Value) DESC;

Q4:

SELECT q.QuestLeader AS [QuestLeader],
q.QuestName AS [QuestName],
ROUND(AVG(t.Value), 2) AS [AvgTreasureValue]
FROM Quests AS q
INNER JOIN Treasures AS t
ON q.QuestID = t.QuestID
GROUP BY q.QuestLeader, q.QuestName
ORDER BY ROUND(AVG(t.Value), 2) DESC;

Q5:

SELECT tt.TypeName AS [TypeName],
COUNT() AS [CountFound]
FROM Treasures AS t
INNER JOIN TreasureTypes AS tt
ON t.TypeID = tt.TypeID
GROUP BY tt.TypeName
ORDER BY COUNT() ASC;

Time: 20 minutes

Difficutly; hard

Janelle_Dadson · February 27, 2025, 6:12pm

Time to complete: 20
Rating: Moderate

1.SELECT DISTINCT QuestLeader
FROM Quests;

2.SELECT Treasure.TypeName, SUM(Treasure.Value) AS TotalValue
FROM Treasures
GROUP BY Treasure.TypeName
ORDER BY TotalValue DESC;

3.SELECT TOP 5 Quests.QuestName, SUM(Treasure.Value) AS TotalLoot
FROM Quests
JOIN Treasures ON Quests.QuestID = Treasure.QuestID
GROUP BY Quests.QuestName
ORDER BY TotalLoot DESC;

4.SELECT Quests.QuestLeader, Quests.QuestName, ROUND(AVG(Treasure.Value), 2) AS AvgTreasureValue
FROM Quests
JOIN Treasures ON Quests.QuestID = Treasure.QuestID
GROUP BY Quests.QuestLeader, Quests.QuestName
ORDER BY AvgTreasureValue DESC;

5.SELECT Treasure.TypeName, COUNT(*) AS CountFound
FROM Treasures
GROUP BY Treasure.TypeName
ORDER BY CountFound ASC;

Cate_Ducote · February 27, 2025, 6:13pm

Time: 20 mins
Level: Beginner

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);

Charles_Winfield · February 27, 2025, 6:29pm

Time to complete: 25
Difficulty: easy but issues with dataset. Some columns were named incorrectly or didn’t have headers.

SELECT DISTINCT Field3 AS QuestLeader
FROM Quests;

SELECT typ.TypeName, SUM(tr.TreasureValue) AS TotalValue

FROM Treasure_Types AS typ INNER JOIN Treasures AS tr ON typ.TypeID = tr.TypeID

GROUP BY typ.TypeName

ORDER BY SUM(tr.TreasureValue) DESC;

SELECT TOP 5 q.Field2 AS QuestName, SUM(t.TreasureValue) AS TotalLoot

FROM Quests AS q INNER JOIN Treasures AS t ON q.Field1 = t.QuestID

GROUP BY q.Field2

ORDER BY SUM(t.TreasureValue) DESC

SELECT q.Field3 as QuestLeader, q.Field2 as QuestName, ROUND(AVG(t.TreasureValue),2) as AvgTreasureValue

FROM Quests as q

INNER JOIN Treasures as t ON q.Field1 = t.QuestID

GROUP BY q.Field3, q.Field2

ORDER BY AVG(t.TreasureValue) DESC

SELECT typ.TypeName, COUNT(tr.TypeID)

FROM Treasure_Types as typ

INNER JOIN Treasures as tr ON typ.TypeID = tr.TypeID

GROUP BY typ.TypeName

ORDER BY COUNT(tr.TypeID)

Jaxson_LaFeber · February 27, 2025, 6:38pm

Time to complete: 30 mins
Difficulty: Intermediate
Solution:

SELECT DISTINCT QuestLeader
FROM Quests;
SELECT typ.TypeName, SUM(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS typ ON t.TypeID = typ.TypeID
GROUP BY TypeName
ORDER BY SUM(t.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);

Jayden_Flake · February 27, 2025, 6:52pm

SELECT DISTINCT q.field3 AS QuestLeader
FROM Quests q

2.SELECT tt.TypeName, SUM(t.TreasureValue) AS TotalValue
FROM Treasure_Types tt LEFT JOIN Treasures t ON tt.TypeID=t.TypeID
GROUP BY tt.TypeName
ORDER BY SUM(t.TreasureValue) DESC;

SELECT TOP 5 q.QuestName, SUM(t.TreasureValue) AS TotalLoot
FROM Quests q INNER JOIN Treasures t ON q.QuestID=t.QuestID
GROUP BY q.QuestName
ORDER BY SUM(t.TreasureValue) DESC;
SELECT q.QuestLeader, q.QuestName, ROUND(AVG(t.TreasureValue), 2) AS AvgTreasureValue
FROM Quests q INNER JOIN Treasures t ON q.QuestID = t.QuestID
GROUP BY q.QuestLeader, q.QuestName
ORDER BY ROUND(AVG(t.TreasureValue), 2) DESC;
SELECT tt.TypeName, COUNT(tt.TypeID) AS CountFound
FROM Treasure_Types tt LEFT JOIN Treasures t ON tt.TypeID=t.TypeID
GROUP BY tt.TypeName
ORDER BY COUNT(tt.TypeID)

Leah_Robertson · February 27, 2025, 6:58pm

Time: 15 mins
Rating: 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);

Jayden_Flake · February 27, 2025, 6:59pm

Correction:
3. SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests q
INNER JOIN Treasures t ON CLng(q.QuestID) = CLng(t.QuestID)
GROUP BY QuestName
ORDER BY TotalLoot DESC

SELECT q.QuestLeader, q.QuestName, ROUND(AVG(NZ(t.TreasureValue, 0)), 2) AS AvgTreasureValue
FROM Quests q
INNER JOIN Treasures t ON CLng(q.QuestID) = CLng(t.QuestID)
GROUP BY q.QuestLeader, q.QuestName
ORDER BY AvgTreasureValue DESC;

Kambrie_Earl · March 13, 2025, 9:37pm

Time to complete: 30 minutes
Difficulty: Easy

SELECT DISTINCT QuestLeader
FROM Quests;
2.
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;
3.
SELECT TOP 5 q.QuestName, SUM(t.TreasureValue) AS TotalLoot
FROM Quests q INNER JOIN Treasures t ON INT(t.QuestID) = INT(q.QuestID)
GROUP BY q.QuestName
ORDER BY SUM(t.TreasureValue) DESC;
4.
SELECT q.QuestLeader, q.QuestName, ROUND(AVG(t.TreasureValue),2) AS AvgTreasureValue
FROM Quests q INNER JOIN Treasures t ON INT(q.QuestID) = INT(t.QuestID)
GROUP BY q.QuestLeader, q.QuestName
ORDER BY AVG(t.TreasureValue) DESC;
5.
SELECT tt.TypeName, COUNT(t.TypeID) AS CountFound
FROM Treasure_Types tt INNER JOIN Treasures t ON tt.TypeID = t.TypeID
GROUP BY tt.TypeName
ORDER BY COUNT(t.TypeID);

Topic		Replies	Views
199\|SQL – LOTR Ledgers Challenges beginner , sql	3	89	March 15, 2025
179\|SQL – Group By for Greatness Challenges sql , intro , group_by	316	566	February 27, 2025
105\|EXCEL – Buried Treasure Challenges excel , intermediate , power-query , other	2	174	July 8, 2024
168\|SQL – Trading Card Treasure Trove Challenges beginner , sql , consulting , valuation , trading-cards	44	231	March 16, 2025
169\|SQL – Workforce Wisdom Challenges beginner , sql , hr , new-hires	66	245	February 27, 2025

166|SQL – The Guild of Gold

Related topics