Vissa lösningsförslag till databastentan 2015-03-07 =================================================== Uppgift 2 (5 p) --------------- drop table Skador; drop table Deltagare; drop table Strider; drop table Robotar; drop table Robottyper; create table Robottyper (nummer integer not null primary key, namn varchar(18) not null unique); insert into Robottyper values (71, 'Strids-giraff 2000'); insert into Robottyper values (72, 'Attackrullare X-1'); insert into Robottyper values (73, 'Strids-giraff 2015'); create table Robotar (nummer integer not null primary key, typ integer not null references Robottyper(nummer), aktiverad date); insert into Robotar values (826, 71, DATE'2012-03-22'); insert into Robotar values (827, 72, DATE'2012-03-22'); insert into Robotar values (828, 72, DATE'2012-04-02'); create table Strider (nummer integer not null primary key, datum date not null); insert into Strider values (1, DATE'2015-03-01'); insert into Strider values (2, DATE'2015-03-01'); insert into Strider values (3, DATE'2015-03-02'); create table Deltagare (robot integer not null references Robotar(nummer), strid integer not null references Strider(nummer), resultat char(1) check (resultat in ('V', 'F')), -- V = Vinnare, F = Förlorare, null = inget primary key (robot, strid)); insert into Deltagare values (826, 1, 'V'); insert into Deltagare values (827, 1, 'F'); insert into Deltagare values (826, 2, 'V'); insert into Deltagare values (828, 2, null); create table Skador (nummer integer not null primary key, strid integer not null references Strider(nummer), kostnad integer not null, beskrivning char(20) not null); insert into Skador values (1, 1, 100000000, 'T-huset krossat'); insert into Skador values (2, 1, 100000000, 'Långhuset krossat'); insert into Skador values (3, 2, 500000000, 'Allt krossat'); Uppgift 3 (10 p) ---------------- a) Här är robot nummer 826. Vad heter robottypen? select Robottyper.namn from Robotar, Robottyper where Robotar.typ = Robottyper.nummer and Robotar.nummer = 826; b) Hur många robotar har vi? select count(*) from Robotar; c) Hur många robotar har vi som inte är förstörda? En robot är förstörd om den förlorat en strid. select count(*) from Robotar where nummer not in (select robot from Deltagare where resultat = 'F'); d) Det står i scenariot att en robot inte kan vara både vinnare och förlorare i samma strid. Kanske har vi ändå råkat lägga in en robot som vinnare och förlorare i samma strid? Skriv en SQL-fråga som visar vilka robotar och strider det i så fall är! (Alternativt: Om din databas från uppgift 2 gör att man inte kan göra detta fel, förklara varför det inte går!) En robot kan bara delta i varje strid en gång, eftersom robot+strid är nyckel i tabellen Deltagare. Där kan den ha antingen 'V' (vinnare) eller 'F' (förlorare) i kolumnen resultat, men inte båda samtidigt. e) Vilken dag inträffade den dyraste striden, dvs den strid som gav störst sammanlagd kostnad för skadorna den orsakade? create view Stridskostnader as select strid, sum(kostnad) as totalkostnad from Skador group by strid; select * from Stridskostnader; select datum from Strider, Stridskostnader where Strider.nummer = Stridskostnader.strid and Stridskostnader.totalkostnad = (select max(totalkostnad) from Stridskostnader); Uppgift 4 (3 p) --------------- Robotar.typ -- Ja, kanske Robottyper.nummer -- JA! Robotar.nummer -- JA! Deltagare.robot -- Ja, kanske Deltagare.resultat -- Ja, kanske Robottyper.namn -- NEJ! Uppgift 7 (3 p) --------------- Klient 1: select * from Strutsar; -- Fråga 1 NUMMER STATUS =========== =========== 1 10 <-- -1p om man missat den raden (totalt -1p med fråga 2) Klient 2: select * from Strutsar; -- Fråga 2 NUMMER STATUS =========== =========== 1 10 2 20 <-- -1p om man missat den raden (totalt -1p med fråga 1) Klient 1: select * from Strutsar; -- Fråga 3 NUMMER STATUS =========== =========== 1 10 2 20 Klient 1: select * from Strutsar; -- Fråga 4 NUMMER STATUS =========== =========== 1 10 2 20 4 40 <-- -1p om man missat den raden! Klient 2: select * from Strutsar; -- Fråga 5 NUMMER STATUS =========== =========== 1 10 2 20 <-- -1p för 4, 40 här Klient 2: select * from Strutsar; -- Fråga 6 NUMMER STATUS =========== =========== 1 10 2 20 4 40 Klient 1: select * from Strutsar; -- Fråga 7 NUMMER STATUS =========== =========== 1 10 2 20 4 40