DROP TABLE Böter CASCADE;
DROP TABLE Parkeringar CASCADE;
DROP TABLE Bilar CASCADE;
DROP TABLE Parkeringsrutor CASCADE;
DROP TABLE Parkeringsplatser CASCADE;

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

CREATE TABLE Parkeringsrutor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Parkeringsplats INTEGER NOT NULL REFERENCES Parkeringsplatser(Nummer));

CREATE TABLE Bilar
(Registreringsnummer CHAR(7) NOT NULL PRIMARY KEY);

CREATE TABLE Parkeringar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Bil CHAR(7) NOT NULL REFERENCES Bilar(Registreringsnummer),
Parkeringsplats INTEGER NOT NULL REFERENCES Parkeringsplatser(Nummer),
Starttid TIMESTAMP NOT NULL,
Sluttid TIMESTAMP NOT NULL,
CHECK (Starttid < Sluttid));

CREATE TABLE Böter
(Nummer INTEGER NOT NULL PRIMARY KEY,
Tidpunkt TIMESTAMP NOT NULL,
Bil CHAR(7) NOT NULL REFERENCES Bilar(Registreringsnummer),
Parkeringsruta INTEGER NOT NULL REFERENCES Parkeringsrutor(Nummer));

INSERT INTO Parkeringsplatser VALUES (1, 'Örebro universitet', 17, 22);
INSERT INTO Parkeringsplatser VALUES (2, 'Norra infarten till Örebro', -17, -22);
INSERT INTO Parkeringsplatser VALUES (3, 'Teleferico de Teide', 28.2698, -16.6389);
INSERT INTO Parkeringsplatser VALUES (4, 'Studentgatan 2-32', 77, 0);

INSERT INTO Parkeringsrutor VALUES (1, 1);
INSERT INTO Parkeringsrutor VALUES (2, 1);
INSERT INTO Parkeringsrutor VALUES (3, 1);
INSERT INTO Parkeringsrutor VALUES (4, 1);
INSERT INTO Parkeringsrutor VALUES (5, 2);
INSERT INTO Parkeringsrutor VALUES (6, 3);
INSERT INTO Parkeringsrutor VALUES (7, 3);
INSERT INTO Parkeringsrutor VALUES (8, 3);

INSERT INTO Bilar VALUES ('GRG 89R');
INSERT INTO Bilar VALUES ('FRP 06P');
INSERT INTO Bilar VALUES ('ASS 666');

INSERT INTO Parkeringar VALUES (1, 'GRG 89R', 1, TIMESTAMP '2025-01-01 23:58:00.00045', TIMESTAMP '2025-01-02 08:00:00');

INSERT INTO Böter VALUES (1, TIMESTAMP '2025-01-02 08:00:00', 'GRG 89R', 3);

SELECT * FROM Parkeringsplatser;
SELECT * FROM Parkeringsrutor;
SELECT * FROM Bilar;
SELECT * FROM Parkeringar;
SELECT * FROM Böter;



-- 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) Vad heter de parkeringsplatser som har ett namn som börjar på
-- Örebro (till exempel Örebro universitet)?

SELECT Namn
FROM Parkeringsplatser
WHERE Namn LIKE 'Örebro%';

-- b) (1p) Vad heter de parkeringsplatser som har ett namn som innehåller
-- Örebro (till exempel Norra infarten till Örebro)?

SELECT Namn
FROM Parkeringsplatser
WHERE Namn LIKE '%Örebro%';

-- c) (2p) Vad är namnen på alla de parkeringsplatser där bilen med
-- registreringsnummer GRG 89R har parkerats?

SELECT Namn
FROM Parkeringsplatser
WHERE Nummer IN (SELECT Parkeringsplats
                 FROM Parkeringar
                 WHERE Bil = 'GRG 89R');

SELECT DISTINCT Namn
FROM Parkeringsplatser, Parkeringar
WHERE Parkeringsplatser.Nummer = Parkeringar.Parkeringsplats
AND Parkeringar.Bil = 'GRG 89R';

SELECT DISTINCT Namn
FROM Parkeringsplatser JOIN Parkeringar ON Parkeringsplatser.Nummer = Parkeringar.Parkeringsplats
WHERE Parkeringar.Bil = 'GRG 89R';

-- d) (2p) Hur många bilar finns det just nu på parkeringsplatsen som
-- heter Studentgatan 2-32? Man kan använda NOW() för att få fram
-- aktuell tid, så man kan jämföra den med starttiden och sluttiden för
-- en parkering.

SELECT *
FROM Parkeringar, Parkeringsplatser
WHERE Parkeringar.Parkeringsplats = Parkeringsplatser.Nummer
AND LOCALTIMESTAMP BETWEEN Parkeringar.Starttid AND Parkeringar.Sluttid
AND Parkeringsplatser.Namn = 'Studentgatan 2-32';

-- e) (2p) Vad heter den parkeringsplats som ligger längst norrut? (Det
-- är alltså den parkeringsplats som har den högsta latituden.)

SELECT Namn
FROM Parkeringsplatser
WHERE Latitud = (SELECT MAX(Latitud) FROM Parkeringsplatser);

-- f) (3p) Vad är namnet på den parkeringsplats där flest bilar fått
-- parkeringsböter?

WITH "Böter per parkeringsplats" AS
(SELECT Parkeringsplatser.Namn AS Parkeringsplats, COUNT(*) AS "Antal böter"
FROM Böter, Parkeringsrutor, Parkeringsplatser
WHERE Böter.Parkeringsruta = Parkeringsrutor.Nummer
AND Parkeringsrutor.Parkeringsplats = Parkeringsplatser.Nummer
GROUP BY Parkeringsplatser.Namn)
SELECT * FROM "Böter per parkeringsplats"
WHERE "Antal böter" = (SELECT MAX("Antal böter") FROM "Böter per parkeringsplats");