10 Correlated Subquery-Übungen mit Lösungen

Korrelierte Unterabfragen sind eine leistungsstarke SQL-Funktion, die für fortgeschrittene Datenanalysen unerlässlich ist. Dieser Artikel enthält 10 praktische Übungen, die Ihnen helfen, korrelierte Unterabfragen zu beherrschen.

SQL ist eine grundlegende Fähigkeit für jeden, der mit Daten arbeitet, sei es als Datenanalyst, SQL-Entwickler, Dateningenieur oder in einem anderen verwandten Beruf. Zur Beherrschung von SQL gehört mehr als nur die Kenntnis der Grundlagen. Es erfordert auch das Erlernen fortgeschrittener SQL-Funktionen, wie z. B. Unterabfragen.

Eine Unterabfrage ist eine SQL-Abfrage, die in eine größere Abfrage eingebettet ist. Es gibt viele verschiedene Arten von Unterabfragen. Eine korrelierte Subquery ist eine Art von Subquery, die sich auf die äußere Abfrage bezieht und nicht unabhängig davon ausgeführt werden kann. Unterabfragen, und insbesondere korrelierte Unterabfragen, können für SQL-Lernende eine Herausforderung darstellen.

Zuvor haben wir in unserem Artikel eine Sammlung von Subquery-Übungen bereitgestellt: SQL Subquery Praxis: 15 Übungen mit Lösungen. In diesem Artikel möchten wir Ihnen eine Reihe von Übungsaufgaben speziell zum Thema korrelierte Unterabfragen geben. Die Übungen in diesem Artikel stammen aus unseren interaktiven Kursen Basic SQL Practice: A Store und SQL-Praxis: Universität. Diese beiden Kurse sind Teil des SQL-Praxis Kurses, der mehrere SQL-Übungskurse anbietet. Alle Kurse in der Reihe basieren auf realen Szenarien und sind nach SQL-Themen geordnet, damit Sie sich auf Ihr Interessengebiet konzentrieren können.

Übungen zu einer Speicherdatenbank

Der erste Satz von Übungen basiert auf der Datenbank eines Online-Shops. Diese Übungen stammen aus unserem Kurs Basic SQL Practice: A Store. Wir werden klein anfangen und zunächst nur mit zwei Tabellen arbeiten: product und category.

Die Tabelle product enthält eine Liste der im Laden verfügbaren Produkte.

• product_id - die ID des Produkts.
• product_name - den Namen des Produkts.
• category_id - die ID der Kategorie des Produkts. Sie hilft Ihnen bei der Verknüpfung mit der Kategorietabelle.
• quantity_per_unit - die Menge der Produktartikel in einer Einheit.
• unit_price - den Preis des Produkts
• discontinued - die Information, ob das Produkt noch in der Filiale erhältlich ist (Wert FALSE), oder ob es eingestellt wurde (TRUE).

Die Tabelle category enthält Informationen über die Kategorien der Produkte:

• category_id - die ID der Kategorie.
• name - den Namen der Kategorie
• description - die optionale Beschreibung der Kategorie

Übung 1: Teure Produkte in jeder Kategorie

Übung: Finden Sie Produkte, die teurer sind als der Durchschnittspreis der Produkte in ihrer eigenen Kategorie. Geben Sie den Kategorienamen, den Produktnamen und den Stückpreis in das Ergebnis ein.

Lösung:

SELECT
  c.name,
  p.product_name,
  p.unit_price
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  );

Erklärung der Lösung:

In der Lösung verwenden wir eine korrelierte Unterabfrage, um den durchschnittlichen Stückpreis für Produkte zu berechnen, die sich in derselben Kategorie befinden wie das Produkt, das von der äußeren Abfrage verarbeitet wird. Diese Unterabfrage verweist auf die Kategorie, die als c bezeichnet wird, aus der äußeren Abfrage. Sie identifiziert die Produkte in dieser Kategorie und berechnet ihren Durchschnittspreis. Produkte, die mehr kosten als der Durchschnitt für ihre Kategorie, werden anhand dieses Durchschnitts herausgefiltert. Die äußere Abfrage ruft dann den Kategorienamen, den Produktnamen und den Stückpreis dieser Produkte ab und zeigt sie an.

Übung 2: Die Anzahl der teuren Produkte in jeder Kategorie

