DROP TABLE TagitEmotLarm; DROP TABLE Operatörer; DROP TABLE Larm; DROP TABLE Larmsändare; DROP TABLE Larmtyper; DROP TABLE Anläggningar; CREATE TABLE Anläggningar (Nummer INTEGER NOT NULL PRIMARY KEY, Namn NVARCHAR(20) NOT NULL UNIQUE, Adress NVARCHAR(20) NOT NULL); CREATE TABLE Larmtyper (Nummer INTEGER NOT NULL PRIMARY KEY, Namn NVARCHAR(30) NOT NULL UNIQUE); CREATE TABLE Larmsändare (Nummer INTEGER NOT NULL PRIMARY KEY, Plats INTEGER NOT NULL REFERENCES Anläggningar(Nummer)); CREATE TABLE Larm (Nummer INTEGER NOT NULL PRIMARY KEY, Datum DATE NOT NULL, Tid TIME NOT NULL, Från INTEGER NOT NULL REFERENCES Larmsändare(Nummer), Typ INTEGER NOT NULL REFERENCES Larmtyper(Nummer)); CREATE TABLE Operatörer (Användarnamn NVARCHAR(20) NOT NULL UNIQUE, Namn NVARCHAR(20) NOT NULL); CREATE TABLE TagitEmotLarm (Operatör NVARCHAR(20) NOT NULL REFERENCES Operatörer(Användarnamn), Larm INTEGER NOT NULL REFERENCES Larm(Nummer), PRIMARY KEY (Operatör, Larm)); INSERT INTO Anläggningar (Nummer, Namn, Adress) VALUES (1, 'Grythyttan 6', 'Grythyttan'); INSERT INTO Anläggningar (Nummer, Namn, Adress) VALUES (2, 'Grythyttan 7', 'Grythyttan'); INSERT INTO Anläggningar (Nummer, Namn, Adress) VALUES (3, 'Grythyttan 8', 'Grythyttan'); INSERT INTO Larmtyper (Nummer, Namn) VALUES (1, 'brandlarm'); INSERT INTO Larmtyper (Nummer, Namn) VALUES (2, 'monsterattack'); INSERT INTO Larmtyper (Nummer, Namn) VALUES (3, 'attackflygplanskrasch'); INSERT INTO Larmtyper (Nummer, Namn) VALUES (4, 'skattackumulator'); INSERT INTO Larmsändare (Nummer, Plats) VALUES (1, 1); INSERT INTO Larmsändare (Nummer, Plats) VALUES (2, 2); INSERT INTO Larmsändare (Nummer, Plats) VALUES (3, 2); INSERT INTO Larmsändare (Nummer, Plats) VALUES (4, 3); INSERT INTO Larm (Nummer, Datum, Tid, Från, Typ) VALUES (1, DATE '2019-03-21', TIME '09:10:00', 1, 2); INSERT INTO Larm (Nummer, Datum, Tid, Från, Typ) VALUES (2, DATE '2019-03-21', TIME '09:10:00', 2, 2); INSERT INTO Larm (Nummer, Datum, Tid, Från, Typ) VALUES (3, DATE '2019-03-21', TIME '09:10:00', 3, 2); INSERT INTO Larm (Nummer, Datum, Tid, Från, Typ) VALUES (4, DATE '2019-03-21', TIME '09:10:00', 3, 3); INSERT INTO Larm (Nummer, Datum, Tid, Från, Typ) VALUES (5, DATE '2019-03-21', TIME '09:10:00', 4, 2); INSERT INTO Operatörer (Användarnamn, Namn) VALUES ('arnold', 'A. Schwarzenegger'); INSERT INTO Operatörer (Användarnamn, Namn) VALUES ('björne', 'Björne'); INSERT INTO Operatörer (Användarnamn, Namn) VALUES ('chomsky', 'Noam Chomsky'); INSERT INTO TagitEmotLarm (Operatör, Larm) VALUES ('chomsky', 1); INSERT INTO TagitEmotLarm (Operatör, Larm) VALUES ('björne', 2); INSERT INTO TagitEmotLarm (Operatör, Larm) VALUES ('arnold', 3); SELECT * FROM Anläggningar; SELECT * FROM Larmtyper; SELECT * FROM Larmsändare; SELECT * FROM Larm; SELECT * FROM Operatörer; SELECT * FROM TagitEmotLarm; -- a) (1p) Vilka olika larmtypsnamn innehåller attack? Det skulle till exempel kunna vara monsterattack, attackflygplanskrasch och skattackumulator. SELECT Namn FROM Larmtyper WHERE Namn LIKE '%attack%'; -- b) (1p) Vad är numren på de larmsändare som är placerade i anläggningen Grythyttan 7? SELECT Larmsändare.Nummer FROM Larmsändare, Anläggningar WHERE Larmsändare.Plats = Anläggningar.Nummer AND Anläggningar.Namn = 'Grythyttan 7'; SELECT Nummer FROM Larmsändare WHERE Plats IN (SELECT Nummer FROM Anläggningar WHERE Namn = 'Grythyttan 7'); SELECT Larmsändare.Nummer FROM Larmsändare JOIN Anläggningar ON Larmsändare.Plats = Anläggningar.Nummer WHERE Anläggningar.Namn = 'Grythyttan 7'; -- c) (2p) Hur många larm har sammanlagt kommit från anläggningen Grythyttan 7? SELECT COUNT(*) FROM Larm, Larmsändare, Anläggningar WHERE Larm.Från = Larmsändare.Nummer AND Larmsändare.Plats = Anläggningar.Nummer AND Anläggningar.Namn = 'Grythyttan 7'; SELECT COUNT(*) FROM Larm WHERE Från IN (SELECT Nummer FROM Larmsändare WHERE Plats IN (SELECT Nummer FROM Anläggningar WHERE Namn = 'Grythyttan 7')); -- d) (2p) Hur många larm av var och en av de olika larmtyperna har kommit från anläggningen Grythyttan 7? SELECT Larmtyper.Namn, COUNT(*) FROM Larm, Larmtyper, Larmsändare, Anläggningar WHERE Larm.Typ = Larmtyper.Nummer AND Larm.Från = Larmsändare.Nummer AND Larmsändare.Plats = Anläggningar.Nummer AND Anläggningar.Namn = 'Grythyttan 7' GROUP BY Larmtyper.Namn; -- e) (2p) Vilka larm har ännu inte mottagits av någon operatör? Vi vill veta de larmens nummer, larmtyp, och namnet på anläggningen som de kommer från. SELECT Larm.Nummer, Larmtyper.Namn, Anläggningar.Namn FROM Larm, Larmtyper, Larmsändare, Anläggningar WHERE Larm.Typ = Larmtyper.Nummer AND Larm.Från = Larmsändare.Nummer AND Larmsändare.Plats = Anläggningar.Nummer AND Larm.Nummer NOT IN (SELECT Larm FROM TagitEmotLarm); SELECT Larm.Nummer, Larmtyper.Namn, Anläggningar.Namn FROM Larm LEFT JOIN TagitEmotLarm ON Larm.Nummer = TagitEmotLarm.Larm, Larmtyper, Larmsändare, Anläggningar WHERE Larm.Typ = Larmtyper.Nummer AND Larm.Från = Larmsändare.Nummer AND Larmsändare.Plats = Anläggningar.Nummer AND TagitEmotLarm.Larm IS NULL; -- f) (3p) Av vilken larmtyp har det kommit flest larm? CREATE VIEW AntalPerLarmtyp AS SELECT Larmtyper.Namn, COUNT(*) AS Antal FROM Larm, Larmtyper WHERE Larm.Typ = Larmtyper.Nummer GROUP BY Larmtyper.Namn; SELECT Namn FROM AntalPerLarmtyp WHERE Antal = (SELECT MAX(Antal) FROM AntalPerLarmtyp);