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:

• Übung 1: Auflisten aller Bücher und ihrer Autoren
• Übung 2: Autoren und Bücher auflisten, die nach 2005 veröffentlicht wurden
• Übung 3: Zeige Bücher, die innerhalb von 4 Jahren adaptiert wurden und eine niedrigere Bewertung als die Adaption haben
• Übung 4: Zeige alle Bücher und ihre Adaptionen (falls vorhanden)
• Übung 5: Alle Bücher und ihre Verfilmungen anzeigen
• Übung 6: Alle Bücher mit ihren Rezensionen anzeigen (falls vorhanden)
• Übung 7: Alle Bücher und alle Autoren auflisten
• Übung 8: Produkte unter 150 Kalorien und ihre Abteilung anzeigen
• Übung 9: Alle Produkte mit ihren Herstellern, Abteilungen und Kohlenhydraten auflisten
• Übung 10: Zeigen Sie alle Produkte, Preise, Hersteller und Abteilungen
• Übung 11: Alle Mitarbeiter und deren direkte Vorgesetzte auflisten
• Übung 12: Autos mit höherem Kilometerstand als ein bestimmtes Auto anzeigen

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:

id	name	birth_year	death_year
1	Marcella Cole	1983	NULL
2	Lisa Mullins	1891	1950
3	Dennis Stokes	1935	1994
4	Randolph Vasquez	1957	2004
5	Daniel Branson	1965	1990
…	…	…	…

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:

id	author_id	title	publish_year	publishing_house	rating
1	NULL	Soulless girl	2008	Golden Albatros	4.3
2	NULL	Weak Heart	1980	Diarmud Inc.	3.8
3	4	Faith Of Light	1995	White Cloud Press	4.3
4	NULL	Memory Of Hope	2000	Rutis Enterprises	2.7
5	6	Warrior Of Wind	2005	Maverick	4.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_id	type	title	release_year	rating
1	movie	Gone With The Wolves: The Beginning	2008	3
3	movie	Companions Of Tomorrow	2001	4.2
5	movie	Homeless Warrior	2008	4
2	movie	Blacksmith With Silver	2014	4.3
4	movie	Patrons And Bearers	2004	3.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_id	review	author
1	An incredible book	Sylvia Jones
1	Great, although it has some flaws	Jessica Parker
2	Dennis Stokes takes the reader for a ride full of emotions	Thomas Green
3	Incredible craftsmanship of the author	Martin Freeman
4	Not the best book by this author	Jude Falth
5	Claudia Johnson at her best!	Joe Marqiz
6	I cannot recall more captivating plot	Alexander 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:

name	title	publish_year
Marcella Cole	Gone With The Wolves	2005
Lisa Mullins	Companions And Officers	1930
Dennis Stokes	Blacksmith With Silver	1984
Randolph Vasquez	Faith Of Light	1995
Michael Rostkovsky	Warrior Of Wind	2005
…	…	…

Ü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.

name	title	publish_year
Darlene Lyons	Temptations In Nature	2007

Ü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_title	adaptation_title	publish_year	release_year
Memory Of Hope	Patrons And Bearers	2000	2004
Music At The Lake	Music At The Lake	2004	2007
Companion Of Tomorrow	Lighting Faith	1949	1952

LINKS JOIN

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.

title	title-2	release_year
Soulless girl	Gone With The Wolves: The Beginning	2008
Faith Of Light	Companions Of Tomorrow	2001
Warrior Of Wind	Homeless Warrior	2008
…	…	…
Guarding The Emperor	NULL	NULL
Blacksmith With Silver	NULL	NULL
…	…	…

Ü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.

title	publishing_house	title-2	type
Soulless girl	Golden Albatros	Gone With The Wolves: The Beginning	movie
Faith Of Light	White Cloud Press	Companions Of Tomorrow	movie
Warrior Of Wind	Maverick	Homeless Warrior	movie
…	…	…	…
Guarding The Emperor	Flying Pen Media	NULL	NULL
Blacksmith With Silver	Diarmud Inc.	NULL	NULL

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.

title	review	author
Soulless girl	An incredible book	Sylvia Jones
Soulless girl	Great, although it has some flaws	Jessica Parker
…	…	…
Guarding The Emperor	NULL	NULL
Companions And Officers	NULL	NULL
Blacksmith With Silver	NULL	NULL
…	…	…

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.

title	name
Gone With The Wolves	Marcella Cole
Companions And Officers	Lisa Mullins
…	…
NULL	Daniel Branson
…	…
Weep Of The West	NULL

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.

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

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:

id	name	department_id	shelf_id	producer_id	price
1	Apple	1	1	NULL	0.5
2	Avocado	1	1	7	1
3	Banana	1	1	7	0.5
4	Grapefruit	NULL	1	1	0.5
5	Grapes	1	1	4	2
…	…	…	…	…	…

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_id	calories	fat	carbohydrate	protein
1	130	0	5	1
2	50	4.5	3	1
3	110	0	30	1
4	60	0	15	1
NULL	90	0	23	0
…	…	…	…	…

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:

id	name
1	BeHealthy
2	HealthyFood Inc.
3	SupremeFoods
4	Foodie
5	Gusto
6	Baker n Sons
7	GoodFoods
8	Tasty 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:

date	product_id	amount
2015-01-14	1	14
2015-01-14	1	13
2015-01-15	2	2
2015-01-16	2	6
2015-01-17	3	8
…	…	…

Ü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.

product	department
Apple	fruits
Avocado	fruits
Banana	fruits
Kiwi	fruits
Lemon	fruits
…	…

Ü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_name	department_name	product_name	carbohydrate
BeHealthy	fruits	Kiwi	20
BeHealthy	vegetables	Broccoli	8
BeHealthy	meat	Chicken	NULL
BeHealthy	NULL	Grapefruit	15
HealthyFood Inc.	vegetables	Celery	4
…	…	…	…

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_name	product_price	producer_name	department_name
Chicken	5.5	BeHealthy	meat
Broccoli	2.5	BeHealthy	vegetables
Kiwi	0.3	BeHealthy	fruits
Grapefruit	0.5	BeHealthy	NULL
Cucumber	0.7	HealthyFood 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:

id	name	specialization	master_id	experience	project_id
1	Mathew Conn	woodworking	NULL	20	1
2	Kate Brown	woodworking	1	4	1
3	John Doe	incrusting	5	3	1
4	John Kowalsky	watchmaking	7	2	3
5	Suzan Gregowitch	incrusting	NULL	15	4

Ü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_name	master_name
Kate Brown	Mathew Conn
John Doe	Suzan Gregowitch
John Kowalsky	Joe Darrington
Peter Parker	Joe Darrington
Mary Smith	Mathew Conn
Carlos Bell	Suzan Gregowitch
Dennis Wright	Joe 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:

id	model	brand	original_price	mileage	prod_year
1	Speedster	Teiko	80,000	150,000	1999
2	Roadmaster	Teiko	110,000	30,000	1980
3	Sundry	Teiko	40,000	25,000	1991
4	Furu	Domus	50,000	10,000	2002
5	Emperor	Domus	65,000	140,000	2005
6	King	Domus	200,000	6,000	1981
7	Empress	Domus	60,000	7,600	1997
8	Fury	Tatsu	150,000	13,000	1993

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_id	initial_price	final_price	buyer_id
1	65,000	NULL	NULL
3	35,000	50,000	1
5	50,000	120,000	3
6	350,000	410,000	4
7	65,000	NULL	NULL

Ü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:

model	brand	final_price
Sundry	Teiko	50,000
Emperor	Domus	120,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!