Zurück zur Artikelliste Artikel
23 Leseminuten

SQL-Joins: 12 Übungsfragen mit detaillierten Antworten

In diesem Artikel gehen wir auf unseren SQL JOINS-Kurs ein und geben Ihnen 12 Join-Übungen zum Lösen. Aber keine Sorge - für alle Übungen gibt es Lösungen und Erklärungen. Wenn Sie nicht weiterkommen, ist Hilfe da! Schließlich ist dieser Kurs zum Üben und Lernen gedacht.

SQL-Joins können knifflig sein. Es geht nicht nur um die Syntax, sondern auch darum, zu wissen, welche Joins in welchen Szenarien zu verwenden sind.

Joins werden verwendet, um Daten aus zwei oder mehr Tabellen in SQL zu kombinieren. Die Tabellen können auf verschiedene Weise verbunden werden, und je nach Tabelle kann jede Art der Verbindung zu einem völlig anderen Ergebnis führen. Es gibt keine andere Möglichkeit, dies zu lernen als die Praxis. Ja, Sie können Erklärungen und typische Anwendungen von SQL-Joins lesen. Das hilft natürlich! Aber die Praxis baut durch Problemlösungen und Wiederholungen darauf auf, so dass Sie Ihr Wissen festigen können. Je mehr Sie üben, desto größer ist die Wahrscheinlichkeit, dass die Datenprobleme, die Sie im wirklichen Leben zu lösen haben, denen, die Sie bereits gelöst haben, ähnlich oder sogar völlig identisch sind!

Und genau das werden wir in diesem Artikel tun! Wir zeigen Ihnen Übungen für einfache und fortgeschrittene Anwendungen von SQL-Joins. Wenn sie Ihnen gefallen, werden Sie unseren SQL-JOINs Kurs noch mehr genießen, da alle Übungen von dort stammen. Insgesamt bietet Ihnen der Kurs 93 SQL-Joins-Übungen. Sie behandeln Themen wie die Arten von Joins in SQL, das Filtern von Daten, das Verbinden von mehr als zwei Tabellen, das Self-Joining einer Tabelle und die Verwendung von Non-Equi Joins.

OK, stellen wir also die Datensätze vor und beginnen wir mit den Übungen, ja? Fühlen Sie sich frei, sich mit dem SQL JOIN Cheat Sheet zu helfen, während Sie gehen.

Liste der Übungen

Hier finden Sie eine Liste aller Übungen in diesem Artikel:

INNER JOIN

INNER JOIN ist eine Art von SQL-Verknüpfung, die nur die übereinstimmenden Zeilen aus den verknüpften Tabellen zurückgibt.

Um Ihnen zu zeigen, wie dies funktioniert, verwenden wir Datensatz 1 aus dem Kurs.

Datensatz 1

Das Dataset besteht aus vier Tabellen: author, book, adaptation, und book_review.

Die erste Tabelle enthält die Autorendaten in den folgenden Spalten:

  • id - Die eindeutige ID des Autors in der Datenbank.
  • name - Der Name des Autors.
  • birth_year - Das Jahr, in dem der Autor geboren wurde.
  • death_year - Das Jahr, in dem der Autor gestorben ist (das Feld ist leer, wenn der Autor noch am Leben ist).

Hier sind die ersten Zeilen der Tabelle:

idnamebirth_yeardeath_year
1Marcella Cole1983NULL
2Lisa Mullins18911950
3Dennis Stokes19351994
4Randolph Vasquez19572004
5Daniel Branson19651990

Die zweite Tabelle, bookzeigt Details über Bücher an. Die Spalten sind:

  • id - Die ID eines bestimmten Buches.
  • author_id - Die ID des Autors, der dieses Buch geschrieben hat.
  • title - Der Titel des Buches.
  • publish_year - Das Jahr, in dem das Buch veröffentlicht wurde.
  • publishing_house - Der Name des Verlags, der das Buch gedruckt hat.
  • Bewertung - Die durchschnittliche Bewertung des Buches.

Dies sind die ersten fünf Zeilen:

idauthor_idtitlepublish_yearpublishing_houserating
1NULLSoulless girl2008Golden Albatros4.3
2NULLWeak Heart1980Diarmud Inc.3.8
34Faith Of Light1995White Cloud Press4.3
4NULLMemory Of Hope2000Rutis Enterprises2.7
56Warrior Of Wind2005Maverick4.6

Die Tabelle adaptation Tabelle hat die folgenden Spalten:

  • book_id - Die ID des adaptierten Buches.
  • type - Die Art der Adaption (z. B. Film, Spiel, Theaterstück, Musical).
  • title - Der Name der Adaption.
  • release_year - Das Jahr, in dem die Adaption erstellt wurde.
  • rating - Die durchschnittliche Bewertung für die Adaption.

Hier ist ein Schnappschuss der Daten aus dieser Tabelle:

book_idtypetitlerelease_yearrating
1movieGone With The Wolves: The Beginning20083
3movieCompanions Of Tomorrow20014.2
5movieHomeless Warrior20084
2movieBlacksmith With Silver20144.3
4moviePatrons And Bearers20043.2

