Unsere Datenschutzrichtlinie wird in Kürze aktualisiert. Bitte sehen Sie sich die Vorschau an.

SQL Grundkurs 2: Aufgaben und Lösungen

Beispiel 25: Mehrere Tabellen mit Gruppierung

Testen Sie unsere 2021 Kurse

10 Tage kostenlos!

Jetzt testen Alle Abonnements anzeigen
Bei welchen Lieferanten haben wir im Januar 2011 mindestens 3.000 Euro Umsatz gemacht? Runden Sie den Umsatz auf ganze Hunderter und sortieren Sie das Ergebnis absteigend nach dem Umsatz.

Transkript

In diesem Film habe ich ein Beispiel für Sie vorbereitet, in dem wir alle wichtigen Bereiche der Data Query Language benötigen. Wir benötigen JOIN, wir benötigen Berechnungen, Wir benötigen Filtern, wir benötigen Gruppierung, und auch ein Filtern nach der Gruppierung und letztendlich eine Sortierung. Also alles was wichtig ist, kommt hier vor. Was ist die Aufgabenstellung? Wir wollen wissen, bei welchen Lieferanten wir im Januar 2011 mindestens 3.000 Euro Umsatz gemacht haben. Wenn wir die Angabe so hören, klingt die gar nicht so kompliziert, aber wir benötigen hier doch einige Dinge. Wobei, so wirklich kompliziert ist es ja nicht, wenn wir die Dinge strukturiert angehen, und in Einzelschritten lösen. Wir benötigen drei Tabellen für diese Aufgabenstellung. Die Tabelle "bestellungen", denn dort finden wir das Bestelldatum, damit wir auf den Januar 2011 filtern können. Wir benötigen die Tabelle "bestellpositionen", denn dort finden wir die Menge, den Preis und den Rabattprozentsatz. Mit dem können wir den Umsatz berechnen. Und natürlich die Tabelle "lieferanten", denn dort finden wir den Namen der Lieferanten. Was müssen Sie hier also tun? Die drei Tabellen joinen, dann auf den Monat "Januar 2011" filtern, dann den Umsatz berechnen und sortieren und nach Lieferanten gruppieren. Und beim erhaltenen Ergebnis, bei der Summe, auf die 3.000 Euro Umsatz filtern. Damit wir ein schönes Ergebnis bekommen, werden wir die zwei Spalten des Lieferanten und das sind die Spalten "firma1" und "firma2" ... werfen wir kurz einen Blick in die Struktur der Lieferantentabelle. Da finden wir hier die Spalten "firma1" und "firma2". Wobei, die "firma2" kann auch leer sein bei kurzen Namen. ... und diese zwei wollen wir hier ... auch das ist eine Berechnung neben der Umsatzberechnung ... hier zu einem Feld mit dem Aliasnamen "lieferant" zusammenfassen. Dem berechneten Umsatz geben Sie den Aliasnamen "umsatz". Und am Ende sortieren wir das Ganze absteigend nach dem Umsatz. Ein Tipp noch: Erldeigen Sie diese Aufgabenstellung in möglichst vielen Einzelschritten. Jeder Einzelschritt sollt e so klein sein, dass er kein Problem darstellt. Zuerst z. B. Joinen, dann einmal Filtern, dann einmal die Berechnungen machen, dann Gruppieren und Summe bilden, usw. Dann sollte es nicht allzu schwierig sein, Auch diese Aufgabenstellung zu meistern. Pausieren Sie nun das Video und lösen Sie die Aufgabe. Wenn Sie damit fertig sind, fahren Sie mit dem Video fort und sehen Sie sich meine Musterlösung an. Vergleichen wir nun die Lösungsschritte. Und ich starte mit dem Microsoft SQL Server. Wir müssen ja diesmal auch auf ein Datum filtern und es gibt hier verschiedene Varianten, wie wir ein Datum eingeben. Und ich habe jetzt hier beim Microsoft SQL Server einmal die Variante verwendet, das Datumsformat mit DATEFORMAT, mit Day-Month-Year, Tag-Monat-Jahr, auf Session-Ebene einzustellen. Dadurch kann ich das Datum hier in meiner WHERE-Klausel so wie wir es gewohnt sind, nämlich mit Tag-Monat-Jahr eingeben. Betrachten wir uns nun die einzelnen Elemente. Wir haben hier einmal eine FROM-Klausel, wo wir die Bestellungen mit den Bestellpositionen über die Bestellnummer und danach mit den Lieferanten über die Lieferantennummer joinen. Damit wir nur die Bestellungen für den Monat "Januar 2011" bekommen, benötigen wir diese WHERE-Klausel. Das Datum der Bestellung, muss also zwischen dem 01.01. und dem 31.01.2011 liegen. Dazu verwenden wir BETWEEN. Um die Firma zusammenzuhängen, zu einem Ausdruck anzuzeigen, verwende ich einerseits die Spalte "firma1" und "firma2". Beim Microsoft SQL Server verwenden wir für das Zusammenfügen von Texten für das Konkatenieren den "+"-Operator. Da ja die "firma2" leer sein könnte, verwenden wir hier die ISNULL-Funktion, um für den Fall, dass diese leer ist, diese durch einen Leer-String zu ersetzen, denn sonst würde der NULL-Wert bei der "firma2" die "firma1" mitschlucken und wir hätten keinen Firmennamen. Außerdem wollen wir zwischen den beiden ein Leerzeichen einfügen das auch verschwinden soll, wenn es keine "firma2" gibt. Deshalb habe ich diesen teilausdruck in die ISNULL-Funktion eingefügt. Zusätzlich habe ich dieser Spalte "AS lieferant" diesen Aliasnamen gegeben. Und das liefert uns, wenn ich das Ergebnis mal ausführe ... das ist auch so gefährlich, wenn man mit der Maus markiert. Sie sehen, ich habe jetzt hier das AS bei SELECT nicht mitmarkiert, und schon bekomme ich einen Fehler. Vielleicht ist es doch geschickter das mit der Tastatur zu machen, da passiert das nicht so leicht. ... Ich habe jetzt hier "Gardena AG", "Konrad KG" und die "MM Metal Goods AG". Und das sind jetzt die zwei zusammengefügten und "Konrad" und "Gardena" hätten beim "lieferanten2" gar nichts enthalten und ohne die ISNULL-Funktion wären diese leer. Das ist die erste Berechnung. Die zweite Berechnung betrifft den Umsatz. Die Grundberechnung ist diese und bezieht sich auf Spalten aus den "bestellpositionen". Die "bestellpositionen" haben bei mir den Aliasnamen "p". Also ich nehme hier die "p.menge" mal den Preis, und um den Rabattprozentsatz abzuziehen, mal "100 - p.rabatt / 100" Das liefert mir den Einzelgesamtpreis je Zeile. Um die Gesamtsumme zu bilden, umschließe ich sie mit der Summenfunktion. Dadurch, dass wir hier eine Summe gebildet haben, muss ich natürlich auch eine Gruppierung hier einbauen. Die GROUP BY-Klausel enthält die Spalten "firma1" und "firma2". Das ist eine Erleichterung, wenn ich in einem Ausdruck Spalten und statische Elemente habe, dann muss ich nur die dynamischen, d. h. die Spalten in die GROUP BY-Klausel einbauen, also "firma1" und "firma2". Zusätzlich möchte ich nicht nur die Summe bilden, sondern diese auf hundert Euro runden. Also verwende ich die ROUND-Funktion um die Summe und mit "-2" runde ich auf zwei Vorkommastellen. Damit mir der SQL Server das sauber formatiert, caste ich das Ergebnis wieder in den Datentyp money. Denn durch das Aufeinandertreffen unterschiedlicher numerischer Datentypen in einem Ausdruck beim SQL Server, käme hier sonst ein anderer Wert heraus. Mit "AS umsatz" vergeben wir den Aliasnamen. Jetzt sind wir ja schon beinahe fertig. Nun hätten wir allerdings auch andere Lieferanten, die weniger als 3.00 Euro Umsatz gemacht haben, im Ergebnis. Damit das nicht passiert, kopiere ich den ganzen Berechnungsausdruck ... das CAST kann ich hier weglassen, das ist nicht von Bedeutung ... in die HAVING-Klausel, und sage, das muss größer gleich "3000" sein. Damit filtern wir auf jene mit mehr oder mindestens 3.000 Euro Umsatz und am Ende sortieren wir noch, ORDER BY und hier können wir den Aliasnamen nehmen, "umsatz absteigend". Und das liefert uns letztendlich dieses Ergebnis. Ich möchte nun als Tipp hier noch zeigen, wie könnte ich jetzt hier den Umsatz z. B. schön formatieren mit Euro- und Tausender-Trennzeichen? Anstelle des CASTens, können Sie beim SQL Server ab der Version 2012 auch die FORMAT-Funktion verwenden. Der gebe ich jetzt einerseits meinen Berechnungsausdruck als Parameter, dann als zweiten Parameter "c" für "currency", weil es ein Währungsformat sein soll und dann Culture, "de-at" oder man kann natürlich auch "de-de" verwenden. Und damit wird auch das Währungssymbol "Euro" zusätzlich hier ergänzt. Und wir bekommen den Umsatzin dieser schönen Art und Weise dargestellt. Was ändert sich eigentlich in der Lösung, wenn wir eine anderes Datenbankmanagement- system verwenden, z. B. Oracle? Relativ wenig. Der gesamte Grundaufbau der Anweisung bleibt gleich. Nur dort, wo wir Berechnungsfunktionen einsetzen, gibt es Unterschiede. Zum Beispiel hier das Konkatenieren, das Zusammenfügen der zwei Spalten "firma1" und "firma2". Das erfolgt bei Oracle nicht mit einem normalen Plus-Zeichen, sondern mit einer doppelten Pipe. Nachdem die doppelte Pipe verhindert, dass ein Ausdruck NULL wird, wenn ein Teil NULL ist, passiert es hier nicht, dass ein NULL-Wert in der "firma2" die "firma1" mitschluckt. Das würde aber bedeuten, dass am Ende jetzt hier noch ein Leerzeichen dazukäme. Das sieht man zwar am Ende nicht so gut es würde gar nicht so stören. Aber mit der TRIM-Funktion glätten entferne ich ein überschüssiges Leerzeichen, falls es keine "firma2" gibt. Und beim Datum verwende ich jetzt hier z. B. die Funktion TO_DATE, um diesen Text formatiert in ein Datum umzuwandeln. Alle anderen Elemente der Lösung sind ident. Und etwas anderes ist natürlich auch die Formatierung des Datums. Ich habe jetzt z. B. hier diese Anweisung verwendet, um das Territorium auf "germany" zu setzen, oder ich kann auch Folgendes hier machen, ich wandle das Ganze in einen Text um und verwende dieses Format, und muss halt hier nur das Euro-Symbol hinten noch dazuhängen, denn das ist im Format hier nicht enthalten. Und ich starte diese Anweisung, um auch hier schön formatiert den Umsatz zu bekommen. Und zum Abschluss noch zu MySQL gewechselt. Auch hier dieselbe Anweisung, lediglich die Berechnung ist unterschiedlich. Zum Zusammenfügen der zwei Firmenteile, "firma1" und "firma2" verwenden wir hier die Funktion CONCAT. Und im ersten Teil mit IFNULL, weill ich das gleiche Problem hätte wie beim Microsoft SQL Server, dass wenn die "firma2" NULL ist, auch die "firma1" beim Konkatenieren weggeschluckt werden würde, ersetze ich diesen Teilausdruck, wenn er NULL ist, nämlich das Leerzeichen plus die "firma2". durch einen Leer-String. und das liefert mir auch hier in der gewohnten Form den Lieferanten. Das Datum habe ich hier im ISO-Format eingegeben, damit das unter MySQL immer korrekt ausgelesen wird und so bekomme ich dieses Ergebnis. Und auch hier kann ich natürlich den Umsatz schön formatieren. Das funktioniert mit der FORMAT-Funktion. Die FORMAT-Funktion bietet mir die Möglichkeit, zu definieren, wie viele Nachkommastellen möchte ich haben? Zwei zum Beispiel. Hätte ich jetzt natürlich weglassen können, wenn ich sage, ich runde schon auf hunderter, aber für die Optik ist es sauber. Und den Parameter Culture. Damit wird festgelegt, dass das Tausender-Trennzeichen hier ein Punkt und das Komma-Trennzeichen ein Komma ist. Das Eurozeichen muss ich auch manuell hinten dazuhängen, deshalb hier außen noch die Funktion CONCAT, um hier damit diesen Ausdruck noch mit dem Eurozeichen zu kombinieren. Ansonsten ist hier alles gleich, wie wir es sonst kennen. Und ich führe diese Anweisung aus und wir bekommen hier dasselbe Ergebnis, wie auch bei den anderen Systemen. Wir haben also in diesem Beispiel gesehen, dass wir sehr gut viele Dinge kombinieren können. Wichtig ist, dass wir das alles in Teilschritten machen. Dass wir z. B. zuerst einmal joinen, dann filtern, dann Berechnungen durchführen, dann gruppieren und Gruppenfunktionen, z. B. SUMME ergänzen, danach noch auf Gruppierung ergänzen und erst am Ende sortieren. Und wenn Sie so Schritt für Schritt an eine Aufgabenstellung herangehen, dann können Sie auch solche umfangreichen Aufgabenstellungen relativ gut lösen.

SQL Grundkurs 2: Aufgaben und Lösungen

Vertiefen Sie Ihre SQL-Kenntnisse. In diesem Workshop erhalten Sie zahlreiche Beispielaufgaben, die Sie selbst lösen können. Anschließend zeigt Ihnen der Trainer die Lösung.

7 Std. 41 min (61 Videos)
Derzeit sind keine Feedbacks vorhanden...
 

Dieses Training setzt SQL-Kenntnisse voraus, wie sie beispielsweise in „SQL lernen und anwenden“ vermittelt werden.

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!