Top 27 Interviewfragen für fortgeschrittenes SQL mit Antworten

Wo kann ein SQL-Experte einen umfassenden Leitfaden für fortgeschrittene SQL-Interview-Fragen finden? Die kürzeste Antwort lautet: hier! Wir haben die 27 wichtigsten SQL-Fragen ausgewählt und sie für Sie beantwortet.

Die Vorbereitung auf ein SQL-Vorstellungsgespräch ist nicht einfach, vor allem, wenn Ihr Job fortgeschrittene SQL-Kenntnisse erfordert. Dieser Artikel enthält die 27 am häufigsten gestellten Fragen zu fortgeschrittenen SQL-Interviews sowie ausführliche Antworten und weiterführende Informationen.

Wir werden diese vier Hauptkonzepte und einige weitere durchgehen:

• JOINs
• GROUP BY, WHERE, und HAVING
• CTEs (Common Table Expressions) und rekursive Abfragen
• Fensterfunktionen

Am besten frischen Sie Ihre fortgeschrittenen SQL-Kenntnisse auf, indem Sie an unserem interaktiven Fortgeschrittenes SQL Kurs. Er enthält über 300 praktische Übungen zu Fensterfunktionen, Common Table Expressions, rekursiven Funktionen und mehr.

Lassen Sie uns diese Fragen ohne Umschweife frontal angehen!

1. was ist ein JOIN in SQL?

JOIN ist ein SQL-Befehl, mit dem Sie zwei oder mehr Tabellen miteinander verbinden können. Dies geschieht über eine gemeinsame Spalte (d. h. eine Spalte, die in beiden Tabellen die gleichen Werte hat), die es ermöglicht, Daten aus zwei oder mehr Tabellen gleichzeitig zu verwenden. Die Verknüpfung von Tabellen in SQL ist aufgrund der Beschaffenheit relationaler Datenbanken unerlässlich: Daten werden in Tabellen atomisiert, wobei jede Tabelle nur einen Teil der in der Datenbank verfügbaren Daten enthält.

Wir werden zwei Tabellen verwenden, um zu zeigen, wie das funktioniert. Die erste Tabelle ist football_players.

id	first_name	last_name	national_team_id	games_played
1	Gianfranco	Zola	1	35
2	Virgil	van Dijk	2	53
3	Marcus	Rashford	3	51
4	Kylian	Mbappé	5	66
5	Phil	Foden	3	22
6	Frenkie	de Jong	2	22
7	Mario	Balotelli	1	36
8	Erling	Haaland	6	23

Die zweite ist national_team.

id	country
1	Italy
2	Netherlands
3	England
4	Croatia

Hier ist eine Abfrage, die zwei Tabellen miteinander verbindet:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Sie wählt Spalten aus beiden Tabellen aus. Um sie zu verbinden, verweisen wir zuerst auf eine Tabelle in der FROM Klausel. Danach folgt JOIN, und danach kommt die zweite Tabelle. Mit der Klausel ON geben wir die Bedingung an, mit der die Tabellen verbunden werden: national_team_id in der Tabelle football_players Tabelle muss gleich der Spalte id in der national_team Tabelle.

Die Ausgabe der Abfrage lautet:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

INNER JOIN ist eine der zahlreichen eindeutigen Verknüpfungen in SQL. Ihr Merkmal ist, dass sie nur Daten aus den verbundenen Tabellen zurückgibt, bei denen die Verbindungsbedingung wahr ist. Hier finden Sie weitere Einzelheiten über die Funktionsweise des SQL INNER JOIN.

2) Was ist der Unterschied zwischen INNER JOIN, LEFT JOIN, RIGHT JOIN und FULL JOIN?

Es gibt verschiedene Arten von Joins in SQL. Die am häufigsten verwendeten Joins sind INNER JOIN, LEFT JOIN, RIGHT JOIN und FULL JOIN. LEFT JOIN, RIGHT JOIN und FULL JOIN sind so genannte äußere Joins. JOIN (auch bekannt als INNER JOIN) ist ein innerer Join. In diesem Fall bedeutet "inner", dass nur die Zeilen aus beiden Tabellen zurückgegeben werden, die die Join-Bedingung erfüllen; bei "outer joins" werden alle Zeilen in einer Tabelle und die passenden Zeilen in der/den anderen Tabelle(n) zurückgegeben. Die Ausnahme ist FULL JOIN, die alle Zeilen aus beiden Tabellen zurückgibt.

Hier ist das Ergebnis von INNER JOIN aus dem vorherigen Beispiel. Lassen Sie es uns hier noch einmal sehen. Auf diese Weise wird es einfacher, den Unterschied zwischen den verschiedenen Joins zu erkennen.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

LEFT JOIN gibt alle Daten aus der linken Tabelle (d. h. der ersten Tabelle, die vor\links vom Schlüsselwort JOIN aufgeführt ist) und nur die übereinstimmenden Zeilen aus der rechten Tabelle (der zweiten Tabelle, die nach\rechts vom Schlüsselwort JOIN aufgeführt ist) zurück. Wenn es in der rechten Tabelle keine übereinstimmenden Daten gibt, werden die fehlenden Werte als NULLs angezeigt. Hier ist die gleiche Abfrage mit LEFT JOIN als Ersatz für INNER JOIN:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
LEFT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Die linke Tabelle ist hier football_players, und die rechte Tabelle ist national_team. Wie zu erwarten war, ist die Ausgabe unterschiedlich:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23

Alle Fußballspieler aus der linken Tabelle sind hier zu finden. Allerdings haben Kylian Mbappe und Erling Haaland kein passendes Land in der rechten Tabelle, daher gibt es NULLs in der Spalte country für diese Spieler. Diese Zeilen waren im Ergebnis von INNER JOIN nicht vorhanden. Sie wurden durch die LEFT JOIN hinzugefügt.

RIGHT JOIN macht das Gegenteil: Es gibt alle Daten aus der rechten Tabelle und nur die passenden Daten aus der linken Tabelle zurück. Wenn es keine übereinstimmenden Daten in der linken Tabelle gibt, werden die fehlenden Werte als NULLs angezeigt.

Hier ist der Code:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
RIGHT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Alles bleibt gleich, außer dass wir RIGHT JOIN anstelle von LEFT JOIN verwenden. Dies ist die Ausgabe:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
NULL	NULL	NULL	NULL	Croatia	NULL

Wir haben jetzt alle Nationalmannschaften und ihre Spieler. Aber Sie können sehen, dass ein Land (Kroatien) keine Spieler in der linken Tabelle hat. Die Spielerspalten für Kroatien sind mit NULLen gefüllt.

FULL JOIN gibt alle Daten aus allen verbundenen Tabellen aus. Wenn in der entsprechenden Tabelle keine übereinstimmenden Daten vorhanden sind, werden die fehlenden Werte wieder als NULL angezeigt.

Auch hier ändern wir den Join-Typ in der Abfrage:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
FULL JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Es werden alle Daten aus beiden Tabellen zurückgegeben. Alle nicht übereinstimmenden Daten werden durch NULLs ersetzt. Alle Spieler sind im Ergebnis enthalten, auch wenn sie kein entsprechendes Land in der anderen Tabelle haben. Alle Länder sind im Ergebnis enthalten, auch wenn sie keine Spieler in der football_player Tabelle haben. Das Ergebnis FULL JOIN ist die Vereinigung von LEFT JOIN und RIGHT JOIN:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23
NULL	NULL	NULL	NULL	Croatia	NULL

Weitere Informationen finden Sie in dem Artikel über die verschiedenen JOIN-Typen. Für eine schnelle Auffrischung können Sie auch unser SQL-JOINs Cheat Sheet lesen.

3. was ist ein CROSS JOIN?

Ein CROSS JOIN ist ein weiterer in SQL verfügbarer Verknüpfungstyp. Er gibt ein kartesisches Produkt zurück. Das bedeutet, dass ein CROSS JOIN jede Zeile aus der ersten Tabelle kombiniert mit jeder Zeile aus der zweiten Tabelle zurückgibt.

