Några lösningsförslag till dbtek-hemtentan 2021-03-15
-----------------------------------------------------

Uppgift 2b
----------

DROP VIEW HarBiverkning;
DROP TABLE Biverkningar;
DROP TABLE Vaccinationer;
DROP TABLE Vaccin;
DROP TABLE Personer;

CREATE TABLE Personer
(ID INTEGER NOT NULL PRIMARY KEY,
Personnummer CHAR(11) NOT NULL UNIQUE,
Namn VARCHAR(15),
Gatuadress VARCHAR(10),
Ort VARCHAR(10),
Ålder INTEGER,
Vikt INTEGER);

CREATE TABLE Vaccin
(ID INTEGER NOT NULL PRIMARY KEY,
Namn VARCHAR(20) NOT NULL UNIQUE);

CREATE TABLE Vaccinationer
(ID INTEGER NOT NULL PRIMARY KEY,
Datum DATE,
Person INTEGER REFERENCES Personer(ID),
Vaccin INTEGER REFERENCES Vaccin(ID));

CREATE TABLE Biverkningar
(ID INTEGER NOT NULL PRIMARY KEY,
Namn VARCHAR(20) NOT NULL UNIQUE);

CREATE TABLE HarBiverkning
(Person INTEGER NOT NULL REFERENCES Personer(ID),
Biverkning INTEGER NOT NULL REFERENCES Biverkningar(ID),
PRIMARY KEY (Person, Biverkning));

INSERT INTO Personer (ID, Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt)
    VALUES (1, '450222-1122', 'Anna Alm', 'Vägen 5', 'Örebro', 75, 100);
INSERT INTO Personer (ID, Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt)
    VALUES (2, '551112-9917', 'Bengt Berg', 'Vägen 6', 'Örebro', 65, 100);
INSERT INTO Personer (ID, Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt)
    VALUES (3, '720515-4599', 'Don Duck', 'Vägen 8', 'Örebro', 48, 100);
INSERT INTO Personer (ID, Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt)
    VALUES (4, '871031-1914', 'Erik Ek', 'Vägen 9', 'Örebro', 33, 200);

INSERT INTO Vaccin (ID, Namn)
    VALUES (1, 'Sputnik V');
INSERT INTO Vaccin (ID, Namn)
    VALUES (2, 'Comirnaty');
INSERT INTO Vaccin (ID, Namn)
    VALUES (3, 'Chernobyl-Surplus');

INSERT INTO Vaccinationer (ID, Datum, Person, Vaccin)
    VALUES (1, DATE '2021-01-13', 1, 1);
INSERT INTO Vaccinationer (ID, Datum, Person, Vaccin)
    VALUES (2, DATE '2021-01-13', 2, 1);
INSERT INTO Vaccinationer (ID, Datum, Person, Vaccin)
    VALUES (3, DATE '2021-01-13', 3, 2);
INSERT INTO Vaccinationer (ID, Datum, Person, Vaccin)
    VALUES (4, DATE '2021-01-13', 3, 3);

INSERT INTO Biverkningar (ID, Namn)
    VALUES (1, 'feber');
INSERT INTO Biverkningar (ID, Namn)
    VALUES (2, 'huvudvärk');
INSERT INTO Biverkningar (ID, Namn)
    VALUES (3, 'frossa');
INSERT INTO Biverkningar (ID, Namn)
    VALUES (4, 'döden');
INSERT INTO Biverkningar (ID, Namn)
    VALUES (5, 'lyser i mörkret');

INSERT INTO HarBiverkning (Person, Biverkning)
    VALUES (1, 1);
INSERT INTO HarBiverkning (Person, Biverkning)
    VALUES (1, 2);
INSERT INTO HarBiverkning (Person, Biverkning)
    VALUES (1, 3);
INSERT INTO HarBiverkning (Person, Biverkning)
    VALUES (2, 3);
INSERT INTO HarBiverkning (Person, Biverkning)
    VALUES (2, 4);
INSERT INTO HarBiverkning (Person, Biverkning)
    VALUES (2, 5);

SELECT * FROM Personer;
SELECT * FROM Vaccin;
SELECT * FROM Vaccinationer;
SELECT * FROM Biverkningar;
SELECT * FROM HarBiverkning;

