Aufgabe 6.2: SQL als DML

Einführung in Data and Knowledge
Engineering
6. Übung: SQL
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 1
SQL „must know“ keywords
SELECT Spalte1, AGGREGAT-FUNKTION(Spalte2) AS AliasName
FROM Tabelle Alias
WHERE <eine Bedingung>
GROUP BY Spalte1
HAVING <Bedingung mit AGGREGAT-FUNKTION(Spalte2)>
ORDER BY <Spalte> DESC
Gute Quelle für SQL (leider nicht immer Standard konform):
http://www.w3schools.com/sql/
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 2
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
Buch
ISBN
PK
Titel
Preis
x
FK
CREATE TABLE Buch (
ISBN
CHAR(10),
Titel
VARCHAR(50),
Preis
DECIMAL(10, 2),
PRIMARY KEY (ISBN)
);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 3
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
Autor
ID
PK
Vorname
Nachname
x
FK
CREATE TABLE Autor (
ID
INT,
Vorname
VARCHAR(30),
Nachname
VARCHAR(30),
PRIMARY KEY (ID)
);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 4
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
BuchAutor
ISBN
ID
PK
x
x
FK
Buch.ISBN
Autor.ID
CREATE TABLE BuchAutor (
ISBN
CHAR(10),
ID
INT,
PRIMARY KEY (ISBN, ID),
FOREIGN KEY (ISBN) REFERENCES Buch (ISBN),
FOREIGN KEY (ID) REFERENCES Autor (ID)
);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 5
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
Bibliothek
Name
PK
Ort
Telefon
x
FK
CREATE TABLE Bibliothek (
Name
VARCHAR(30),
Ort
VARCHAR(30),
Telefon
VARCHAR(20),
PRIMARY KEY (Name)
);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 6
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
Exemplar
PK
FK
ExemplarNr
ISBN
x
x
Buch.ISBN
Bibliothek
Leser
Bibliothek.Name
Leser.ID
Tabelle Leser muss zuerst erstellt werden, um über
den Fremdschlüssel referenziert zu werden.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 7
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
Leser
ID
PK
Vorname
Nachname
x
FK
CREATE TABLE Leser (
ID
INT,
Vorname
VARCHAR(30),
Nachname
VARCHAR(30),
PRIMARY KEY (ID)
);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 8
Aufgabe 6.1: SQL als DDL
Formulieren Sie das Datenbankschema in SQL.
Exemplar
PK
ExemplarNr
ISBN
x
x
FK
Buch.ISBN
Bibliothek
Leser
Bibliothek.Name
Leser.ID
CREATE TABLE Exemplar (
ExemplarNr
INT,
ISBN
CHAR(10),
Bibliothek
VARCHAR(30),
Leser
INT,
PRIMARY KEY (Exemplarnr, ISBN),
FOREIGN KEY (ISBN) REFERENCES Buch(ISBN),
FOREIGN KEY (Bibliothek) REFERENCES Bibliothek(Name),
FOREIGN KEY (Leser) REFERENCES Leser(ID)
);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 9
Aufgabe 6.1: SQL als DDL
Löschen Sie das Attribut Ort aus der Tabelle
Bibliothek und fügen Sie es anschließend wieder
ein.
ALTER TABLE Bibliothek DROP COLUMN Ort;
ALTER TABLE Bibliothek ADD Ort VARCHAR(30);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 10
Aufgabe 6.1: SQL als DDL
Welches Problem tritt auf, wenn Sie versuchen die Tabelle Autor zu löschen?
Wie können sie das Problem beheben?
Normalerweise würde man die Tabelle wie folgt löschen:
DROP TABLE Autor;
Dies funktioniert aber nicht, da ein Primärschlüsselattribut dieser Relation durch
einen Fremdschlüssel in BuchAutor referenziert wird.
Folgende Anfrage dagegen hat Erfolg:
DROP TABLE Autor CASCADE;
Dabei ist aber zu beachten, dass durch obigen Befehl nicht das Attribut ID aus
BuchAutor oder gar die ganze Tabelle BuchAutor gelöscht wird, sondern
BuchAutor.ID einfach kein Fremdschlüssel mehr ist, sondern ein normales Attribut.
Es wird also nur das Constraint FOREIGN KEY auf BuchAutor.ID gelöscht.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 11
DROP SCHEMA IF EXISTS uebung6 CASCADE; -- delete old data
CREATE SCHEMA uebung6; -- new schema
SET search_path TO uebung6; -- set our schema as default
CREATE TABLE Buch ( ISBN CHAR(10), Titel VARCHAR(50), Preis DECIMAL(10, 2), PRIMARY KEY (ISBN));
CREATE TABLE Autor ( ID INT, Vorname VARCHAR(30), Nachname VARCHAR(30), PRIMARY KEY (ID));
CREATE TABLE BuchAutor ( ISBN CHAR(10), ID INT,
PRIMARY KEY (ISBN, ID),
FOREIGN KEY (ISBN) REFERENCES Buch (ISBN),
FOREIGN KEY (ID) REFERENCES Autor (ID));
CREATE TABLE Bibliothek ( Name VARCHAR(30), Ort VARCHAR(30), Telefon VARCHAR(20), PRIMARY KEY (Name));
CREATE TABLE Leser ( ID INT, Vorname VARCHAR(30), Nachname VARCHAR(30), PRIMARY KEY (ID));
CREATE TABLE Exemplar ( ExemplarNr INT, ISBN CHAR(10), Bibliothek VARCHAR(30), Leser INT,
PRIMARY KEY (Exemplarnr, ISBN),
FOREIGN KEY (ISBN) REFERENCES Buch(ISBN),
FOREIGN KEY (Bibliothek) REFERENCES Bibliothek(Name),
FOREIGN KEY (Leser) REFERENCES Leser(ID));
ALTER TABLE Bibliothek DROP COLUMN Ort;
ALTER TABLE Bibliothek ADD Ort VARCHAR(30);
Aufgabe 6.2: SQL als DML
BuchAutor
ISBN
PK
FK
Bibliothek
Name
PK
X
FK
ID
X
Buch.ISBN
X
Autor.ID
Buch
PK
FK
Ort Telefon
Autor
ISBN
X
Titel
Exemplar
ExemplarNr
PK
X
FK
Preis
ID Vorname
X
PK
FK
Nachname
Leser
ISBN
X
Buch.ISBN
Bibliothek
Leser
Bibliothek.Name Leser.ID
PK
FK
ID
X
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 13
Vorname
Nachname
Aufgabe 6.2: SQL als DML
Geben Sie die Vor- und Nachnamen aller Leser aus.
SELECT Vorname, Nachname
FROM Leser;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 14
Aufgabe 6.2: SQL als DML
Finden Sie den Titel des Buchs mit der ISBN 0201542633.
SELECT Titel
FROM Buch
WHERE ISBN='0201542633';
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 15
Aufgabe 6.2: SQL als DML
Geben Sie von allen Autoren die Nachnamen aus,
die mit S anfangen.
SELECT Nachname
FROM Autor
WHERE Nachname LIKE 'S%';
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 16
Aufgabe 6.2: SQL als DML
Finden Sie die Vor- und Nachnamen der Autoren
des Buchtitels Objektdatenbanken.
SELECT Vorname, Nachname
FROM Autor A, BuchAutor BA, Buch B
WHERE A.ID=BA.ID
AND BA.ISBN=B.ISBN
AND B.Titel='Objektdatenbanken';
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 17
Aufgabe 6.2: SQL als DML
Geben Sie die ExemplarNr und die ISBN aller Exemplare aus und,
falls das Exemplar ausgeliehen ist, den Nachnamen des Lesers,
der es ausgeliehen hat.
SELECT E.Exemplarnr, E.ISBN, L.Nachname
FROM Exemplar E LEFT OUTER JOIN Leser L
ON E.Leser = L.ID;
Hinweis: Der LEFT OUTER JOIN bewirkt, das alle Tupel der linken
Seite des Joins (also alle Exemplare) in die Ergebnismenge
gelangen, auch wenn sich kein passendes Tupel auf der rechten
Seite findet (also in diesem Fall kein Leser, der das Exemplar
ausgeliehen hat).
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 18
Aufgabe 6.2: SQL als DML
Finden Sie die IDs aller Leser, die aus der Informatik-Bibliothek Exemplare
geliehen haben, jedoch nicht aus der Mathematik-Bibliothek.
SELECT DISTINCT Leser FROM Exemplar
WHERE Bibliothek = 'Informatik'
AND NOT Leser IN
(SELECT Leser FROM Exemplar
WHERE Bibliothek='Mathematik' AND Leser IS NOT NULL);
Leser
1
NULL
3
Hinweis: Die Klausel "Leser IS NOT NULL" ist notwendig, damit in der
verschachtelten Anfrage keine NULL-Werte auftauchen. Beispielsweise liefert die
Frage, ob der Leser mit der ID "2" in der Tabelle NICHT enthalten ist, ein
negatives Ergebnis. Das ist offensichtlich falsch, da der Leser "2" tatsächlich nicht
darin vorkommt. Der in der Tabelle enthaltene NULL-Wert verfälscht das Ergebnis
aber, so dass man ihn vorher eliminieren muss. Dieses Beispiel zeigt, dass NULLWerte schwer auffindbare Fehler produzieren können und daher vermieden werden
sollten.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 19
Aufgabe 6.2: SQL als DML
Was kostet das billigste Buch?
SELECT MIN(Preis)
FROM Buch;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 20
Aufgabe 6.2: SQL als DML
Wie lautet der Titel des teuersten Buchs und was
kostet es?
SELECT Titel, Preis
FROM Buch
WHERE Preis = ( SELECT MAX(Preis)
FROM Buch);
Wie viele Zeilen liefert das das Ergebniss?
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 21
Aufgabe 6.2: SQL als DML
Welchen Wert hat der gesamte Exemplarbestand?
SELECT SUM(Preis)
FROM Buch B, Exemplar E
WHERE B.ISBN=E.ISBN;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 22
Aufgabe 6.2: SQL als DML
Was kostet ein Exemplar der Informatikbibliothek
im Durchschnitt?
SELECT AVG(Preis)
FROM Buch B, Exemplar E
WHERE B.ISBN=E.ISBN
AND E.Bibliothek='Informatik';
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 23
Aufgabe 6.2: SQL als DML
Formulieren Sie die folgende Anfrage ohne die
Verwendung von Aggregationsfunktionen:
Wie lautet der Titel des teuersten Buchs und was
kostet es?
SELECT Titel, Preis
FROM Buch
WHERE Preis >= ALL
(SELECT Preis
FROM Buch
WHERE Preis IS NOT NULL);
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 24
Aufgabe 6.2: SQL als DML
Wie viele Exemplare umfasst der Gesamtbestand
der Bibliotheken?
SELECT COUNT(*)
FROM Exemplar;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 25
Aufgabe 6.2: SQL als DML
Wie viele Autoren sind in der Informatikbibliothek
vertreten?
SELECT COUNT(DISTINCT BA.ID)
FROM BuchAutor BA, Exemplar E
WHERE BA.ISBN = E.ISBN
AND E.Bibliothek = 'Informatik';
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 26
Aufgabe 6.2: SQL als DML
Erstellen Sie eine Liste aller Leser, die Bücher aus der
Informatikbibliothek entliehen haben, sortiert nach Nachnamen.
SELECT DISTINCT L.Nachname, L.Vorname
FROM Leser L, Exemplar E
WHERE E.Leser=L.ID
AND E.Bibliothek ='Informatik'
ORDER BY L.Nachname;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 27
Aufgabe 6.2: SQL als DML
Geben Sie für jeden Leser, der wenigstens ein Exemplar ausgeliehen hat,
die ID und die Anzahl aller ausgeliehenen Bücher an.
SELECT L.ID, COUNT(E.ISBN)
SELECT Leser, COUNT(ISBN)
FROM Exemplar E, Leser L
FROM Exemplar
WHERE E.Leser = L.ID
WHERE Leser IS NOT NULL
GROUP BY L.ID;
GROUP BY Leser;
HINWEIS: L.ID darf im SELECT-Statement auftauchen, da nach diesem
Attribut gruppiert wird. L.Nachname darf hier beispielsweise nicht
verwendet werden, womit es deutlich schwieriger wird, wenn man sich
statt der ID den Nachnamen anzeigen lassen möchte.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 28
Aufgabe 6.2: SQL als DML
Geben Sie für jeden Leser, der wenigstens ein Exemplar ausgeliehen hat,
den Nachnamen und die Anzahl aller ausgeliehenen Bücher an.
Verwenden Sie dazu eine verschachtelte Anfrage im FROM-Statement
SELECT L.Nachname, Anzahl
FROM Leser L,
(SELECT L.ID AS ID, COUNT(E.ISBN) AS Anzahl
FROM Exemplar E, Leser L
WHERE E.Leser = L.ID
GROUP BY L.ID) Temp
WHERE L.ID = Temp.ID;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 29
Aufgabe 6.2: SQL als DML
Geben Sie für jeden Leser, der wenigstens zwei Exemplare
ausgeliehen hat, die ID und die Anzahl aller ausgeliehenen
Bücher an.
SELECT L.ID, COUNT(E.ISBN) as Anzahl
FROM Exemplar E, Leser L
WHERE E.Leser = L.ID
GROUP BY L.ID
HAVING COUNT(E.ISBN) > 1;
Alias kann nicht im HAVING verwendet werden!
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 30
Aufgabe 6.2: SQL als DML
Die Buchpreise erhöhen sich um 2 Prozent. Ändern
Sie die Relation Buch entsprechend.
UPDATE Buch SET Preis=Preis*1.02;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 31
Aufgabe 6.2: SQL als DML
Herr Meier hat alle Bücher, die er ausgeliehen hat,
zurückgegeben.
Ändern sie die Datenbank entsprechend.
UPDATE Exemplar
SET Leser=NULL
WHERE Leser IN
(SELECT ID
FROM Leser
WHERE Nachname='Meier');
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 32
Aufgabe 6.2: SQL als DML
Frau Schmidt hat ihren Leseausweis zurückgegeben.
Löschen Sie sie aus der Datenbank.
DELETE FROM Leser
WHERE Nachname='Schmidt';
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 33
Anmerkung zu Joins
Für einfache Joins gibt es zwei Arten diese zu schreiben:
SELECT *
SELECT *
FROM Exemplar E, Leser L
FROM Exemplar E
WHERE E.Leser = L.ID;
JOIN Leser L
ON E.Leser = L.ID;
Diese sind äquivalent und liefern das gleiche Ergebnis.
Ohne die Join Bedingung (E.Leser = L.ID) produziert man ein CROSS JOIN
Folgendes ist äquivalent:
SELECT * FROM Exemplar E, Leser L
SELECT * FROM Exemplar E CROSS JOIN Leser L
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 34
Joins – Mehrere Tabellen
Joint man mehr als zwei Tabellen funktionieren weiterhin beide Möglichkeiten
SELECT *
SELECT *
FROM TableA
FROM TableA, TableB, TableC, TableD
JOIN TableB
ON TableB.aID = TableA.aID
JOIN TableC
WHERE TableB.aID = TableA.aID
AND TableC.cID = TableB.cID
AND TableD.dID = TableA.dID
ON TableC.cID = TableB.cID
JOIN TableD
ON TableD.dID = TableA.dID;
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 35
Weitere Joins
Will man aber Outer Joins funktioniert nur noch die explizite Schreibweise.
(Sowohl mit 2 als auch mehr Tabellen. Hier ein Beispiel mit drei Tabellen und
verschiedenen outer Joins)
SELECT *
FROM TableA
RIGHT OUTER JOIN TableB
ON TableB.aID = TableA.aID
LEFT OUTER JOIN TableC
ON TableC.cID = TableB.cID
Noch ausführlichere Erklärungen zu Joins:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 36
Aufgabe 6.3: SQL und die Musikschule
a) Vornamen aller Schüler:
𝜋𝑉𝑜𝑟𝑛𝑎𝑚𝑒 (Schüler)
SELECT Vorname FROM Schüler
b) Inv# aller Noten, die ausgeliehen sind:
𝜋𝐼𝑛𝑣# 𝜎𝐴𝑢𝑠𝑙𝑒𝑖ℎ𝑒𝑟≠′−′ Noten
SELECT Inv# FROM Noten WHERE Ausleiher <> '-‚
Viele DBMS erlauben es auch != für Ungleich zu verwenden. Dies
entspricht aber NICHT dem Standard; dieser sieht nur <> vor.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 37
Aufgabe 6.3: SQL und die Musikschule
c) Eine Zuordnung der Schüler-Ids zu den Inv# der Noten, die sie
ausgeliehen haben
𝜋𝐼𝑑,𝐼𝑛𝑣# Schüler ⋈𝐼𝑑=𝐴𝑢𝑠𝑙𝑒𝑖ℎ𝑒𝑟 Noten
SELECT Id,Inv# FROM Schüler
JOIN Noten ON Id=Ausleiher
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 38
Aufgabe 6.3: SQL und die Musikschule
d) Name und Kosten der Instrumente, die von Schülern gelernt werden, welche
nach dem 1.1.1995 geboren wurden.
𝜋
𝑁𝑎𝑚𝑒,𝐾𝑜𝑠𝑡𝑒𝑛
𝜎𝐺𝑒𝑏𝑢𝑟𝑡𝑠𝑑𝑎𝑡𝑢𝑚>1.1.1995 Schüler ⋈𝐼𝑑=𝑆𝑐ℎü𝑙𝑒𝑟 Unterricht ⋈𝑁𝑎𝑚𝑒=𝐼𝑛𝑠𝑡𝑟𝑢𝑚𝑒𝑛𝑡 Instrument
SELECT Name, Kosten FROM Schüler, Unterricht, Instrument
WHERE Id=Schüler AND Name=Instrument AND Geburtsdatum > 1.1.1995
Hinweis: Der > Operator für Daten ist natürlich nicht SQL Standard. Er wurde
hier nur der Einfachheit wegen verwendet.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 39
Aufgabe 6.4: Transaktionen
Stellen Sie sich vor, Alice möchte Bob 100€ überweisen.
Überlegen Sie sich zunächst, aus welchen Teilschritten dieser
Vorgang besteht.
Was ist nun das Problem, wenn man diese Teilschritte nicht zu
einer Transaktion zusammenfasst, bzw. was wird vom DBMS
gewährleistet, wenn man alle Teilschritte in einer Transaktion
ausführt?
Hinweis: Überlegen Sie sich, was die ACID Kriterien in diesem
Zusammenhang bedeuten.
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 40
Aufgabe 6.4: Transaktionen
Kunde
…
Kontostand
Alice
…
5300
Bob
…
6200
Einzelschritte:
 Alice Kontostand um 100 verringern
 Bobs Kontostand um 100 erhöhen
Problem:
2. Aktion wird nicht ausgeführt (z.B. wegen Absturz, ConstraintVerletzung, etc.)
Atomicity
Consistency
Isolation
Durability
-
Die Transaktion wird ganz, oder gar nicht ausgeführt
Von konsistentem Zustand, in konsistenten Zustand
Die Transaktion hat die Datenbank für sich
Die Änderungen der Transaktion sind von Dauer
05.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Robert Rehner | 41