SQL-Subquery-Übung: 15 Übungen mit Lösungen

Unterabfragen sind für Anfänger oft eine Herausforderung. Übung macht den Meister, also arbeiten Sie sich mit uns durch diese 15 SQL-Subquery-Übungen!

In SQL ist eine Subquery eine Abfrage, die in einer anderen Abfrage verschachtelt ist. Sie vereinfacht die Erstellung komplizierter Abfragen zum Abrufen von Daten, die bestimmte Bedingungen erfüllen, aus verschiedenen Tabellen.

In diesem Artikel stellen wir Ihnen verschiedene Möglichkeiten vor, wie Sie mit Hilfe von Unterabfragen komplexe Abfragen erstellen können. Wir beginnen mit einer Einführung in SQL-Subqueries und den häufigsten Anwendungsfällen. Anschließend führen wir Sie durch 15 SQL-Subquery-Übungen - komplett mit Lösungen und Erklärungen.

Wenn Sie noch nie etwas von SQL-Subqueries gehört haben, lesen Sie unseren kostenlosen Leitfaden für Einsteiger zur SQL-Subquery. Praktische Erfahrungen beim Erstellen von Unterabfragen erhalten Sie auch in unserem SQL für Anfänger Kurs.

Aber wenn Sie die Grundlagen kennen und bereit sind, Ihr Wissen zu verbessern, dann lassen Sie uns mit dem Üben von SQL-Subqueries beginnen.

Grundlagen der SQL-Subabfrage

Zur Erinnerung: Eine SQL-Subquery ist eine SELECT -Anweisung, die in eine andere SELECT -Anweisung eingebettet ist. Sie können sich Unterabfragen als Bausteine vorstellen, aus denen komplexe Abfragen bestehen: Mit ihnen lassen sich komplizierte Aufgaben in kleinere Teile zerlegen und der Code wird leichter lesbar.

Stellen Sie sich vor, Sie stellen eine Frage innerhalb einer anderen - so funktionieren Unterabfragen. Mit Hilfe von Unterabfragen können Sie bestimmte Informationen aus verschiedenen Tabellen, gefiltert nach verschiedenen Bedingungen, auf einmal abrufen.

Hier sind einige häufige Anwendungsfälle für SQL-Unterabfragen:

• Filtern von Daten: Verwenden Sie Unterabfragen in der WHERE Klausel, um Daten auf der Grundlage bestimmter Bedingungen zu filtern und Ihre Abfragen dynamischer zu gestalten. Wird in den Subquery-Übungen 1, 2, 3, 4, 8 und 9 behandelt.
• Verschachtelte Aggregationen: Verwenden Sie Unterabfragen, um Aggregationen innerhalb von Aggregationen durchzuführen, was komplexere Berechnungen ermöglicht. Behandelt in den Subquery-Übungen 5, 6und 7.
• Existenz prüfen: Feststellen, ob ein bestimmter Wert in einer anderen Tabelle vorhanden ist, unter Verwendung von Unterabfragen mit EXISTS oder IN Wird in den Unterabfrageübungen 1, 2 und 14 behandelt.
• Korrelierte Unterabfragen: Erstellen Sie Unterabfragen, die auf Spalten aus der äußeren Abfrage verweisen und so eine kontextabhängige Filterung ermöglichen. Abgedeckt in den Subquery-Übungen 10, 11, 12 und 13.
• Unterabfrage in SELECT Klausel: Fügen Sie eine Subquery in die SELECT Klausel ein, um einen einzelnen Wert oder einen Satz von Werten abzurufen, der in der Hauptabfrage verwendet werden kann. Wird in den Subquery-Übungen 10 und 13 behandelt.
• Unterabfrage in der FROM Klausel: Verwenden Sie eine Subquery in der FROM Klausel, um eine temporäre Tabelle zu erstellen, die komplexere Joins ermöglicht. Behandelt in den Subquery-Übungen 14 und 15.
  

SQL-Subquery-Übungen

Dataset: Orchester

Die folgenden Übungen verwenden das Orchestras Dataset, das drei Tabellen enthält.

• Die Tabelle orchestras Tabelle speichert alle Orchester. Die Spalten sind id, name, rating, city_origin, country_origin und year, in denen das Orchester gegründet wurde.
• Die Tabelle concerts Tabelle enthält alle Konzerte, die von den Orchestern gespielt wurden. Die Spalten lauten id, city, country, year, rating und orchestra_id (verweist auf die orchestras Tabelle).
• Die Tabelle members speichert die Mitglieder (d.h. die Musiker, die in jedem Orchester spielen). Die Spalten lauten id, name, position (d. h. das gespielte Instrument), wage, experience und orchestra_id (verweist auf die orchestras Tabelle).

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren. Die folgenden Übungen stammen aus dem SQL-Übungssatz Kurs.