Die endgültige Tabelle lautet book_review. Sie besteht aus den folgenden Spalten:

  • book_id - Die ID eines rezensierten Buches.
  • review - Die Zusammenfassung der Rezension.
  • author - Der Name des Autors der Rezension.

Hier sind die Daten:

book_idreviewauthor
1An incredible bookSylvia Jones
1Great, although it has some flawsJessica Parker
2Dennis Stokes takes the reader for a ride full of emotionsThomas Green
3Incredible craftsmanship of the authorMartin Freeman
4Not the best book by this authorJude Falth
5Claudia Johnson at her best!Joe Marqiz
6I cannot recall more captivating plotAlexander Durham

Übung 1: Alle Bücher und ihre Autoren auflisten

Übung: Zeigen Sie den Namen jedes Autors zusammen mit dem Titel des Buches, das er geschrieben hat, und dem Jahr, in dem das Buch veröffentlicht wurde.

Lösung:

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id;

Erklärung der Lösung: Die Abfrage wählt den Namen des Autors, den Buchtitel und das Erscheinungsjahr des Buches aus. Es handelt sich um Daten aus den beiden Tabellen: author und . book. Wir können auf beide Tabellen zugreifen, indem wir INNER JOIN verwenden. Sie gibt nur Zeilen mit übereinstimmenden Werten (Werte, die die Verknüpfungsbedingung erfüllen) aus beiden Tabellen zurück.

Wir verweisen zunächst auf die Tabelle author in der FROM -Klausel. Dann fügen wir die Klausel JOIN hinzu (die in SQL auch als INNER JOIN geschrieben werden kann) und verweisen auf die Tabelle book.

Die Tabellen werden über die gemeinsame Spalte verbunden. In diesem Fall ist es id aus der Tabelle author und author_id aus der Tabelle book. Wir wollen die Zeilen verbinden, in denen diese Spalten den gleichen Wert haben. Dazu verwenden wir die Klausel ON und geben die Spaltennamen an. Wir setzen auch den Tabellennamen vor jede Spalte, damit die Datenbank weiß, wo sie suchen muss. Das liegt vor allem daran, dass es in beiden Tabellen eine id-Spalte gibt, aber wir wollen die id-Spalte nur in der author Tabelle. Durch den Verweis auf den Tabellennamen weiß die Datenbank, aus welcher Tabelle wir diese Spalte benötigen.

Ausgabe der Lösung:

Hier ist der Schnappschuss der Ausgabe. Wir haben all diese Daten durch die Verknüpfung zweier Tabellen erhalten:

nametitlepublish_year
Marcella ColeGone With The Wolves2005
Lisa MullinsCompanions And Officers1930
Dennis StokesBlacksmith With Silver1984
Randolph VasquezFaith Of Light1995
Michael RostkovskyWarrior Of Wind2005

Übung 2: Autoren und Bücher auflisten, die nach 2005 veröffentlicht wurden

Übung: Zeigen Sie den Namen jedes Autors zusammen mit dem Titel des Buches, das er geschrieben hat, und dem Jahr, in dem das Buch veröffentlicht wurde, an. Zeigen Sie nur Bücher, die nach 2005 veröffentlicht wurden.

Lösung:

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id
WHERE publish_year > 2005;

Erklärung der Lösung: Diese Aufgabe und ihre Lösung sind fast dieselben wie die vorherige. Dies zeigt sich darin, dass die Abfrage dieselben Spalten auswählt und die Tabellen auf dieselbe Weise verknüpft wie zuvor.

Der Unterschied besteht darin, dass in dieser Aufgabe nur Bücher angezeigt werden sollen, die nach 2005 veröffentlicht wurden. Dazu muss die Ausgabe gefiltert werden; dies geschieht mit der Klausel WHERE.

WHERE ist eine Klausel, die Bedingungen akzeptiert, um die Daten herauszufiltern. Sie wird nach der Verknüpfung der Tabellen geschrieben. In unserem Beispiel filtern wir, indem wir die Spalte publish_year nach WHERE referenzieren und den Vergleichsoperator "größer als" (>) verwenden, um die Jahre nach 2005 zu finden.

Ausgabe der Lösung:

Die Ausgabe zeigt nur ein Buch an, das nach 2005 veröffentlicht wurde.

nametitlepublish_year
Darlene LyonsTemptations In Nature2007

Übung 3: Bücher anzeigen, die innerhalb von 4 Jahren adaptiert wurden und eine niedrigere Bewertung als die Adaption haben

Aufgabe: Zeigen Sie für jedes Buch den Titel, den Titel der Adaption, das Jahr der Adaption und das Erscheinungsjahr an.

Geben Sie nur Bücher an, deren Bewertung niedriger ist als die der entsprechenden Adaption. Zeigen Sie außerdem nur die Bücher an, für die eine Adaption innerhalb von vier Jahren nach der Veröffentlichung des Buches veröffentlicht wurde.

Benennen Sie die Spalte title aus der Tabelle book Tabelle in book_title und die Spalte title aus der adaptation Tabelle in adaptation_title um.

Lösung:

SELECT
  book.title AS book_title,
  adaptation.title AS adaptation_title,
  book.publish_year,
  adaptation.release_year
FROM book
JOIN adaptation
  ON book.id = adaptation.book_id
WHERE adaptation.release_year - book.publish_year <= 4
  AND book.rating < adaptation.rating;

Erklärung der Lösung: Beginnen wir mit der Erläuterung der Lösung anhand der Klauseln FROM und JOIN. Die Spalten, die wir anzeigen müssen, sind aus den Tabellen book und adaptation. Wir verweisen auf die erste Tabelle in FROM und auf die zweite in JOIN.

In der ON -Klausel werden die beiden Buch-ID-Spalten gleichgesetzt und die Tabellen der einzelnen Spalten angegeben. Dies ist dasselbe wie zuvor, nur mit anderen Tabellen- und Spaltennamen.

Nun müssen wir die gewünschten Spalten auswählen. Die Sache ist die, dass es in beiden Tabellen eine Spalte title gibt. Um Mehrdeutigkeiten zu vermeiden, ist es am besten, den Tabellennamen vor jeder Spalte in SELECT zu nennen.

Hinweis: Der obige Hinweis ist nur für mehrdeutige Spalten obligatorisch. Es ist jedoch eine gute Idee, dies bei allen Spalten zu tun; es verbessert die Lesbarkeit des Codes und der Ansatz bleibt konsistent.

Nachdem wir die Spalten ausgewählt haben, müssen wir einige von ihnen umbenennen. Dazu verwenden wir das Schlüsselwort AS und schreiben danach einen neuen Spaltennamen. Auf diese Weise wird eine Spalte title zu book_title und die andere zu adaptation_title. Die Vergabe von Aliasen für die Spaltennamen hilft auch, Mehrdeutigkeiten zu vermeiden.

Nun müssen wir die Ausgabe filtern. Die erste Bedingung ist, dass die Verfilmung vier Jahre oder weniger nach dem Buch veröffentlicht werden muss. Auch hier verwenden wir WHERE und ziehen einfach das Erscheinungsjahr des Buches vom Erscheinungsjahr der Adaption ab. Dann sagen wir, dass die Differenz kleiner als oder gleich (<=) 4 sein muss.

Wir müssen auch die zweite Bedingung hinzufügen, bei der das Buch eine niedrigere Bewertung hat als die Verfilmung. Es ist ganz einfach! Die Frage impliziert, dass sowohl die erste als auch die zweite Bedingung erfüllt sein müssen. Der Hinweis liegt in AND, einem logischen Operator, den wir zum Hinzufügen der zweiten Bedingung verwenden. Hier wird der Operator "kleiner als" (<) verwendet, um die beiden Bewertungen zu vergleichen.

Ausgabe der Lösung:

Die Ausgabe zeigt drei Buch-Anpassungspaare, die die Bedingungen erfüllen.

book_titleadaptation_titlepublish_yearrelease_year
Memory Of HopePatrons And Bearers20002004
Music At The LakeMusic At The Lake20042007
Companion Of TomorrowLighting Faith19491952

Nachdem Sie nun das Wesentliche von INNER JOIN verstanden haben, kommen wir zu LEFT JOIN. Es handelt sich um eine Art von äußerer Verknüpfung, die alle Spalten aus der linken (ersten) Tabelle und nur die passenden Zeilen aus der rechten (zweiten) Tabelle zurückgibt. Wenn es nicht übereinstimmende Daten gibt, werden diese als NULL angezeigt.

Weitere Informationen finden Sie in unserem Artikel über LEFT JOIN.

Übung 4: Alle Bücher und ihre Verfilmungen anzeigen (falls vorhanden)

Übung: Zeigen Sie den Titel jedes Buches zusammen mit dem Titel seiner Adaption und dem Datum der Veröffentlichung an. Zeigen Sie alle Bücher, unabhängig davon, ob es Verfilmungen gab.

Lösung:

SELECT
  book.title,
  adaptation.title,
  adaptation.release_year
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id;

Erklärung der Lösung: Zunächst wählen wir die gewünschten Spalten aus den beiden Tabellen aus. Dann verbinden wir book (die linke Tabelle) mit adaptation (der rechten Tabelle) mit LEFT JOIN. Sie sehen, dass die SQL-Join-Syntax für INNER JOIN dieselbe ist. Das einzige, was sich ändert, ist das Schlüsselwort join.

Hinweis: SQL akzeptiert sowohl LEFT JOIN als auch LEFT OUTER JOIN. Es handelt sich um denselben Befehl.

Ausgabe der Lösung:

Der Ausgabeschnappschuss zeigt die erforderlichen Daten, wobei einige der Daten als NULL angezeigt werden. Dies sind die Bücher ohne die Anpassung.

titletitle-2release_year
Soulless girlGone With The Wolves: The Beginning2008
Faith Of LightCompanions Of Tomorrow2001
Warrior Of WindHomeless Warrior2008
Guarding The EmperorNULLNULL
Blacksmith With SilverNULLNULL

Übung 5: Alle Bücher und ihre Verfilmungen anzeigen

