Commits

Anton Golov committed e8fc35d

Kept adding queries.

  • Participants
  • Parent commits 64a3dfd

Comments (0)

Files changed (1)

     )
 );
 
+-- 4
+
+SELECT *
+FROM Member
+WHERE MemId IN (
+    SELECT MemId
+    FROM Player
+    NATURAL JOIN ParticipatesIn
+    WHERE NOT EXISTS (
+        SELECT *
+        FROM Campaign
+        WHERE Campaign.CampId != ParticipatesIn.CampId
+    )
+);
+
+-- 5
+-- 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)
+        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
+                )
+            )
+        )
+    ) < (
+        SELECT SUM(Cost)
+        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
+                )
+            )
+        )
+    )
+);
+
+-- 6
+
+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
+    )
+);