Übung: Zeigen Sie die Namen der Kategorien und die Anzahl der Produkte aus dieser Kategorie an, deren Stückpreis größer ist als der Durchschnittspreis eines Produkts in dieser Kategorie. Zeigen Sie nur die Kategorien, die solche Produkte haben. Zeigen Sie zwei Spalten an: name (der Name der Kategorie) und expensive_products (die Anzahl der Produkte, die mehr kosten als der Durchschnittspreis in dieser Kategorie).

Lösung:

SELECT
  c.name,
  COUNT(*) AS expensive_products
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT
    AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  )
GROUP BY
  c.category_id,
  c.name;

Erklärung der Lösung:

Diese Aufgabe ist ähnlich wie die vorherige. Wir müssen ebenfalls Produkte finden, deren Stückpreis höher ist als der Durchschnittspreis der jeweiligen Kategorie. Diesmal wollen wir jedoch die Produkte in jeder Kategorie zählen.

In der Lösung verwenden wir dieselbe korrelierte Unterabfrage wie zuvor: Wir berechnen den durchschnittlichen Stückpreis für Produkte in derselben Kategorie wie das Produkt, das von der äußeren Abfrage verarbeitet wird. Dieser Durchschnitt wird dann in der Hauptabfrage verwendet, um nur die Produkte herauszufiltern, deren Stückpreis über dem Durchschnitt der Kategorie liegt. Die Hauptabfrage zählt diese Produkte mit COUNT(*) und gruppiert die Ergebnisse nach Kategorie mit GROUP BY. Das Endergebnis zeigt den Kategorienamen und die Anzahl der Produkte an.

Übung 3: Ausgelaufene, fortgesetzte und alle Produkte in einer Kategorie

Übung: Geben Sie für jede Kategorie die name, die Anzahl der eingestellten Produkte in dieser Kategorie (discontinued_products), die Anzahl der fortgeführten Produkte in dieser Kategorie (continued_products) und die Anzahl aller Produkte in dieser Kategorie (all_products) an.

Lösung:

SELECT
  c.name,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS TRUE) AS discontinued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS FALSE) AS continued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id) AS all_products
FROM category c;

Erklärung der Lösung:

Hier verwenden wir drei korrelierte Unterabfragen in der Anweisung SELECT, um die Anzahl der Produkte auf der Grundlage ihres fortgeführten/ausgeführten Status für jede Kategorie zu ermitteln. Jede Unterabfrage korreliert mit der Hauptabfrage über category_id, damit die Zählungen für jede Kategorie korrekt sind.

Die erste Unterabfrage zählt die Anzahl der Produkte in einer Kategorie, in der das discontinued Flag TRUE lautet. So erhalten wir eine Gesamtzahl aller Produkte, die in der jeweiligen Kategorie nicht mehr verfügbar sind. Die zweite Unterabfrage zählt die Anzahl der Produkte, die in jeder Kategorie noch aktiv sind, wobei eine ähnliche Methode verwendet wird. Die dritte Unterabfrage zählt einfach die Anzahl aller Produkte in einer Kategorie.

Diese Aufgabe könnte auch ohne Unterabfragen gelöst werden, indem eine Kombination von CASE WHEN mit SUM und GROUP BY verwendet wird. Als alternative Übung: Können Sie diese Aufgabe auf beide Arten lösen?

Weitere Tabellen in der Filialdatenbank: Kunden- und Einkaufstabellen

In den folgenden Übungen arbeiten wir mit weiteren Tabellen in der Shop-Datenbank, die sich auf Einkäufe beziehen: customer, purchase, und purchase_item.

Die Tabelle customer enthält die Informationen über die Kunden. Sie hat die folgenden Spalten:

• customer_id - die ID des Kunden.
• contact_name - den vollständigen Namen des Kunden.
• contact_email - die E-Mail des Kunden.

Die Tabelle purchase enthält die Informationen über die einzelnen Bestellungen:

• purchase_id - die ID der Bestellung.
• customer_id - die ID des Kunden.
• total_price - den Gesamtpreis der Bestellung.
• purchase_date - den Zeitstempel des Kaufs.

Die Tabelle purchase_item verknüpft die Einkäufe mit den Produkten. Die Tabelle enthält die folgenden Spalten:

• purchase_id - die Kauf-ID.
• product_id - die ID des gekauften Produkts.
• unit_price - den Preis für eine Einheit eines Produkts.
• quantity - die Anzahl der gekauften Einheiten eines Produkts.

Übung 4: Produkte in Kategorien

