1. Anton Golov
  2. Database Assignment

Commits

Anton Golov  committed 2a98177

Worked on 6.

  • Participants
  • Parent commits 88e6c39
  • Branches default

Comments (0)

Files changed (2)

File dbvul.txt

View file
  • Ignore whitespace
 INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (4, 3, 1);
 INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (8, 3, 1);
 INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (1, 4, 45);
-INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (13, 4, 2);
+INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (13, 4, 10);
 INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (2, 4, 1);
 INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (1, 5, 1);
 INSERT INTO OwnedBy (ItemId, CharId, Quantity) VALUES (3, 5, 1);

File queries.txt

View file
  • Ignore whitespace
 .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
 -- 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 ItemName
+FROM (
     SELECT *
-    FROM Item AS I2
-    WHERE I2.ItemId != (
-        SELECT ItemId
-        FROM Item AS I3
-        WHERE NOT EXISTS (
+    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 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
+            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
             )
         )
-    ) AND NOT EXISTS (
-        -- Dummy code to make it compile
-        SELECT *
-        FROM Item
-        WHERE 1 == 0
-    )
+        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