1. Anton Golov
  2. Database Assignment

Source

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 *
FROM Item
WHERE NOT EXISTS (
    SELECT *
    FROM Item AS I2
    WHERE I2.ItemId != (
        SELECT ItemId
        FROM Item AS I3
        WHERE NOT EXISTS (
            SELECT *
            FROM Item AS I4
            WHERE (
                SELECT SUM(I5.Cost)
                FROM Item AS I5
                NATURAL JOIN OwnedBy
                WHERE I5.ItemId == I3.ItemID
            ) < (
                SELECT SUM(I5.Cost)
                FROM Item AS I5
                NATURAL JOIN OwnedBy
                WHERE I5.ItemID == I4.ItemID
            )
        )
    ) AND NOT EXISTS (
        -- Dummy code to make it compile
        SELECT *
        FROM Item
        WHERE 1 == 0
    )
);

-- 7

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