Übung: Zeigen Sie alle Bücher mit ihren Verfilmungen an. Wählen Sie für jedes Buch den Titel, den Namen des Verlags, den Titel der Verfilmung und die Art der Verfilmung aus. Behalten Sie die Bücher ohne Verfilmungen im Ergebnis.

Lösung:

SELECT
  book.title,
  publishing_house,
  adaptation.title,
  adaptation.type
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id
WHERE type = 'movie'
  OR type IS NULL;

Erklärung der Lösung:

Die Frage verlangt, dass alle Zeilen angezeigt werden, auch die ohne Verfilmungen. Es ist möglich, dass es Bücher ohne Bearbeitungen gibt, daher verwenden wir LEFT JOIN.

Wir wählen zunächst den Buchtitel, den Verlag, den Titel der Adaption und den Buchtyp aus.

Dann verbinden wir book (die linke Tabelle) mit adaptation (der rechten Tabelle) mit LEFT JOIN. Wir verknüpfen die Tabellen anhand der Buch-ID. Alle Bücher, die die Bedingungen nicht erfüllen, haben NULLals Titel und Typ der Verfilmung.

Wir filtern die Daten mit WHERE. Die erste Bedingung ist, dass es sich bei der Art der Verfilmung um einen Film handeln muss. Daher wird die Spalte "Typ" mit einem Film gleichgesetzt, indem das Gleichheitszeichen verwendet wird (=). Hinweis: Wenn Textdaten in der Bedingung WHERE verwendet werden, müssen sie in einfache Anführungszeichen gesetzt werden ('').

Die zweite Filterbedingung wird mit dem logischen Operator OR hinzugefügt. Sie besagt, dass der Typ auch NULL sein kann, wenn es sich nicht um einen Film handelt. In der Übung werden wir aufgefordert, Bücher ohne Verfilmungen in den Ergebnissen zu behalten.

Ausgabe der Lösung:

Hier ist der Schnappschuss der Ausgabe. Sie können sehen, dass nur Bücher angezeigt werden, die als Film adaptiert wurden oder überhaupt nicht adaptiert wurden.

titlepublishing_housetitle-2type
Soulless girlGolden AlbatrosGone With The Wolves: The Beginningmovie
Faith Of LightWhite Cloud PressCompanions Of Tomorrowmovie
Warrior Of WindMaverickHomeless Warriormovie
Guarding The EmperorFlying Pen MediaNULLNULL
Blacksmith With SilverDiarmud Inc.NULLNULL

RECHTSVERBINDUNG

Wo es LEFT JOIN gibt, gibt es auch RIGHT JOIN, richtig? Obwohl der RIGHT JOIN das Spiegelbild des LEFT JOIN ist, ist er immer noch ein Teil der SQL-Join-Praxis.

Es handelt sich um eine Art der Verknüpfung, die alle Spalten der rechten (zweiten) Tabelle und nur die passenden Zeilen der linken (ersten) Tabelle zurückgibt. Wenn es nicht übereinstimmende Daten gibt, werden diese als NULL angezeigt.

Übung 6: Alle Bücher mit ihren Rezensionen anzeigen (falls vorhanden)

Übung: Verbinden Sie die Tabellen book_review und book mit RIGHT JOIN. Zeigen Sie den Titel des Buches, die entsprechende Rezension und den Namen des Autors der Rezension. Berücksichtigen Sie alle Bücher, auch die, die nicht rezensiert wurden.

Lösung:

SELECT
  book.title,
  book_review.review,
  book_review.author
FROM book_review
RIGHT JOIN book
  ON book.id = book_review.book_id;

Erklärung der Lösung:

Zuerst wählen wir die gewünschten Spalten aus. Dann tun wir, was man uns sagt: Wir verknüpfen die Tabellen mit RIGHT JOIN. Wir verknüpfen die Tabellen über die Buch-ID. Die Tabelle book ist die richtige Tabelle; wir wollen alle Daten aus ihr, unabhängig von den Rezensionen.

Wie Sie sehen, bleibt die Syntax dieselbe wie in INNER JOIN und LEFT JOIN.

Hinweis: SQL akzeptiert sowohl RIGHT JOIN als auch RIGHT OUTER JOIN.

Ausgabe der Lösung:

Die Abfrage gibt alle Buchtitel, ihre Rezensionen und Autoren zurück. Wo es keine Informationen zu Rezensionen oder Autoren gibt, wird NULL angezeigt.

titlereviewauthor
Soulless girlAn incredible bookSylvia Jones
Soulless girlGreat, although it has some flawsJessica Parker
Guarding The EmperorNULLNULL
Companions And OfficersNULLNULL
Blacksmith With SilverNULLNULL

VOLLER JOIN

Hier ist ein weiterer Verknüpfungstyp, der in einigen Szenarien nützlich ist: FULL JOIN. Dies ist eine Kombination aus LEFT JOIN und RIGHT JOIN. Er zeigt übereinstimmende Zeilen aus beiden Tabellen, Zeilen ohne Übereinstimmung in der linken Tabelle und Zeilen ohne Übereinstimmung in der rechten Tabelle. Kurz gesagt, es werden alle Daten aus beiden Tabellen angezeigt.

