Wie berechnet man zu jedem Produkt die durchschnittliche Bewertung? Welcher Kunde hat die meisten Produkte bestellt oder den größten Umsatz generiert? Um solche Fragen zu beantworten, lernen Sie in dieser Lektion alle Datensätze, die z. B. zum gleichen Produkt oder zum gleichen Kunden gehören, in einer Gruppe zusammenzufassen.
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
Die Zusammenfassung von Datensätzen zu einer Gruppe erfolgt mithilfe des Schlüsselworts GROUP BY gefolgt von einem Attribut. Die GROUP BY-Klausel wird zwischen der WHERE- und der ORDER BY-Klausel eingefügt: SELECT ... FROM ... WHERE ... GROUP BY Attribut ORDER BY ... LIMIT ...
Alle Datensätze, die für das in der GROUP BY-Klausel angegebene Attribut den gleichen Wert haben, werden zu einer Gruppe zusammengefasst. Für jede Gruppe wird ein Datensatz bzw. eine Zeile ausgegeben.
Eine Gruppierung von Datensätzen wird in der Regel in Kombination mit einer Aggregatfunktion in der SELECT-Klausel verwendet. Die Aggregatfunktion berechnet dann den entsprechenden Wert für jede Gruppe.
Das Attribut aus der GROUP BY-Klausel darf ebenfalls in der SELECT-Klausel angegeben werden. Andere Attribute dürfen nicht ausgegeben werden, da sie für die Datensätze einer Gruppe unterschiedliche Werte haben können.
I) Geben Sie eine SQL-Anweisung ein, welche die Datensätze der Tabelle Produkte nach dem Attribut Kategorie gruppiert. Lassen Sie für jede Gruppe die Kategorie und den Gesamtbestand an Produkten im Lager ausgeben.
Tipp
Sie benötigen eine SELECT- eine FROM- und eine GROUP BY-Klausel. Ergänzen Sie das Attribut Kategorie sowohl in der SELECT- als auch in der GROUP BY-Klausel.
Verwenden Sie die Aggregatfunktion SUM um die Summe der Werte des Attributs Lagerbestand für jede Gruppe zu berechnen.
mögliche Lösung
SELECT Kategorie, SUM(Lagerbestand) FROM Produkte GROUP BY Kategorie
Die GROUP BY-Klausel kann mit einer WHERE-Klausel und der Verknüpfung von Tabellen kombiniert werden. In diesem Fall werden zunächst die Tabellen verknüpft und alle Datensätze ausgewählt, welche die Bedingungen der WHERE-Klausel erfüllen.
Anschließend werden die ausgewählten Datensätze nach dem in der GROUP BY-Klausel angegebenen Attribut gruppiert.
II) Geben Sie eine SQL-Anweisung ein, welche die Tabellen Produkte und Bewertungen verknüpft und zu jedem Produkt den Namen und die durchschnittliche Bewertung ausgibt.
Tipp
Die Bedingung in der WHERE-Klausel muss prüfen, ob das Primärschlüsselattribut Produkt_ID der Tabelle Produkte den gleichen Wert hat wie das Fremdschlüsselattribut Produkt_ID der Tabelle Bewertungen.
Gruppieren Sie die Datensätze nach dem Attribut Name.
Verwenden Sie die Aggregatfunktion AVG, um für jede Gruppe den Durchschnitt der Werte des Attributs Bewertung zu berechnen.
mögliche Lösung
SELECT Name, AVG(Bewertung) FROM Produkte, Bewertungen WHERE Produkte.Produkt_ID = Bewertungen.Produkt_ID GROUP BY Name
In der WHERE-Klausel können weitere Bedingungen ergänzt werden, um vor der Gruppierung nur bestimmte Datensätze auszuwählen. III) Geben Sie einen SQL-Befehl ein, der für alle Bestellungen, die im Jahr 2023 aufgegeben wurden, die Anzahl der Bestellpositionen pro Bestellung ausgibt. Lassen Sie neben der Aggregatfunktion nur das Attribut Bestellung_ID ausgeben.Hinweis: Datumsangaben haben das Format JJJJ-MM-TT.
Tipp
Verknüpfen Sie die Tabellen Bestellungen und Bestellpositionen.
Prüfen Sie in der WHERE-Klausel mithilfe des Operators BETWEEN, ob eine Bestellung zwischen dem 01.01.2023 und dem 31.12.2023 aufgegeben wurde.
Gruppieren Sie die Datensätze nach dem Attribut Bestellung_ID und zählen Sie für jede Gruppe die Datensätze mit der Aggregatfunktion COUNT.
mögliche Lösung
SELECT Bestellungen.Bestellung_ID, COUNT(*) FROM Bestellungen, Bestellpositionen WHERE Bestellungen.Bestellung_ID = Bestellpositionen.Bestellung_ID AND Bestelldatum BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY Bestellungen.Bestellung_ID
Eine Gruppierung kann auch nach mehr als einem Attribut erfolgen. Alle Attribute, nach denen gruppiert werden soll, stehen durch Komma getrennt hinter dem Schlüsselwort GROUP BY.
Es wird dann zunächst nach dem ersten Attribut gruppiert. Innerhalb dieser Gruppen wird nach dem zweiten Attribut gruppiert usw. IV) Geben Sie einen SQL-Befehl ein, welcher die Datensätze der Tabelle Kunden nach Stadt und Anrede gruppiert und zu jeder Gruppe diese beiden Attribute sowie die Anzahl der Datensätze ausgibt.
Tipp
Sie benötigen eine SELECT-, eine FROM- und eine GROUP BY-Klausel.
Geben Sie die Attribute Stadt und Anrede in der SELECT- und in der GROUP BY-Klausel an.
Verwenden Sie die Aggregatfunktion COUNT, um die Datensätze pro Gruppe zu zählen.
mögliche Lösung
SELECT Stadt, Anrede, COUNT(*) FROM Kunden GROUP BY Stadt, Anrede
In der SELECT-Klausel dürfen nur Attribute ausgewählt werden, nach denen in der GROUP BY-Klausel gruppiert wird. Daher ist es manchmal notwendig, mehrere Attribute in der GROUP BY-Klausel anzugeben, auch wenn dadurch keine weiteren Untergruppen entstehen.
V) Geben Sie einen SQL-Befehl ein, der die Tabellen Kunden und Bewertungen verknüpft und für jeden Kunden bzw. jede Kundin die Attribute Vorname, Nachname und Email ausgibt sowie die niedrigste Bewertung, die er oder sie bislang für ein Produkt vergeben hat.
Tipp
Sie benötigen die Tabellen Kunden und Bewertungen. Formulieren Sie in der WHERE-Klausel eine Bedingung, um die Tabellen anhand der Schlüsselattribute passend zu verknüpfen.
Geben Sie in der GROUP BY-Klausel alle Attribute an, die in der SELECT-Klausel ausgewählt werden sollen.
Verwenden Sie die Aggregatfunktion MIN, um für jede Gruppe den kleinsten Wert des Attributs Bewertung zu bestimmen.
mögliche Lösung
SELECT Vorname, Nachname, Email, MIN(Bewertung) FROM Kunden, Bewertungen WHERE Kunden.Kunden_Nr = Bewertungen.Kunden_Nr GROUP BY Kunden.Kunden_Nr, Vorname, Nachname, Email
Verbinden Sie eine Gruppierung mit einer ORDER BY-Klausel, um die Datensätze der Gruppen sortiert auszugeben. Als Sortierkriterium darf hinter ORDER BY ein Attribut bzw. Alias aus der SELECT-Klausel oder eine Aggregatfunktion angegeben werden. VI) Geben Sie einen SQL-Befehl ein, der zu jedem Produkt die durchschnittliche Bewertung als 'Durchschnittsbewertung' ausgibt. Die Datensätze sollen absteigend nach der Durchschnittsbewertung sortiert werden.
Tipp
Sie benötigen die Tabellen Kunden und Bewertungen. In der WHERE-Klausel benötigen Sie eine Bedingung, um die Tabellen passend zu verknüpfen.
Gruppieren Sie die Datensätze nach dem Attribut Produkt_ID.
Verwenden Sie die Aggregatfunktion AVG, um für jede Gruppe die Durchschnittsbewertung zu berechnen und verwenden Sie das Schlüsselwort AS, um die Spalte entsprechend zu benennen.
Ordnen Sie die Datensätze nach der mit Durchschnittsbewertung benannten Spalte.
mögliche Lösung
SELECT Name, AVG(Bewertung) AS 'Durchschnittsbewertung' FROM Produkte, Bewertungen WHERE Produkte.Produkt_ID = Bewertungen.Produkt_ID GROUP BY Produkte.Produkt_ID, Name ORDER BY Durchschnittsbewertung DESC
Verwenden Sie eine Gruppierung in Kombination mit einer ORDER BY-Klausel und LIMIT, um beispielsweise nur die Gruppen mit den drei größten Werten oder die Gruppe mit dem niedrigsten Wert für ein Attribut oder eine Aggregatfunktion auszugeben. VII) Geben Sie einen SQL-Befehl ein, der die drei am schlechtesten bewerteten Produkte mit ihrer Durchschnittsbewertung ausgibt.
Tipp
Bauen Sie auf dem SQL-Befehl aus der vorherigen Aufgabe auf: Ändern Sie die Sortierung von absteigend in aufsteigend. Ergänzen Sie LIMIT 3, um nur die ersten drei Datensätze auszugeben.
mögliche Lösung
SELECT Name, AVG(Bewertung) AS 'Durchschnittsbewertung' FROM Produkte, Bewertungen WHERE Produkte.Produkt_ID = Bewertungen.Produkt_ID GROUP BY Produkte.Produkt_ID, Name ORDER BY Durchschnittsbewertung ASC LIMIT 3