Er wird nicht sehr häufig verwendet. Aber wenn Sie versucht sind, es zu benutzen, überlegen Sie es sich zweimal. Die Rückgabe aller Zeilenkombinationen kann einige Zeit dauern - wenn die Abfrage überhaupt beendet wird!

Lassen Sie uns als Beispiel die Tabellen aus den beiden vorherigen Fragen verwenden. Um die Abfrage zu schreiben, verwenden Sie das Schlüsselwort CROSS JOIN. Da dies ein Join-Typ ist, der alle Zeilenkombinationen aus allen Tabellen zurückgibt, gibt es keine ON Klausel. Schauen Sie sich das an:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
CROSS JOIN national_team nt;

Hier ist die Ausgabe. Alle Spieler in der Tabelle football_players Tabelle sind mit allen Ländern in der national_team Tabelle aufgelistet.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Italy	53
3	Marcus	Rashford	3	Italy	51
4	Kylian	Mbappé	5	Italy	66
5	Phil	Foden	3	Italy	22
6	Frenkie	de Jong	2	Italy	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	Italy	23
1	Gianfranco	Zola	1	Netherlands	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	Netherlands	51
4	Kylian	Mbappé	5	Netherlands	66
5	Phil	Foden	3	Netherlands	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Netherlands	36
8	Erling	Haaland	6	Netherlands	23
1	Gianfranco	Zola	1	England	35
2	Virgil	van Dijk	2	England	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	England	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	England	22
7	Mario	Balotelli	1	England	36
8	Erling	Haaland	6	England	23
1	Gianfranco	Zola	1	Croatia	35
2	Virgil	van Dijk	2	Croatia	53
3	Marcus	Rashford	3	Croatia	51
4	Kylian	Mbappé	5	Croatia	66
5	Phil	Foden	3	Croatia	22
6	Frenkie	de Jong	2	Croatia	22
7	Mario	Balotelli	1	Croatia	36
8	Erling	Haaland	6	Croatia	23

Mehr über CROSS JOIN erfahren Sie in dieser illustrierten Anleitung zum SQL CROSS JOIN.

4 Was ist ein Self-Join in SQL?

Wie Sie wahrscheinlich schon vermuten, liegt ein Self-Join vor, wenn die Tabelle mit sich selbst verbunden wird. Es ist wichtig zu wissen, dass es sich dabei nicht um einen speziellen SQL-Befehl handelt: Jeder JOIN -Typ kann verwendet werden, um eine Tabelle mit sich selbst zu verbinden.

Die Verknüpfung erfolgt wie jede andere JOIN, aber dieses Mal verweisen Sie auf beiden Seiten des JOIN Schlüsselworts auf dieselbe Tabelle. Self-joining ist besonders nützlich, wenn eine Tabelle einen Fremdschlüssel hat, der auf ihren Primärschlüssel verweist. Damit können Sie hierarchische Daten abfragen, z. B. Familienstammbäume oder die Organisationshierarchie eines Unternehmens. Es ist auch hilfreich, wenn Sie Wertepaare finden wollen. Im folgenden Beispiel suchen wir nach Spielern der gleichen Nationalmannschaft:

SELECT 
  fp1.id,
  fp1.first_name,
  fp1.last_name,
  fp1.national_team_id,
  fp2.id AS id_2,
  fp2.first_name AS first_name_2,
  fp2.last_name AS last_name_2,
  fp2.national_team_id as national_team_id_2
FROM football_players fp1 
JOIN football_players fp2
ON fp1.id <> fp2.id
AND fp1.national_team_id = fp2.national_team_id;

Self-joining bedeutet, dass Sie anstelle von zwei Tabellen dieselbe Tabelle zweimal angeben: einmal in der FROM -Klausel und einmal nach der JOIN -Klausel. Da Sie dieselbe Tabelle zweimal verwenden, müssen Sie Aliasnamen für die Tabellen verwenden. Jedem Vorkommen der Tabelle sollte ein eindeutiger Alias gegeben werden (fp1, fp2 in unserer Abfrage), damit klar ist, auf welches Vorkommen der Tabelle wir uns beziehen.

Wir fügen Spieler aus derselben Nationalmannschaft zusammen (ihre national_team_id Werte sind gleich). Wir wollen jedoch nicht einen Spieler mit sich selbst auflisten, also schließen wir den Fall aus, dass fp1.id und fp2.id gleich sind.

Die Ausgabe der Abfrage ist diese:

id	first_name	last_name	national_team_id	id_2	first_name_2	last_name_2	national_team_id_2
1	Gianfranco	Zola	1	7	Mario	Balotelli	1
2	Virgil	van Dijk	2	6	Frenkie	de Jong	2
3	Marcus	Rashford	3	5	Phil	Foden	3
5	Phil	Foden	3	3	Marcus	Rashford	3
6	Frenkie	de Jong	2	2	Virgil	van Dijk	2
7	Mario	Balotelli	1	1	Gianfranco	Zola	1

Sie können die Spalten national_team_id aus beiden Tabellen verwenden, um zu bestätigen, dass Gianfranco Zola und Mario Balotelli tatsächlich für dieselbe Mannschaft gespielt haben.

Mehr dazu erfahren Sie in unserem Artikel über Self-Join-Beispiele.

5. zwei Tabellen mit einem zweispaltigen JOIN verbinden

Sie haben zwei Tabellen. Die erste Tabelle ist employee, die die folgenden Daten enthält:

id	first_name	last_name
1	Steve	Bergman
2	Steve	Johnson
3	Steve	King

Die zweite Tabelle ist customerdie die folgenden Daten enthält:

id	first_name	last_name
1	Ann	Coleman
2	Steve	Bergman
3	Steve	Young
4	Donna	Winter
5	Steve	King

Ihre Aufgabe ist es, alle Mitarbeiter zu finden, die auch Kunden des Unternehmens sind. Leider können Sie die Spalte id nicht verwenden, da es sich um die Mitarbeiter-ID in einer Tabelle und die Kunden-ID in einer anderen handelt. Mit anderen Worten, es gibt keine einzelne Spalte in einer Tabelle, die auf die andere verweist.

Die Lösung besteht darin, Tabellen über den Vor- und Nachnamen zu verknüpfen, d. h. eine zweispaltige JOIN zu verwenden.

Der folgende Code verbindet die Tabellen zunächst über den Vornamen. Danach wird mit dem Schlüsselwort AND die zweite Verknüpfungsbedingung festgelegt, nämlich der Nachname. Auf diese Weise erhalten Sie Daten aus beiden Tabellen, bei denen die Kombination aus Vorname und Nachname gleich ist. Hätten wir nur eine dieser Spalten verwendet, hätten wir möglicherweise die falschen Daten erhalten, da Mitarbeiter und Kunden denselben Vornamen, aber unterschiedliche Nachnamen haben können (oder umgekehrt). Hier ist die Abfrage:

SELECT 
  e.first_name, 
  e.last_name
FROM employee e
JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;

Hier ist die Code-Ausgabe..:

first_name	last_name
Steve	Bergman
Steve	King

Das Ergebnis zeigt, dass Steve Bergman und Steve King sowohl Mitarbeiter als auch Kunden des Unternehmens sind.

6. zwei Tabellen mit einem Nicht-Gleichheits-Join verbinden

Bisher haben wir Equi-Joins verwendet: Joins, bei denen die Bedingung ON ein Gleichheitszeichen enthält. Der Nicht-Gleichheits-Join hingegen ist ein Join, der eine Nicht-Gleichheits-Bedingung in der ON Klausel enthält.

Diesmal haben wir Daten über mobile Benutzer und ihre Datennutzung. Die erste Tabelle ist mobile_userdie Mobilfunknutzer und ihr monatliches mobiles Datenlimit in MB anzeigt:

id	first_name	last_name	mobile_data_limit
1	Michael	Watson	5,000
2	Nicole	Gomez	10,000
3	Sam	Stone	8,000

Die zweite Tabelle ist data_usagedie den tatsächlichen monatlichen Datenverbrauch des Benutzers in MB anzeigt:

id	mobile_user_id	data_used	period
1	1	4,987	2022_10
2	2	6,875	2022_10
3	3	12,547	2022_10
4	1	5,037	2022_11
5	2	11,111	2022_11
6	3	4,897	2022_11

Die Aufgabe besteht darin, alle Daten zu finden, bei denen die tatsächliche Nutzung über dem monatlichen Limit lag. Wir möchten den Vor- und Nachnamen des Benutzers, das monatliche Limit, die tatsächlich genutzten Daten und den Zeitraum sehen.

Die Lösung ist die Verwendung der nicht-gleichnamigen Verknüpfung, wie unten gezeigt:

SELECT 
  first_name,
  last_name,
  mobile_data_limit,
  data_used,
  period
FROM mobile_user mu
JOIN data_usage du
ON mu.id = du.mobile_user_id
AND mobile_data_limit < data_used;

Die Abfrage wählt alle erforderlichen Informationen aus zwei Tabellen aus. Die Tabellen werden mit INNER JOIN verknüpft. Zunächst werden sie verbunden, wenn die Benutzer-ID dieselbe ist. Dann fügen wir die zweite Bedingung nach dem Schlüsselwort AND hinzu. Hier haben wir eine Nicht-Gleichheitsbedingung, die uns Daten liefert, bei denen das Limit unter der monatlichen Nutzung liegt.

Das Ergebnis können Sie unten sehen:

first_name	last_name	mobile_data_limit	data_used	period
Sam	Stone	8,000	12,547	2022_10
Michael	Watson	5,000	5,037	2022_11
Nicole	Gomez	10,000	11,111	2022_11

Wenn Sie interessiert sind, finden Sie hier weitere Beispiele für ungleiche Verknüpfungen.

7. was macht DISTINCT?

DISTINCTDer Zweck von DISTINCT besteht im Allgemeinen darin, doppelte Werte zu entfernen. Oder, anders ausgedrückt, um eindeutige Werte in der Ausgabe Ihrer Abfrage anzuzeigen.

Stellen Sie sich vor, Sie arbeiten mit dieser loans Tabelle, die Darlehens-IDs und deren Laufzeiten in Monaten zeigt.

loan_id	loan_duration
100112	60
100205	60
100208	48
100333	120
100357	48
100398	120

Verschiedene Darlehen können die gleiche Laufzeit haben, also möchten Sie die Liste der möglichen Darlehenslaufzeiten extrahieren. Dies können Sie mit DISTINCT tun:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration; 

Die Ausgabe zeigt, dass es Darlehen mit Laufzeiten von 48, 60 und 120 Monaten gibt:

loan_duration
48
60
120

DISTINCT kann in SELECT mit einer Spalte verwendet werden, um nur die eindeutigen Werte dieser Spalte anzuzeigen, wie im obigen Beispiel. Wenn es in SELECT mit mehreren Spalten verwendet wird, zeigt die Ausgabe die eindeutigen Kombinationen all dieser Spalten an.

Sie können DISTINCT auch mit Aggregatfunktionen verwenden. Wenn Sie das tun, wird Ihre Abfrage doppelte Aggregationsergebnisse eliminieren.

Beispiele für diese Verwendungen finden Sie in unserem Artikel über die Rolle von DISTINCT in SQL.

8. was bewirkt GROUP BY in SQL?

GROUP BY ist eine SQL-Klausel, mit der Daten auf der Grundlage eines oder mehrerer gemeinsamer Werte in Gruppen geordnet werden. Sie wird am häufigsten mit Aggregatfunktionen verwendet; diese Kombination liefert aggregierte Daten für jede Gruppe. Es ist jedoch wichtig zu wissen, dass die Verwendung von Aggregatfunktionen innerhalb der GROUP BY Klausel nicht erlaubt ist.

Die allgemeine GROUP BY Syntax lautet:

SELECT 
  column_1,
  column_2,
  …,
FROM table_name
WHERE …
GROUP BY column_1, column_2
HAVING …
ORDER BY column_1, column_2;

Angenommen, es gibt die Tabelle salaries:

id	first_name	last_name	salary	department
1	Nicholas	Poirot	4,798.44	IT
2	Samantha	Wolf	5,419.24	IT
3	Stewart	Johnsons	5,419.24	IT
4	Jackie	Biden	8,474.54	Sales
5	Mark	Hamilton	10,574.84	Sales
6	Mariana	Costa	9,747.54	Sales
7	Paul	Stewart	3,498.12	Accounting
8	Mary	Rutte	4,187.23	Accounting
9	Thomas	Schwarz	3,748.55	Accounting

Wir verwenden GROUP BY und AVG(), um das Durchschnittsgehalt nach Abteilung zu ermitteln:

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department;

Wir wollen die Abteilungen sehen, also wählen wir diese Spalte aus. Um das Durchschnittsgehalt zu berechnen, wenden wir die Funktion AVG() auf die Spalte salary an.

Alle in GROUP BY aufgeführten Spalten definieren die Datengruppen. In unserem Beispiel werden die Gruppen durch die Spalte Abteilung definiert: Wir berechnen das Durchschnittsgehalt für jede Abteilung.

Unsere Datengruppierung und -aggregation sieht folgendermaßen aus:

department	average_salary
Accounting	3,811.30
Sales	9,598.97
IT	5,212.31

Das Durchschnittsgehalt in der Buchhaltung beträgt 3.811,30. Die Durchschnittsgehälter in den beiden anderen Abteilungen betragen 9.598,97 bzw. 5.212,31.

Wenn Sie eine Abfrage schreiben, muss GROUP BY immer nach WHERE, aber vor der Klausel HAVING stehen. Mehr darüber erfahren Sie in diesem Artikel über GROUP BY in SQL.

9 Wie filtert man GROUP BY-Gruppen?

Sobald Sie die Gruppen erhalten haben, die Sie in GROUP BY angegeben haben, möchten Sie sie manchmal filtern. Der Schlüssel dazu liegt in der Syntax aus der vorherigen Frage. Die Klausel, mit der Sie Gruppen filtern können, lautet HAVING.

Nachdem die Filterkriterien in HAVING angegeben wurden, gibt die Abfrage alle Daten zurück, die die Kriterien erfüllen. Alle anderen Daten werden herausgefiltert.

So funktioniert es mit den Daten aus der vorherigen Frage, wenn wir nur Abteilungen mit einem Durchschnittsgehalt unter 5.500 Dollar anzeigen wollen.

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department
HAVING AVG(salary) < 5500;

Der Code ist dem in der vorigen Frage sehr ähnlich. Der Unterschied ist die HAVING Klausel. Wir verwenden sie, um die Ergebnisse zu filtern und nur Abteilungen mit Gehältern unter 5.500 Dollar anzuzeigen.

Der Code gibt Folgendes zurück:

department	average_salary
Accounting	3,811.30
IT	5,212.31

Die Abteilung, die in der Ausgabe fehlt, ist der Vertrieb, da ihr Durchschnittsgehalt 9.598,97 beträgt.

Weitere praktische Beispiele für diese Klausel finden Sie in diesem Artikel, der HAVING in SQL erklärt.

10. was ist der Unterschied zwischen WHERE und HAVING?

Wenn Sie die Antworten auf die beiden vorherigen Fragen kennen, wissen Sie wahrscheinlich auch die Antwort auf diese Frage.

Der Hauptunterschied besteht darin, dass WHERE zum Filtern von Daten verwendet wird , bevor sie gruppiert werden. Seine Position in der Anweisung SELECT zeigt dies: Er steht vor GROUP BY. Aufgrund seines Zwecks sind in WHERE keine Aggregatfunktionen erlaubt.