Sie können mehr darüber lesen, wie und wann Sie FULL JOIN verwenden.

Übung 7: Alle Bücher und alle Autoren auflisten

Übung: Zeigen Sie den Titel eines jeden Buches zusammen mit dem Namen des Autors an. Zeigen Sie alle Bücher an, auch die ohne Autor. Zeigen Sie alle Autoren an, auch die, die noch kein Buch veröffentlicht haben. Verwenden Sie eine FULL JOIN.

Lösung:

SELECT
  title,
  name
FROM book
FULL JOIN author
  ON book.author_id = author.id;

Erklärung der Lösung: Die Frage erfordert die Anzeige aller Bücher, aber auch aller Autoren - FULL JOIN eignet sich perfekt, um dies auf elegante Weise zu tun.

Wir wählen den Buchtitel und den Namen des Autors aus. Dann wird die Tabelle FULL JOIN book mit der Tabelle author. Die Verknüpfungsbedingung ist, dass die Autoren-ID in beiden Tabellen gleich sein muss. Auch hier ist die Syntax dieselbe wie bei allen vorherigen Join-Typen.

Hinweis: SQL akzeptiert sowohl FULL JOIN als auch FULL OUTER JOIN.

Ausgabe der Lösung:

Die Ausgabe zeigt alle Bücher und alle Autoren an, unabhängig davon, ob die Autoren oder Bücher in beiden Tabellen vorhanden sind oder nicht.

titlename
Gone With The WolvesMarcella Cole
Companions And OfficersLisa Mullins
NULLDaniel Branson
Weep Of The WestNULL

Verknüpfung von 3 oder mehr Tabellen

Ja, SQL-Joins erlauben das Verbinden von mehr als zwei Tabellen. Wie das geht, sehen wir in diesem Teil der SQL-Joins-Praxis. Eine ausführlichere Erklärung von Mehrfach-Joins finden Sie hier.

Außerdem benötigen wir ein neues Dataset, das wir hier vorstellen wollen.

Dataset 2

Die erste Tabelle im Dataset ist department. Ihre Spalten sind:

  • id - Die eindeutige ID der Abteilung.
  • name - Der Name der Abteilung, d.h. wo eine bestimmte Art von Produkt verkauft wird.

Hier sind die Daten aus der Tabelle.

idname
1fruits
2vegetables
3seafood
4deli
5bakery
6meat
7dairy

Die zweite Tabelle ist productund besteht aus den folgenden Spalten:

  • id - Die ID eines bestimmten Produkts.
  • name - Der Name des Produkts.
  • department_id - Die ID der Abteilung, in der sich das Produkt befindet.
  • shelf_id - Die Kennung des Regals dieser Abteilung, in dem sich das Produkt befindet.
  • producer_id - Die ID des Unternehmens, das dieses Produkt herstellt.
  • price - Der Preis des Produkts.

Das ist der Schnappschuss der Daten:

idnamedepartment_idshelf_idproducer_idprice
1Apple11NULL0.5
2Avocado1171
3Banana1170.5
4GrapefruitNULL110.5
5Grapes1142

Die nächste Tabelle lautet nutrition_data. Ihre Spalten und Daten sind unten angegeben:

  • product_id - Die ID eines Produkts.
  • calories - Der Brennwert des Produkts.
  • fat - Die Menge an Fett in diesem Produkt.
  • carbohydrate - Die Menge an Kohlenhydraten in diesem Produkt.
  • protein - Die Menge an Eiweiß in diesem Produkt.
product_idcaloriesfatcarbohydrateprotein
1130051
2504.531
31100301
4600151
NULL900230

Die vierte Tabelle heißt producer. Sie hat die folgenden Spalten:

  • id - Die ID eines bestimmten Lebensmittelherstellers.
  • name - Der Name des Herstellers.

Nachfolgend sind die Daten dieser Tabelle aufgeführt:

idname
1BeHealthy
2HealthyFood Inc.
3SupremeFoods
4Foodie
5Gusto
6Baker n Sons
7GoodFoods
8Tasty n Healthy

Die letzte Tabelle im Datensatz ist sales_history. Sie enthält die folgenden Spalten:

  • date - Das Datum des Verkaufs.
  • product_id - Die ID des verkauften Produkts.
  • amount - Die Menge dieses Produkts, die an einem bestimmten Tag verkauft wurde.

Das sind auch die Daten:

dateproduct_idamount
2015-01-14114
2015-01-14113
2015-01-1522
2015-01-1626
2015-01-1738

Übung 8: Produkte unter 150 Kalorien und ihre Abteilung anzeigen

Übung: Listen Sie alle Produkte auf, die weniger als 150 Kalorien haben. Geben Sie für jedes Produkt den Namen (benennen Sie die Spalte product) und den Namen der Abteilung an, in der es zu finden ist (benennen Sie die Spalte department).

Lösung:

SELECT
  p.name AS product,
  d.name AS department
FROM department d
JOIN product p
  ON d.id = p.department_id
JOIN nutrition_data nd
  ON nd.product_id = p.id
WHERE nd.calories < 150;

