CREATE TABLE Platser
(Nummer INTEGER NOT NULL PRIMARY KEY,
Latitud FLOAT NOT NULL CHECK (Latitud BETWEEN -180 AND +180),
Longitud FLOAT NOT NULL CHECK (Longitud BETWEEN -180 AND +180),
Synlighet INTEGER CHECK (Synlighet BETWEEN 1 AND 10));

CREATE TABLE Tunnor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Volym INTEGER CHECK (Volym >= 1),
Material NVARCHAR(15) NOT NULL,
Tillverkningsår INTEGER CHECK (Tillverkningsår >= 1523),
Placering INTEGER REFERENCES Platser (Nummer));

CREATE TABLE Kemikalier
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(15) NOT NULL UNIQUE,
Giftighet INTEGER CHECK (Giftighet BETWEEN 1 AND 10),
Miljöskadlighet INTEGER CHECK (Miljöskadlighet BETWEEN 1 AND 10));

CREATE TABLE Innehåller
(Tunna INTEGER NOT NULL REFERENCES Tunnor (Nummer),
Kemikalie INTEGER NOT NULL REFERENCES Kemikalier (Nummer),
PRIMARY KEY (Tunna, Kemikalie));

INSERT INTO Platser VALUES (1, 59.2539, 15.2472, 2); -- mitt rum
INSERT INTO Platser VALUES (2, 59.3323, 18.0641, 10); -- Sergels torg
INSERT INTO Platser VALUES (3, 71.17, 25.78, 8); -- Nordkap
INSERT INTO Platser VALUES (4, 180, 0, 1); -- Nordpolen

INSERT INTO Tunnor VALUES (1, 208, 'plast', 1959, 1);
INSERT INTO Tunnor VALUES (2, 208, 'plast', 1970, 1);
INSERT INTO Tunnor VALUES (3, 208, 'plast', 1962, 1);
INSERT INTO Tunnor VALUES (4, 208, 'kartong', 2025, 3);
INSERT INTO Tunnor VALUES (5, 208, 'stål', 2025, 3);
INSERT INTO Tunnor VALUES (6, 208, 'plast', 1970, 4);
INSERT INTO Tunnor VALUES (7, 208, 'plast', 1970, 4);
INSERT INTO Tunnor VALUES (9000, 208, 'porslin', 1970, 4);

INSERT INTO Kemikalier VALUES (1, 'PCB', 10, 10);
INSERT INTO Kemikalier VALUES (2, 'pentaklorfenol', 10, 10);
INSERT INTO Kemikalier VALUES (3, 'dioxin', 10, 10);
INSERT INTO Kemikalier VALUES (4, 'botox', 10, 10);
INSERT INTO Kemikalier VALUES (5, 'polonium', 10, 10);
INSERT INTO Kemikalier VALUES (6, 'vaniljsås', 1, 2);

INSERT INTO Innehåller VALUES (9000, 1);
INSERT INTO Innehåller VALUES (9000, 6);
INSERT INTO Innehåller VALUES (1, 1);
INSERT INTO Innehåller VALUES (2, 2);
INSERT INTO Innehåller VALUES (3, 3);
INSERT INTO Innehåller VALUES (4, 4);
INSERT INTO Innehåller VALUES (5, 5);
INSERT INTO Innehåller VALUES (6, 6);
INSERT INTO Innehåller VALUES (7, 1);
INSERT INTO Innehåller VALUES (7, 2);
INSERT INTO Innehåller VALUES (7, 3);
INSERT INTO Innehåller VALUES (7, 4);
INSERT INTO Innehåller VALUES (7, 5);
INSERT INTO Innehåller VALUES (7, 6);

SELECT * FROM Platser;
SELECT * FROM Tunnor;
SELECT * FROM Kemikalier;
SELECT * FROM Innehåller;

SELECT *
FROM Platser, Tunnor, Innehåller, Kemikalier
WHERE Platser.Nummer = Tunnor.Placering
AND Tunnor.Nummer = Innehåller.Tunna
AND Innehåller.Kemikalie = Kemikalier.Nummer;

DROP TABLE Platser CASCADE;
DROP TABLE Tunnor CASCADE;
DROP TABLE Kemikalier CASCADE;
DROP TABLE Innehåller CASCADE;

-- a) (1p) Vi har hittat en tunna som är märkt med numret 9000. Vad är den gjord av?

SELECT Material FROM Tunnor WHERE Nummer = 9000;

-- b) (2p) Vilka kemikalier innehåller den?

SELECT Kemikalier.Namn
FROM Tunnor, Innehåller, Kemikalier
WHERE Tunnor.Nummer = 9000
AND Tunnor.Nummer = Innehåller.Tunna
AND Innehåller.Kemikalie = Kemikalier.Nummer;

-- c) (2p) Vi upptäcker att en del av tunnorna är gjorda av kartong. De kommer nog inte att hålla så länge. Vad är koordinaterna för de platser där de står?

SELECT DISTINCT Platser.Latitud, Platser.Longitud
FROM Tunnor, Platser
WHERE Tunnor.Material = 'kartong'
AND Tunnor.Placering = Platser.Nummer;

-- d) (2p) Hur många tunnor som innehåller polonium finns det?

SELECT COUNT(*)
FROM Tunnor, Innehåller, Kemikalier
WHERE Tunnor.Nummer = Innehåller.Tunna
AND Innehåller.Kemikalie = Kemikalier.Nummer
AND Kemikalier.Namn = 'polonium';

-- e) (3p) Vilken av de giftigaste kemikalierna (med giftighetsklass 10) finns i flest olika tunnor?

WITH AntalTunnorPerKemikalie AS
(SELECT Kemikalier.Namn, COUNT(*) AS Antal
FROM Tunnor, Innehåller, Kemikalier
WHERE Tunnor.Nummer = Innehåller.Tunna
AND Innehåller.Kemikalie = Kemikalier.Nummer
AND Kemikalier.Giftighet = 10
GROUP BY Kemikalier.Namn)
SELECT Namn
FROM AntalTunnorPerKemikalie
WHERE Antal = (SELECT MAX(Antal) FROM AntalTunnorPerKemikalie);

-- f) (2p) Tunnor som står på platser med synlighet 1 kommer nog ingen att hitta, så vi kan låtsas som att de inte finns. Skriv de delete-kommandon som behövs för att radera all information om dessa tunnor ur databasen. 

DELETE FROM Innehåller WHERE Tunna IN (SELECT Nummer FROM Tunnor WHERE Placering IN (SELECT Nummer FROM Platser WHERE Synlighet = 1));
DELETE FROM Tunnor WHERE Placering IN (SELECT Nummer FROM Platser WHERE Synlighet = 1);