Wie erstellt man Empfehlungen der Art "Kunden kauften auch ... "? Wie findet man alle Produkte, die ein Kunde bereits bestellt hat? Um solche Fragen zu beantworten, lernen Sie in dieser Lektion, wie Datensätze aus verschiedenen Tabellen miteinander verknüpft werden.
Kurzbeschreibung der Datenbank Onlineshop
Die Datenbank enthält eine Tabelle Kunden und eine Tabelle Produkte mit Daten zu den Kundinnen und Kunden eines fiktiven Onlineshops bzw. zu den angebotenen Produkten.
In zwei weiteren Tabellen Bestellungen und Bestellpositionen werden die Daten zu den Bestellungen der Kundinnen und Kunden verwaltet. Die Tabelle Bestellungen enthält Daten, die die gesamte Bestellung betreffen, während die Tabelle Bestellpositionen einen Datensatz für jedes bestellte Produkt mit der entsprech Menge enthält und dieses einer Bestellung zuordnet.
Kundinnen und Kunden können Produkte auf einer Skala von 1 bis 5 bewerten und einen Kommentar dazu erstellen. Die entsprechenden Daten speichert die Tabelle Bewertungen.
Die Daten, die in der Datenbank gespeichert sind, stellen eine exemplarische, aber fiktive Auswahl typischer Daten dar, die in einer Datenbank eines Onlineshops zu finden sind.
Eine Übersicht über die Attribute der einzelnen Tabellen sowie die Beziehungen der Tabellen untereinander zeigt das realtionale Datenbankschema.
Lernaufgaben zur Datenbank Onlineshop
Hinter dem Schlüsselwort FROM können mehrere Tabellen durch Komma getrennt angegeben werden, um die Datensätze aus verschiedenen Tabellen miteinander zu kombinieren. I) Stellen Sie eine Vermutung auf, welche Information die folgende SQL-Anweisung liefert: SELECT * FROM Kunden, Bestellungen Geben Sie die SQL-Anweisung ein und vergleichen Sie das Ergebnis mit Ihrer Erwartung.
Tipp
Kopieren Sie die SQL-Anweisung in das Eingabefenster. Können Sie erkennen, welcher Kunde welche Bestellung aufgegeben hat?
Das Attribut Kunden_Nr wird zweimal ausgegeben: einmal als Attribut der Tabelle Kunden und einem als Attribut der Tabelle Bestellungen. Vergleichen Sie die Werte.
mögliche Lösung
SELECT * FROM Kunden, Bestellungen Die Ergebnistabelle kombiniert jeden Kunden mit jeder Bestellung.
Die Angabe von zwei Tabellen hinter dem Schlüsselwort FROM führt dazu, dass das Kreuzprodukt der beiden Tabellen gebildet wird. Das heißt, jeder Datensatz der ersten Tabelle wird mit jedem Datensatz der zweiten Tabelle zu einem neuen Datensatz kombiniert.
Eine sinnvollere Verknüpfung der Datensätze erfolgt, wenn Sie in der WHERE-Klausel Bedingungen für die sogenannten Schlüsselattribute ergänzen. Im Datenbankschema sind in jeder Tabelle ein oder mehrere Attribute unterstrichen. Das sind die Primärschlüsselattribute, die einen Datensatz in der Tabelle eindeutig identifizieren. Andere Tabellen, die ergänzende Daten zu diesen Datensätzen enthalten, verfügen ebenfalls über diese Attribute. Sie sind hier mit einem vorangestellten Pfeil als Fremdschlüsselattribute gekennzeichnet. Datensätze, die sich aufeinander beziehen, haben für die Primärschlüsselattribute und die entsprechenden Fremdschlüsselattribute denselben Wert.
II) Erweitern Sie die SQL-Anweisung SELECT * FROM Kunden, Bestellungen um eine WHERE-Klausel mit einer Bedingung, die dafür sorgt, dass jedem Kunden die zugehörigen Bestellungen zugeordnet werden.Hinweis: Um Attribute, die in mehreren Tabellen gleich heißen, eindeutig zu bennenen, wird dem Attribut der Name der Tabelle vorangestellt und mit einem Punkt abgerenzt. Beispiel: Kunden.Kunden_Nr
Tipp
Die Bedingung in der WHERE-Klausel muss prüfen, ob das Primärschlüsselattribut Kunden_Nr der Tabelle Kunden den gleichen Wert hat wie das Fremdschlüsselattribut Kunden_Nr der Tabelle Bestellungen.
mögliche Lösung
SELECT * FROM Kunden, Bestellungen WHERE Kunden.Kunden_Nr = Bestellungen.Kunden_Nr
In der WHERE-Klausel können weitere Bedingungen ergänzt werden, um aus den verknüpften Tabellen nur bestimmte Datensätze auszuwählen. III) Geben Sie einen SQL-Befehl ein, der alle Bestellungen des Kunden Florian Becker ausgibt. Lassen Sie nur die Attribute Vorname, Nachname, Kunden_Nr, Bestellung_ID und Bestelldatum ausgeben
Tipp
Verknüpfen Sie in der WHERE-Klausel mehrere Bedingungen mit AND. Eine Bedingung für das Attribut Vorname, eine für das Attribut Nachname und eine für den Vergleich von Primärschlüssel- und Fremdschlüsselattribut.
mögliche Lösung
SELECT Vorname, Nachname, Kunden.Kunden_Nr, Bestellung_ID, Bestelldatum FROM Kunden, Bestellungen WHERE Kunden.Kunden_Nr = Bestellungen.Kunden_Nr AND Vorname = 'Florian' AND Nachname = 'Becker'
Um den Tabellennamen nicht vor jedem uneindeutigen Attribut ausschreiben zu müssen, kann in der FROM-Klausel hinter dem Tabellennamen eine Abkürzung angegeben werden, z. B. K für Kunden und B für Bestellungen IV) Geben Sie einen SQL-Befehl ein, der alle Bestellungen, die im Dezember 2022 aufgegeben wurden, mit Bestellung_ID, Bestelldatum und zugehöriger Email-Adresse des Kunden bzw. der Kundin ausgibt. Verwenden Sie dabei Abkürzungen für die Tabellen.Hinweis: Datumsangaben haben das Format JJJJ-MM-TT.
Tipp
Ergänzen Sie in der FROM-Klausel K bzw. B hinter den Tabellen und verwenden Sie die Buchstaben statt der Tabellennamen in der SELECT- und in der WHERE-Klausel.
Formulieren Sie in der WHERE-Klausel eine zusätzliche Bedingung für das Attribut Bestelldatum. Das Bestelldatum muss zwischen dem 01.12.2022 und dem 31.12.2022 liegen.
mögliche Lösung
SELECT Bestellung_ID, Bestelldatum, Email FROM Kunden K, Bestellungen B WHERE K.Kunden_Nr = B.Kunden_Nr AND Bestelldatum BETWEEN '2022-12-01' AND '2022-12-31'
Es lassen sich auch Datensätze aus mehr als zwei Tabellen miteinander verbinden. Dabei müssen entsprechende Bedingungen für die Schlüsselattibute formuliert werden, die die Tabellen paarweise verknüpfen.
V) Geben Sie einen SQL-Befehl ein, der die Email-Adressen aller Kundinnen und Kunden ausgibt, die das Produkt 'Bürostuhl Ergonomic' bewertet haben. Ausgegeben werden sollen außerdem die Bewertung, der Kommentar und der Name des Produktes.
Tipp
Sie benötigen die Tabellen Kunden, Bewertungen und Produkte. Formulieren Sie in der WHERE-Klausel zwei Bedingungen, um die Tabellen anhand der Schlüsselattribute passend zu verknüpfen.
Um die Datensätze auf Bewertungen des Produktes 'Bürostuhl Ergonomic' einzugrenzen, ist eine weitere Bedingung erforderlich.
mögliche Lösung
SELECT Email, Name, Bewertung, Kommentar FROM Kunden K, Bewertungen B, Produkte P WHERE K.Kunden_Nr = B.Kunden_Nr AND B.Produkt_ID = P.Produkt_ID AND Name = 'Bürostuhl Ergonomic'
Manchmal werden für die Verknüpfung von Datensätzen auch Tabellen benötigt, aus denen gar keine Daten ausgegeben werden sollen. VI) Geben Sie einen SQL-Befehl ein, der für den Kunden Florian Becker alle Produkte ausgibt, die er schon einmal bestellt hat. Angezeigt werden sollen Vorname und Nachame des Kunden sowie der Name des Produktes und das Bestelldatum.
Tipp
Sie benötigen die Tabellen Kunden, Bestellungen, Bestellpositionen und Produkte. In der WHERE-Klausel benötigen Sie drei Bedingungen, um die Tabellen passend zu verknüpfen sowie zwei Bedingungen um die Ausgabe auf die Daten des Kunden Florian Becker zu begrenzen.
mögliche Lösung
SELECT Vorname, Nachname, Bestelldatum, Name FROM Kunden K, Bestellungen B, Bestellpositionen S, Produkte P WHERE K.Kunden_Nr = B.Kunden_Nr AND B.Bestellung_ID = S.Bestellung_ID AND S.Produkt_ID = P.Produkt_ID AND Vorname = 'Florian' AND Nachname = 'Becker'
Die Umbenennung der Tabellen in der FROM-Klausel ermöglicht es, einen Datensatz mit einem anderen Datensatz der gleichen Tabelle zu verküpfen.
Beispielsweise ordnet die folgende SQL-Anweisung der Kundin Anna Neumann alle anderen Kunden zu, die in der gleichen Stadt wohnen. SELECT K1.Vorname, K1.Nachname, K1.Stadt, K2.Stadt, K2.Vorname, K2.Nachname FROM Kunden K1, Kunden K2 WHERE K1.Stadt = K2.Stadt AND K1.Kunden_Nr != K2.Kunden_Nr AND K1.Vorname = 'Anna' AND K1.Nachname = 'Neumann'
Sie können sich die Tabellen K1 und K2 jeweils als eine Kopie der Tabelle Kunden vorstellen. Beide Tabellen enthalten die gleichen Datensätze.
Die Bedingung K1.Stadt = K2.Stadt sorgt dafür, dass die Tabellen K1 und K2 so verknüpft werden, dass jedem Datensatz aus der Tabelle K1 die Datensätze aus der Tabelle K2 zugeordnet werden, die für das Attribut Stadt den gleichen Wert haben.
Die Bedingung K1.Kunden_Nr != K2.Kunden_Nr verhindert, das dem Datensatz eines Kunden aus der Tablelle K1 sein eigener Datensatz aus der Tabelle K2 zugeordnet wird.
VII) Geben Sie einen SQL-Befehl ein, der alle Produkte ausgibt, die zusammen mit dem Produkt 'Whiteboard 120x90cm' bestellt wurden. Ordnen Sie dazu dem Produkt 'Whiteboard 120x90cm' andere Produkte aus der gleichen Bestellung zu.
Tipp
Sie benötigen die Tabellen Produkte und Bestellpositionen jeweils zweimal. Benennen Sie diese z. B. mit P1 und P2 bzw. B1 und B2.
Formulieren Sie in der WHERE-Klausel Bedingungen, sodass dem Datensatz mit dem Wert 'Whiteboard 120x90cm' für das Attribut Name in P1 alle passenden Datensätze (Bestellungen) aus B1 zugeordnet werden.
Verknüpfen Sie B1 und B2 so, dass das Attribut Bestellung_ID in beiden Tabellen den gleichen Wert hat, aber das Attribut Produkt_ID unterschiedliche Werte hat. Überlegen Sie, warum das wichtig ist.
Verküpfen Sie die Datensätze aus B2 schließlich passend mit den Datensätzen aus P2, um die Namen der Produkte zu erhalten, die zusammen mit dem Produkt 'Whiteboard 120x90cm' bestellt wurden.
mögliche Lösung
SELECT P1.Name, B1.Bestellung_ID, B2.Bestellung_ID, P2.Name FROM Produkte P1, Bestellpositionen B1, Bestellpositionen B2, Produkte P2 WHERE P1.Produkt_ID = B1.Produkt_ID AND B1.Bestellung_ID = B2.Bestellung_ID AND B1.Produkt_ID != B2.Produkt_ID AND B2.Produkt_ID = P2.Produkt_ID AND P1.Name = 'Whiteboard 120x90cm'