Commits

Anton Golov committed 4407480

Worked on query 7.

  • Participants
  • Parent commits d734502

Comments (0)

Files changed (3)

 INSERT INTO Member (MemName, Address) VALUES ("Harry", "Bussum");
 
 
-INSERT INTO MemEmail (Email, MemId) VALUES ("a@a.com", 1);
-INSERT INTO MemEmail (Email, MemId) VALUES ("b@b.com", 2);
-INSERT INTO MemEmail (Email, MemId) VALUES ("c@c.com", 3);
-INSERT INTO MemEmail (Email, MemId) VALUES ("d@d.com", 4);
-INSERT INTO MemEmail (Email, MemId) VALUES ("e@e.com", 5);
-INSERT INTO MemEmail (Email, MemId) VALUES ("f@f.com", 6);
-INSERT INTO MemEmail (Email, MemId) VALUES ("h@h.com", 7);
+INSERT INTO MemEmail (Email, MemId) VALUES ("icreu@gmail.com", 1);
+INSERT INTO MemEmail (Email, MemId) VALUES ("jkoy@gmail.com", 1);
+INSERT INTO MemEmail (Email, MemId) VALUES ("eokx@hotmail.com", 2);
+INSERT INTO MemEmail (Email, MemId) VALUES ("dlit@gmail.com", 4);
+INSERT INTO MemEmail (Email, MemId) VALUES ("uiqk@gmail.com", 6);
+INSERT INTO MemEmail (Email, MemId) VALUES ("iuhsns@gmail.com", 7);
 
 
 INSERT INTO MemPhone (Phone, MemId) VALUES ("0622222222", 2);
 INSERT INTO Character (CharName, RaceId) VALUES ("Grumpf", 1);
 INSERT INTO Character (CharName, RaceId) VALUES ("Krugmar", 2);
 INSERT INTO Character (CharName, RaceId) VALUES ("Silverleaf", 3);
-INSERT INTO Character (CharName, RaceId) VALUES ("Knibbler", 5);
+INSERT INTO Character (CharName, RaceId) VALUES ("Moroz", 2);
 INSERT INTO Character (CharName, RaceId) VALUES ("John", 6);
 INSERT INTO Character (CharName, RaceId) VALUES ("Grace", 7);
 
 INSERT INTO Runs (DmId, CampId) VALUES (7, 2);
 INSERT INTO Runs (DmId, CampId) VALUES (6, 3);
 
-INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (1, 3, 5);
+INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (1, 2, 5);
 INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (1, 4, 3);
 INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (2, 2, 6);
 INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (3, 5, 2);
-INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (4, 1, 3);
+INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (4, 2, 3);
 INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (4, 3, 4);
 INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (5, 2, 1);
+INSERT INTO IsOfClass (CharId, ClassId, Level) VALUES (6, 4, 4);
 
 INSERT INTO HasPower (CharId, PowId) VALUES (1, 4);
 INSERT INTO HasPower (CharId, PowId) VALUES (4, 1);
 INSERT INTO RequiresClass (PowId, ClassId) VALUES (3, 1);
 INSERT INTO RequiresClass (PowId, ClassId) VALUES (4, 7);
 
-INSERT INTO Controls (PlayId, CharId) VALUES ( 1, 1 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 1, 3 );
+INSERT INTO Controls (PlayId, CharId) VALUES ( 1, 3 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 3, 3 );
 INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 1, 3 );
+INSERT INTO Controls (PlayId, CharId) VALUES ( 2, 6 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 3, 6 );
+INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 2, 3 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 1, 6 );
+INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 2, 1 );
+INSERT INTO Controls (PlayId, CharId) VALUES ( 3, 1 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 1, 1 );
+INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 3, 1 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 2, 1 );
+INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 3, 2 );
+INSERT INTO Controls (PlayId, CharId) VALUES ( 4, 2 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 3, 2 );
+INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 4, 3 );
 INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 1, 2 );
-INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 1, 2 );
-INSERT INTO Controls (PlayId, CharId) VALUES ( 2, 5 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 5, 3 );
-INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 2, 3 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 5, 2 );
-INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 2, 2 );
-INSERT INTO Controls (PlayId, CharId) VALUES ( 3, 4 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 4, 2 );
-INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 3, 2 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 4, 1 );
-INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 3, 1 );
-INSERT INTO Controls (PlayId, CharId) VALUES ( 4, 6 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 6, 2 );
-INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 4, 2 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 6, 1 );
 INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 4, 1 );
-INSERT INTO Controls (PlayId, CharId) VALUES ( 5, 3 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 3, 2 );
+INSERT INTO Controls (PlayId, CharId) VALUES ( 5, 4 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 3, 4 );
+INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 5, 3 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 2, 4 );
 INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 5, 2 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 3, 1 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 1, 4 );
 INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 5, 1 );
