20th Sep 2023 23 Leseminuten Fortgeschrittene SQL-Übung: 10 SQL-Übungsaufgaben mit Lösungen Martyna Sławińska SQL SQL-Übung Da SQL-Kenntnisse bei Datenexperten und Entwicklern gleichermaßen gefragt sind, kann die Bedeutung praktischer Übungen nicht genug betont werden. Lesen Sie weiter, um in die Welt des fortgeschrittenen SQL einzutauchen und Ihre Fähigkeiten durch praktische Übungen zu verbessern. In diesem Artikel finden Sie eine Sammlung von zehn anspruchsvollen SQL-Übungen, die sich speziell an diejenigen richten, die ihre fortgeschrittenen SQL-Kenntnisse verbessern möchten. Die Übungen decken eine Auswahl von SQL-Konzepten ab und helfen Ihnen, Ihre fortgeschrittenen SQL-Kenntnisse aufzufrischen. Zu jeder Übung gibt es eine detaillierte Lösung, mit der Sie Ihr Wissen testen und ein tieferes Verständnis für komplexe SQL-Konzepte erlangen können. Die Übungen stammen aus unseren SQL-Übungskursen für Fortgeschrittene. Wenn Sie mehr Übungen wie diese sehen möchten, schauen Sie sich diese Kurse an: Fensterfunktionen Übungssatz 2021 Monatlich SQL-Übungssatzs - Fortgeschrittene 2022 Monatlich SQL-Übungssatzs - Fortgeschrittene Fangen wir an. Üben Sie Ihren Weg zur SQL-Kenntnis Übung ist ein wesentlicher Bestandteil der Beherrschung von SQL; ihre Bedeutung kann nicht hoch genug eingeschätzt werden. Der Weg zur Beherrschung von SQL für Fortgeschrittene erfordert Hingabe, Ausdauer und ein starkes Engagement für kontinuierliches Üben. Durch regelmäßiges Üben von SQL für Fortgeschrittene können Sie Ihre Fähigkeiten schärfen, Ihr Wissen erweitern und ein tiefes Verständnis für die Feinheiten der Datenverwaltung und -manipulation entwickeln. Fortgeschrittenes SQL Die Übungen dienen als unschätzbare Hilfsmittel, die die Lernenden herausfordern, ihr theoretisches Wissen in praktischen Szenarien anzuwenden und ihr Verständnis komplexer Konzepte weiter zu festigen. Mit jeder SQL-Übungseinheit können Sie effiziente Techniken entdecken und das nötige Selbstvertrauen gewinnen, um reale Datenherausforderungen zu meistern. Schauen wir uns die Übungen und ihre Lösungen an. Fortgeschrittenes SQL Praxisübungen Wir stellen Ihnen verschiedene fortgeschrittene SQL-Übungen vor, die Fensterfunktionen, JOINs, GROUP BY, gemeinsame Tabellenausdrücke (CTEs) und mehr abdecken. Abschnitt 1: Fortgeschrittenes SQL JOIN-Übungen In den folgenden fortgeschrittenen SQL-Übungen verwenden wir eine Sportbekleidungsdatenbank, die Informationen über Kleidung, Kleidungskategorien, Farben, Kunden und Bestellungen speichert. Sie enthält fünf Tabellen: color, customer, category, clothing, und clothing_order. Schauen wir uns die Daten in dieser Datenbank an. Die Tabelle color enthält die folgenden Spalten: idspeichert die eindeutige ID für jede Farbe. name speichert den Namen der Farbe. extra_fee speichert den Aufpreis (falls vorhanden), der für die in dieser Farbe bestellte Kleidung berechnet wird. In der Tabelle customer finden Sie die folgenden Spalten: id speichert die Kunden-IDs. first_name speichert den Vornamen des Kunden. last_name speichert den Nachnamen des Kunden. favorite_color_idspeichert die ID der Lieblingsfarbe des Kunden (verweist auf die Farbtabelle). Die Tabelle category enthält diese Spalten: id speichert die eindeutige ID für jede Kategorie. name speichert den Namen der Kategorie. parent_id speichert die ID der Hauptkategorie für diese Kategorie (wenn es sich um eine Unterkategorie handelt). Wenn dieser Wert NULL ist, bedeutet dies, dass diese Kategorie eine Hauptkategorie ist. Hinweis: Die Werte beziehen sich auf die Werte in der Spalte id in dieser Tabelle. Die Tabelle clothing speichert Daten in den folgenden Spalten: id speichert die eindeutige ID für jeden Artikel. name speichert den Namen des Artikels. size speichert die Größe des Kleidungsstücks: S, M, L, XL, 2XL oder 3XL. price speichert den Preis des Artikels. color_id speichert den Artikel color (verweist auf die Farbtabelle). category_id speichert die Kategorie des Artikels (verweist auf die Kategorietabelle). Die Tabelle clothing_order enthält die folgenden Spalten: id speichert die eindeutige Bestell-ID. customer_id speichert die ID des Kunden, der die Kleidung bestellt hat (verweist auf die Tabelle customer ). clothing_id speichert die ID des bestellten Artikels (verweist auf die Tabelle clothing ). items speichert, wie viele Kleidungsstücke der Kunde bestellt hat. order_date speichert das Datum der Bestellung. Es folgen nun einige fortgeschrittene SQL-Übungen, die sich auf JOINs konzentrieren. Übung 1: Auflisten aller Kleidungsstücke Übung: Zeigen Sie den Namen der Kleidungsstücke (benennen Sie die Spalte clothes), ihre Farbe (benennen Sie die Spalte color) und den Nachnamen und den Vornamen des/der Kunden an, der/die dieses Kleidungsstück in seiner/ihrer Lieblingsfarbe gekauft hat/haben. Sortieren Sie die Zeilen nach der Farbe in aufsteigender Reihenfolge. Lösung: SELECT cl.name AS clothes, col.name AS color, cus.last_name, cus.first_name FROM clothing_order co JOIN clothing cl ON cl.id = co.clothing_id JOIN color col ON col.id = cl.color_id JOIN customer c ON cus.id = co.customer_id WHERE cus.favorite_color_id = cl.color_id ORDER BY col.name; Erklärung der Lösung: Wir möchten die Spaltenwerte aus drei verschiedenen Tabellen (clothing, color und customer) anzeigen, einschließlich der Informationen darüber, welcher Kunde einen bestimmten Artikel bestellt hat (aus der Tabelle clothing_order ). Daher müssen wir diese vier Tabellen über ihre gemeinsamen Spalten verbinden. Zunächst wählen wir aus der Tabelle clothing_order (alias co) aus und verbinden sie mit der Tabelle clothing (alias cl). Wir verbinden die Tabellen über die Primärschlüsselspalte der Tabelle clothing (id) und die Fremdschlüsselspalte der Tabelle clothing_order (clothing_id); diese Fremdschlüsselspalte verbindet die Tabellen clothing und clothing_order. Als nächstes verbinden wir die Tabelle color (alias col) mit der Tabelle clothing (alias cl). Hier verwenden wir die Primärschlüsselspalte der Tabelle color (id) und die Fremdschlüsselspalte der Tabelle clothing (color_id). Schließlich verbinden wir die Tabelle customer (alias cus) mit der Tabelle clothing_order (alias co). Der Fremdschlüssel der Tabelle clothing_order (customer_id) verweist auf den Primärschlüssel der Tabelle customer (id). Die ON Klausel speichert die Bedingung für die JOIN Anweisung. Beispielsweise wird ein Artikel aus der Tabelle clothing mit einem id von 23 mit einer Bestellung aus der Tabelle clothing_order verknüpft, bei der der Wert clothing_id gleich 23 ist. In diesem Artikel finden Sie weitere Beispiele für das JOINing von drei (oder mehr) Tabellen. Und hier erfahren Sie, wie Sie mehrere Tabellen LEFT JOINen können. Übung 2: Alle nicht kaufenden Kunden ermitteln Übung: Wählen Sie den Nachnamen und den Vornamen von Kunden und den Namen ihrer Lieblingsfarbe für Kunden, die keine Einkäufe tätigen. Lösung: SELECT cus.last_name, cus.first_name, col.name FROM customer cus JOIN color col ON col.id = cus.favorite_color_id LEFT JOIN clothing_order o ON o.customer_id = cus.id WHERE o.customer_id IS NULL; Erklärung der Lösung: Hier müssen wir den Vor- und Nachnamen der Kunden aus der Tabelle customer und ihren Lieblingsnamen color aus der Farbtabelle anzeigen. Wir müssen dies nur für Kunden tun, die noch keine Bestellungen aufgegeben haben; daher benötigen wir Informationen aus der Tabelle clothing_order. Der nächste Schritt besteht also darin, diese drei Tabellen zu verbinden. Zuerst verbinden wir die Tabelle customer (alias cus) mit der Tabelle color (alias col). Dazu verwenden wir die folgende Bedingung: Die Primärschlüsselspalte der Tabelle color (id) muss gleich der Fremdschlüsselspalte der Tabelle customer (favorite_color_id) sein. Dadurch können wir den Namen der Lieblingsfarbe anstelle ihrer ID auswählen. So stellen wir sicher, dass wir nur Kunden auflisten, die noch keine Bestellung aufgegeben haben: Wir LEFT JOIN die Tabelle clothing_order (alias o) mit der Tabelle customer (alias cus), um sicherzustellen, dass alle Zeilen der Tabelle customer (auch die, die nicht übereinstimmen) aufgelistet werden. In der WHERE -Klausel definieren wir eine Bedingung, um nur die Zeilen anzuzeigen, bei denen die Spalte customer_id der Tabelle clothing_order gleich NULL ist (d. h. es werden nur die Kunden zurückgegeben, deren IDs nicht in der Tabelle clothing_order enthalten sind). Es gibt verschiedene Arten von JOINs, einschließlich INNER JOIN, LEFT JOIN, RIGHT JOIN und FULL JOIN. Sie können mehr erfahren, wenn Sie den verlinkten Artikeln folgen. Übung 3: Alle Hauptkategorien und ihre Unterkategorien auswählen Übung: Wählen Sie den Namen der Hauptkategorien (die in der Spalte parent_id einen NULL-Wert haben) und den Namen ihrer direkten Unterkategorie (falls eine existiert). Benennen Sie die erste Spalte Kategorie und die zweite Spalte Unterkategorie. Lösung: SELECT c1.name AS category, c2.name AS subcategory FROM category c1 JOIN category c2 ON c2.parent_id = c1.id WHERE c1.parent_id IS NULL; Erklärung der Lösung: Jede in der Tabelle category aufgeführte Kategorie hat ihre eigene ID (gespeichert in der Spalte id ); einige haben auch die ID ihrer übergeordneten Kategorie (gespeichert in der Spalte parent_id ). Wir können also die Tabelle category mit sich selbst verknüpfen, um die Hauptkategorien und ihre Unterkategorien aufzulisten. Die Art von JOIN, bei der wir eine Tabelle mit sich selbst verknüpfen, wird umgangssprachlich als Self-Join bezeichnet. Wenn Sie eine Tabelle mit sich selbst verknüpfen, müssen Sie jeder Kopie der Tabelle einen anderen Aliasnamen geben. Hier haben wir eine Tabelle category mit dem Alias c1 und eine andere Tabelle category mit dem Alias c2. Wir wählen die Tabelle name aus der Tabelle category (mit dem Alias c1) aus und stellen sicher, dass wir nur die Hauptkategorien auflisten, indem wir die Spalte parent_id gleich NULL in der Klausel WHERE setzen. Anschließend verbinden wir die Tabelle category (alias c1) mit der Tabelle category (alias c2). Die letztgenannte Tabelle enthält Unterkategorien für die Hauptkategorien. In der Klausel ON legen wir daher fest, dass die Spalte parent_id von c2 gleich der Spalte id von c1 sein muss. Lesen Sie diesen Artikel, um mehr über Self-Joins zu erfahren. Die Übungen in diesem Abschnitt wurden unserem Kurs 2021 Monthly SQL Practice Sets - Advanced entnommen. Jeden Monat veröffentlichen wir einen neuen SQL-Praxiskurs in unserem Monthly SQL-Praxis Track; in jedem ungeraden Monat ist der Kurs auf einem fortgeschrittenen Niveau. Die fortgeschrittenen SQL-Praxiskurse aus dem Jahr 2021 haben wir in unserem Kurs 2021 Monthly SQL Practice Sets - Advanced zusammengefasst. Hier finden Sie weitere JOIN-Übungen und andere fortgeschrittene SQL-Herausforderungen. Abschnitt 2: Fortgeschrittene GROUP BY-Übungen In den folgenden fortgeschrittenen SQL-Übungen verwenden wir eine Sportvereinsdatenbank, die Informationen über Läufer und Laufveranstaltungen speichert. Sie enthält drei Tabellen: runner, event, und runner_event. Schauen wir uns die Daten in dieser Datenbank an. Die Tabelle runner enthält die folgenden Spalten: id speichert die eindeutige ID des Läufers. name speichert den Namen des Läufers. main_distance speichert die Distanz (in Metern), die der Läufer bei Wettkämpfen läuft. age speichert das Alter des Läufers. is_female gibt an, ob der Läufer männlich oder weiblich ist. Die Tabelle event enthält die folgenden Spalten: id speichert die eindeutige ID des Ereignisses. name speichert den Namen des Ereignisses (z.B. London Marathon, Warschauer Läufe oder Neujahrslauf). start_date speichert das Datum der Veranstaltung. city speichert die Stadt, in der die Veranstaltung stattfindet. Die Tabelle runner_event enthält die folgenden Spalten: runner_id speichert die ID des Läufers. event_id speichert die ID des Ereignisses. Im Folgenden werden einige fortgeschrittene SQL-Übungen durchgeführt, die sich auf GROUP BY konzentrieren. Übung 4: Organisieren von Läufern in Gruppen Übung: Wählen Sie die Hauptdistanz und die Anzahl der Läufer aus, die die gegebene Distanz gelaufen sind (runners_number). Zeigen Sie nur die Zeilen an, in denen die Anzahl der Läufer größer als 3 ist. Lösung: SELECT main_distance, COUNT(*) AS runners_number FROM runner GROUP BY main_distance HAVING COUNT(*) > 3; Erklärung der Lösung: Wir wollen die Anzahl der Läufer für jede gelaufene Distanz ermitteln. Dazu müssen wir alle Läufer nach der Distanz gruppieren und die Aggregatfunktion COUNT() verwenden, um zu berechnen, wie viele Läufer in jeder Distanzgruppe sind. Wir wählen die Spalte main_distance aus und GROUP BY diese Spalte. Wenn wir nun die COUNT() Aggregatfunktionen verwenden, erhalten wir die Anzahl der Läufer, die jedem main_distance Wert entsprechen. Die GROUP BY Klausel wird verwendet, um Zeilen aus einer Tabelle anhand einer oder mehrerer Spalten zu gruppieren. Sie unterteilt die Ergebnismenge in Teilmengen oder Gruppen, wobei jede Gruppe die gleichen Werte in der/den angegebenen Spalte(n) aufweist. Auf diese Weise lassen sich Aggregatfunktionen (wie SUM(), COUNT(), AVG(), usw.) für jede Gruppe getrennt durchführen. Hier sind die häufigsten GROUP BY-Fragen. Um nur die Gruppen mit mehr als drei Läufern anzuzeigen, verwenden wir eine HAVING Klausel, die die Werte filtert, die von der COUNT() Aggregatfunktion zurückgegeben werden. Die HAVING -Klausel wird oft zusammen mit der GROUP BY -Klausel verwendet, um die gruppierten Daten nach bestimmten Bedingungen zu filtern. Sie funktioniert ähnlich wie die WHERE -Klausel, aber sie wirkt auf die gruppierten Daten und nicht auf einzelne Zeilen. Lesen Sie diesen Artikel, um mehr über die HAVING-Klausel zu erfahren. Übung 5: Wie viele Läufer nehmen an jeder Veranstaltung teil? Übung: Zeigen Sie den Namen der Veranstaltung und die Anzahl der Vereinsmitglieder an, die an dieser Veranstaltung teilnehmen (nennen Sie diese Spalte runner_count). Beachten Sie, dass es Veranstaltungen geben kann, an denen keine Vereinsmitglieder teilnehmen. Für diese Veranstaltungen sollte die runner_count gleich 0 sein. Lösung: SELECT event.name, COUNT(runner.id) AS runner_count FROM event LEFT JOIN runner_event ON runner_event.event_id = event.id LEFT JOIN runner ON runner_event.runner_id = runner.id GROUP BY event.name; Erklärung der Lösung: Hier wollen wir den Namen der Veranstaltung aus der Tabelle event und die Anzahl der Teilnehmer aus der Tabelle runner anzeigen. Die Tabellen event und runner sind durch eine Many-to-many-Beziehung verbunden; um diese Tabellen zu verbinden, benötigen wir auch die Tabelle runner_event, die Veranstaltungen und Teilnehmer miteinander verknüpft. Zunächst wählen wir aus der Tabelle event aus. Dann verbinden wir LEFT JOIN mit der Tabelle runner_event, die wiederum LEFT JOINed mit der Tabelle runner verbindet. Warum verwenden wir hier die LEFT JOIN? Weil wir sicherstellen wollen, dass alle Veranstaltungen (auch die ohne Teilnehmer) angezeigt werden. Wir wählen den Namen der Veranstaltung und die Anzahl aller Teilnehmer aus; daher müssen wir den Namen der Veranstaltung unter GROUP BY eingeben, um die Anzahl der Teilnehmer pro Veranstaltung zu erhalten. Bitte beachten Sie, dass wir COUNT(runner_id) anstelle von COUNT(*) verwenden. Damit stellen wir sicher, dass wir für Veranstaltungen ohne Teilnehmer (d. h. für Veranstaltungen, die mit keiner runner_id verknüpft sind) Null anzeigen. Mehr über die verschiedenen Varianten der Funktion COUNT() können Sie hier nachlesen. Übung 6: Läufer nach Hauptdistanz und Alter gruppieren Übung: Zeigen Sie die Distanz und die Anzahl der Läufer für die folgenden Altersklassen an: unter 20, 20-29, 30-39, 40-49 und über 50. Verwenden Sie die folgenden Spaltenalias: under_20, age_20_29, age_30_39, age_40_49, und over_50. Lösung: SELECT main_distance, COUNT(CASE WHEN age < 20 THEN id END) AS under_20, COUNT(CASE WHEN age >= 20 AND age < 30 THEN id END) AS age_20_29, COUNT(CASE WHEN age >= 30 AND age < 40 THEN id END) AS age_30_39, COUNT(CASE WHEN age >= 40 AND age < 50 THEN id END) AS age_40_49, COUNT(CASE WHEN age >= 50 THEN id END) AS over_50 FROM runner GROUP BY main_distance; Erklärung der Lösung: Dies ist ähnlich wie in Übung 4 - wir wollen die Anzahl der Läufer pro Entfernungswert wissen. Wir wählen also die Spalte main_distance und GROUP BY aus. Dann verwenden wir mehrere COUNT() Aggregatfunktionen, um die Anzahl der Läufer pro Strecke zu ermitteln. Hier müssen wir die Läufer jedoch weiter nach ihrem Alter unterteilen. Die Anweisung CASE WHEN ist hier sehr nützlich, da sie verwendet werden kann, um Bedingungen auszuwerten und je nach den Ergebnissen dieser Bedingungen verschiedene Werte zurückzugeben. Wir können sie als Argument an die Aggregatfunktion COUNT() übergeben, um die Anzahl der Läufer zu ermitteln, die eine bestimmte Bedingung erfüllen. Schauen wir uns an, wie das funktioniert. CASE WHEN age >= 20 AND age < 30 THEN id END Die Anweisung CASE WHEN gibt id nur zurück, wenn das Alter eines Läufers größer oder gleich 20 und kleiner als 30 ist. Andernfalls gibt sie NULL zurück. Wenn sie in die Aggregatfunktion COUNT() eingeschlossen wird, gibt sie die Anzahl der Läufer zurück, die die in der Anweisung CASE WHEN definierte Bedingung erfüllen. Um die Anzahl der Läufer für jede der fünf Altersgruppen zu ermitteln, müssen wir so viele COUNT() Funktionen und CASE WHEN Anweisungen verwenden, wie wir Altersgruppen haben. Über das Zählen von Zeilen durch die Kombination von CASE WHEN und GROUP BY können Sie hier lesen. Abschnitt 3: Fortgeschrittene Fensterfunktionen Übungen In den folgenden fortgeschrittenen SQL-Übungen verwenden wir eine Northwind-Datenbank für einen Online-Shop mit zahlreichen Lebensmitteln. Sie enthält sechs Tabellen: customers, orders, products, categories, order_items, und channels. Schauen wir uns die Daten in dieser Datenbank an. Die Tabelle customers hat 15 Spalten: customer_id speichert die ID des Kunden. email speichert die E-Mail-Adresse des Kunden. full_name speichert den vollständigen Namen des Kunden. address speichert die Straße und die Hausnummer des Kunden. city speichert die Stadt, in der der Kunde wohnt. region speichert die Region des Kunden (nicht immer zutreffend). postal_code speichert die Postleitzahl des Kunden. country speichert das Land des Kunden. phone speichert die Telefonnummer des Kunden. registration_date speichert das Datum, an dem sich der Kunde registriert hat. channel_id speichert die ID des Kanals, über den der Kunde den Shop gefunden hat. first_order_id speichert die ID der ersten Bestellung des Kunden. first_order_date speichert das Datum der ersten Bestellung des Kunden. last_order_id speichert die ID der letzten (d.h. jüngsten) Bestellung des Kunden. last_order_date speichert das Datum der letzten Bestellung des Kunden. Die Tabelle orders hat die folgenden Spalten: order_id speichert die ID der Bestellung. customer_id speichert die ID des Kunden, der die Bestellung aufgegeben hat. order_date speichert das Datum, an dem die Bestellung aufgegeben wurde. total_amount den Gesamtbetrag, der für die Bestellung bezahlt wurde. ship_name stores den Namen der Person, an die die Bestellung gesendet wurde. ship_address speichert die Adresse (Hausnummer und Straße), an die die Bestellung geschickt wurde. ship_city speichert die Stadt, in der die Bestellung aufgegeben wurde. ship_region speichert die Region, in der sich die Stadt befindet. ship_postalcode speichert die Ziel-Postleitzahl. ship_country speichert das Zielland. shipped_date speichert das Datum, an dem die Bestellung verschickt wurde. Die Tabelle products enthält die folgenden Spalten: product_id speichert die ID des Produkts. product_name speichert den Namen des Produkts. category_id speichert die Kategorie, zu der das Produkt gehört. unit_price speichert den Preis für eine Einheit des Produkts (z.B. pro Flasche, Packung, etc.). discontinued gibt an, ob das Produkt nicht mehr verkauft wird. Die Tabelle categories hat die folgenden Spalten: category_id speichert die ID der Kategorie. category_name speichert den Namen der Kategorie description speichert eine kurze Beschreibung der Kategorie. Die Tabelle order_items hat die folgenden Spalten: order_id speichert die ID der Bestellung, in der das Produkt gekauft wurde. product_id speichert die ID des Produkts, das in der Bestellung gekauft wurde. unit_price speichert den Stückpreis des Produkts. (Beachten Sie, dass dieser Preis von dem Preis in der Produktkategorie abweichen kann; der Preis kann sich im Laufe der Zeit ändern und es können Rabatte gewährt werden). quantity speichert die Anzahl der in der Bestellung gekauften Einheiten. discount speichert den auf das jeweilige Produkt angewandten Rabatt. Die Tabelle channels hat die folgenden Spalten: id stores the ID of the channel. channel_name stores the name of the channel through which the customer found the shop. Im Folgenden werden wir einige fortgeschrittene SQL-Übungen durchführen, die sich auf die Fensterfunktionen konzentrieren. Übung 7: Die 3 teuersten Bestellungen auflisten Übung: Erstellen Sie eine dichte Rangliste der Aufträge auf der Grundlage ihrer total_amount. Je größer der Betrag, desto höher sollte der Auftrag sein. Wenn zwei Aufträge die gleiche total_amount haben, sollte der ältere Auftrag höher stehen (Sie müssen die Spalte order_date zur Rangliste hinzufügen). Benennen Sie die Rangordnungsspalte rank. Wählen Sie anschließend nur die Aufträge mit den drei höchsten dichten Rangfolgen aus. Zeigen Sie den Rang, order_id, und total_amount. Lösung: WITH orders_with_ranking AS ( SELECT DENSE_RANK() OVER(ORDER BY total_amount DESC, order_date) AS rank, order_id, total_amount FROM orders ) SELECT * FROM orders_with_ranking WHERE rank <= 3; Erklärung der Lösung: Beginnen wir mit dem ersten Teil der Anweisung. Wir wollen eine dichte Rangfolge der Aufträge auf der Grundlage ihres total_amount (je größer der Wert, desto höher der Rang) und ihres order_date (je älter das Datum, desto höher der Rang) erstellen. Bitte beachten Sie, dass der Rangwert nur dann dupliziert werden kann, wenn die Spalten total_amount und order_date für mehr als eine Zeile gleich sind. Hierfür verwenden wir die Fensterfunktion DENSE_RANK(). In der OVER() Klausel geben wir die Reihenfolge an: absteigend für total_amount Werte und aufsteigend für order_date Werte. Wir zeigen auch die Spalten order_id und total_amount aus der Tabelle orders an. Bis jetzt haben wir alle Ordnungen zusammen mit ihren dichten Rangwerten aufgelistet. Wir möchten jedoch nur die ersten 3 Bestellungen anzeigen (bei denen die Rangspalte kleiner oder gleich 3 ist). Analysieren wir nun die Schritte, die wir von hier aus unternehmen: Wir definieren einen Common Table Expression (CTE) mit dieser SELECT Anweisung - d.h. wir verwenden die WITH Klausel gefolgt vom Namen des CTE und setzen die SELECT Anweisung in Klammern. Dann wählen wir aus dieser CTE aus und geben die Bedingung für die Rangspalte in der WHERE -Klausel an. Sie fragen sich vielleicht, warum wir eine so komplexe Syntax benötigen, die ein CTE definiert und es dann abfragt. Sie könnten sagen, dass wir die Bedingung für die Rangspalte in der WHERE -Klausel der ersten SELECT -Abfrage festlegen könnten. Nun, das ist aufgrund der Ausführungsreihenfolge der SQL-Abfrage nicht möglich. Wir müssen hier den Common Table Expression verwenden, weil Sie keine Fensterfunktionen in der WHERE Klausel verwenden können. Die Reihenfolge der Operationen in SQL ist wie folgt: FROM, JOIN WHERE GROUP BY Aggregate functions HAVING Window functions SELECT ORDER BY Sie können Fensterfunktionen nur in den Klauseln SELECT und ORDER BY verwenden. Wenn Sie sich in der WHERE Klausel auf Fensterfunktionen beziehen möchten, müssen Sie die Fensterfunktionsberechnung in einer CTE (wie in unserem Beispiel) oder in einer Unterabfrage platzieren und sich in der äußeren Abfrage auf die Fensterfunktion beziehen. Lesen Sie diesen Artikel, um mehr über CTEs und rekursive CTEs zu erfahren. Um Ihnen einige Hintergrundinformationen zu den verfügbaren Ranking-Funktionen zu geben, gibt es drei Funktionen, mit denen Sie Ihre Daten einordnen können: RANK(), DENSE_RANK(), und ROW_NUMBER(). Schauen wir sie uns in Aktion an. Values to be ranked RANK() DENSE_RANK() ROW_NUMBER() 1 1 1 1 1 1 1 2 1 1 1 3 2 4 2 4 3 5 3 5 3 5 3 6 4 7 4 7 5 8 5 8 Die Funktion RANK() weist denselben Rang zu, wenn mehrere aufeinanderfolgende Zeilen denselben Wert haben. Dann erhält die nächste Zeile den nächsten Rang, als ob die vorherigen Zeilen unterschiedliche Werte hätten. In diesem Fall folgt auf den Rang 1,1,1 die Zahl 4 (als ob es 1,2,3 statt 1,1,1 wäre). Die Funktion DENSE_RANK() weist auch dann denselben Rang zu, wenn mehrere aufeinanderfolgende Zeilen denselben Wert haben. Dann erhält die nächste Zeile den nächsthöheren Rang als die vorherige. Hier folgt auf 1,1,1 die Zahl 2. Die Funktion ROW_NUMBER() weist der jeweils nächsten Zeile eine fortlaufende Nummer zu, ohne die Zeilenwerte zu berücksichtigen. Hier finden Sie einen Artikel darüber, wie man Daten ordnet. Sie können auch mehr über die Unterschiede zwischen den SQL-Rank-Funktionen erfahren. Übung 8: Berechnen von Deltas zwischen aufeinanderfolgenden Reihenfolgen Übung: In dieser Übung werden wir die Differenz zwischen zwei aufeinanderfolgenden Bestellungen desselben Kunden berechnen. Geben Sie die ID der Bestellung (order_id), die ID des Kunden (customer_id), die total_amount der Bestellung, die total_amount der vorherigen Bestellung basierend auf der order_date (benennen Sie die Spalte previous_value) und die Differenz zwischen der total_amount der aktuellen Bestellung und der vorherigen Bestellung (benennen Sie die Spalte delta) an. Lösung: SELECT order_id, customer_id, total_amount, LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS previous_value, total_amount - LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS delta FROM orders; Erklärung der Lösung: Hier wählen wir die Bestell-ID, die Kunden-ID und den Gesamtbetrag aus der Tabelle orders aus. Die Funktion LAG() holt den vorherigen Wert total_amount. In der OVER() Klausel definieren wir die Funktion LAG() separat für jeden Kunden und ordnen das Ergebnis nach einem Bestelldatum. Schließlich subtrahieren wir den von der Funktion LAG() zurückgegebenen Wert vom Wert total_amount für jede Zeile, um das Delta zu erhalten. In der Spalte previous_value wird für die erste Zeile der Wert Null gespeichert, da es keine vorherigen Werte gibt. Daher ist die Deltaspalte für die erste Zeile ebenfalls Null. Die folgenden Werte der Deltaspalte speichern die Unterschiede zwischen aufeinanderfolgenden Bestellungen desselben Kunden. Es ist erwähnenswert, dass ein Delta die Differenz zwischen zwei Werten darstellt. Durch die Berechnung des Deltas zwischen den täglichen Verkaufsbeträgen können wir die Richtung des Umsatzwachstums/-rückgangs auf Tagesbasis bestimmen. In diesem Artikel erfahren Sie mehr über die Berechnung von Differenzen zwischen zwei Zeilen. Und hier erfahren Sie, wie Sie die Unterschiede zwischen den einzelnen Jahren berechnen. Übung 9: Berechnen Sie die laufende Summe der Käufe pro Kunde Übung: Zeigen Sie für jeden Kunden und seine Bestellungen Folgendes an: customer_id - die ID des Kunden. full_name - den vollständigen Namen des Kunden. order_id - die ID der Bestellung. order_date - das Datum der Bestellung. total_amount - den Gesamtbetrag, der für diese Bestellung ausgegeben wurde. running_total - die laufende Gesamtsumme, die der betreffende Kunde ausgegeben hat. Sortieren Sie die Zeilen nach Kunden-ID und Bestelldatum. Lösung: SELECT orders.customer_id, customers.full_name, orders.order_id, orders.order_date, orders.total_amount, SUM(orders.total_amount) OVER(PARTITION BY orders.customer_id ORDER BY orders.order_date) AS running_total FROM orders JOIN customers ON orders.customer_id = customers.customer_id ORDER BY orders.customer_id, orders.order_date; Erklärung der Lösung: Eine laufende Summe bezieht sich auf die Berechnung, die die Werte einer bestimmten Spalte oder eines Ausdrucks akkumuliert, während Zeilen in einer Ergebnismenge verarbeitet werden. Sie liefert eine laufende Summe der Werte, die bis zur aktuellen Zeile auftreten. Eine laufende Summe wird berechnet, indem der aktuelle Wert zur Summe aller vorherigen Werte addiert wird. Dies kann in verschiedenen Szenarien besonders nützlich sein, z. B. bei der Verfolgung kumulierter Verkäufe, der Berechnung laufender Salden oder der Analyse des kumulierten Fortschritts im Laufe der Zeit. In diesem Artikel erfahren Sie mehr über die Berechnung einer laufenden Summe. Und hier ist ein Artikel über die Berechnung laufender Durchschnittswerte. Wir wählen Kunden-ID, Bestell-ID, Bestelldatum und Bestellsumme aus der Tabelle orders aus. Dann verknüpfen wir die Tabelle orders mit der Tabelle customers über ihre jeweiligen Spalten customer_id, damit wir den vollständigen Namen des Kunden anzeigen können. Mit der Fensterfunktion SUM() berechnen wir die laufende Summe für jeden Kunden einzeln (PARTITION BY orders.customer_id) und sortieren dann aufsteigend nach Datum (ORDER BY orders.order_date). Schließlich ordnen wir die Ausgabe dieser Abfrage nach Kunden-ID und Bestelldatum. Abschnitt 4: Fortgeschrittene rekursive Abfrageübungen In den folgenden fortgeschrittenen SQL-Übungen verwenden wir eine Website-Datenbank, die Informationen über Studenten und Kurse speichert. Sie enthält drei Tabellen: student, course, und student_course. Schauen wir uns die Daten in dieser Datenbank an. Die Tabelle student enthält die folgenden Spalten: id speichert die eindeutige ID-Nummer für jeden Studenten. name speichert den Namen des Schülers. email speichert die E-Mail des Schülers. invited_by_id speichert die ID des Schülers, der diesen Schüler auf die Website eingeladen hat. Wenn der Schüler sich ohne Einladung angemeldet hat, ist diese Spalte NULL. Die Tabelle course besteht aus den folgenden Spalten: id speichert die eindeutige ID-Nummer für jeden Kurs. name speichert den Namen des Kurses. Die Tabelle student_course enthält die folgenden Spalten: id speichert die eindeutige ID für jede Zeile. student_id speichert die ID des Schülers. course_id speichert die ID des Kurses. minutes_spent speichert die Anzahl der Minuten, die der Teilnehmer mit dem Kurs verbracht hat. is_completed wird auf True gesetzt, wenn der Teilnehmer den Kurs beendet hat. Die Übungen in diesem Abschnitt stammen aus unserem Fensterfunktionen Practice Set. In diesem Set finden Sie weitere Übungen zu Fensterfunktionen in Datenbanken, die Einzelhandelsgeschäfte speichern, Wettbewerbe verfolgen und den Datenverkehr auf Websites erfassen. Lassen Sie uns einige fortgeschrittene SQL-Übungen machen, die sich auf rekursive Abfragen konzentrieren. Übung 10: Finden Sie den Einladungspfad für jeden Schüler Übung: Zeigen Sie den Pfad der Einladungen für jeden Schüler an (nennen Sie diese Spalte path). Wenn zum Beispiel Mary von Alice eingeladen wurde und Alice von niemandem eingeladen wurde, sollte der Pfad für Mary wie folgt aussehen: Alice->Mary. Schließen Sie die id, name und invited_by_id der einzelnen Schüler in die Ergebnisse ein. Lösung: WITH RECURSIVE hierarchy AS ( SELECT id, name, invited_by_id, CAST(name AS text) AS path FROM student WHERE invited_by_id IS NULL UNION ALL SELECT student.id, student.name, student.invited_by_id, hierarchy.path || '->' || student.name FROM student, hierarchy WHERE student.invited_by_id = hierarchy.id ) SELECT * FROM hierarchy; Erklärung der Lösung: Für diese Übung müssen wir einen benutzerdefinierten Wert für die Spalte path erstellen, der den Einladungspfad für jeden Kunden enthält. Zum Beispiel wurde Ann Smith von Veronica Knight eingeladen, der wiederum von Karli Roberson eingeladen wurde; daher erhalten wir die Pfadspalte als Karli Roberson->Veronica Knight->Ann Smith für den Namen Ann Smith. Wie Sie vielleicht bemerken, benötigen wir einen Rekursionsmechanismus, um den Einladungspfad zu ergründen. Wir können eine rekursive Abfrage schreiben, indem wir sie mit der Anweisung WITH RECURSIVE definieren, gefolgt von dem Abfragenamen. Der Inhalt der rekursiven Abfrage hierarchy ist wie folgt: Wir wählen die Spalten id, name und invited_by_id aus der Tabelle student aus. Anschließend wird mit der Funktion CAST() der Spaltentyp name in den Datentyp TEXT umgewandelt, um eine reibungslose Verkettung (mit -> und den folgenden Namen) in der Hauptabfrage sicherzustellen. Die WHERE Klauselbedingung stellt sicher, dass nur Studenten, die nicht eingeladen wurden, in dieser Abfrage aufgeführt werden. Der Operator UNION ALL kombiniert die Ergebnismengen von zwei oder mehr SELECT Anweisungen, ohne Duplikate zu entfernen. In diesem Fall haben die Abfragen, auf denen UNION ALL ausgeführt wird, die gleichen Mengen von vier Spalten; die Ergebnismenge der einen wird an die Ergebnismenge der anderen angehängt. In der nächsten SELECT -Anweisung wählen wir wieder die Spalten id, name und invited_by_id aus der Tabelle student aus. Dann verketten wir die Pfadspalte (die aus der rekursiven Hierarchieabfrage stammt, wie in der ersten SELECT -Anweisung definiert) mit dem ->-Zeichen und dem Schülernamen. Um diese Verkettung zu erreichen, wählen wir sowohl aus der Studententabelle als auch aus der rekursiven Hierarchieabfrage aus (hier kommt der Rekursionsmechanismus ins Spiel). In der WHERE -Klausel definieren wir, dass die invited_by_id -Spalte der Tabelle student gleich der id-Spalte der rekursiven Hierarchieabfrage ist, so dass wir den Namen des Studenten erhalten, der den aktuellen Studenten eingeladen hat; bei der nächsten Iteration erhalten wir den Namen des Studenten, der diesen Studenten eingeladen hat, und so weiter. Dies wird als rekursive Abfrage bezeichnet, da sie sich selbst abfragt, um sich den Einladungspfad hinunter zu arbeiten. Eine Abfrage nach der anderen Die in diesem Artikel vorgestellten fortgeschrittenen SQL-Übungen bieten eine umfassende Plattform, um Ihre SQL-Kenntnisse Abfrage für Abfrage zu verbessern. Durch die Beschäftigung mit Fensterfunktionen, JOINs, GROUP BY und mehr haben Sie Ihr Verständnis komplexer SQL-Konzepte erweitert und praktische Erfahrungen bei der Lösung realer Datenprobleme gesammelt. Übung ist der Schlüssel zur Beherrschung von SQL-Kenntnissen. Durch konsequentes Üben können Sie Ihre Kenntnisse verbessern und Ihr theoretisches Wissen in praktisches Fachwissen umwandeln. In diesem Artikel wurden Übungen aus unseren Kursen vorgestellt; weitere Übungen dieser Art können Sie entdecken, wenn Sie sich für unseren Kurs anmelden: Fensterfunktionen Praxis-Set 2021 Monatlich SQL-Übungssatzs - Fortgeschrittene 2022 Monatlich SQL-Übungssatzs - Fortgeschrittene Melden Sie sich jetzt an und fangen Sie kostenlos an! Viel Erfolg! Tags: SQL SQL-Übung