Übung 1: Auswählen von Orchestern mit einer Herkunftsstadt, in der 2013 ein Konzert stattgefunden hat

Übung:

Wählen Sie die Namen aller Orchester aus, die denselben Herkunftsort haben wie eine beliebige Stadt, in der im Jahr 2013 ein beliebiges Orchester aufgetreten ist.

Lösung:

SELECT name
FROM orchestras
WHERE city_origin IN (SELECT city FROM concerts WHERE year = 2013);

Erklärung der Lösung:

Wir wollen Namen von Orchestern auswählen, die eine bestimmte Bedingung erfüllen, also beginnen wir mit SELECT name FROM orchestras. Dann wird die Bedingung, wie in der Anleitung erwähnt, auf die Spalte city_origin angewandt.

Wir wollen nur die Orchester auswählen, deren Herkunftsstadt zu der Gruppe der Städte gehört, in denen im Jahr 2013 Konzerte stattfanden. Um diese Bedingung in der WHERE Klausel zu erstellen, verwenden wir eine SQL-Subquery.

Erstellen wir eine (Unter-)Abfrage, die alle Städte auswählt, in denen im Jahr 2013 Konzerte stattfanden: SELECT city FROM concerts WHERE year = 2013. Sie gibt eine Spalte mit Städtenamen zurück.

Um sicherzustellen, dass die Herkunftsstadt zu den Städten gehört, die von der Unterabfrage zurückgegeben werden, verwenden wir den Operator IN.

Übung 2: Auswählen von Mitgliedern, die zu hochrangigen Orchestern gehören

Übung:

Wählen Sie die Namen und Positionen (d.h. das gespielte Instrument) aller Orchestermitglieder aus, die mehr als 10 Jahre Erfahrung haben und nicht zu Orchestern mit einer Bewertung unter 8,0 gehören.

Lösung:

SELECT
  name,
  position
FROM members
WHERE experience > 10
AND orchestra_id NOT IN (SELECT id FROM orchestras WHERE rating < 8.0);

Erklärung der Lösung:

Wir möchten Namen und Positionen von Orchestermitgliedern auswählen, die die in den Anweisungen angegebenen Bedingungen erfüllen, also beginnen wir mit SELECT name, position FROM members.

Dann filtern wir nach den Jahren der Erfahrung der Mitglieder und den Orchestern, denen sie angehören. Wir wollen Mitglieder auswählen, die mehr als 10 Jahre Erfahrung haben. Daher fügen wir die erste Bedingung der Klausel WHERE hinzu: experience > 10.

Wir möchten keine Mitglieder auswählen, die Orchestern mit einer Bewertung unter 8,0 angehören. Erstellen wir eine (Unter-)Abfrage, die alle Orchester mit einer Bewertung unter 8,0 auswählt: SELECT id FROM orchestras WHERE rating < 8.0.

Um sicherzustellen, dass die Orchester nicht zu den Orchestern gehören, die in dieser Unterabfrage aufgeführt sind, verwenden wir den Operator NOT IN.

Übung 3: Auswählen von Mitgliedern, die mehr verdienen als Violinisten

Übung:

Geben Sie den Namen und die Position der Orchestermitglieder an, die mehr verdienen als der Durchschnittslohn aller Geiger.

Lösung:

SELECT name, position
FROM members
WHERE wage > (SELECT AVG(wage)
              FROM members
              WHERE position = 'violin');

Erklärung der Lösung:

Wir wählen die Spalten Name und position aus der members Tabelle.

Wir verwenden eine Unterabfrage, um das Durchschnittsgehalt aller Geiger zu ermitteln: SELECT AVG(wage) FROM members WHERE position = 'violin'.

Um sicherzustellen, dass wir Orchestermitglieder auswählen, deren Gehalt größer ist als das Durchschnittsgehalt aller Geiger, legen wir eine Bedingung für die Spalte Gehalt fest, die größer sein muss als der von der Unterabfrage zurückgegebene Durchschnittswert.

Übung 4: Auswählen von hochrangigen Orchestern, die jünger sind als das Kammerorchester

Übung:

Zeigen Sie die Namen von Orchestern an, die nach dem "Kammerorchester" gegründet wurden und eine Bewertung von mehr als 7,5 haben.