Erklärung der Lösung: Das allgemeine Prinzip, wie Sie die dritte (vierte, fünfte...) Tabelle verbinden, ist, dass Sie einfach eine weitere JOIN hinzufügen. Wie das geht, können Sie in diesem Artikel über Mehrfachverknüpfungen nachlesen. Wir werden hier genauso vorgehen.

Wir verbinden zuerst die department Tabelle mit der product Tabelle über die Abteilungs-ID mit JOIN. Aber wir brauchen auch die dritte Tabelle. Um die Daten aus ihr zu erhalten, fügen wir einfach eine weitere JOIN hinzu, die die product Tabelle mit der nutrition_data Tabelle verbindet. Die Syntax ist die gleiche wie bei der ersten Verknüpfung. In diesem Fall verknüpft die Abfrage die Tabellen anhand der Produkt-ID.

Dann verwenden wir WHERE, um Produkte mit weniger als 150 Kalorien zu finden. Schließlich wählen wir die Produkt- und Abteilungsnamen aus und benennen die Spalten gemäß den Übungsanweisungen um.

Hinweis: Sie haben wahrscheinlich bemerkt, dass beide ausgewählten Spalten denselben ursprünglichen Namen haben. Und Sie haben auch bemerkt, dass wir diese Mehrdeutigkeit gelöst haben, indem wir allen Spalten in der Abfrage einige seltsame kurze Tabellennamen vorangestellt haben. Diese verkürzten Namen sind Tabellen-Aliase, die Sie einfach nach dem Tabellennamen in FROM oder JOIN eingeben. Indem Sie den Tabellen Aliase geben, können Sie die Tabellennamen verkürzen. Sie müssen also nicht die vollständigen Namen schreiben (die manchmal sehr lang sein können!), sondern nur die kurzen Aliasnamen. Das spart Zeit und Platz.

Ausgabe der Lösung:

Die Ausgabe zeigt eine Liste der Produkte und der Abteilung, zu der sie gehören. Sie enthält nur die Produkte mit weniger als 150 Kalorien.

productdepartment
Applefruits
Avocadofruits
Bananafruits
Kiwifruits
Lemonfruits

Übung 9: Alle Produkte mit ihren Herstellern, Abteilungen und Kohlenhydraten auflisten

Übung: Zeigen Sie für jedes Produkt Folgendes an:

  • Name des Unternehmens, das das Produkt hergestellt hat (nennen Sie die Spalte producer_name).
  • Name der Abteilung, in der sich das Produkt befindet (benennen Sie sie department_name).
  • Den Namen des Produkts (nennen Sie ihn product_name).
  • Gesamtzahl der Kohlenhydrate im Produkt.

Ihre Abfrage sollte auch Produkte berücksichtigen, die keine Informationen über producer_id oder department_id enthalten.

Lösung:


SELECT
  prod.name AS producer_name,
  d.name AS department_name,
  p.name AS product_name,
  nd.carbohydrate
FROM product p
LEFT JOIN producer prod
  ON prod.id = p.producer_id
LEFT JOIN department d
  ON d.id = p.department_id
LEFT JOIN nutrition_data nd
  ON nd.product_id = p.id;

Erklärung der Lösung: Die Abfrage wählt die erforderlichen Spalten aus. Dann verknüpft sie die Tabelle product mit der Tabelle producer über die Hersteller-ID mit LEFT JOIN. Wir wählen diese Art der Verknüpfung, weil wir Produkte ohne Herstellerdaten einbeziehen müssen.

Dann fügen wir eine weitere LEFT JOIN hinzu. Dieser fügt die department Tabelle hinzu und verknüpft sie mit der product Tabelle. Auch hier wählen wir LEFT JOIN, weil wir Produkte anzeigen müssen, die keine Abteilung haben.

Es gibt auch eine dritte Verknüpfung! Wir fügen ihn einfach zur Kette der vorherigen Verknüpfungen hinzu. Es ist wieder LEFT JOIN, denn wir fügen die nutrition_data Tabelle hinzu und verknüpfen sie mit der product Tabelle.

Dies ist ein interessantes Thema, das es zu erforschen gilt, daher finden Sie hier einen Artikel, der mehrere LEFT JOINs erklärt, um Ihnen dabei zu helfen.

Ausgabe der Lösung:

Die Ausgabe zeigt alle Produkte mit ihren Hersteller- und Abteilungsnamen und den Kohlenhydratmengen:

producer_namedepartment_nameproduct_namecarbohydrate
BeHealthyfruitsKiwi20
BeHealthyvegetablesBroccoli8
BeHealthymeatChickenNULL
BeHealthyNULLGrapefruit15
HealthyFood Inc.vegetablesCelery4

Wenn Sie mehr Details benötigen, lesen Sie bitte , wie man mehrere Tabellen in SQL LEFT JOIN.

Übung 10: Alle Produkte, Preise, Hersteller und Abteilungen anzeigen

Übung: Zeigen Sie für jedes Produkt den Namen, den Preis, den Namen des Herstellers und den Namen der Abteilung an.