-INSERT INTO Controls (PlayId, CharId) VALUES ( 6, 2 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 2, 1 );
+INSERT INTO Controls (PlayId, CharId) VALUES ( 6, 5 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 1, 5 );
 INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 6, 1 );
-INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 2, 2 );
+INSERT INTO FeaturesIn (CampId, CharId) VALUES ( 2, 5 );
 INSERT INTO ParticipatesIn (PlayId, CampId) VALUES ( 6, 2 );
-INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 3, 1 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 1 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 1, 1 );
+INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 1, 1 );
+INSERT INTO PresentAt (MemId, SitId) VALUES ( 7, 1 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 2, 1 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 3, 1 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 4, 1 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 1 );
 INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 2, 2 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 7, 2 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 1, 2 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 2, 2 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 3, 2 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 4, 2 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 5, 2 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 2 );
 INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 1, 3 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 7, 3 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 1, 3 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 2, 3 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 3, 3 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 4, 3 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 5, 3 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 3 );
-INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 2, 4 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 7, 4 );
+INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 3, 4 );
+INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 4 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 1, 4 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 2, 4 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 3, 4 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 4, 4 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 5, 4 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 4 );
-INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 3, 5 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 5 );
-INSERT INTO PresentAt (MemId, SitId) VALUES ( 1, 5 );
+INSERT INTO PlayedDuring (CampId, SitId) VALUES ( 1, 5 );
+INSERT INTO PresentAt (MemId, SitId) VALUES ( 7, 5 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 2, 5 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 3, 5 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 4, 5 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 5, 5 );
 INSERT INTO PresentAt (MemId, SitId) VALUES ( 6, 5 );
+

File make_combinations.py

     p_insert('Controls', (p, ch))
     ca = random.sample(range(1, 4), 2)
     for c in ca:
-        p_insert('FeaturesIn', (ch, c))
+        p_insert('FeaturesIn', (c, ch))
         p_insert('ParticipatesIn', (p, c))
     p_ch_c_combos.append((p, ch, ca))
 
     p_insert('PlayedDuring', (ca, s))
     p_insert('PresentAt', (6 if ca == 3 else 7, s))
     for p, ch, c in p_ch_c_combos:
-        p_insert('PresentAt', (p, s))
+        if ca in c:
+            p_insert('PresentAt', (p, s))
         
 
+.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 Character.CharId IN (
         SELECT CharId
         FROM Controls
-        WHERE Controls.PlayId IN (
-            SELECT PlayId
-            FROM Player
-            WHERE Player.MemId == Member.MemId
-        )
-    ) AND 1 >= (
+        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
     SELECT *
     FROM PresentAt
     WHERE Member.MemId == PresentAt.MemID
-    AND SitId IN (
+    AND SitId NOT IN (
         SELECT SitId
         FROM PlayedDuring
         WHERE NOT EXISTS (
-            SELECT SitId
+            SELECT *
             FROM Sitting
-            WHERE Sitting.Date >= (
+            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 MemId IN (
-    SELECT MemId
-    FROM Player
-    NATURAL JOIN ParticipatesIn
-    WHERE NOT EXISTS (
-        SELECT *
-        FROM Campaign
-        WHERE Campaign.CampId != ParticipatesIn.CampId
+WHERE NOT EXISTS (
+    SELECT *
+    FROM Campaign
+    WHERE Campaign.CampId NOT IN (
+        SELECT CampId
+        FROM ParticipatesIn 
+        NATURAL JOIN Player
+        WHERE Player.MemId == Member.MemId
     )
 );
 
+
 -- 5
--- This could be done much more elegantly with a view.
+-- 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
     SELECT *
     FROM Member AS M2
     WHERE (
-        SELECT SUM(Cost)
+        SELECT SUM(Cost*Quantity)
         FROM Item
-        WHERE ItemId IN (
-            SELECT ItemId
-            FROM OwnedBy
-            WHERE CharId IN (
-                SELECT CharId
-                FROM Controls
-                WHERE PlayId IN (
-                    SELECT PlayId
-                    FROM Player
-                    WHERE Player.MemId == M2.MemId
-                )
+        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)
+    ) > (
+        SELECT SUM(Cost*Quantity)
         FROM Item
-        WHERE ItemId IN (
-            SELECT ItemId
-            FROM OwnedBy
-            WHERE CharId IN (
-                SELECT CharId
-                FROM Controls
-                WHERE PlayId IN (
-                    SELECT PlayId
-                    FROM Player
-                    WHERE Player.MemId == Member.MemId
-                )
+        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 1 == 0
     )
 );
+
+-- 7
+
+SELECT RaceName, ClassName
+FROM Character
+NATURAL JOIN IsOfClass, Class, 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
+);
+