HAVINGDie Anweisung GROUP BY dient dag egen zum Filtern von Daten nach der Gruppierung; deshalb wird sie nach verwendet. Auch HAVING lässt Bedingungen zu, die Aggregatfunktionen enthalten.

Am besten lernen Sie die Unterscheidung, indem Sie diesen Artikel über WHERE vs. HAVING in SQL lesen.

11 Was ergibt die folgende Abfrage, die versucht, NULLs zu filtern?

Diese Art von Frage wird Ihnen in Ihrem SQL-Interview für Fortgeschrittene häufig gestellt: Sie erhalten einen Code und müssen beschreiben, was die Abfrage zurückgibt. Obwohl das Schreiben und Lesen von SQL-Code Hand in Hand gehen, ist es doch etwas anderes, wenn Sie den Code analysieren müssen, den jemand anderes geschrieben hat.

Sie haben Daten in der Tabelle contributors:

id	first_name	last_name	start_date	termination_date
1	Valeria	Bogdanov	2022-10-11	NULL
2	Nicholas	Bertolucci	2022-04-07	2022-11-11
3	Mathilde	Bauman	2022-05-25	2022-10-01
4	Trevor	Trucks	2022-01-28	NULL
5	Maria	Szabo	2022-03-15	NULL

Was wird dieser Code zurückgeben?

SELECT 
  first_name,
  last_name,
  start_date,
  termination_date
FROM contributors
WHERE termination_date != '2022-10-01';

Wenn Sie antworten, dass er alle Zeilen außer ID = 3 zurückgeben wird, liegen Sie falsch! Dies ist eine Art Trickfrage. Wenn Sie die Bedingung WHERE lesen, könnten Sie sie wie folgt lesen: Alle Daten zurückgeben, bei denen das Enddatum von 2022-10-01 verschieden ist. Wenn man sich die Tabelle ansieht, könnte man meinen, dass es sich um alle Zeilen außer einer handelt.

Das ist es auch, aber nicht für SQL! Wie Sie sehen können, gibt es drei Zeilen mit NULL Werten. Für SQL ist NULL nicht gleich einem Wert, sondern ein Nicht-Wert. Wenn Sie also die Bedingung in WHERE so einrichten, schließen Sie alle Datumsangaben aus, die nicht gleich dem Wert 2022-10-01 und NULL sind.

Hier ist die Ausgabe als Beweis:

first_name	last_name	start_date	termination_date
Nicholas	Bertolucci	2022-04-07	2022-11-11

Sie können mehr über diesen und andere Vergleichsoperatoren, die mit NULL verwendet werden, erfahren.

12. schreiben Sie eine Abfrage, die die Anzahl der Songs nach Künstler ermittelt. Verwenden Sie LEFT JOIN und COUNT().

Angenommen, Sie haben zwei Tabellen: artist und song.

Hier sind die artist Daten:

id	artist_name
1	Prince
2	Jimi Hendrix
3	Santana
4	Otis Redding
5	Lou Rawls

Nachfolgend die song Daten:

id	artist_id	song_title
1	1	Purple Rain
2	2	Purple Haze
3	3	Europa
4	1	Cream
5	1	Bambi
6	1	Why You Wanna Treat Me So Bad?
7	2	Spanish Castle Magic
8	3	Taboo
9	3	Incident at Neshabur
10	3	Flor D' Luna

Sie müssen LEFT JOIN und COUNT() verwenden, um alle Künstler, ihre IDs und die Anzahl ihrer Lieder in der Datenbank zu finden.

Sie könnten versucht sein, diese Lösung vorzuschlagen:

SELECT 
  a.id,
  artist_name,
  COUNT(*) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Werfen wir einen Blick auf die Ausgabe:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	1
5	Lou Rawls	1

Die Ausgabe zeigt alle Künstler an; das ist in Ordnung. Allerdings ist die Anzahl der Songs für Otis Redding und Lou Rawls eins, was falsch ist! Werfen Sie einen Blick auf die Tabelle songund Sie werden sehen, dass es keine Künstler-IDs gibt, die gleich 4 oder 5 sind.

Was ist falsch gelaufen? Wenn Sie COUNT(*) mit LEFT JOIN verwenden, zählt die Aggregatfunktion alle nicht übereinstimmenden Werte (NULLs). Deshalb zeigte das Ergebnis je einen Song für Otis Redding und Lou Rawls an, obwohl sie keine Songs in der Tabelle haben.

COUNT(*) wird verwendet, um alle Zeilen zu zählen. Um eine korrekte Antwort zu geben, sollten Sie stattdessen COUNT(song_title) verwenden.

SELECT 
  a.id,
  artist_name,
  COUNT(song_title) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Mit diesem Code erhalten Sie die richtige Ausgabe:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	0
5	Lou Rawls	0

Die Anzahl der Songs von Prince, Jimi Hendrix und Santana ist die gleiche geblieben wie in der vorherigen Ausgabe. Die Anzahl der Lieder der beiden anderen Künstler ist jetzt jedoch Null, und das ist die richtige Zahl.

13. was ist der Unterschied zwischen JOIN und UNION?

JOIN ist eine SQL-Klausel, mit der zwei oder mehr Tabellen verbunden werden. Sie ermöglicht die Verwendung von Daten aus allen verbundenen Tabellen. Mit anderen Worten: Spalten aus allen Tabellen werden nebeneinander angezeigt, d. h. die Daten werden horizontal gestapelt.

UNION ist ein Mengenoperator, der für die Kombination der Ergebnisse von zwei oder mehr SELECT Anweisungen verwendet wird. Die Daten werden vertikal gestapelt. Eine der Voraussetzungen für die Verwendung von UNION ist, dass die Anzahl der Spalten in allen vereinigten SELECT Anweisungen gleich sein muss. Außerdem müssen alle ausgewählten Spalten vom gleichen Datentyp sein.

14. was ist der Unterschied zwischen UNION und UNION ALL?

Beide haben gemeinsam, dass sie Mengenoperatoren sind. Außerdem werden beide Operatoren für denselben Zweck verwendet: die Zusammenführung von Daten aus zwei oder mehr SELECT Anweisungen.

Auch die Anforderungen an die Anzahl der Spalten und deren Datentyp sind die gleichen.

Der Unterschied besteht nun darin, dass UNION nur eindeutige Datensätze zurückgibt. Im Gegensatz dazu gibt UNION ALL alle Datensätze zurück, einschließlich der Duplikate.

Normalerweise ist UNION ALL schneller, weil das Ergebnis nicht sortiert wird, um die Duplikate zu entfernen. Als Faustregel gilt, dass Sie standardmäßig UNION ALL verwenden sollten. Verwenden Sie UNION nur, wenn Sie eindeutige Ergebnisse benötigen oder absolut sicher sind, dass Ihre Abfrage keine doppelten Daten liefert.

Mehr über die Syntax und die Verwendung von UNION erfahren Sie in diesem Artikel über die Unterschiede zwischen UNION und UNION ALL.

15 Was ist eine Unterabfrage in SQL?

Eine Unterabfrage ist eine Abfrage, die innerhalb einer anderen SQL-Abfrage geschrieben wird. Die "andere" Abfrage wird als Hauptabfrage bezeichnet, während eine Unterabfrage manchmal auch als verschachtelte Abfrage bezeichnet wird.

Unterabfragen können in den Anweisungen SELECT, INSERT, UPDATE, und DELETE verwendet werden. Sie können auch in Klauseln wie FROM oder WHERE verwendet werden, was die häufigste Verwendung ist.

Hier ist ein Beispiel. Die Tabelle ist productsund speichert Informationen über Produktnamen, Mengen und Kategorien:

id	product_name	quantity	product_category
1	Apple MacBook Air (2020) MGN63N/A Space Gray	319	Laptop
2	Fairphone 4 128GB Green 5G	208	Mobile phone
3	Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver	157	Desktop
4	HP 17-cp0971nd	487	Laptop
5	Huawei P30 Pro - 128GB - Blue	148	Mobile phone
6	Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC	514	Desktop
7	Toshiba Dynabook Satellite Pro E10-S-101 Notebook	207	Laptop
8	Samsung Galaxy S23 5G - 256GB - Phantom Black	56	Mobile phone
9	Intel Compleet PC | Intel Core i7-10700	459	Desktop