Lösung:

SELECT name
FROM orchestras 
WHERE year > (SELECT year FROM orchestras 
              WHERE name = 'Chamber Orchestra') 
AND rating > 7.5;

Erklärung der Lösung:

Zuerst wählen wir die Namen aus der Tabelle orchestras Tabelle.

Dann erstellen wir eine Unterabfrage, die das Jahr zurückgibt, in dem das Kammerorchester gegründet wurde.

Da wir Orchester auflisten wollen, die nach dem Kammerorchester gegründet wurden, legen wir eine Bedingung für die Spalte Jahr fest, die größer sein muss als die von dieser Unterabfrage zurückgegebene.

Schließlich definieren wir eine Bedingung für die Spalte rating, die größer als 7,5 sein muss.

Übung 5: Auswählen von Spielern in großen Orchestern

Übung:

Zeigen Sie den Namen und die Anzahl der Mitglieder für jedes Orchester an, das mehr Mitglieder hat als der Durchschnitt aller Orchester in der Tabelle.

Lösung:

SELECT 
  o.name,
  COUNT(m.id)
FROM orchestras o
JOIN members m
ON o.id = m.orchestra_id
GROUP BY o.name
HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d);

Erklärung der Lösung:

Um den Namen des Orchesters anzuzeigen, markieren Sie einfach die Namensspalte in der orchestras Tabelle. Und um die Anzahl der Mitglieder jedes Orchesters anzuzeigen, müssen wir die orchestras Tabelle mit der members Tabelle über ihre gemeinsame Spalte (die Orchester-ID) verbinden. Dann verwenden wir die Funktion COUNT() , um alle Mitglieder (COUNT(m.id)) zu zählen, wobei wir nach der Namensspalte der Tabelle orchestras Tabelle (GROUP BY o.name).

Wir wollen nur Orchester auswählen, die mehr als die durchschnittliche Anzahl von Mitgliedern haben. Daher müssen wir COUNT(m.id) eine Bedingung auferlegen, die größer ist als die durchschnittliche Anzahl der Mitglieder. Um einer Aggregatfunktion eine Bedingung aufzuerlegen, müssen wir die Klausel HAVING verwenden, die auf die Klausel GROUP BY folgt.

Wir können die durchschnittliche Anzahl der Orchestermitglieder mit Hilfe von Unterabfragen ermitteln. Um genau zu sein, verwenden wir eine verschachtelte Subquery - eine Subquery innerhalb einer Subquery.

• Die innere Subquery ermittelt die Anzahl der Orchestermitglieder für jedes Orchester mithilfe der Aggregatfunktion COUNT():

SELECT orchestra_id, COUNT(id) FROM members GROUP BY orchestra_id

• Die äußere Subquery berechnet den Durchschnitt aller COUNT(id)-Werte, die von der inneren Subquery zurückgegeben werden, unter Verwendung der Aggregatfunktion AVG():

SELECT AVG(d.count) FROM (<inner subquery>) AS d

Die gesamte Unterabfrage lautet schließlich:

(SELECT AVG(d.count) 
 FROM (SELECT orchestra_id, COUNT(id) 
       FROM members GROUP BY orchestra_id) AS d)

Und sie gibt die durchschnittliche Anzahl der Mitglieder pro Orchester zurück.

Da wir nun die durchschnittliche Anzahl der Orchestermitglieder haben, können wir eine Bedingung auf COUNT(m.id) anwenden, um sicherzustellen, dass sie größer ist als die durchschnittliche Anzahl der Orchestermitglieder:

HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d)

Dataset: Universität

In den folgenden Übungen wird der Datensatz "Universität" verwendet, der sechs Tabellen enthält.

• Die Tabelle course Tabelle speichert Informationen über Kurse. Die Spalten sind id, title, learning_path, short_description, lecture_hours, tutorial_hours, ects_points, has_exam und has_project.
• Die Tabelle lecturer Tabelle speichert Informationen über Dozenten. Die Spalten sind id, first_name, last_name, degree, und email.
• Die Tabelle student Tabelle enthält Informationen über die Studierenden. Die Spalten lauten id, first_name, last_name, email, birth_date, und start_date.
• Die Tabelle academic_semester enthält Informationen über die einzelnen Studiensemester. Die Spalten sind id, calendar_year, term, start_date, und end_date.
• Die Tabelle course_edition Tabelle enthält Informationen darüber, welche Dozenten die einzelnen Lehrveranstaltungen in den einzelnen Semestern unterrichten. Die Spalten lauten id, course_id (verweist auf die course Tabelle), academic_semester_id (verweist auf die academic_semester Tabelle), und lecturer_id (verweist auf die lecturer Tabelle).
• Die Tabelle course_enrollment Tabelle enthält Informationen über die in den einzelnen Kursen eingeschriebenen Studenten. Die Spalten sind course_edition_id (verweist auf die Tabelle course_edition Tabelle), student_id (verweist auf die student Tabelle), midterm_grade, final_grade, course_letter_grade, und passed.

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren. Die folgenden Übungen stammen aus dem Grundkurs SQL-Praxis University.

