DROP TABLE Vaccinationer CASCADE;
DROP TABLE Sjukdomar CASCADE;
DROP TABLE Ägg CASCADE;
DROP TABLE Hönor CASCADE;

CREATE TABLE Hönor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(6) NOT NULL,
Vikt INTEGER NOT NULL CHECK (Vikt > 0),
Kläckt DATE NOT NULL,
Mamma INTEGER REFERENCES Hönor(Nummer));

INSERT INTO Hönor VALUES (1, 'Agda', 2, DATE '2024-01-01', NULL);
INSERT INTO Hönor VALUES (2, 'Agda', 2, DATE '2024-01-01', NULL);
INSERT INTO Hönor VALUES (3, 'Agda', 3, DATE '2024-03-13', 1);
INSERT INTO Hönor VALUES (4, 'Pullan', 3, DATE '2023-01-01', NULL);

CREATE TABLE Ägg
(Nummer INTEGER NOT NULL PRIMARY KEY,
Vikt INTEGER NOT NULL CHECK (Vikt > 0),
Värpt DATE NOT NULL,
Mamma INTEGER REFERENCES Hönor(Nummer));

INSERT INTO Ägg VALUES (1, 200, DATE '2024-03-13', 4);
INSERT INTO Ägg VALUES (2, 200, DATE '2024-03-13', 4);
INSERT INTO Ägg VALUES (3, 200, DATE '2024-03-13', 4);
INSERT INTO Ägg VALUES (4, 200, DATE '2024-03-13', 4);
INSERT INTO Ägg VALUES (1234567, 200, DATE '2024-03-13', 4);

CREATE TABLE Sjukdomar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL UNIQUE);

INSERT INTO Sjukdomar VALUES (1, 'ankpest');
INSERT INTO Sjukdomar VALUES (2, 'duvpest');
INSERT INTO Sjukdomar VALUES (3, 'gåspest');
INSERT INTO Sjukdomar VALUES (4, 'dödskolera');
INSERT INTO Sjukdomar VALUES (5, 'ispestdöd');

CREATE TABLE Vaccinationer
(Höna INTEGER REFERENCES Hönor(Nummer),
Sjukdom INTEGER REFERENCES Sjukdomar(Nummer),
PRIMARY KEY (Höna, Sjukdom));

INSERT INTO Vaccinationer VALUES (1, 1);
INSERT INTO Vaccinationer VALUES (2, 1);
INSERT INTO Vaccinationer VALUES (3, 1);
INSERT INTO Vaccinationer VALUES (4, 1);
INSERT INTO Vaccinationer VALUES (1, 2);
INSERT INTO Vaccinationer VALUES (2, 2);
INSERT INTO Vaccinationer VALUES (3, 2);
INSERT INTO Vaccinationer VALUES (4, 2);
INSERT INTO Vaccinationer VALUES (1, 3);
INSERT INTO Vaccinationer VALUES (2, 4);


-- Uppgift 3 (12 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) Vilka olika sorters pest finns det? Dvs, vad heter de
-- sjukdomar vars namn innehåller "pest"?

SELECT Namn FROM Sjukdomar WHERE Namn LIKE '%pest%';

-- b) (1p) Vad heter de sjukdomar vars namn inte innehåller "pest"?

SELECT Namn FROM Sjukdomar WHERE Namn NOT LIKE '%pest%';

-- c) (1p) Här är ägg nummer 1234567. Vad heter hönan som värpt det
-- ägget?

SELECT Namn
FROM Hönor
WHERE Nummer IN (SELECT Mamma FROM Ägg WHERE Nummer = 1234567);

-- d) (1p) Vad väger alla hönorna tillsammans?

SELECT SUM(Vikt) FROM Hönor;

-- e) (3p) Det kan vara svårt att hitta på namn till alla hönorna, och
-- det blir lätt så att man har hundratals hönor som heter Agda. Hur
-- många hönor finns det som har vart och ett av de olika namnen? Vi vill
-- ha dem sorterade i ordning med namnet som har flest hönor först.

SELECT Namn, COUNT(*) AS Antal
FROM Hönor
GROUP BY Namn
ORDER BY Antal DESC;

-- f) (3p) Vad heter den sjukdom som vi har vaccinerat flest hönor mot?

WITH AntalVaccinationerPerSjukdom AS
(SELECT Sjukdom, COUNT(*) AS Antal
FROM Vaccinationer
GROUP BY Sjukdom)
SELECT Namn
FROM Sjukdomar
WHERE Nummer IN (SELECT Sjukdom
                 FROM AntalVaccinationerPerSjukdom
                 WHERE Antal = (SELECT MAX(Antal)
                                FROM AntalVaccinationerPerSjukdom));

-- g) (2p) En sjukdom som alla hönorna är vaccinerade mot är det ingen
-- risk att vi får ett utbrott av. Vilka sjukdomar har vi inte vaccinerat
-- alla hönorna mot?

SELECT Namn
FROM Sjukdomar
WHERE EXISTS (SELECT * FROM Hönor
              WHERE Nummer NOT IN (SELECT Höna
                                   FROM Vaccinationer
                                   WHERE Sjukdom = Sjukdomar.Nummer));