Wir verwenden eine Unterabfrage und zeigen die Gesamtmenge nach Produktkategorie an, aber nur für die einzelnen Produkte, deren Menge über der durchschnittlichen Menge aller Produkte liegt.

Hier ist die Lösung:

SELECT 
  product_category,
  SUM(quantity) AS product_quantity
FROM products
WHERE quantity > (SELECT AVG(quantity)
			FROM products)
GROUP BY product_category;

Die Abfrage wählt die Produktkategorie aus und summiert die Menge mit der Aggregatfunktion SUM(). Es gibt eine Bedingung in WHERE, die besagt, dass nur die einzelnen Produkte, deren Menge über dem Durchschnitt liegt, in die Summe aufgenommen werden. Wir verwenden die Unterabfrage und die Funktion AVG(), um diesen Durchschnitt zu ermitteln.

Die Abfrage gibt zwei Zeilen zurück:

product_category	product_quantity
Laptop	806
Desktop	973

Eine Kategorie fehlt, weil sie die Filterkriterien nicht erfüllt - Handys.

Es gibt verschiedene Arten von Unterabfragen, z. B. skalare, mehrzeilige und korrelierte Abfragen. Mehr darüber erfahren Sie in unserem Artikel über Unterabfragetypen.

16 Schreiben Sie eine Abfrage, die Verkäufer und ihre monatlichen Umsatzdaten über ihrem persönlichen Umsatzdurchschnitt zurückgibt. Verwenden Sie eine korrelierte Unterabfrage.

Eine korrelierte Unterabfrage ist ein Typ von Unterabfrage, der Werte aus der äußeren Abfrage verwendet. Sie wird für jede Zeile, die die äußere Abfrage zurückgibt, einmal überprüft, was die Leistung beeinträchtigen kann.

In der Frage wird jedoch darauf bestanden, sie zu verwenden, also sehen wir uns die Daten an.

Die erste Tabelle ist salesperson:

id	first_name	last_name
1	Nina	Lee
2	Carolina	Green
3	Mick	Johnson

Die andere Tabelle ist sales:

id	salesperson_id	monthly_sales	period
1	1	1,200.47	2021_10
2	2	5,487.22	2021_10
3	3	700.47	2021_10
4	1	15,747.54	2021_11
5	2	16,700.87	2021_11
5	3	14,322.87	2021_11
6	1	9,745.55	2021_12
7	2	9,600.97	2021_12
8	3	6,749.58	2021_12

Ihre Aufgabe ist es, eine korrelierte Unterabfrage zu verwenden und den vollständigen Namen des Verkäufers, seinen monatlichen Umsatz und die Zeiträume, in denen sein Umsatz über seinem persönlichen Durchschnitt liegt, zurückzugeben.

Hier ist die Lösung:

SELECT 
  first_name,
  last_name,
  monthly_sales,
  period
FROM salesperson sp
JOIN sales s
ON sp.id = s.salesperson_id
WHERE monthly_sales > 
   (SELECT AVG(monthly_sales)
    FROM sales
    WHERE salesperson_id = sp.id);

Die Abfrage wählt alle erforderlichen Spalten aus. Diese Daten stammen aus beiden Tabellen, also haben wir sie verbunden.

Jetzt kommt der entscheidende Teil. Um die Daten zu filtern, verwenden wir die WHERE Klausel. Die Bedingung besagt, dass die Abfrage alle Daten zurückgeben soll, bei denen die monatlichen Umsätze höher sind als die durchschnittlichen Umsätze der einzelnen Vertriebsmitarbeiter. Wie berechnen wir diese individuellen Durchschnittsumsätze? Durch Verwendung der Funktion AVG() in der Unterabfrage, die wir in der Klausel WHERE schreiben.

Hier ist die Ausgabe:

first_name	last_name	monthly_sales	period
Nina	Lee	15,747.54	2021_11
Carolina	Green	16,700.87	2021_11
Mick	Johnson	14,322.87	2021_11
Nina	Lee	9,745.55	2021_12

Weitere Beispiele finden Sie in diesem Artikel über korrelierte Unterabfragen.

17 Was sind Fensterfunktionen in SQL?

Die SQL-Fensterfunktionen verdanken ihren Namen der Tatsache, dass sie auf ein Datenfenster angewendet werden. Dieses Fenster ist einfach eine Reihe von Zeilen, die mit der aktuellen Zeile zusammenhängen.

Fensterfunktionen werden durch die Klausel OVER() eingeleitet. Eine weitere wichtige Klausel ist PARTITION BY, die Datenpartitionen innerhalb eines Fensterrahmens definiert. Wenn diese Klausel weggelassen wird, ist die Partition die gesamte Ergebnistabelle. Wenn PARTITION BY verwendet wird, können Sie eine oder mehrere Spalten definieren, nach denen die Daten partitioniert werden sollen. Sie können sie als GROUP BY für Fensterfunktionen betrachten.

Eine weitere wichtige Klausel ist ORDER BY. Sie sortiert die Daten innerhalb des Fensters. Im Zusammenhang mit Fensterfunktionen gibt diese Klausel Anweisungen über die Reihenfolge, in der die Funktion ausgeführt werden soll.

Weitere Informationen finden Sie in diesem Artikel über Fensterfunktionen.

18. was ist der Unterschied zwischen Fensterfunktionen und GROUP BY?

Die einzige Gemeinsamkeit besteht darin, dass sowohl GROUP BY als auch die Fensterfunktionen mit den Aggregatfunktionen verwendet werden können - und dies auch sehr häufig tun - und dass sie beide mit einer Reihe von Zeilen arbeiten.

Bei der Verwendung von GROUP BY wird die Ausgabe jedoch als Gruppe angezeigt, und Sie können die einzelnen Zeilen, die die Gruppe bilden, nicht sehen.

Bei Fensterfunktionen gibt es solche Probleme nicht. Sie zeichnen sich unter anderem dadurch aus, dass sie die einzelnen Zeilen bei der Anzeige aggregierter Daten nicht einklappen. Das bedeutet, dass es möglich ist, aggregierte und nicht aggregierte Daten gleichzeitig anzuzeigen.

Fensterfunktionen werden nicht nur für die Aggregation von Daten verwendet, wie Sie in der folgenden Frage sehen werden. Wenn Sie mehr über das aktuelle Thema wissen möchten, finden Sie hier einen Artikel, der Fensterfunktionen und GROUP BY erklärt.

19. welche Fensterfunktionen kennen Sie?

SQL-Fensterfunktionen lassen sich im Allgemeinen in vier Kategorien einteilen:

• Ranking-Funktionen
• Verteilungsfunktionen
• Analytische Funktionen
• Aggregat-Funktionen

Die Rangfolge-Funktionen sind:

• ROW_NUMBER() - Gibt eine eindeutige Nummer für jede Zeile innerhalb einer Partition zurück; gleichrangige Werte haben unterschiedliche Zeilennummern.
• RANK() - Ordnet Daten innerhalb einer Partition ein; gleichrangige Werte haben den gleichen Rang, und es gibt eine Lücke nach gleichrangigen Werten (z. B. 1, 2, 3, 3, 5).
• DENSE_RANK() - Ordnet Daten innerhalb einer Partition ein; gebundene Werte haben denselben Rang und es gibt keine Ranglücke (z. B. 1, 2, 3, 3, 4).

Die Verteilungsfunktionen sind:

• PERCENT_RANK() - Gibt den relativen Rang innerhalb einer Partition zurück.
• CUME_DIST() - Gibt die kumulative Verteilung innerhalb einer Partition zurück.

Die analytischen Funktionen sind:

• LEAD() - Ermöglicht den Zugriff auf Werte aus einer nachfolgenden Zeile in Bezug auf die aktuelle Zeile.
• LAG() - Ermöglicht den Zugriff auf die Werte einer vorherigen Zeile in Bezug auf die aktuelle Zeile.
• NTILE() - Unterteilt Zeilen innerhalb einer Partition in annähernd gleiche Gruppen.
• FIRST_VALUE() - Ermöglicht den Zugriff auf Werte aus der ersten Zeile innerhalb einer Partition.
• LAST_VALUE() - Ermöglicht den Zugriff auf Werte aus der letzten Zeile innerhalb einer Partition.
• NTH_VALUE() - Ermöglicht den Zugriff auf die n-te Zeile innerhalb einer Partition.

Schließlich gibt es noch die Aggregatfunktionen:

• AVG() - Gibt einen Durchschnittswert für die Zeilen in einer Partition zurück.
• COUNT() - Gibt die Anzahl der Werte in den Zeilen einer Partition zurück.
• MAX() - Gibt den Maximalwert für die Zeilen in einer Partition zurück.
• MIN() - Gibt den Mindestwert für die Zeilen in einer Partition zurück.
• SUM() - Gibt den Summenwert der Zeilen in einer Partition zurück.

In unserem Fensterfunktionen Cheat Sheet finden Sie weitere Informationen zu all diesen Funktionen.

20 Wie erstellt man eine Rangliste in SQL?

Der einfachste Weg, Daten in SQL zu ordnen, ist die Verwendung einer der drei Funktionen des Ranglistenfensters:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Sie erhalten ein Dataset mit dem Namen album_sales mit den folgenden Daten:

id	artist	album	copies_sold
1	Eagles	Hotel California	42,000,000
2	Led Zeppelin	Led Zeppelin IV	37,000,000
3	Shania Twain	Come On Over	40,000,000
4	Fleetwood Mac	Rumours	40,000,000
5	AC/DC	Back in Black	50,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Michael Jackson	Thriller	70,000,000
8	Pink Floyd	The Dark Side of the Moon	45,000,000
9	Whitney Houston	The Bodyguard	45,000,000
10	Eagles	Their Greatest Hits (1971-1975)	44,000,000

Dies sind die Verkaufszahlen der zehn meistverkauften Alben der Geschichte. Wie Sie sehen können, sind die Alben nicht geordnet. Genau das werden wir hier tun: sie mit Hilfe von Fensterfunktionen von den besten zu den schlechtesten Verkäufen ordnen.

Wenn Sie ROW_NUMBER() verwenden, sieht die Abfrage wie folgt aus:

SELECT 
  ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Die Syntax ist einfach. Zuerst wählen Sie die Fensterfunktion aus. Dann verwenden Sie die obligatorische OVER() -Klausel, die signalisiert, dass es sich um eine Fensterfunktion handelt. In ORDER BY sortieren Sie die Daten absteigend. Das bedeutet, dass die Zeilennummern entsprechend den verkauften Exemplaren von oben nach unten vergeben werden.

Natürlich können Sie auch alle anderen benötigten Spalten auflisten und auf die Tabelle verweisen, um die gleiche Ausgabe zu erhalten:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
4	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
8	Fleetwood Mac	Rumours	40,000,000
9	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Wie Sie sehen können, sind die Alben von eins bis zehn geordnet. Zwei Alben haben 45 Millionen Exemplare verkauft. Sie werden jedoch nach zufälligen Kriterien unterschiedlich eingestuft (dritter und vierter Platz). Dasselbe geschieht mit drei Alben, die sich 40 Millionen Mal verkauft haben.

Wenn Sie RANK() verwenden, ist die Syntax dieselbe, nur dass Sie eine andere Fensterfunktion verwenden:

SELECT 
  RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Die Ausgabe ist jedoch anders:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
7	Fleetwood Mac	Rumours	40,000,000
7	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Sie können sehen, dass die Alben mit Gleichstand an dritter Stelle stehen (zwei Mal). Das nächste Album ohne Gleichstand liegt auf dem fünften Platz. Dasselbe geschieht mit den Alben auf Platz sieben.

Schauen wir uns an, was passiert, wenn wir DENSE_RANK() verwenden:

SELECT 
  DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Hier ist das Ergebnis:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
4	Eagles	Their Greatest Hits (1971-1975)	44,000,000
5	Eagles	Hotel California	42,000,000
6	Shania Twain	Come On Over	40,000,000
6	Fleetwood Mac	Rumours	40,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Led Zeppelin	Led Zeppelin IV	37,000,000

Die ersten unentschiedenen Alben werden an dritter Stelle platziert, was dem vorherigen Ergebnis entspricht. Der Unterschied besteht jedoch darin, dass der nächste unentschiedene Rang der vierte ist - was bedeutet, dass die Rangliste nicht übersprungen wird.

Die drei anderen gleichauf liegenden Alben liegen nun auf dem sechsten und nicht wie zuvor auf dem siebten Platz. Auch der höchste Rang ist der siebte und nicht der zehnte.

Wie Sie sehen können, liefert jede Methode unterschiedliche Ergebnisse. Sie sollten die Methode verwenden, die am besten zu Ihren Daten und den gewünschten Ergebnissen passt. Lesen Sie den Artikel über die Rangfolge von Zeilen in SQL, um mehr zu erfahren.

21. was ist der Unterschied zwischen RANK() und DENSE_RANK()?

Wir haben den Unterschied bereits in der vorherigen Frage angesprochen. Sie haben ihn dort in einem praktischen Beispiel gesehen, und jetzt lassen Sie uns ihn formulieren, um diese Frage zu beantworten.

RANK() ordnet Zeilen mit gleichen Werten den gleichen Rang zu. Wenn es zur nächsten nicht gebundenen Zeile kommt, überspringt es den Rang um die Anzahl der gebundenen Ränge.

DENSE_RANK() gibt auch den gebundenen Werten den gleichen Rang. Der Rang wird jedoch nicht übersprungen, wenn er die nächste nicht gebundene Zeile erreicht. Mit anderen Worten: DENSE_RANK() ordnet die Daten sequentiell.

Weitere Einzelheiten werden in diesem Artikel über die Unterschiede zwischen RANK() und DENSE_RANK() erläutert.

22. die obersten n Zeilen in SQL mit einer Fensterfunktion und einem CTE finden.

Dies ist eine häufig gestellte Frage, die auf verschiedene Weise gelöst werden kann. Wir werden die Window-Funktion in einer CTE verwenden, um das gewünschte Ergebnis zu erhalten.

Die verfügbaren Daten sind in der salary Tabelle:

id	first_name	last_name	salary	department
1	Tim	Thompson	10,524.74	Sales
2	Martina	Hrabal	7,895.14	Accounting
3	Susan	Truman	15,478.69	Sales
4	Ciro	Conte	8,794.41	Accounting
5	Jorge	De Lucia	7,489.15	Sales
6	Carmen	Lopez	10,479.15	Accounting
7	Catherine	Molnar	8,794.89	Sales
8	Richard	Buchanan	12,487.69	Accounting
9	Mark	Wong	9,784.19	Sales
10	Silvia	Karelias	9,748.64	Accounting

Die Aufgabe besteht darin, die drei höchstbezahlten Mitarbeiter in jeder Abteilung mit ihrem Gehalt und ihrer Abteilung zu ermitteln.

Die Vorgehensweise ist wie folgt:

WITH ranking AS (
  SELECT 
    first_name,
    last_name,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM salary
)

SELECT *
FROM ranking
WHERE salary_rank <= 3
ORDER BY department, salary_rank;

Der erste Teil des Codes ist ein Common Table Expression, kurz CTE. Er wird mit dem Schlüsselwort WITH eingeleitet. Der CTE trägt den Namen ranking. Nach dem Schlüsselwort AS schreiben wir die CTE-Definition als SELECT -Anweisung in Klammern.