Übung 6: Kurse für das Frühjahrssemester auswählen

Übung:

Zeigen Sie die IDs und Titel aller Kurse an, die während eines beliebigen Frühjahrssemesters stattgefunden haben.

Lösung:

SELECT
  id,
  title
FROM course
WHERE id = ANY (SELECT course_id
                FROM course_edition ce
                JOIN academic_semester asem
                ON ce.academic_semester_id = asem.id
                WHERE asem.term = 'spring');

Erklärung der Lösung:

Wir beginnen mit der Auswahl von IDs und Titeln aus der course Tabelle. In der Klausel WHERE müssen wir eine Bedingung für die Spalte id der Tabelle course Tabelle eine Bedingung auferlegen, indem wir Unterabfragen und den Operator ANY verwenden.

Wir wollen Kurse auswählen, die mindestens einmal im Frühjahrssemester stattgefunden haben, also erstellen wir zunächst eine Unterabfrage, die solche Kurs-IDs auswählt. Beachten Sie, dass wir die Tabelle course_edition Tabelle mit der academic_semester über ihre gemeinsame Spalte (academic_semester_id bzw. id) verknüpfen müssen, um die Kurse des Frühjahrssemesters filtern zu können.

Der ANY-Operator gibt true zurück, wenn mindestens ein von der Unterabfrage zurückgegebener Wert die Bedingung erfüllt.

Veranschaulichen wir das:

Die Zeile in grün gibt wahr zurück, weil 9 gleich einer der Zahlen ist, die von der Unterabfrage zurückgegeben werden.

Die rote Zeile ist falsch, weil 3 nicht gleich einer der von der Unterabfrage zurückgegebenen Zahlen ist.

Übung 7: Alle Studenten auswählen, die mindestens einen Kurs bestanden haben

Übung:

Wählen Sie die IDs und Namen der Studenten aus, die mindestens einen Kurs bestanden haben.

Lösung:

SELECT
  id,
  first_name,
  last_name
FROM student
WHERE id = ANY (SELECT student_id
                FROM course_enrollment
                WHERE passed = 't');

Erklärung der Lösung:

Wir beginnen mit der Auswahl von IDs und Namen aus der student Tabelle. In der Klausel WHERE müssen wir eine Bedingung für die Spalte id der Tabelle student Tabelle eine Bedingung auferlegen, indem wir Unterabfragen und den Operator ANY verwenden.

Wir möchten Studenten auswählen, die mindestens einen Kurs bestanden haben, also erstellen wir zunächst eine Unterabfrage, die die IDs aller Studenten auswählt, die einen oder mehrere Kurse bestanden haben: SELECT student_id FROM course_enrollment WHERE passed = 't'

Der Operator ANY gibt true zurück, wenn mindestens ein von der Unterabfrage zurückgegebener Wert die Bedingung erfüllt.

Veranschaulichen wir das:

Die Zeilen in grün geben wahr zurück, weil sowohl 5 als auch 8 zu den IDs gehören, die von der Unterabfrage zurückgegeben werden.

Die rote Zeile liefert false, weil 3 nicht zu den von der Subquery zurückgegebenen IDs gehört.

Übung 8: Den/die Dozenten auswählen, der/die die wenigsten Kurse unterrichtet/unterrichten

Übung:

Finden Sie den/die Dozenten mit der geringsten Anzahl an Lehrveranstaltungen. Zeigen Sie den Vor- und Nachnamen des Dozenten und die Anzahl der von ihm gehaltenen Lehrveranstaltungen an (als no_of_courses).

Lösung:

SELECT
  l.first_name,
  l.last_name,
  COUNT(ce.id) AS no_of_courses
FROM lecturer l
JOIN course_edition ce
ON l.id = ce.lecturer_id
GROUP BY l.first_name, l.last_name
HAVING COUNT(ce.id) 
            <= ALL (SELECT COUNT(id)
                    FROM course_edition
                    GROUP BY lecturer_id);

