Databasteknik: Teoriövning 7, fortsättning på SQL

Välj själv om du vill göra övningen ensam eller i någon typ av grupp. Det gör inget om en del av frågorna skulle vara svåra att svara på, för de är kanske avsedda att inleda en diskussion snarare än att ha ett enda rätt svar.

Förberedelser

Innan denna övning bör du ha tittat på föreläsning nummer 8, om fortsättning på SQL, på YouTube (uppdelad i två Youtube-videor). Man kan också läsa i kursboken eller motsvarande.

Scenario till uppgifterna

Forskningsgruppen AASS vid Örebro universitet forskar om robotar. De påstår att robotarna ska användas i gruvor och på äldreboenden, men egentligen är det förstås onda stridsrobotar man bygger:

Onda robotar

Nu behöver de en databas för att hålla reda på robotarna. Här är ett ER-diagram för databasen:

Ett ER-diagram

Tabeller med exempeldata:

Robotar
Nummer Färg Vikt
1 Blå 40
2 Blå 40
3 Svart 1000
4 Stålgrå 800
Deltagare
Robot Strid Resultat
1 1 Oavgjort
2 1 Oavgjort
2 2 Vinst
3 2 Förlust
1 3 Oavgjort
2 3 Oavgjort
3 3 Oavgjort
Strider
Nummer Datum
1 2022-11-23
2 2022-11-23
3 2022-11-24

SQL-kommandon för att underlätta för provkörningar: 

DROP TABLE Robotar CASCADE;
DROP TABLE Strider CASCADE;
DROP TABLE Deltagare CASCADE;

CREATE TABLE Robotar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Färg VARCHAR(10),
Vikt INTEGER);

INSERT INTO Robotar (Nummer, Färg, Vikt) VALUES (1, 'Blå', 40);
INSERT INTO Robotar (Nummer, Färg, Vikt) VALUES (2, 'Blå', 40);
INSERT INTO Robotar (Nummer, Färg, Vikt) VALUES (3, 'Svart', 1000);
INSERT INTO Robotar (Nummer, Färg, Vikt) VALUES (4, 'Stålgrå', 800);

CREATE TABLE Strider
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE);

INSERT INTO Strider (Nummer, Datum) VALUES (1, DATE '2022-11-23');
INSERT INTO Strider (Nummer, Datum) VALUES (2, DATE '2022-11-23');
INSERT INTO Strider (Nummer, Datum) VALUES (3, DATE '2022-11-24');

create table Deltagare
(Robot INTEGER NOT NULL REFERENCES Robotar(Nummer),
Strid INTEGER NOT NULL REFERENCES Strider(Nummer),
Resultat VARCHAR(8) CHECK (Resultat IN ('Vinst', 'Förlust', 'Oavgjort')),
PRIMARY KEY (Robot, Strid));

INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (1, 1, 'Oavgjort');
INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (2, 1, 'Oavgjort');
INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (2, 2, 'Vinst');
INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (3, 2, 'Förlust');
INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (1, 3, 'Oavgjort');
INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (2, 3, 'Oavgjort');
INSERT INTO Deltagare (Robot, Strid, Resultat) VALUES (3, 3, 'Oavgjort');

SELECT * FROM Robotar ORDER BY Nummer;
SELECT * FROM Strider ORDER BY Nummer;
SELECT * FROM Deltagare ORDER BY Strid, Robot;

Uppgift 1

Skriv som SQL-frågor:

a) Hur många robotar har vi?
b) Hur många blåa robotar har vi?
c) Vad är det högsta numret på en robot?
d) Vilken färg har robot 3?
e) Vilken färg har roboten med det högsta numret? Tips: Man kan stoppa in frågan från c-uppgiften i where-villkoret i frågan från d-uppgiften!

Uppgift 2

Skriv med SQL:

a) Hur många robotar finns det av varje färg? Tips: group by
b) Skapa en vy av frågan ovan!
c) Använd vyn för att ta reda på vilken färg som är vanligast!

Uppgift 3

Om man behöver använda data från flera tabeller behöver man ofta para ihop de rader från två tabeller som hör ihop. En sådan sammanslagning av tabeller kallas "join".

a) Vad är det för skillnad på en explicit join och en implict join?
b) Måste man alltid ange ett join-villkor?
c) Ett annat sätt att slå samman två tabeller är operationen union. Hur skiljer den sig från en join?

Uppgift 4

Ta fram ett resultat som består av varje robots nummer, tillsammans med de datum den deltagit i en strid, och resultatet av striden. Med våra exempeldata bör resultatet bli:

Robot Datum Resultat
1 2022-11-23 Oavgjort
2 2022-11-23 Oavgjort
2 2022-11-23 Vinst
3 2022-11-23 Förlust
1 2022-11-24 Oavgjort
2 2022-11-24 Oavgjort
3 2022-11-24 Oavgjort

Skriv SQL-frågan både med implicit och explicit join!

Uppgift 5

Diskutera: Vart tog robot nummer 4 vägen i sökningen ovan?

Uppgift 6

Skriv som SQL: Hur många strider har varje robot varit med om? Robotar som inte har varit med om några strider alls behöver inte vara med i resultatet. Med våra exempeldata bör resultatet bli:

Robot Antal
1 2
2 3
3 2

Tips: Vilka tabeller behöver man hämta data ur för att få det sökta svaret?

Uppgift 7

Vad är skillnaden på en inre (eller "vanlig") join och en yttre join?

Vad är skillnaden på en vänster-yttre join, en höger-yttre join och en full yttre join?

Uppgift 8

Samma uppgift som i uppgift 6 ovan, men nu ska även de robotar som inte har några strider alls vara med i resultatet, med antalet noll.

Robot Antal
1 2
2 3
3 2
4 0

Samma tips igen: Vilka tabeller behöver man använda för denna sökning?

Förslag på lösningar

Det finns lösningsförslag till en del av uppgifterna, men försök lösa dem själv först.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 22 november 2022