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