Nach der Auswahl aller erforderlichen Spalten kommt ranking, wir verwenden die DENSE_RANK() Funktion. Sie können auch jede andere Funktion des Ranking-Fensters verwenden, wenn Sie möchten.

Die Syntax ist bekannt. Um die Rangfolge nach Abteilung zu erhalten, müssen wir die Daten nach dieser Spalte partitionieren. Außerdem wollen wir die Gehälter von hoch nach niedrig einstufen. Mit anderen Worten: Die Daten in der Partition müssen nach Gehalt in absteigender Reihenfolge geordnet werden.

Die zweite Anweisung SELECT (d. h. die äußere Abfrage) wählt alle Spalten aus dem CTE aus und setzt die Bedingung in der Klausel WHERE, um nur die drei höchsten Gehälter nach Abteilung zu filtern. Schließlich wird die Ausgabe nach Abteilung und Gehaltsrang sortiert.

Hier ist das Ergebnis:

first_name	last_name	salary	department	salary_rank
Richard	Buchanan	12,487.69	Accounting	1
Carmen	Lopez	10,479.15	Accounting	2
Silvia	Karelias	9,748.64	Accounting	3
Susan	Truman	15,478.69	Sales	1
Tim	Thompson	10,524.74	Sales	2
Mark	Wong	9,784.19	Sales	3

23. die Differenz zwischen zwei Zeilen (Delta) berechnen mit Fensterfunktionen

Dieses Problem wird am elegantesten mit der Funktion LAG() window gelöst. Denken Sie daran, dass es sich um eine Funktion handelt, die auf den Wert der vorherigen Zeile zugreift.

Die Beispieldaten finden Sie in der Tabelle revenue:

id	actual_revenue	period
1	8,748,441.22	2022_07
2	10,487,444.59	2022_08
3	7,481,457.15	2022_09
4	7,497,441.89	2022_10
5	8,697,415.36	2022_11
6	12,497,441.56	2022_12

Sie müssen den tatsächlichen Umsatz, den Zeitraum und die monatliche Differenz (Delta) zwischen dem tatsächlichen Umsatz und dem des Vormonats anzeigen.

So wird es gemacht.

SELECT 
  actual_revenue,
  actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change,	 
 period
FROM revenue
ORDER BY period;

Ein Delta wird berechnet, indem der Vormonat vom aktuellen Monat abgezogen wird. Genau das macht diese Abfrage! Um den Umsatz des Vormonats zu erhalten, ist die Funktion LAG() sehr nützlich. Die Spalte actual_revenue ist das Argument der Funktion, da wir auf die Umsatzdaten der vorherigen Zeile zugreifen wollen. Wie bei jeder Fensterfunktion gibt es eine OVER() -Klausel. Darin haben wir die Daten nach Zeitraum aufsteigend sortiert, weil es logisch ist, das Delta chronologisch zu berechnen.

Dies ist die Ausgabe der Abfrage:

actual_revenue	monthly_revenue_change	period
8,748,441.22	NULL	2022_07
10,487,444.59	1,739,003.37	2022_08
7,481,457.15	-3,005,987.44	2022_09
7,497,441.89	15,984.74	2022_10
8,697,415.36	1,199,973.47	2022_11
12,497,441.56	3,800,026.20	2022_12

Die erste zeigt die Umsatzänderung als NULL an. Dies ist zu erwarten, da es keinen früheren Monat gibt, von dem man abziehen könnte. Im Jahr 2022_08 gab es eine Umsatzsteigerung von 1.739.003,37 = Umsatz des aktuellen Monats - Umsatz des Vormonats = 10.487.444,59 - 8.748.441,22.

Ähnliche Beispiele finden Sie in dem Artikel über die Berechnung der Differenz zwischen zwei Zeilen in SQL.

24. Fensterfunktionen verwenden, um eine laufende Summe zu berechnen

Eine laufende oder kumulative Summe ist die Summe einer Zahlenfolge. Die laufende Summe wird jedes Mal aktualisiert, wenn ein neuer Wert zu der Folge hinzugefügt wird. Denken Sie an die monatlichen Einnahmen: Die Gesamteinnahmen des aktuellen Monats umfassen die Summe der Einnahmen des aktuellen Monats und aller Vormonate.

Die Fensterfunktion, die sich perfekt für die Berechnung einer laufenden Summe (kumulative Summe) eignet, ist SUM().

Zeigen wir den Ansatz anhand der gleichen Daten wie in der vorherigen Frage. Ziel ist es, die kumulierten Einnahmen für alle verfügbaren Monate im Jahr 2022 zu berechnen.

Hier ist die Lösung:

SELECT 
  actual_revenue,
  SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue,
  period
FROM revenue;

Die kumulative Summe ist die Summe der Einnahmen des aktuellen Monats und die Summe der Einnahmen aller Vormonate. Die Funktion SUM() window wendet diese Logik an. Das Argument der Funktion ist der aktuelle Umsatz, denn das ist es, was wir summieren. Damit die Funktion alle vorherigen Einnahmen und die aktuellen Einnahmen summiert, sortieren Sie die Daten aufsteigend nach Zeitraum. Auch hier ist es logisch, eine kumulierte Summe vom frühesten bis zum letzten Monat zu berechnen.

Dies ist das Ergebnis des Codes:

actual_revenue	cumulative_revenue	period
8,748,441.22	8,748,441.22	2022_07
10,487,444.59	19,235,885.81	2022_08
7,481,457.15	26,717,342.96	2022_09
7,497,441.89	34,214,784.85	2022_10
8,697,415.36	42,912,200.21	2022_11
12,497,441.56	55,409,641.77	2022_12

Der kumulierte Umsatz in der ersten Zeile ist gleich dem tatsächlichen Umsatz. Für die zweite Zeile beträgt die kumulierte Summe 19.235.885,81 = 8.748.441,22 + 10.487.444,59. Im September beträgt die kumulierte Summe 26.717.342,96 = 8.748.441,22 + 10.487.444,59 + 7.481.457,15.

Die gleiche Logik gilt auch für den Rest der Tabelle.

Hier erfahren Sie mehr über die laufende Summe und wie man sie berechnet.

25. einen gleitenden Durchschnitt finden mit Fensterfunktionen

Ein gleitender Durchschnitt wird bei der Analyse einer Reihe verwendet. Sie finden ihn auch unter anderen Bezeichnungen wie gleitendes Mittel, gleitender Durchschnitt oder laufender Durchschnitt. Es handelt sich dabei um einen Durchschnitt aus dem aktuellen Wert und der festgelegten Anzahl der vorangegangenen Werte. Ein gleitender 7-Tage-Durchschnitt ist zum Beispiel der Durchschnitt des aktuellen Tages und der sechs vorangegangenen Tage.

Um Ihnen zu zeigen, wie Sie ihn berechnen können, verwenden wir die eur_usd_rate Tabelle:

id	exchange_rate	date
1	1.0666	2022-12-30
2	1.0683	2023-01-02
3	1.0545	2023-01-03
4	1.0599	2023-01-04
5	1.0601	2023-01-05
6	1.0500	2023-01-06
6	1.0696	2023-01-09
7	1.0723	2023-01-10
8	1.0747	2023-01-11
9	1.0772	2023-01-12
10	1.0814	2023-01-13

Wir berechnen den gleitenden 3-Tage-Durchschnitt auf folgende Weise:

SELECT 
  exchange_rate,
  AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average,
  date
FROM eur_usd_rate;

Wir verwenden die Fensterfunktion AVG() für die Spalte exchange_rate. In der Klausel OVER() werden die Daten nach Datum in aufsteigender Reihenfolge sortiert. Jetzt kommt der wichtige Teil! Erinnern Sie sich, dass wir einen gleitenden 3-Tage-Durchschnitt benötigen, der die aktuelle und die beiden vorherigen Zeilen umfasst. Wir geben das in der BETWEEN Klausel an: Wir sagen der Funktion, dass sie die zwei vorangegangenen Zeilen und die aktuelle Zeile einbeziehen soll.