Benennen Sie die Spalten als product_name, product_price, producer_name und department_name. Geben Sie alle Produkte an, auch die ohne Hersteller oder Abteilung. Nehmen Sie auch die Hersteller und Abteilungen ohne Produkt auf.

Lösung:

SELECT
  p.name AS product_name,
  p.price AS product_price,
  prod.name AS producer_name,
  d.name AS department_name
FROM product p
FULL JOIN producer prod
  ON p.producer_id = prod.id
FULL JOIN department d
  ON d.id = p.department_id;

Erklärung der Lösung: Diese Übung erfordert die Verwendung von FULL JOIN, da wir alle Daten aus den Tabellen benötigen, die wir verwenden werden: product, producer, und department.

Die Syntax ist dieselbe wie in den vorherigen Beispielen. Wir verbinden einfach die verschiedenen Tabellen (product und producer) über die Hersteller-ID und verwenden eine andere Art der Verknüpfung: FULL JOIN.

Die zweite FULL JOIN verknüpft die product Tabelle mit der department Tabelle.

Nach Auswahl der erforderlichen Spalten und deren Umbenennung erhalten wir die folgende Ausgabe.

Ausgabe der Lösung:

Die Lösung zeigt alle Daten aus den ausgewählten Tabellen und Spalten:

product_nameproduct_priceproducer_namedepartment_name
Chicken5.5BeHealthymeat
Broccoli2.5BeHealthyvegetables
Kiwi0.3BeHealthyfruits
Grapefruit0.5BeHealthyNULL
Cucumber0.7HealthyFood Inc.vegetables

Self-Join

Ein Self-Join ist kein eigener Typ von SQL JOIN - jeder Join kann für den Self-Join einer Tabelle verwendet werden. Es handelt sich einfach um einen Join, der dazu dient, die Tabelle mit sich selbst zu verbinden. Durch die Vergabe verschiedener Aliasnamen für dieselbe Tabelle wird diese beim Self-Join als zwei verschiedene Tabellen behandelt.

Weitere Einzelheiten finden Sie in unserer illustrierten Anleitung zum SQL-Self-Join.

Dataset 3

Das Dataset für dieses Beispiel besteht aus nur einer Tabelle: workshop_workers. Sie hat die folgenden Spalten.

  • id - Die ID des Arbeiters.
  • name - Vor- und Nachname des Arbeiters.
  • specialization - Die Spezialisierung des Arbeiters.
  • master_id - Die ID des Vorgesetzten des Mitarbeiters.
  • experience - Die Jahre der Erfahrung des Mitarbeiters.
  • project_id - Die ID des Projekts, dem der Mitarbeiter derzeit zugewiesen ist.

Hier sind die Daten:

idnamespecializationmaster_idexperienceproject_id
1Mathew ConnwoodworkingNULL201
2Kate Brownwoodworking141
3John Doeincrusting531
4John Kowalskywatchmaking723
5Suzan GregowitchincrustingNULL154

Übung 11: Alle Mitarbeiter und ihre direkten Vorgesetzten auflisten

Übung: Zeigen Sie die Namen aller Arbeiter zusammen mit den Namen ihrer direkten Vorgesetzten an. Benennen Sie die Spalten apprentice_name bzw. master_name um. Betrachten Sie nur die Arbeiter, die einen Vorgesetzten (d.h. einen Meister) haben.

Lösung:

SELECT
  apprentice.name AS apprentice_name,
  master.name AS master_name
FROM workshop_workers apprentice
JOIN workshop_workers master
  ON apprentice.master_id = master.id;

Erklärung der Lösung: Beginnen wir mit der Erläuterung des Self-Join. Das allgemeine Prinzip ist das gleiche wie bei regulären Joins. Wir verweisen auf die Tabelle in FROM und geben ihr einen Alias, apprentice. Dann verwenden wir JOIN und verweisen dort auf dieselbe Tabelle. Dieses Mal geben wir der Tabelle den Alias master. Im Grunde genommen tun wir so, als ob eine Tabelle die Daten der Auszubildenden und die andere die Stammdaten enthält.

Die Tabellen werden über die Stamm-ID aus der Tabelle apprentice Tabelle und der ID aus der master Tabelle.

Dieses Beispiel ist eine typische Anwendung eines Self-Join: Die Tabelle hat eine Spalte (master_id), die auf eine andere Spalte aus derselben Tabelle (id) verweist. Beide Spalten enthalten die ID des Arbeiters. Wenn NULL in master_id vorkommt, bedeutet dies, dass der Arbeiter keinen Master hat. Mit anderen Worten, er ist der Master.

Nach dem Self-Join wählen wir einfach die gewünschten Spalten aus und benennen sie um.

Ausgabe der Lösung:

Die Ausgabe zeigt alle Auszubildenden und deren direkte Vorgesetzte.

apprentice_namemaster_name
Kate BrownMathew Conn
John DoeSuzan Gregowitch
John KowalskyJoe Darrington
Peter ParkerJoe Darrington
Mary SmithMathew Conn
Carlos BellSuzan Gregowitch
Dennis WrightJoe Darrington

Nicht-Equi Joins