Übung: Zeigen Sie für jedes gekaufte Produkt den Produktnamen, die Höchstmenge, in der es gekauft wurde, und die Anzahl dieser Käufe (dieses Produkts in dieser Höchstmenge) an. Zeigen Sie drei Spalten an: product_name, quantity, und purchases_number.

Lösung:

SELECT 
  product_name,
  quantity,
  COUNT(purchase_id) AS purchases_number
FROM purchase_item pi
JOIN product p
  ON pi.product_id = p.product_id 
WHERE quantity = (SELECT MAX(quantity) FROM purchase_item WHERE product_id = pi.product_id)
GROUP BY
  pi.product_id,
  product_name,
  quantity;

Erklärung der Lösung:

Hier wollen wir die maximale Menge finden, in der jedes Produkt gekauft wurde, und wie oft solche Käufe stattfanden. Wir verwenden eine korrelierte Unterabfrage und GROUP BY, um dieses Ergebnis zu erzielen.

Zunächst verwenden wir eine korrelierte Unterabfrage in WHERE, um die Höchstmenge zu ermitteln, in der jedes Produkt gekauft wurde. Dann verwenden wir diesen Wert in der äußeren Abfrage, um Käufe zu finden, bei denen die Menge dieser maximalen Menge entspricht. Schließlich gruppiert die äußere Abfrage die Ergebnisse nach product_id, product_name und quantity und verwendet die Aggregatfunktion COUNT(purchase_id), um die Anzahl der Käufe für jedes Produkt mit der maximalen Menge zu berechnen.

Übung 5: Prozentsatz des Geldes, das der Kunde für den Kauf ausgegeben hat

Übung: Zeigen Sie für jeden Kunden, der einen Kauf getätigt hat, die ID jedes von diesem Kunden getätigten Kaufs und den prozentualen Anteil des für diesen Kauf ausgegebenen Geldes im Verhältnis zu allen von diesem Kunden ausgegebenen Geldern an. Runden Sie die Prozentsätze auf ganze Zahlen. Zeigen Sie drei Spalten an: contact_name, purchase_id und percentage.

Lösung:

SELECT
  contact_name,
  purchase_id,
  ROUND(total_price * 100.0 / (SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id)) AS percentage
FROM purchase p
JOIN customer 
  ON p.customer_id = customer.customer_id;

Erklärung der Lösung:

In der äußeren Abfrage listen wir jeden Kauf auf, der von jedem Kunden getätigt wurde. Wir verbinden die Tabellen purchase und customer, um den Kontaktnamen des Kunden und die Kauf-ID anzuzeigen. Wir verwenden eine korrelierte Unterabfrage, um den Gesamtbetrag herauszufinden, den der aktuelle Kunde ausgegeben hat. Anhand des durch die Unterabfrage ermittelten Betrags wird berechnet, welchen Prozentsatz der Gesamtausgaben der aktuelle Kauf ausmacht.

Übung 6: Kunden mit Käufen über ihrem durchschnittlichen Kaufbetrag

Übung: Finden Sie Kunden, deren letzte Einkaufssumme höher war als ihr durchschnittlicher Einkaufsbetrag. Zeigen Sie den Kundennamen und die letzte Einkaufssumme an.

Lösung:

SELECT 
  c.contact_name, 
  p.total_price AS last_purchase_total
FROM purchase p
JOIN customer c 
ON p.customer_id = c.customer_id
WHERE p.purchase_date = (
    SELECT MAX(lp.purchase_date)
    FROM purchase lp
    WHERE lp.customer_id = p.customer_id
  )
AND p.total_price > (
    SELECT AVG(ap.total_price)
    FROM purchase ap
    WHERE ap.customer_id = p.customer_id
);

Erklärung der Lösung:

Für diese Aufgabe sind zwei Unterabfragen erforderlich. Die erste Subquery wird verwendet, um das Datum des letzten Kaufs für jeden Kunden zu finden, ähnlich wie die Subquery in Übung 4, aber mit Schwerpunkt auf dem Transaktionsdatum und nicht auf den Mengen. Die zweite Subquery berechnet den durchschnittlichen Gesamtpreis der Einkäufe des Kunden und wird verwendet, um Einkäufe herauszufiltern, deren Preis über dem Durchschnitt liegt. Beide Unterabfragen werden in der WHERE Klausel mit einem AND Operator kombiniert.

Übungen zum Datenmodell der Universität

