Database Assignment / queries.txt

.header ON


-- 1
-- We maken een tabel van personen met hun emailadressen, waarbij personen
-- zonder emailadres ook in de tabel komen. Hun emailadres is dan NULL. Dit is
-- handig om afspraken te maken.

-- Trivial.

SELECT *
FROM Member
LEFT OUTER JOIN MemEmail ON Member.MemId = MemEmail.MemId;


-- 2
-- We zoeken hierbij naar personen waarvan geen enkele van hun characters in
-- meer dan 1 campaign meedoet. Dit is om mensen te zoeken die nog niet helemaal
-- tevreden met hun character zijn.

-- Finds all characters that are in multiple campaigns, and then excludes the
-- members who control those characters, returning the rest.

SELECT *
FROM Member
WHERE NOT EXISTS (
    SELECT *
    FROM Character
    WHERE Character.CharId IN (
        SELECT CharId
        FROM Controls
        NATURAL JOIN Player
        WHERE Player.MemId == Member.MemId
    ) AND 1 < (
        SELECT COUNT(*)
        FROM FeaturesIn
        WHERE FeaturesIn.CharId == Character.CharId
    )
);


-- 3
-- Deze query is geschreven om te zoeken naar personen die uitsluitend aan de
-- eerste sitting van een campaign meedoen, of aan geen enkele sitting. Dit is
-- handig om spelers die eigenlijk nooit meedoen te vinden.

-- In the innermost part of the query, we find all sittings where the same
-- campaign was played before a given sitting.  Working outwards, we only consider
-- sittings which have no predecessors.  We consider players who have been to
-- sittings not amongst those, and then exclude them.  The result consists
-- entirely of players who have not been to any non-first sitting, i.e. have
-- only been to first sittings.  (Obviously, players who have not been to any
-- sitting are included.)

SELECT *
FROM Member
WHERE NOT EXISTS (
    SELECT *
    FROM PresentAt
    WHERE Member.MemId == PresentAt.MemID
    AND SitId NOT IN (
        SELECT SitId
        FROM PlayedDuring
        WHERE NOT EXISTS (
            SELECT *
            FROM Sitting
            WHERE Sitting.Date < (
                SELECT Date
                FROM Sitting AS S2
                WHERE S2.SitId == PlayedDuring.SitId
            ) AND Sitting.SitId IN (
                SELECT SitId
                FROM PlayedDuring AS P2
                WHERE P2.CampId == PlayedDuring.CampId
            )
        )
    )
);


-- 4
-- We gebruiken een division om te kijken naar de personen die in alle campaigns
-- meespelen. Dit kan worden gebruikt om de meest actieve speler te vinden.

SELECT *
FROM Member
WHERE NOT EXISTS (
    SELECT *
    FROM Campaign
    WHERE Campaign.CampId NOT IN (
        SELECT CampId
        FROM ParticipatesIn 
        NATURAL JOIN Player
        WHERE Player.MemId == Member.MemId
    )
);


-- 5
-- Hier zoeken we de personen die over al hun characters verdeeld het meeste
-- geld hebben verkregen. Dit kan een indicatie geven van hoe zuinig een persoon
-- is.

-- The two inner-most queries are almost the same, they simply get the total
-- value of items owned by a given character, but with different characters
-- specified.  We are interested in members such that there are no members with
-- a greater total value.  So, we find all members that do have such a value and
-- then exclude them.


-- Grumble-grumble, this could be done much more elegantly with a view.

SELECT *
FROM Member
WHERE NOT EXISTS (
    SELECT *
    FROM Member AS M2
    WHERE (
        SELECT SUM(Cost*Quantity)
        FROM Item
        NATURAL JOIN OwnedBy
        WHERE CharId IN (
            SELECT CharId
            FROM Controls
            WHERE PlayId IN (
                SELECT PlayId
                FROM Player
                WHERE Player.MemId == M2.MemId
            )
        )
    ) > (
        SELECT SUM(Cost*Quantity)
        FROM Item
        NATURAL JOIN OwnedBy
        WHERE CharId IN (
            SELECT CharId
            FROM Controls
            WHERE PlayId IN (
                SELECT PlayId
                FROM Player
                WHERE Player.MemId == Member.MemId
            )
        )
    )
);


-- 6
-- Bij deze laatste query zoeken we naar het tweede meest voorkomende item,
-- vermenigvuldigd met hun waarde. We kijken dus naar het item wat de op een na
-- grootste plek in de economie inneemt.
-- Omdat de eerste bijna altijd Gold Pieces zal zijn, is het hierbij nuttig om
-- naar de op een na grootste te kijken.

SELECT ItemName
FROM (
    SELECT *
    FROM Item
    NATURAL JOIN OwnedBy
    GROUP BY ItemId
    HAVING SUM(Quantity) != (
        SELECT MAX(temp.v)
        FROM (
            SELECT SUM(Quantity) AS v
            FROM OwnedBy AS O
            GROUP BY O.ItemId
        ) temp
    )
)
NATURAL JOIN OwnedBy
GROUP BY ItemId
HAVING SUM(Quantity) == (
    SELECT MAX(temp2.v)
    FROM (
        SELECT SUM(Quantity) AS v
        FROM (
            SELECT *
            FROM Item
            NATURAL JOIN OwnedBy
            GROUP BY ItemId
            HAVING SUM(Quantity) != (
                SELECT MAX(temp.v)
                FROM (
                    SELECT SUM(Quantity) AS v
                    FROM OwnedBy AS O
                    GROUP BY O.ItemId
                ) temp
            )
        )
        AS O
        GROUP BY O.ItemId
    ) temp2
);


-- 7
-- Find the maximum number of characters that share the same class/race
-- combination, then return all combinations that have that number of
-- characters.

SELECT RaceName, ClassName
FROM Character
NATURAL JOIN IsOfClass
NATURAL JOIN Class
NATURAL JOIN Race
GROUP BY Character.RaceId, IsOfClass.ClassId
HAVING COUNT(*) == (
    SELECT MAX(temp.v)
    FROM (
        SELECT COUNT(*) AS v
        FROM Character AS CH
        NATURAL JOIN IsOfClass AS C
        GROUP BY CH.RaceId, C.ClassId
    ) temp
);
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.