166|SQL – The Guild of Gold

William_Kirk · February 27, 2025, 5:23am

Time: 20 Minutes, Difficulty: intermediate, Comments: A diagram of the tables would help as a reference for table/column names.

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)

Colton_Grimshaw · February 27, 2025, 5:30am

Time to Complete: 15 minutes
Difficulty: Intermediate
Solution

Query 1
SELECT DISTINCT QuestLeader
FROM Quests;

Query 2
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(TreasureValue) DESC

Query 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

Query 4
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

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

Isabel_McKinlay · February 27, 2025, 6:01am

Time to complete: 15 min
Difficulty level: Easy
Q1: SELECT DISTINCT Quests.QuestLeader
FROM Quests;
Q2: 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;
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);

Jessica_Bertolio · February 27, 2025, 6:04am

Time: 40 minutes
Rating: Unnecessarily Difficult
I was able to work through 1, 2, and 5, but I haven’t been able to get 3 or 4 to work. I wonder if the PKs and FKs on the quests tab is working properly because they’re listed below the data. I compared my answers with other responses, and I believe I should have been able to get something to work if the data was formatted correctly. My friend who’s a senior in cyber security tried to help and after 40 minutes we concluded that something was wrong with the problem itself. The technical difficulty was pretty discouraging. I haven’t been able to get anyone else’s SQLs to work on Assembly for 3 or 4 either.

SELECT DISTINCT quests.field3

FROM Quests;

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;

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(TreasureValue) DESC;

SELECT Quests.Field3 AS QuestLeader, Quests.Field2 AS QuestName, Round(Avg(Treasures.TreasureValue)2) AS AvgTreasureValue

FROM Quests INNER JOIN Treasures ON Quests.Field1 = Treasures.QuestID;

Group By Quests.Field3

Order By Avg(Treasures.TreasureValue) DESC;

SELECT Treasure_TypesTypeName, Count(Treasures.TypeID) AS CountFound

FROM Treasures INNER JOIN Treasure_Types ON Treasures.TypeID = Treasure_Types.TypeID

GROUP BY Treasure_Types.TypeName

ORDER BY Count(Treasures.TypeID);

Clayton_Roberts · February 27, 2025, 6:16am

TIME: 25 min
Difficulty: Intermediate
Notes: The quests table has the column values as field 1, field 2, field 3. Wherefore I put the fields in my SQL then renamed them.
Solution:
1.
SELECT DISTINCT Field3 AS QuestLeader

FROM Quests

SELECT tt.TypeName, SUM(t.TreasureValue) AS TotalValue

FROM Treasures t INNER JOIN Treasure_Types tt ON t.TypeID = tt.TypeID

GROUP BY t.TypeName

ORDER BY Sum(t.TreasureValue) DESC

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

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

GROUP BY q.Field2

ORDER BY SUM(t.TreasureValue) DESC

SELECT q.Field3, q.Field2, Round(Avg(t.TreasureValue), 2) AS AvgTreasureValue

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

GROUP BY q.Field3, q.field2, avg(t.TreasureValue)

ORDER BY AVG(t.TreasureValue) DESC

SELECT tt.TypeName, Count(t.TypeID) AS CountFound

FROM Treasures t INNER JOIN Treasure_Types tt ON t.TypeID = tt.TypeID

GROUP BY tt.TypeName

ORDER BY COUNT(T.TypeID)

Logan_Messenger · February 27, 2025, 6:27am

Time to complete: 15 minutes
Difficulty: easy to medium.
Notes: A better problem to start with, not super complex.

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

Sam_Jezik · February 27, 2025, 7:09am

Time: 25 minutes
Difficulty: medium

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

Griffin_Karras · February 27, 2025, 7:45am

30 mins
Intermediate

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

John_Colton · February 27, 2025, 7:57am

Time 20 mins
Difficulty easy

1 - Select Distinct QuestLeader
From Quests

2 - Select Typename, sum(TreasureValue) as TotalValue
From Treasures as Tre
Inner Join Treasure_Types as TreTy on TreTy.typeid = Tre.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 q.questName
Order By Sum(t.TreasureValue) desc
4 - Select QuestLeader, 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 Treasures as t
Inner Join Treasure_Types as TT on t.typeID = tt.typeID
Group By TypeName
Order By Count(t.typeID)

Elizabeth_Gillespie · February 27, 2025, 8:00am

Time: 20 min
Difficulty: difficult
Solution:

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

Olivia_Egbert · February 27, 2025, 8:25am

Time to Complete 25 min
Rating: Intermediate
Solutions:

SELECT DISTINCT QuestLeader
FROM Quests
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
SELECT TOP 5 Quests.QuestName, Sum (Treasures.TreasureValue) AS TotalLoot
FROM Quests INNER JOIN Treasures ON Treasures.QuestID = Quests.QuestID
GROUP BY Quests.QuestName
ORDER BY SUM(Treasures.TreasureValue) DESC
SELECT Quests.QuestLeader, Quests.QuestName, ROUND(AVG(Treasures.TreasureValue),2) AS AvgTreasureValue
FROM Quests INNER JOIN Treasures ON Treasures.QuestID = Quests.QuestID
GROUP BY Quests.QuestLeader, Quests.QuestName
ORDER BY AVG(Treasures.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)

Colton_Bissegger · February 27, 2025, 2:30pm

Q1

SELECT DISTINCT QuestLeader

FROM Quests

Q2

SELECT tt.TypeName, sum(t.Treasuryvalue) AS t.TotalValue

FROM Treasures AS t

INNER JOIN Treasures_Types AS tt ON t.typeID=tt.typeID

GROUP BY tt.TypeName;

ORDER BY sum(t.Treasuryvalue) DESC;

Q3

SELECT TOP 5 q.QuestName, sum(t.treasuryvalue) AS TotalLoot

FROM Quests AS q

INNER JOIN Treasures AS t ON q.QuestID=t.QuestID

GROUP BY q.Quests;

ORDER BY sum(t.Treasuryvalue) DESC;

Q4

SELECT QuestLeader, QuestName, Avg(TreasureValue)

FROM Quests

INNER JOIN Treasures AS t ON q.QuestID=t.QuestID

GROUP BY QuestLeader, QuestName;

ORDER BY AvgTreasureValue DESC;

Q5

SELECT tt.TypeName, Count(t.TypeID) AS CountFound

FROM Treasures AS t

INNER JOIN Treasures_Types AS tt ON t.typeID=tt.typeID

GROUP BY tt.TypeName;

ORDER BY Count(t.TypeID);

Mariafe_Torrejon · February 27, 2025, 4:08pm

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

Gwen_Chandler · February 27, 2025, 4:24pm

Time: 20 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);

Quinton_Blackwelder · February 27, 2025, 4:50pm

Time: 14 Minutes
Difficulty: Easy
Solution:

Q1.
SELECT DISTINCT QuestLeader
FROM Quests;

Q2.
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;

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

Weston_Brockbank · February 27, 2025, 5:01pm

Time To Complete: 20-30
Difficulty: Intermediate
1.
SELECT DISTINCT QuestLeader
FROM Quest
2.
SELECT t.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 q.QuestName, Sum(t.TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON q.QuestID = t.QuestID
GROUP BY q.QuestName
ORDER BY Sum(t.TreasureValue) DESC;
4.
SELECT q.QuestLeader, q.QuestName, Round(Avg(TreasureValue),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 Avg(t.TreasureValue) DESC;
5.
ELECT 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);

Emma_Brown · February 27, 2025, 5:10pm

Time to Complete:
Difficulty:

SELECT DISTINCT QuestLeader
FROM Quests;
SELECT TypeName, SUM(Treasures.TreasureValue) AS TotalValue
FROM Treasures
INNER JOIN treasure_types ON treasures.typeID=treasure_types.typeID
GROUP BY TypeName
ORDER BY SUM(Treasures.TreasureValue) DESC;
SELECT TOP 5 QuestName, SUM(TreasureValue) AS TotalLoot
FROM Quests
INNER JOIN treasures ON quests.questid=treasures.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 AS tt ON treasures.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(treasures.TypeID);

Cozette_Rogers · February 27, 2025, 5:19pm

Time to Complete: 25 mins
Difficulty: Easy
Solution:
SELECT DISTINCT QuestLeader
FROM Quests;

SELECT TypeName, SUM(TreasureValue) AS TotalValue
FROM Treasure_Types as tt
INNER JOIN Treasures as t ON tt.TypeID = t.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) DESC;

marcushurd · February 27, 2025, 5:19pm

Time to Complete: 20 minutes
Rating: Fine
Solution:

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

Raegan_Allen · February 27, 2025, 5:49pm

Time to complete: 30 minutes
Difficulty: Intermediate (some technical difficulties and columns without headers)
Solutions:
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 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
LIMIT 5
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);

Topic		Replies	Views
34\|SQL – Audit Attention Challenges beginner , audit , sql	73	1020	December 15, 2023
176\|SQL – Precision Plane Data Presentation Challenges managerial , sql , intro , planes	324	1321	February 27, 2025
12\|SQL – New Management, New Focus Challenges beginner , managerial , sql	51	646	November 10, 2023
181\|SQL – Precision Plane Aggregation Adventure Challenges managerial , sql , intro , planes , having	12	159	March 13, 2025
168\|SQL – Trading Card Treasure Trove Challenges beginner , sql , consulting , valuation , trading-cards	44	300	March 16, 2025

166|SQL – The Guild of Gold

Related topics