Die zweite Reihe von Übungen in diesem Artikel basiert auf der Datenbank einer Universität. Diese Übungen stammen aus unserem Kurs Grundlagen SQL-Praxis: Universität. Die Datenbank der Universität hat 4 Tabellen.

Die Tabelle course enthält Informationen über die Kurse, die an der Universität angeboten werden, und die Lernpfade, denen sie zugeordnet sind:

• id - Eine eindeutige ID für jeden Kurs.
• title - Der Name des Kurses.
• lecture_hours - Gesamtzahl der Vorlesungsstunden des Kurses.
• tutorial_hours - Gesamtzahl der Tutoriumsstunden für den Kurs.

Die Tabelle student enthält alle Informationen über jeden Studenten, der die Universität besucht:

• id - Eine eindeutige ID für jeden Studenten.
• first_name - den Vornamen des Studenten.
• last_name - Der Nachname des Studenten.

Die Tabelle course_edition enthält Informationen darüber, welche Dozenten die einzelnen Kurse in den einzelnen Semestern unterrichten:

• id - Die ID der Kursausgabe.
• course_id - Die ID der Lehrveranstaltung.
• academic_semester - das Kalenderjahr und das Semester (Herbst oder Frühjahr) des Semesters
• lecturer - den Namen des Dozenten, der die Lehrveranstaltung unterrichtet.

Die Tabelle course_enrollment enthält die Informationen über die Studenten, die in einer Kursausgabe eingeschrieben sind:

• course_edition_id - die ID der Lehrveranstaltungsausgabe.
• student_id - die ID des Studenten.
• midterm_grade - Die Note der Zwischenprüfung, die der Student erhalten hat.
• final_grade - Die Endnote, die der Student erhalten hat.
• course_letter_grade - Die Kursnote in Form eines Buchstabens (A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D-, oder F).
• passed - Ob der Student den Kurs bestanden (TRUE) oder nicht bestanden (FALSE) hat.

Wenn ein Student seine Prüfung noch nicht abgelegt hat, können Sie in der Tabelle einige NULL Felder sehen.

Übung 7: Studenten mit überdurchschnittlicher Note

Übung: Finden Sie Studenten, die in einem der besuchten Kurse eine überdurchschnittliche Abschlussnote erhalten haben. Zeigen Sie: Vor- und Nachname des Schülers, Kurstitel und seine final_grade. Zeigen Sie nur die Schüler, deren Abschlussnote in dieser Kursausgabe höher war als die durchschnittliche Abschlussnote in dieser Kursausgabe.

Lösung:

SELECT
  first_name,
  last_name,
  title,
  final_grade
FROM course_enrollment AS c_en
JOIN student AS s
  ON c_en.student_id = s.id
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_e
  WHERE c_e.course_edition_id = c_en.course_edition_id
);

Erklärung der Lösung:

Die Lösung verwendet eine einzige korrelierte Abfrage. In der korrelierten Unterabfrage wird die durchschnittliche Abschlussnote für die in der Hauptabfrage verarbeitete Kursausgabe ermittelt. Anschließend wird der in der Unterabfrage ermittelte Durchschnitt verwendet, um die Kurseinschreibungen zu filtern, bei denen die Abschlussnote des Teilnehmers über dem Durchschnitt lag. In der Hauptabfrage werden die Tabellen course_enrollment, student, course_edition und course verknüpft, um den Vor- und Nachnamen des Kursteilnehmers mit dem Kurstitel und der erhaltenen Note zu kombinieren.

Übung 8: Anzahl der Schüler mit gleichen oder besseren Noten

Aufgabe:Zählen Sie für jede Endnote in der Tabelle course_enrollment die Anzahl der Schüler, die genau diese Note oder eine bessere Note erhalten haben. Zeigen Sie zwei Spalten: final_grade und students_number.

Lösung:

SELECT DISTINCT
  final_grade,
  (SELECT COUNT (student_id)
   FROM course_enrollment
   WHERE final_grade >= c.final_grade) AS students_number
FROM course_enrollment c;

Erklärung der Lösung:

Die Hauptabfrage wählt jede einzelne (eindeutige) Endnote aus der Tabelle course_enrollment aus. Für jede dieser Noten zählt die korrelierte Unterabfrage die Anzahl der Schüler, deren Endnote gleich oder höher ist als die von der äußeren Abfrage verarbeitete Note. Das Ergebnis wird in zwei Spalten angezeigt: final_grade, die die betreffende Note anzeigt, und students_number, die die Anzahl der Schüler angibt, die diese oder eine höhere Note erreicht haben.