Uppgift 3
---------

-- a) (1p) Vilka vaccin har ett namn som börjar med Sputnik? Exempelvis
-- matchar Sputnik V, men inte Ultra-Sputnik 2000.

select * from Vaccin where Namn like 'Sputnik%';

-- b) (1p) Vilka personer väger mellan 70 och 80 kilo (inklusive
-- gränserna)?

select * from Personer where Vikt >= 70 and Vikt <= 80;

select * from Personer where Vikt between 70 and 80;

-- c) (2p) Vilka biverkningar har Anna Alm drabbats av?

select Biverkningar.Namn
from Personer, HarBiverkning, Biverkningar
where Personer.ID = HarBiverkning.Person
and HarBiverkning.Biverkning = Biverkningar.ID
and Personer.Namn = 'Anna Alm';

-- d) (2p) Vilka personer som vaccinerats med vaccinet Sputnik V har
-- drabbats av biverkningen lyser i mörkret?

-- Med implicit join:

select Personer.Personnummer, Personer.Namn
from Personer, HarBiverkning, Biverkningar, Vaccinationer, Vaccin
where Personer.ID = HarBiverkning.Person
and HarBiverkning.Biverkning = Biverkningar.ID
and Personer.ID = Vaccinationer.Person
and Vaccinationer.Vaccin = Vaccin.ID
and Vaccin.Namn = 'Sputnik V'
and Biverkningar.Namn = 'lyser i mörkret';

-- Med nästlade frågor:

select Personnummer, Namn
from Personer
where ID in (select Person from Vaccinationer where Vaccin in (select ID from Vaccin where Namn = 'Sputnik V'))
and ID in (select Person from HarBiverkning where Biverkning in (select ID from Biverkningar where Namn = 'lyser i mörkret'));

-- e) (2p) Finns det några personer som inte vaccinerats med Sputnik V,
-- men som ändå lyser i mörkret?

select Personnummer, Namn
from Personer
where ID not in (select Person from Vaccinationer where Vaccin in (select ID from Vaccin where Namn = 'Sputnik V'))
and ID in (select Person from HarBiverkning where Biverkning in (select ID from Biverkningar where Namn = 'lyser i mörkret'));

-- f) (3p) Vilken biverkning är vanligast bland de personer som
-- vaccinerats med Sputnik V?

-- Med en CTE:

with Biverkningsantal as
(select Biverkningar.Namn, count(*) as Antal
from Personer, HarBiverkning, Biverkningar, Vaccinationer, Vaccin
where Personer.ID = HarBiverkning.Person
and HarBiverkning.Biverkning = Biverkningar.ID
and Personer.ID = Vaccinationer.Person
and Vaccinationer.Vaccin = Vaccin.ID
and Vaccin.Namn = 'Sputnik V'
group by Biverkningar.Namn)
select Namn from Biverkningsantal where Antal in (select max(Antal) from Biverkningsantal);

-- Med en vy:

create view Biverkningsantal as
select Biverkningar.Namn, count(*) as Antal
from Personer, HarBiverkning, Biverkningar, Vaccinationer, Vaccin
where Personer.ID = HarBiverkning.Person
and HarBiverkning.Biverkning = Biverkningar.ID
and Personer.ID = Vaccinationer.Person
and Vaccinationer.Vaccin = Vaccin.ID
and Vaccin.Namn = 'Sputnik V'
group by Biverkningar.Namn;

select Namn from Biverkningsantal where Antal in (select max(Antal) from Biverkningsantal);

-- Med Mimer-syntax:

select Biverkningar.Namn, count(*) as Antal
from Personer, HarBiverkning, Biverkningar, Vaccinationer, Vaccin
where Personer.ID = HarBiverkning.Person
and HarBiverkning.Biverkning = Biverkningar.ID
and Personer.ID = Vaccinationer.Person
and Vaccinationer.Vaccin = Vaccin.ID
and Vaccin.Namn = 'Sputnik V'
group by Biverkningar.Namn
order by antal desc
fetch first 1;

-- Även MySQLs syntax med "LIMIT" och SQL Servers syntax med "TOP" är godkända.