166|SQL – The Guild of Gold

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

We provide the solution to this challenge using:

  • SQL

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.

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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