Übung 9: Schüler, die bestanden haben und solche, die nicht bestanden haben

Machen Sie die Übung: Teilen Sie die Schüler jeder Kursausgabe in zwei Gruppen ein: diejenigen, die den Kurs bestanden haben und diejenigen, die ihn nicht bestanden haben. Zeigen Sie für jede Gruppe die folgenden Spalten an:

• course_edition_id
• passed
• average_final_grade - Den Durchschnitt der Studierenden in dieser Gruppe (bestanden oder nicht bestanden), gerundet auf 2 Dezimalstellen.
• average_edition_grade - Der Gesamtdurchschnitt der Studenten in dieser Kursausgabe, gerundet auf 2 Dezimalstellen.

Lösung:

SELECT
  course_edition_id,
  passed,
  ROUND(AVG(final_grade), 2) AS average_final_grade,
  (SELECT ROUND(AVG(final_grade), 2)
         FROM course_enrollment
         WHERE course_edition_id = c_e.course_edition_id) AS average_edition_grade
FROM course_enrollment c_e
GROUP BY 
  course_edition_id, 
  passed;

Erklärung der Lösung:

Die äußere Abfrage wählt die Kursausgabe-ID, den Wert passed und die durchschnittliche Abschlussnote für diese Gruppe von Studenten (entweder "bestanden" oder "nicht bestanden") unter Verwendung der regulären GROUP BY Klausel aus. Sie verwendet außerdem eine korrelierte Unterabfrage, um die durchschnittliche Gesamtabschlussnote für die in der äußeren Abfrage verarbeitete Kursausgabe zu ermitteln, und fügt diesen Durchschnitt zum Ergebnis der Abfrage hinzu. Auf diese Weise können Sie die Endnote in jeder Gruppe mit dem Gesamtdurchschnitt vergleichen.

Übung 10: Wiederum Durchschnittswerte

Übung: Zeigen Sie für jede Kursausgabe die folgenden Daten:

• title
• average_result - Die durchschnittliche Abschlussnote der Schüler, deren Abschlussnote höher als der Durchschnitt der Kursausgabe ist. Runden Sie das Ergebnis auf 0 Dezimalstellen (d.h. eine ganze Zahl).
• results_better_than_average - Die Anzahl der Schüler, deren Abschlussnote größer ist als die durchschnittliche Abschlussnote für diese Kursausgabe.

Lösung:

SELECT
  c.title,
  ROUND(AVG(final_grade)) AS average_result,
  COUNT (student_id) AS results_better_than_average
FROM course_enrollment AS c_en
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_en2
  WHERE c_en2.course_edition_id = c_en.course_edition_id
)
GROUP BY 
  c.id, 
  c.title;

Erklärung der Lösung:

In der korrelierten Unterabfrage berechnen wir die durchschnittliche Abschlussnote für die Kursausgabe, die von der äußeren Abfrage verarbeitet wird. Anhand dieses Wertes werden die Studenten gefiltert, deren Abschlussnote in dieser Kursausgabe über dem Durchschnitt liegt. In der äußeren Abfrage werden der Titel des Kurses, die Anzahl der Studenten mit einer über dem Durchschnitt liegenden Abschlussnote und die durchschnittliche Abschlussnote für diese Gruppe von Studenten angezeigt.

Schlussfolgerung und nächste Schritte

In diesem Artikel haben wir mehrere Übungen zu korrelierten Unterabfragen durchgeführt, um Ihr Verständnis für diese wichtige SQL-Funktion zu verbessern. Für diejenigen, die ihre SQL-Kenntnisse weiter ausbauen möchten, empfehlen wir unseren SQL-Praxis Track. Er umfasst 10 verschiedene SQL-Übungskurse. Jeder Kurs ist mit praktischen Übungen gefüllt, die auf realen Szenarien basieren, und ist nach SQL-Themen gegliedert, damit Sie sich auf Ihr Interessengebiet konzentrieren können.

Außerdem empfehlen wir Ihnen unser Alle für immer SQL-Paket - das ultimative Angebot für SQL-Lernende. Dieses einmalige Zahlungsangebot bietet lebenslangen Zugang zu allen aktuellen und zukünftigen Kursen in unserem Katalog, einschließlich aller Kurse im Bereich 'SQL-Praxis'. Es ist eine unschätzbare Ressource für jeden, der ernsthaft ein SQL-Experte werden möchte.