Erklärung der Lösung:

Wir wählen die Namen aus der Tabelle lecturer und verknüpfen sie mit der Tabelle course_edition Tabelle über die Dozenten-ID. Wir zählen die Zeilen in der Tabelle course_edition Tabelle für jeden Dozenten: COUNT(ce.id) AS no_of_courses. Wir gruppieren also nach Dozentennamen.

Um sicherzustellen, dass nur die Dozenten mit den wenigsten Lehrveranstaltungen ausgewählt werden, muss die Bedingung COUNT(ce.id) kleiner oder gleich der Anzahl der Lehrveranstaltungen der einzelnen Dozenten sein.

Erstellen wir eine Unterabfrage, die die Anzahl der unterrichteten Kurse für jeden Dozenten auswählt: SELECT COUNT(id) FROM course_edition GROUP BY lecturer_id.

Der Operator ALL gibt true zurück, wenn die Bedingung in allen von der Unterabfrage zurückgegebenen Zeilen erfüllt ist. Hier wollen wir sicherstellen, dass wir nur den/die Dozenten auswählen, deren no_of_courses kleiner ist als die aller anderen Dozenten (und nur gleich der kleinsten Anzahl von unterrichteten Kursen, daher das kleinere/gleiche Zeichen).

Veranschaulichen wir das:

Die grüne Zeile ist wahr, weil 4 kleiner ist als jede von der Unterabfrage zurückgegebene Zahl und nur gleich der kleinsten von der Unterabfrage zurückgegebenen Zahl.

Die rote Linie ist falsch, weil 8 nicht kleiner ist als alle von der Unterabfrage zurückgegebenen Zahlen (d. h. 8 > 4, 8 > 5, 8 > 6).

Übung 9: Auswählen von Studenten, die in den meisten Kursen eingeschrieben sind

Übung:

Finden Sie den/die Studenten, der/die in der größten Anzahl von Kursausgaben eingeschrieben ist/sind. Zeigen Sie die ID, den Vor- und Nachnamen und die Anzahl der Kursausgaben an, in denen die Teilnehmer eingeschrieben sind ( no_of_course_ed).

Lösung:

SELECT
  student_id,
  first_name,
  last_name,
  COUNT(course_edition_id) AS no_of_course_ed
FROM course_enrollment
JOIN student
ON course_enrollment.student_id = student.id
GROUP BY student_id, first_name, last_name
HAVING COUNT(course_edition_id)
            >= ALL (SELECT COUNT(course_edition_id)
                    FROM course_enrollment
                    GROUP BY student_id);

Erklärung der Lösung:

Wir wählen IDs und Namen aus der Tabelle student Tabelle und verknüpfen student mit der Tabelle course_edition Tabelle über ihre gemeinsame Spalte (Schüler-ID). Wir zählen die Zeilen in der Tabelle course_edition Tabelle für jeden Schüler (COUNT(course_edition_id) AS no_of_course_ed). Wir gruppieren also nach Studenten-IDs und Namen.

Um sicherzustellen, dass nur Studenten mit der größten Anzahl an eingeschriebenen Kursen ausgewählt werden, müssen wir eine Bedingung für COUNT(course_edition_id) festlegen, die größer oder gleich der Anzahl der eingeschriebenen Kurse für jeden Studenten ist.

Erstellen wir eine Unterabfrage, die die Anzahl der eingeschriebenen Kurse für jeden Studenten auswählt: SELECT COUNT(course_edition_id) FROM course_enrollment GROUP BY student_id

Der ALL-Operator gibt true zurück, wenn die Bedingung in allen von der Unterabfrage zurückgegebenen Zeilen erfüllt ist. Hier wollen wir sicherstellen, dass nur Studenten ausgewählt werden, deren no_of_course_ed größer ist als die aller anderen Studenten (und nur gleich der größten Anzahl eingeschriebener Kurse - daher das Größer/Gleichheitszeichen).

Veranschaulichen wir das:

Die grüne Zeile ist wahr, weil 9 größer ist als jede von der Unterabfrage zurückgegebene Zahl und nur gleich der größten von der Unterabfrage zurückgegebenen Zahl.

Die rote Linie ist falsch, weil 6 nicht größer ist als alle von der Unterabfrage zurückgegebenen Zahlen (d. h. 6 < 8 und 6 < 9).

Dataset: Store

Für die folgenden Übungen wird die Datenbank Store verwendet, die sechs Tabellen enthält:

• Die Tabelle customer enthält Informationen über Die Spalten sind customer_id, contact_name, company_name, contact_email, address, city und country.
• Die Tabelle product Tabelle speichert Informationen über Produkte. Die Spalten sind product_id, product_name, category_id (verweist auf die category Tabelle), quantity_per_unit, unit_price, units_in_stock, und discontinued.
• Die Tabelle category Tabelle speichert Informationen über Produktkategorien. Die Spalten lauten category_id, name, description und parent_category_id (verweist auf sich selbst).
• Die Tabelle purchase speichert Informationen über die von Kunden getätigten Käufe. Die Spalten sind purchase_id, customer_id (verweist auf die customer Tabelle), employee_id (verweist auf die employee Tabelle), total_price, purchase_date, shipped_date, ship_address, ship_city, und ship_country.
• Die Tabelle purchase_item ordnet alle Käufe den Produkten zu. Die Spalten sind purchase_id (verweist auf die purchase Tabelle), product_id (verweist auf die product Tabelle), unit_price, und quantity.
• Die Tabelle employee Tabelle speichert Informationen über Mitarbeiter. Die Spalten lauten employee_id, last_name, first_name, birth_date, address, city, country und reports_to.

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren. Die folgenden Übungen stammen aus dem Kurs Basic SQL-Praxis Store.

Übung 10: Berechnen Sie den Prozentsatz, den der Kunde bei jedem Kauf ausgegeben hat

Übung:

Zeigen Sie für jeden Kunden, der mindestens 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 c
ON p.customer_id = c.customer_id;

Erklärung der Lösung:

Um Kunden zu identifizieren, die mindestens einen Kauf getätigt haben, müssen wir die Tabelle purchase Tabelle mit der customer über ihre gemeinsame Spalte (Kunden-ID) verbinden.

Um den prozentualen Anteil der Ausgaben für einen Kauf im Verhältnis zu allen Ausgaben dieses Kunden zu berechnen, benötigen wir Unterabfragen. Die Unterabfrage berechnet, wie viel Geld ein Kunde für alle Einkäufe ausgegeben hat: SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id

Beachten Sie, dass die Unterabfrage auf die Tabelle der äußeren Abfrage purchase Tabelle (alias p) verweist, um die richtige Kunden-ID zu erhalten. Dies nennt man eine korrelierte Unterabfrage.

Schließlich berechnen wir den Prozentwert, indem wir total_price durch den von der Unterabfrage zurückgegebenen Wert dividieren. Zusätzlich müssen wir diesen Wert mit 100 multiplizieren, um den Prozentsatz zu erhalten, und ROUND() in eine ganze Zahl umwandeln.

Wenn Sie mehr über korrelierte Unterabfragen erfahren möchten, lesen Sie Lernen Sie, eine korrelierte Unterabfrage in 5 Minuten zu schreiben.

Übung 11: Finden Sie die Anzahl der teuren Produkte in jeder Kategorie

Übung:

Zeigen Sie die Namen der Kategorien und die Anzahl der Produkte aus dieser Kategorie, deren Stückpreis höher ist als der Durchschnittspreis eines Produkts in dieser Kategorie. Zeigen Sie nur die Kategorien an, die solche Produkte enthalten. 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.name;

Erklärung der Lösung:

Wir wollen die Namen der Kategorien und die Anzahl der Produkte anzeigen; daher müssen wir die Tabelle category Tabelle mit der product über ihre gemeinsame Spalte (Kategorie-ID) verbinden.

Um die Anzahl der Produkte pro Kategorie anzuzeigen, verwenden wir die Funktion COUNT(). Da wir den Kategorienamen (c.name) und die Anzahl der Produkte pro Kategoriename (COUNT(*)) auswählen, müssen wir nach der Spalte mit dem Kategorienamen (GROUP BY c.name) gruppieren.

In die Funktion COUNT() sollen nur Produkte aufgenommen werden, deren Stückpreis höher ist als der Durchschnittspreis eines Produkts in dieser Kategorie. Zu diesem Zweck verwenden wir eine korrelierte Unterabfrage.

In der Unterabfrage verbinden wir die Spalten product und category zusammen und wählen den Durchschnittswert der Stückpreise aus. Um sicherzustellen, dass wir den Durchschnitt der Werte aus der spezifischen Kategorie-ID nehmen, erzwingen wir eine Bedingung in der WHERE Klausel, die besagt, dass die category_id der Unterabfrage gleich der category_id der äußeren Abfrage sein muss.

Die Bedingung der WHERE -Klausel der Hauptabfrage besagt, dass unit_price größer sein muss als der von der Unterabfrage zurückgegebene Durchschnitt unit_price für diese Kategorie.