Werfen wir einen Blick auf das Ergebnis:

exchange_rate	eur_usd_moving_average	date
1.0666	1.0666	2022-12-30
1.0683	1.0675	2023-01-02
1.0545	1.0631	2023-01-03
1.0599	1.0609	2023-01-04
1.0601	1.0582	2023-01-05
1.0500	1.0567	2023-01-06
1.0696	1.0599	2023-01-09
1.0723	1.0640	2023-01-10
1.0747	1.0722	2023-01-11
1.0772	1.0747	2023-01-12
1.0814	1.0778	2023-01-13

Der gleitende Durchschnitt des ersten Datums ist derselbe wie der Wechselkurs, denn: 1.0666/1 = 1.0666. Für 2023-01-02 wird er wie folgt berechnet: (1,0666 + 1,0683)/2 = 1,0675.

Im Jahr 2023-01-03 haben wir schließlich drei Daten: (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Diese Logik gilt auch für alle anderen Datumsangaben.

Weitere Beispiele finden Sie in diesem Artikel über die Berechnung von gleitenden Durchschnitten in SQL.

26. was ist der Unterschied zwischen ROWS und RANGE?

Sowohl ROWS als auch RANGE sind Klauseln, die zur Definition eines Fensterrahmens verwendet werden. Sie begrenzen den in einer Fensterfunktion verwendeten Datenbereich innerhalb einer Partition.

Die Klausel ROWS begrenzt die Zeilen. Sie wird verwendet, um eine feste Anzahl von Zeilen vor und nach der aktuellen Zeile anzugeben. Der Wert der Zeilen wird dabei nicht berücksichtigt.

Die RANGE Klausel schränkt den Datenbereich logisch ein. Mit anderen Worten, sie schränkt die Daten ein, indem sie die Werte der vorangehenden und nachfolgenden Zeilen im Verhältnis zur aktuellen Zeile betrachtet. Die Anzahl der Zeilen bleibt dabei unberücksichtigt.

Wie verwendet man sie in der Praxis? Lesen Sie unseren Artikel über ROWS und RANGE für weitere Einzelheiten.

27 Verwenden Sie eine rekursive Abfrage, um alle Mitarbeiter unter einem bestimmten Manager zu finden.

Eine rekursive Abfrage ist eine spezielle Art von CTE, die auf sich selbst verweist, bis sie das Ende der Rekursion erreicht. Sie ist ideal für die Abfrage von Graphdaten oder hierarchischen Strukturen.

Ein Beispiel für Letzteres ist die Organisationsstruktur des Unternehmens, die in der company_organization Tabelle:

employee_id	first_name	last_name	manager_id
5529	Jack	Simmons	5125
5238	Maria	Popovich	5329
5329	Dan	Jacobsson	5125
5009	Simone	Gudbois	5329
5125	Albert	Koch	NULL
5500	Jackie	Carlin	5529
5118	Steve	Nicks	5952
5012	Bonnie	Presley	5952
5952	Harry	Raitt	5529
5444	Sean	Elsam	5329

Diese Tabelle zeigt alle Mitarbeiter und die ID ihres direkten Vorgesetzten.

Die Aufgabe besteht hier darin, die Rekursion zu nutzen und alle direkten und indirekten Untergebenen von Jack Simmons zurückzugeben. Außerdem fügen wir eine Spalte hinzu, die zur Unterscheidung der verschiedenen Organisationsebenen verwendet werden kann. Hier ist der Code:

WITH RECURSIVE subordinates AS (
	SELECT
  employee_id,
	  first_name,
	  last_name,
	  manager_id,
	  0 AS level
	FROM company_organization
	WHERE employee_id= 5529

	UNION ALL

	SELECT
 	  co.employee_id, 
	  co.first_name,
	  co.last_name,
	  co.manager_id,
	  level + 1
	FROM company_organization co 
JOIN subordinates s 
ON co.manager_id = s.employee_id
)

SELECT
  s.employee_id,
  s.first_name AS employee_first_name,
  s.last_name AS employee_last_name,
  co.employee_id AS direct_manager_id,
  co.first_name AS direct_manager_first_name,
  co.last_name AS direct_manager_last_name,
  s.level
FROM subordinates s 
JOIN company_organization co 
ON s.manager_id = co.employee_id
ORDER BY level;

Wir starten die Rekursion mit WITH RECURSIVE. (Wenn Sie in MS SQL Server arbeiten, verwenden Sie nur WITH.)

Die erste SELECT in einer CTE wird Ankermitglied genannt. Darin verweisen wir auf das Dataset und wählen alle erforderlichen Spalten aus. Außerdem erstellen wir eine neue Spalte mit dem Wert Null und filtern die Daten in der WHERE Klausel. Warum wird genau diese Bedingung in WHERE verwendet? Weil die Mitarbeiter-ID von Jack Simmons 5529 ist und wir ihn und seine Untergebenen anzeigen wollen.

Dann folgt die UNION ALL, die die Ergebnisse der Ankerabfrage und der rekursiven Abfrage kombiniert, d. h. die zweite SELECT Anweisung.

Wir möchten, dass die Rekursion bis hinunter in die Organisationsstruktur geht. In der rekursiven Abfrage verknüpfen wir die CTE mit der company_organization Tabelle. Wir listen wiederum alle erforderlichen Spalten der letztgenannten Tabelle auf. Außerdem wollen wir bei jeder Rekursion eine Organisationsebene hinzufügen.

Schließlich kommen wir zu der Abfrage, die die CTE verwendet. Diese Abfrage dient dazu, Daten sowohl aus der CTE als auch aus der company_organization Tabelle. Wir verwenden die CTE, um die Mitarbeiterdaten anzuzeigen. Die andere Tabelle wird verwendet, um die Daten des direkten Vorgesetzten anzuzeigen.

Wenn Sie den Code ausführen, erhalten Sie dieses Ergebnis:

employee_id	employee_first_name	employee_last_name	direct_manager_id	direct_manager_first_name	direct_manager_last_name	level
5529	Jack	Simmons	5125	Albert	Koch	0
5952	Harry	Raitt	5529	Jack	Simmons	1
5500	Jackie	Carlin	5529	Jack	Simmons	1
5012	Bonnie	Presley	5952	Harry	Raitt	2
5118	Steve	Nicks	5952	Harry	Raitt	2

Die obige Tabelle zeigt, dass der direkte Vorgesetzte von Jack Simmons Albert Koch ist. Direkt unter Simmons stehen Harry Raitt und Jackie Carlin. Die indirekten Untergebenen sind Bonnie Presley und Steve Nicks. Ihr direkter Vorgesetzter ist Harry Raitt.

Einige andere Varianten dieser Aufgabe finden Sie im Artikel über rekursive CTEs.

Mehr über Fensterfunktionen erfahren Sie in diesem Artikel über SQL-Fensterfunktionen, der sich mit Fragen zu Vorstellungsgesprächen befasst.

Sind Sie bereit, die SQL-Interviewfragen zu meistern?

Es war nicht einfach, diesen Leitfaden zu schreiben. Aber es hat sich gelohnt, wenn wir daran denken, wie Sie Ihr fortgeschrittenes SQL-Vorstellungsgespräch einfacher gestalten können.

Natürlich sind dies nicht alle Fragen, die Ihnen im Vorstellungsgespräch gestellt werden könnten. Wir glauben jedoch, dass diese Auswahl Ihnen eine solide Grundlage für die wichtigsten fortgeschrittenen SQL-Konzepte bietet. Dieser Leitfaden ist außerdem kurz genug, damit Sie ihn vor dem Vorstellungsgespräch schnell durchgehen und Ihr Gedächtnis auffrischen können.

Wenn Sie weitere Auffrischungen zu fortgeschrittenen SQL-Themen benötigen, besuchen Sie unseren Fensterfunktionen Kurs oder Fortgeschrittenes SQL Kurs.