DROP TABLE Berör CASCADE;
DROP TABLE Skrivit CASCADE;
DROP TABLE Rapporter CASCADE;
DROP TABLE Spioner CASCADE;
DROP TABLE Platser CASCADE;

CREATE TABLE Platser
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(11) NOT NULL UNIQUE,
Latitud FLOAT NOT NULL CHECK (Latitud >= -180 AND Latitud <= 180),
Longitud FLOAT NOT NULL CHECK (Longitud >= -180 AND Longitud <= 180));

INSERT INTO Platser VALUES (1, 'Röda Torget', 55.7541, 37.6205);
INSERT INTO Platser VALUES (2, 'Thomas rum', 59.2539, 15.2473);

CREATE TABLE Spioner
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(11) NOT NULL,
Född DATE NOT NULL,
Lön INTEGER NOT NULL CHECK (Lön >= 0));

INSERT INTO Spioner VALUES (007, 'James Bond', DATE '1920-11-11', 29000);
INSERT INTO Spioner VALUES (008, 'Örjan Bond', DATE '1920-11-11', 229000);
INSERT INTO Spioner VALUES (009, 'Lars Bond', DATE '1920-11-11', 129000);

CREATE TABLE Rapporter
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE NOT NULL,
Text NVARCHAR(10));

INSERT INTO Rapporter VALUES (1, DATE '2024-05-01', 'Första maj');
INSERT INTO Rapporter VALUES (2, DATE '2024-05-02', 'De vet!');
INSERT INTO Rapporter VALUES (12345, DATE '2024-05-03', 'Hemligt!');

CREATE TABLE Skrivit
(Spion INTEGER NOT NULL REFERENCES Spioner(Nummer),
Rapport INTEGER NOT NULL REFERENCES Rapporter(Nummer),
PRIMARY KEY (Spion, Rapport));

INSERT INTO Skrivit VALUES (007, 1);
INSERT INTO Skrivit VALUES (007, 2);
INSERT INTO Skrivit VALUES (008, 2);
INSERT INTO Skrivit VALUES (009, 12345);

CREATE TABLE Berör
(Rapport INTEGER NOT NULL REFERENCES Rapporter(Nummer),
Plats INTEGER NOT NULL REFERENCES Platser(Nummer),
PRIMARY KEY (Rapport, Plats));

INSERT INTO Berör VALUES (1, 1);
INSERT INTO Berör VALUES (2, 1);
INSERT INTO Berör VALUES (2, 2);

SELECT * FROM Platser;
SELECT * FROM Spioner;
SELECT * FROM Rapporter;
SELECT * FROM Skrivit;
SELECT * FROM Berör;

-- Uppgift 5 (11 p)
-- Formulera följande frågor i SQL. Definiera gärna vyer eller CTE:er om det
-- underlättar, men skapa inte nya tabeller.

-- a) (1p) Nu ska vi betala ut spionernas löner. Vad är deras sammanlagda lön?

SELECT SUM(Lön) FROM Spioner;

-- b) (2p) I kafeterian på underrättelsetjänstens högkvarter har någon glömt kvar en
-- rapport med nummer 12345. Vad heter den eller de spioner som skrivit den
-- rapporten?

SELECT Namn
FROM Spioner, Skrivit, Rapporter
WHERE Spioner.Nummer = Skrivit.Spion
AND Skrivit.Rapport = Rapporter.Nummer
AND Rapporter.Nummer = 12345;

SELECT Namn
FROM Spioner
WHERE Nummer IN (SELECT Spion
                 FROM Skrivit
                 WHERE Rapport = 12345);

-- c) (2p) Vad är datumen på de rapporter som spionen James Bond skickat från
-- Röda Torget? (Det finns bara en James Bond i databasen.)

SELECT Datum
FROM Spioner, Skrivit, Rapporter, Berör, Platser
WHERE Spioner.Nummer = Skrivit.Spion
AND Skrivit.Rapport = Rapporter.Nummer
AND Rapporter.Nummer = Berör.Rapport
AND Berör.Plats = Platser.Nummer
AND Spioner.Namn = 'James Bond'
AND Platser.Namn = 'Röda Torget';

-- d) (3p) Ryssland är stort, och som en grov uppskattning kan man säga att landet
-- upptar en rektangel som sträcker sig mellan latituderna 41 och 96, och longituderna
-- 20 och 180. Vilket är datumet på den senaste rapport som kommer från det
-- området?

SELECT MAX(Datum)
FROM Rapporter, Berör, Platser
WHERE  Rapporter.Nummer = Berör.Rapport
AND Berör.Plats = Platser.Nummer
AND Platser.Latitud BETWEEN 41 AND 96
AND Platser.Longitud BETWEEN 20 AND 180;

-- e) (3p) Vad heter den spion som skickat flest rapporter? Både rapporter där spionen
-- är ensam författare och en av flera författare räknas.

CREATE VIEW AntalRapporterPerSpion AS
SELECT Spioner.Nummer AS Spionnummer, Spioner.Namn AS Spionnamn, COUNT(*) AS AntalRapporter
FROM Spioner, Skrivit, Rapporter
WHERE Spioner.Nummer = Skrivit.Spion
AND Skrivit.Rapport = Rapporter.Nummer
GROUP BY Spioner.Nummer, Spioner.Namn;

SELECT Spionnamn
FROM AntalRapporterPerSpion
WHERE AntalRapporter = (SELECT MAX(AntalRapporter) FROM AntalRapporterPerSpion);