Das letzte Thema, mit dem wir uns in dieser Übung zu SQL-Joins befassen, sind Non-Equi-Joins. Die bisher verwendeten Joins werden als Equi-Joins bezeichnet, weil sie das Gleichheitszeichen (=) in der Verknüpfungsbedingung verwenden. Nicht-Gleichheits-Joins sind alle anderen Joins, die andere Operatoren - Vergleichsoperatoren (<, >, <=, >=, !=, <>), den Operator BETWEEN oder eine andere logische Bedingung - verwenden, um Tabellen zu verbinden.

Dataset 4

Wir verwenden einen Datensatz, der aus zwei Tabellen besteht. Die erste Tabelle ist car. Hier sind ihre Spalten:

  • id - Die ID des Fahrzeugs in der Datenbank.
  • model - Das Modell des Fahrzeugs.
  • brand - Die Marke des Fahrzeugs.
  • original_price - Der ursprüngliche Neupreis des Fahrzeugs.
  • mileage - Der Gesamtkilometerstand des Fahrzeugs.
  • prod_year - Das Produktionsjahr des Fahrzeugs.

Die Daten sehen wie folgt aus:

idmodelbrandoriginal_pricemileageprod_year
1SpeedsterTeiko80,000150,0001999
2RoadmasterTeiko110,00030,0001980
3SundryTeiko40,00025,0001991
4FuruDomus50,00010,0002002
5EmperorDomus65,000140,0002005
6KingDomus200,0006,0001981
7EmpressDomus60,0007,6001997
8FuryTatsu150,00013,0001993

Die zweite Tabelle ist charity_auction mit diesen Spalten:

  • car_id - Die ID des Fahrzeugs.
  • initial_price - Der ursprüngliche (d.h. anfängliche) Preis des Fahrzeugs.
  • final_price - Der tatsächliche Preis beim Verkauf des Fahrzeugs.
  • buyer_id - Die ID der Person, die das Auto gekauft hat.

Hier sind die Daten:

car_idinitial_pricefinal_pricebuyer_id
165,000NULLNULL
335,00050,0001
550,000120,0003
6350,000410,0004
765,000NULLNULL

Übung 12: Autos mit höherem Kilometerstand anzeigen als ein bestimmtes Auto

Übung: Zeigen Sie das Modell, die Marke und den Endpreis jedes Autos, das auf der Auktion verkauft wurde. Betrachten Sie nur die verkauften Autos, die einen höheren Kilometerstand haben als das Auto mit der Adresse id = 4.

Lösung:

SELECT
  car.model,
  car.brand,
  car.final_price
FROM car
JOIN charity_auction ca
  ON car.id = ca.car_id
JOIN car car2
  ON car.mileage > car2.mileage
WHERE car2.id = 4
  AND final_price IS NOT NULL;

Erklärung der Lösung: Wir wählen das Automodell, die Marke und den Endpreis.

In der ersten JOIN verbinden wir die car Tabelle mit der charity_auction Tabelle. Die Tabellen werden verbunden, wenn die Fahrzeug-IDs gleich sind. Dies ist unsere reguläre Equi JOIN.

Wir fügen den zweiten JOIN hinzu, der ein Self-Join ist. Er fügt die Tabelle car hinzu, so dass wir die Daten mithilfe der Nicht-Äqui-Join-Bedingung filtern können. Die Bedingung gibt alle Fahrzeuge aus der Tabelle car Tabelle und alle Fahrzeuge aus der Tabelle car2 Tabelle mit dem niedrigeren Kilometerstand. Dies ist eine Nicht-Gleichheitsbedingung, da sie den Operator "größer als" ( > ) verwendet. Die Syntax ist dieselbe, nur dass diesmal > anstelle von = steht.

Schließlich müssen wir die Daten mit WHERE filtern. Wir sind nicht daran interessiert, den Kilometerstand aller Autos zu vergleichen. Wir wollen die Autos anzeigen, die einen höheren Kilometerstand haben als das Auto mit id = 4. Dies wird durch die erste Filterbedingung erreicht.

Wir fügen eine weitere Filterbedingung hinzu, die besagt, dass der Endpreis nicht NULL sein darf, d. h. das Auto muss in der Auktion verkauft worden sein.

Ausgabe der Lösung:

Das Ergebnis zeigt zwei Autos an:

modelbrandfinal_price
SundryTeiko50,000
EmperorDomus120,000

SQL-JOINs Übung macht den Meister. Mehr Übung? Noch perfekter!

Zwölf SQL-Join-Übungen sind eine solide Menge an Übung. Mithilfe dieser Übungen konnten Sie die häufigsten Join-Themen lernen und üben, die Anfängern und fortgeschrittenen Benutzern Schwierigkeiten bereiten.

Jetzt müssen Sie nur noch weitermachen! Wenn Sie noch mehr üben, werden Sie noch perfekter. Wenn Ihnen also unsere Übungen gefallen haben, können Sie noch mehr davon in unserem SQL JOINS-Kurs oder im Artikel über die SQL JOIN-Interviewfragen finden.

Ich hoffe, dass Sie alle Übungen, die dort auf Sie warten, mit Bravour meistern!