Übung 12: Anzeige der gekauften Produkte mit der maximal gekauften Menge

Übung:

Zeigen Sie für jedes gekaufte Produkt seinen Namen, die größte Menge, in der es gekauft wurde, und die Anzahl der Käufe mit maximaler Menge für dieses Produkt 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:

Um Informationen über Produkte und die Mengen, in denen sie gekauft wurden, zu erhalten, müssen wir die Tabelle purchase_item Tabelle mit der product über ihre gemeinsame Spalte (Produkt-ID) verbinden.

Wir verwenden die Aggregatfunktion COUNT(), um die Anzahl der Käufe zu ermitteln (COUNT(purchase_id)).

Um sicherzustellen, dass wir nur die größte Menge auswählen, in der ein bestimmtes Produkt gekauft wurde, müssen wir eine Unterabfrage erstellen. Die Unterabfrage bezieht sich auf den Wert product_id aus der äußeren Abfrage, um sicherzustellen, dass wir die maximale Menge für das richtige Produkt auswählen - es handelt sich also um eine korrelierte Unterabfrage.

In der WHERE Klausel der Hauptabfrage wird die Bedingung aufgestellt, dass der Mengenwert gleich dem von der Unterabfrage zurückgegebenen Wert sein muss.

Übung 13: Auflisten der eingestellten, fortgeführten und gesamten Produkte in jeder Kategorie

Übung:

Zeigen Sie für jede Kategorie an:

• Ihren Namen.
• Die Anzahl der auslaufenden (d.h. nicht mehr verfügbaren) Produkte in dieser Kategorie (nennen Sie diese Spalte discontinued_products).
• Die Anzahl der fortgeführten (d.h. aktuell verfügbaren) Produkte in dieser Kategorie (benennen Sie diese Spalte continued_products).
• Die Anzahl aller Produkte in dieser Kategorie (Name dieser Spalte all_products).

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:

In dieser Übung verwenden wir nicht eine oder zwei, sondern drei korrelierte Unterabfragen.

Wir wählen den Kategorienamen aus der Tabelle category Tabelle.

Die erste korrelierte Unterabfrage zählt alle Produkte, die discontinued wurden. Diese Unterabfrage bezieht sich auf den Wert category_id aus der äußeren Abfrage, um sicherzustellen, dass die auslaufenden Produkte pro Kategorie gezählt werden.

Die zweite korrelierte Unterabfrage zählt alle Produkte, die noch nicht eingestellt wurden. Diese Unterabfrage bezieht sich auf den Wert category_id aus der äußeren Abfrage, um sicherzustellen, dass die fortgeführten Produkte pro Kategorie gezählt werden.

Die dritte korrelierte Unterabfrage zählt alle Produkte pro Kategorie. Diese Unterabfrage bezieht sich auf den Wert category_id aus der äußeren Abfrage, um sicherzustellen, dass alle Produkte pro Kategorie gezählt werden.

Übung 14: Zählen der von jedem Mitarbeiter in Houston getätigten Einkäufe

Übung:

Zeigen Sie die Mitarbeiter-ID und die Gesamtzahl der von diesem Mitarbeiter bearbeiteten Einkäufe an. Verwenden Sie eine Unterabfrage, um Informationen über die Anzahl der Bestellungen, die jeder Mitarbeiter pro Kunde bearbeitet hat, abzurufen, und lassen Sie die Hauptabfrage FROM dieser Unterabfrage auswählen. Berücksichtigen Sie nur Mitarbeiter, die in Houston wohnen.

Lösung:

SELECT
  employee_per_customer.employee_id,
  SUM(employee_per_customer.no_of_purchases) AS total_no_of_purchases
FROM (SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id
     ) AS employee_per_customer
GROUP BY employee_per_customer.employee_id;

Erklärung der Lösung:

Stellen wir zunächst sicher, dass wir nur Mitarbeiter berücksichtigen, die in Houston wohnen. Dazu verwenden wir das Schlüsselwort EXISTS. Es gibt true zurück, wenn die Unterabfrage mindestens eine Zeile zurückgibt.

Beachten Sie, dass die an das Schlüsselwort EXISTS übergebene Unterabfrage eine korrelierte Unterabfrage ist, da sie sich auf den employee_id Wert ihrer äußeren Abfrage bezieht (die eine Unterabfrage für die Hauptabfrage ist).

