Advanced Database Reports (SQL Queries and Stored Procedures)

This page includes advanced SQL queries and stored procedures for generating reports from the *Legends of the Balkans* game database. The queries are based on the following tables:

1. Player Progress Report

This query provides a summary of each player's progress—including their character details and any associated faction reputation.

SELECT p.PlayerID,
       p.Username,
       c.Name AS CharacterName,
       c.Health,
       c.EXP,
       c.Gold,
       f.Name AS FactionName,
       r.ReputationPoints,
       r.Rank
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
LEFT JOIN Reputation r ON c.CharacterID = r.CharacterID
LEFT JOIN Faction f ON r.FactionID = f.FactionID;

2. Faction Reputation Update After Quest Completion

The following stored procedure updates a character’s reputation for a given faction based on the EXP reward from a completed quest.

CREATE OR REPLACE FUNCTION UpdateFactionReputation(
    char_id INT,
    quest_id INT,
    faction_id INT
)
RETURNS VOID AS
$$
DECLARE
    reputation_change INT;
BEGIN
    -- Get the EXP reward from the quest
    SELECT EXP_Reward INTO reputation_change
    FROM Quest
    WHERE QuestID = quest_id;

    -- Update the Reputation record for the character and faction
    UPDATE Reputation
    SET ReputationPoints = ReputationPoints + reputation_change
    WHERE CharacterID = char_id AND FactionID = faction_id;
END;
$$ LANGUAGE plpgsql;

3. Completed Quests Report for a Specific Player

This query retrieves the list of quests completed by a specific player. (Note: The join table *Character_Quest* contains only the CharacterID and QuestID.)

SELECT p.Username,
       q.Name AS QuestName
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
JOIN Character_Quest cq ON c.CharacterID = cq.CharacterID
JOIN Quest q ON cq.QuestID = q.QuestID
WHERE p.PlayerID = ?;  -- Replace ? with the specific PlayerID

4. List of Players in a Specific Faction

This query lists all players whose characters have a reputation record with a specific faction. Replace the faction name as needed.

SELECT DISTINCT p.PlayerID,
       p.Username,
       c.Name AS CharacterName
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
JOIN Reputation r ON c.CharacterID = r.CharacterID
JOIN Faction f ON r.FactionID = f.FactionID
WHERE f.Name = 'Kingdom of Zeta';  -- Replace with the desired faction name

5. Market Listings for a Specific Player

This query retrieves all items listed on the market by a specific player.

SELECT m.MarketID,
       i.Name AS ItemName,
       m.Price
FROM Market m
JOIN Item i ON m.ItemID = i.ItemID
WHERE m.SellerID = ?;  -- Replace ? with the specific Seller's PlayerID

6. Top Players by Total Experience and Completed Quests

This query provides a list of top players, ordered by the total EXP of all their characters and the number of quests they have completed.

SELECT p.PlayerID,
       p.Username,
       SUM(c.EXP) AS TotalEXP,
       COUNT(cq.QuestID) AS CompletedQuests
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
LEFT JOIN Character_Quest cq ON c.CharacterID = cq.CharacterID
GROUP BY p.PlayerID, p.Username
ORDER BY TotalEXP DESC, CompletedQuests DESC;

7. Items Owned by a Specific Player

This query lists the items owned by a specific player's characters.

SELECT p.Username,
       i.Name AS ItemName
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
JOIN Character_Item ci ON c.CharacterID = ci.CharacterID
JOIN Item i ON ci.ItemID = i.ItemID
WHERE p.PlayerID = ?;  -- Replace ? with the specific PlayerID

8. Items Listed in the Market

This query retrieves all items currently listed on the market along with seller information.

SELECT m.MarketID,
       i.Name AS ItemName,
       m.Price,
       p.Username AS Seller
FROM Market m
JOIN Item i ON m.ItemID = i.ItemID
JOIN Player p ON m.SellerID = p.PlayerID;

9. List of Guilds and Their Leaders

This query lists all guilds along with the username of their leader.

SELECT g.GuildID,
       g.Name AS GuildName,
       p.Username AS LeaderName
FROM Guild g
JOIN Player p ON g.LeaderID = p.PlayerID;

10. Characters in Guilds

This query retrieves characters that belong to guilds, along with their guild name.

SELECT p.Username,
       c.Name AS CharacterName,
       g.Name AS GuildName
FROM Character_Guild cg
JOIN Character c ON cg.CharacterID = c.CharacterID
JOIN Player p ON c.PlayerID = p.PlayerID
JOIN Guild g ON cg.GuildID = g.GuildID;