Welche Produkte wurden durchschnittlich mit mindestens 4 bewertet? Welche Kunden haben bereits mehr als 3 Bestellungen aufgegeben? Um solche Fragen zu beantworten, lernen Sie in dieser Lektion, wie Sie nach einer Gruppierung nur die Gruppen auswählen können, die bestimmte Bedingungen erfüllen.
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
Sollen nach der Gruppierung nur Gruppen ausgewählt werden, die bestimmte Bedingungen erfüllen, so benötigen Sie dafür eine HAVING-Klausel. Diese wird nach der GROUP BY-Klausel ergänzt: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING Bedingung ORDER BY ... LIMIT ...
Eine Bedingung in der HAVING-Klausel bezieht sich auf eine Eigenschaft der Gruppe und damit in der Regel auf das Ergebnis einer Aggregatfunktion. I) Geben Sie eine SQL-Anweisung ein, welche die Produkte nach dem Attribut Kategorie gruppiert und für jede Gruppe den Preis des teuersten Produktes bestimmt. Lassen Sie nur die Kategorien ausgeben, bei denen das teuerste Produkt mehr als 500 (€) kostet.
Tipp
Sie benötigen eine SELECT-, eine FROM-, eine GROUP BY- und eine HAVING-Klausel.
Ergänzen Sie das Attribut Kategorie sowohl in der SELECT- als auch in der GROUP BY-Klausel.
Verwenden Sie die Aggregatfunktion MAX, um das Maximum des Attributs Preis für jede Gruppe zu bestimmen.
Geben Sie in der HAVING-Klausel eine Bedingung an, die ebenfalls die Aggregatfunktion verwendet, um zu überprüfen, ob das Ergebnis größer 500 ist.
mögliche Lösung
SELECT Kategorie, MAX(Preis) FROM Produkte GROUP BY Kategorie HAVING MAX(Preis) > 500
Eine Auswahl bestimmter Gruppen kann auch für eine Gruppierung der Datensätze verknüpfter Tabellen erfolgen.
II) Geben Sie eine SQL-Anweisung ein, die zu jedem Produkt die durchschnittliche Bewertung bestimmt. Lassen Sie nur die Produkte mit Namen und Durchschnittsbewertung ausgeben, bei denen die Durchschnittsbewertung besser als 4 ist.
Tipp
Verknüpfen Sie die Tabellen Produkte und Bewertungen mithilfe einer geeigneten Bedingung in der WHERE-Klausel.
Gruppieren Sie die Datensätze nach den Attributen Produkt_ID und Name.
Verwenden Sie zur Berechnung der Durchschnittsbewertung die Aggregatfunktion AVG.
Formulieren Sie in der HAVING-Klausel eine Bedingung, die nur solche Gruppen auswählt, bei denen die Durchschnittsbewertung größer als 4 ist.
Wählen Sie in der SELECT-Klausel das Attribut Name aus und ergänzen Sie die Aggregatfunktion zur Berechnung der Durchschnittsbewertung.
mögliche Lösung
SELECT Name, AVG(Bewertung) FROM Produkte, Bewertungen WHERE Produkte.Produkt_ID = Bewertungen.Produkt_ID GROUP BY Produkte.Produkt_ID, Name HAVING AVG(Bewertung) > 4
Mit WHERE und HAVING stehen Ihnen nun zwei Klauseln zur Verfügung, in denen Bedingungen formuliert werden können. Als Entscheidungshilfe, ob eine Bedinung der WHERE- oder der HAVING-Klausel zugeordnet werden muss, ist folgende Unterscheidung wichtig:
Die Bedingungen in der WHERE-Klausel beziehen sich auf einzelne Datensätze. Die Auswahl der Datensätze gemäß der Bedingungen in der WHERE-Klausel erfolgt vor der Gruppierung.
Die Bedinungen in der HAVING-Klausel beziehen sich hingegen auf Gruppen. Die Auswahl der Gruppen gemäß der Bedingungen in der HAVING-Klausel erfolgt daher nach der Gruppierung. III) Geben Sie eine SQL-Anweisung ein, die nur für die Städte, in denen es mehr als 3 Kundinnen gibt, die Anzahl der Kundinnen ausgibt. Wählen Sie dazu die Datensätze mit der Anrede 'Frau' aus, um diese nach der Stadt zu gruppieren.
Tipp
Die Auswahl der Datensätze mit dem Attributwert 'Frau' für das Attribut Anrede aus der Tabelle Kunde erfolgt mithilfe der WHERE-Klausel.
Die Auswahl der Städte, in denen es mehr als 3 Kundinnen gibt, erfolgt mithilfe der HAVING-Klausel.
Mit der Aggregatfunktion COUNT in Kombination mit einer Gruppierung nach dem Attribut Stadt kann die Anzahl der Kundinnen je Stadt bestimmt werden.
mögliche Lösung
SELECT Stadt, COUNT(*) FROM Kunden WHERE Anrede = 'Frau' GROUP BY Stadt HAVING COUNT(*) > 3
Überlegen Sie auch für Aufgabe 4 zunächst, welche Bedingungen sich auf einen Datensatz und welche auf eine Gruppe beziehen. IV) Geben Sie eine SQL-Anweisung ein, die alle Kunden ausgibt, die männlich sind, in Hannover wohnen und schon mindestens drei Bestellungen aufgegeben haben.
Tipp
Die Eigenschaften 'männlich' und 'wohnhaft in Hannover' beziehen sich auf den Datensatz eines Kunden in der Tabelle Kunde. Es müssen entsprechende Bedingungen für die Attribute Anrede und Stadt in der WHERE-Klausel formuliert werden.
Um die Anzahl der Bestellungen zu bestimmen, müssen die Tabellen Kunden und Bestellungen verknüpft und anschließend nach der Kunden_Nr gruppiert werden.
Mit der Aggregatfunktion COUNT kann die Anzahl der Bestellungen bestimmt und eine entsprechende Gruppenbedingung in der HAVING-Klausel formuliert werden.
mögliche Lösung
SELECT Vorname, Nachname, COUNT(Bestellung_ID) FROM Kunden, Bestellungen WHERE Kunden.Kunden_Nr = Bestellungen.Kunden_Nr AND Anrede = 'Herr' AND Stadt = 'Hannover' GROUP BY Kunden.Kunden_Nr, Vorname, Nachname HAVING COUNT(Bestellung_ID) >= 3
Mehrere Bedingungen können in der HAVING-Klausel mit AND bzw. OR verknüpft werden.
V) Geben Sie eine SQL-Anweisung ein, die alle Städte ausgibt, in denen es sowohl besonders junge als auch schon ältere Kunden gibt. Das heißt, das Geburtsdatum des ältesten Kunden bzw. der ältesten Kundin soll vor dem 01.01.1970 liegen und das des jüngsten Kunden bzw. der jüngsten Kundin nach dem 01.01.2005.
Hinweis: Datumsangaben haben das Format JJJJ-MM-TT.
Tipp
Gruppieren Sie die Datensätze der Tabelle Kunden nach dem Attribut Stadt.
Verwenden Sie die Aggregatfunktion MIN bzw. MAX, um für jede Gruppe das früheste bzw. das späteste Geburtsdatum zu bestimmen und in der HAVING-Klausel geeignete Bedingungen zu formulieren.
mögliche Lösung
SELECT Stadt, MIN(Geburtsdatum), MAX(Geburtsdatum) FROM Kunden GROUP BY Stadt HAVING MIN(Geburtsdatum) < '1970-01-01' AND MAX(Geburtsdatum) > '2005-01-01'
Verwenden Sie eine ORDER BY-Klausel, um die ausgewählten Gruppen nach einer Eigenschaft der Gruppe sortiert auszugeben. VI) Geben Sie eine SQL-Anweisung ein, die für jede Kategorie von Produkten die bereits verkaufte Anzahl an Produkten ausgibt. Berücksichtigen Sie dabei nur Bestellungen mit dem Status 'abgeschlossen' und Kategorien, in denen bereits mehr als 50 Produkte bestellt wurden. Geben Sie die Kategorien absteigend sortiert nach der Anzahl der verkauften Produkte aus.
Tipp
Sie benötigen die Tabellen Bestellungen, Bestellpositionen und Produkte. In der WHERE-Klausel benötigen Sie entsprechende Bedingungen, um die Tabellen passend zu verknüpfen.
Gruppieren Sie die Datensätze nach dem Attribut Kategorie
Verwenden Sie die Aggregatfunktion SUM, um für jede Gruppe die Werte des Attributs Menge zu addieren.
Formulieren Sie in der HAVING-Klausel eine Bedingung, die für jede Gruppe prüft, ob die Summe der Mengen größer 50 ist.
Ordnen Sie die Datensätze absteigend nach der Summe der Mengen.
mögliche Lösung
SELECT Kategorie, SUM(Menge) FROM Bestellungen, Bestellpositionen, Produkte WHERE Bestellungen.Bestellung_ID = Bestellpositionen.Bestellung_ID AND Bestellpositionen.Produkt_ID = Produkte.Produkt_ID AND Status = 'abgeschlossen' GROUP BY Kategorie HAVING SUM(Menge) > 50 ORDER BY SUM(Menge) DESC