Analysieren wir nun die Unterabfrage, die in der FROM Klausel an die Hauptabfrage übergeben wird. Sie wählt Mitarbeiter- und Kunden-IDs aus und zählt, wie viele Käufe pro Mitarbeiter und pro Kunde getätigt wurden (daher die Gruppierung nach Mitarbeiter- und Kunden-ID-Werten).

      SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id

Der rot markierte Teil stellt sicher, dass nur Mitarbeiter berücksichtigt werden, die in Houston wohnen.

Diese Unterabfrage ist mit AS employee_per_customer verknüpft, und die Hauptabfrage wählt aus ihr aus.

Die Hauptabfrage wählt das Folgende aus:

• Mitarbeiter-IDs aus der Unterabfrage (aus employee_per_customer),
• Die Gesamtzahl der Einkäufe, die von jedem Mitarbeiter getätigt wurden. Dies erfordert eine Gruppierung nach Mitarbeiter-ID (GROUP BY employee_per_customer.employee_id).

Beachten Sie, dass die korrelierte Unterabfrage COUNT() verwendet, um die Einkäufe (oder Zeilen) pro Mitarbeiter und pro Kunde zu zählen. Die Hauptabfrage verwendet jedoch die Funktion SUM(), um alle Werte zu addieren, die von COUNT() in der Unterabfrage zurückgegeben werden.

Weitere Informationen über Aggregatfunktionen finden Sie hier. Und in diesem Artikel über die Verwendung von SUM() mit OVER(PARTITION BY) finden Sie weitere Details über Fensterfunktionen.

In dieser Übung wurde die Idee der Verwendung von Unterabfragen als Bausteine vorgestellt - hier haben wir drei Bausteine verwendet, um die gewünschten Daten zu erhalten.

Übung 15: Finden Sie die größte Anzahl von Produktkategorien in einem Kauf

Übung:

Verwenden Sie eine Unterabfrage, um die Kauf-ID und die Anzahl der in diesem Kauf enthaltenen unterschiedlichen Kategorien auszuwählen. Wählen Sie in der Hauptabfrage die maximale Anzahl der Kategorien aus dieser Unterabfrage aus.

Lösung:

SELECT MAX(categories_per_purchase.category_count) 
         AS max_categories_per_purchase
FROM (SELECT 
        purchase_id, 
        COUNT(DISTINCT category_id) AS category_count
      FROM purchase_item pi
      JOIN product p
      ON pi.product_id = p.product_id
      GROUP BY purchase_id) AS categories_per_purchase;

Erklärung der Lösung:

Beginnen wir mit der Unterabfrage, die in der FROM-Klausel der Hauptabfrage übergeben wird.

Wir verknüpfen die purchase_item Tabelle mit der product Tabelle über ihre gemeinsame Spalte (Produkt-ID). Wir wählen die Kauf-ID und COUNT DISTINCT Kategorie-IDs pro Kauf aus. Daher gruppieren wir nach der Spalte purchase_id

Die Hauptabfrage verwendet die Funktion MAX(), um (aus der Unterabfrage AS categories_per_purchase) die maximale Anzahl der in einem Kauf enthaltenen Produktkategorien auszuwählen.

Weitere SQL-Subquery-Praxis

In diesem Artikel wurden verschiedene Anwendungsfälle von Unterabfragen vorgestellt, darunter die erweiterte Filterung von Daten oder die Verschachtelung von Abfragen ineinander. Eine grundlegende Idee, wie Sie von der Verwendung von Unterabfragen profitieren können, besteht darin, eine Frage in kleinere (Unter-)Fragen zu unterteilen - jede kleine (Unter-)Frage sollte mit einer Unterabfrage beantwortet werden können.

Üben Sie ruhig selbst - das ist der beste Weg, um weitere Techniken für die Verwendung von Unterabfragen zur Vereinfachung komplexer Datenextraktionsaufgaben zu erlernen. Weitere Subquery-Übungen finden Sie hier und hier.

Wir ermutigen Sie, mit unseren SQL-Kursen einzutauchen und zu üben. Jeder dieser Kurse hat einen separaten Abschnitt über Subqueries und separate Abschnitte zu anderen anspruchsvollen SQL-Themen: Single Table Queries, JOINs, Grouping und mehr.

1. SQL-Übungssatz
2. Grundlagen SQL-Praxis: A Store
3. Grundlagen SQL-Praxis: Universität
4. Basic SQL-Praxis: Blog-Verkehrsdaten
5. Basic SQL-Praxis: Abfragen durchlaufen

Melden Sie sich jetzt an und fangen Sie kostenlos an. Viel Erfolg!