Zurück zur Artikelliste Artikel
33 Leseminuten

Top 27 Interviewfragen für fortgeschrittenes SQL mit Antworten

Inhaltsverzeichnis

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.

idfirst_namelast_namenational_team_idgames_played
1GianfrancoZola135
2Virgilvan Dijk253
3MarcusRashford351
4KylianMbappé566
5PhilFoden322
6Frenkiede Jong222
7MarioBalotelli136
8ErlingHaaland623

Die zweite ist national_team.

idcountry
1Italy
2Netherlands
3England
4Croatia

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

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.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
NULLNULLNULLNULLCroatiaNULL

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23
NULLNULLNULLNULLCroatiaNULL

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.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Italy53
3MarcusRashford3Italy51
4KylianMbappé5Italy66
5PhilFoden3Italy22
6Frenkiede Jong2Italy22
7MarioBalotelli1Italy36
8ErlingHaaland6Italy23
1GianfrancoZola1Netherlands35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3Netherlands51
4KylianMbappé5Netherlands66
5PhilFoden3Netherlands22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Netherlands36
8ErlingHaaland6Netherlands23
1GianfrancoZola1England35
2Virgilvan Dijk2England53
3MarcusRashford3England51
4KylianMbappé5England66
5PhilFoden3England22
6Frenkiede Jong2England22
7MarioBalotelli1England36
8ErlingHaaland6England23
1GianfrancoZola1Croatia35
2Virgilvan Dijk2Croatia53
3MarcusRashford3Croatia51
4KylianMbappé5Croatia66
5PhilFoden3Croatia22
6Frenkiede Jong2Croatia22
7MarioBalotelli1Croatia36
8ErlingHaaland6Croatia23

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:

idfirst_namelast_namenational_team_idid_2first_name_2last_name_2national_team_id_2
1GianfrancoZola17MarioBalotelli1
2Virgilvan Dijk26Frenkiede Jong2
3MarcusRashford35PhilFoden3
5PhilFoden33MarcusRashford3
6Frenkiede Jong22Virgilvan Dijk2
7MarioBalotelli11GianfrancoZola1

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:

idfirst_namelast_name
1SteveBergman
2SteveJohnson
3SteveKing

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

idfirst_namelast_name
1AnnColeman
2SteveBergman
3SteveYoung
4DonnaWinter
5SteveKing

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_namelast_name
SteveBergman
SteveKing

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:

idfirst_namelast_namemobile_data_limit
1MichaelWatson5,000
2NicoleGomez10,000
3SamStone8,000

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

idmobile_user_iddata_usedperiod
114,9872022_10
226,8752022_10
3312,5472022_10
415,0372022_11
5211,1112022_11
634,8972022_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_namelast_namemobile_data_limitdata_usedperiod
SamStone8,00012,5472022_10
MichaelWatson5,0005,0372022_11
NicoleGomez10,00011,1112022_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_idloan_duration
10011260
10020560
10020848
100333120
10035748
100398120

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:

idfirst_namelast_namesalarydepartment
1NicholasPoirot4,798.44IT
2SamanthaWolf5,419.24IT
3StewartJohnsons5,419.24IT
4JackieBiden8,474.54Sales
5MarkHamilton10,574.84Sales
6MarianaCosta9,747.54Sales
7PaulStewart3,498.12Accounting
8MaryRutte4,187.23Accounting
9ThomasSchwarz3,748.55Accounting

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:

departmentaverage_salary
Accounting3,811.30
Sales9,598.97
IT5,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:

departmentaverage_salary
Accounting3,811.30
IT5,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:

idfirst_namelast_namestart_datetermination_date
1ValeriaBogdanov2022-10-11NULL
2NicholasBertolucci2022-04-072022-11-11
3MathildeBauman2022-05-252022-10-01
4TrevorTrucks2022-01-28NULL
5MariaSzabo2022-03-15NULL

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_namelast_namestart_datetermination_date
NicholasBertolucci2022-04-072022-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:

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

Nachfolgend die song Daten:

idartist_idsong_title
11Purple Rain
22Purple Haze
33Europa
41Cream
51Bambi
61Why You Wanna Treat Me So Bad?
72Spanish Castle Magic
83Taboo
93Incident at Neshabur
103Flor 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:

idartist_namenumber_of_songs
1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding1
5Lou Rawls1

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:

idartist_namenumber_of_songs
1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding0
5Lou Rawls0

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:

idproduct_namequantityproduct_category
1Apple MacBook Air (2020) MGN63N/A Space Gray319Laptop
2Fairphone 4 128GB Green 5G208Mobile phone
3Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver157Desktop
4HP 17-cp0971nd487Laptop
5Huawei P30 Pro - 128GB - Blue148Mobile phone
6Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC514Desktop
7Toshiba Dynabook Satellite Pro E10-S-101 Notebook207Laptop
8Samsung Galaxy S23 5G - 256GB - Phantom Black56Mobile phone
9Intel Compleet PC | Intel Core i7-10700459Desktop

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_categoryproduct_quantity
Laptop806
Desktop973

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:

idfirst_namelast_name
1NinaLee
2CarolinaGreen
3MickJohnson

Die andere Tabelle ist sales:

idsalesperson_idmonthly_salesperiod
111,200.472021_10
225,487.222021_10
33700.472021_10
4115,747.542021_11
5216,700.872021_11
5314,322.872021_11
619,745.552021_12
729,600.972021_12
836,749.582021_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_namelast_namemonthly_salesperiod
NinaLee15,747.542021_11
CarolinaGreen16,700.872021_11
MickJohnson14,322.872021_11
NinaLee9,745.552021_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:

idartistalbumcopies_sold
1EaglesHotel California42,000,000
2Led ZeppelinLed Zeppelin IV37,000,000
3Shania TwainCome On Over40,000,000
4Fleetwood MacRumours40,000,000
5AC/DCBack in Black50,000,000
6Bee GeesSaturday Night Fever40,000,000
7Michael JacksonThriller70,000,000
8Pink FloydThe Dark Side of the Moon45,000,000
9Whitney HoustonThe Bodyguard45,000,000
10EaglesTheir 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:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
4Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
8Fleetwood MacRumours40,000,000
9Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,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:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
7Fleetwood MacRumours40,000,000
7Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,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:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
4EaglesTheir Greatest Hits (1971-1975)44,000,000
5EaglesHotel California42,000,000
6Shania TwainCome On Over40,000,000
6Fleetwood MacRumours40,000,000
6Bee GeesSaturday Night Fever40,000,000
7Led ZeppelinLed Zeppelin IV37,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:

idfirst_namelast_namesalarydepartment
1TimThompson10,524.74Sales
2MartinaHrabal7,895.14Accounting
3SusanTruman15,478.69Sales
4CiroConte8,794.41Accounting
5JorgeDe Lucia7,489.15Sales
6CarmenLopez10,479.15Accounting
7CatherineMolnar8,794.89Sales
8RichardBuchanan12,487.69Accounting
9MarkWong9,784.19Sales
10SilviaKarelias9,748.64Accounting

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_namelast_namesalarydepartmentsalary_rank
RichardBuchanan12,487.69Accounting1
CarmenLopez10,479.15Accounting2
SilviaKarelias9,748.64Accounting3
SusanTruman15,478.69Sales1
TimThompson10,524.74Sales2
MarkWong9,784.19Sales3

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:

idactual_revenueperiod
18,748,441.222022_07
210,487,444.592022_08
37,481,457.152022_09
47,497,441.892022_10
58,697,415.362022_11
612,497,441.562022_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_revenuemonthly_revenue_changeperiod
8,748,441.22NULL2022_07
10,487,444.591,739,003.372022_08
7,481,457.15-3,005,987.442022_09
7,497,441.8915,984.742022_10
8,697,415.361,199,973.472022_11
12,497,441.563,800,026.202022_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_revenuecumulative_revenueperiod
8,748,441.228,748,441.222022_07
10,487,444.5919,235,885.812022_08
7,481,457.1526,717,342.962022_09
7,497,441.8934,214,784.852022_10
8,697,415.3642,912,200.212022_11
12,497,441.5655,409,641.772022_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:

idexchange_ratedate
11.06662022-12-30
21.06832023-01-02
31.05452023-01-03
41.05992023-01-04
51.06012023-01-05
61.05002023-01-06
61.06962023-01-09
71.07232023-01-10
81.07472023-01-11
91.07722023-01-12
101.08142023-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_rateeur_usd_moving_averagedate
1.06661.06662022-12-30
1.06831.06752023-01-02
1.05451.06312023-01-03
1.05991.06092023-01-04
1.06011.05822023-01-05
1.05001.05672023-01-06
1.06961.05992023-01-09
1.07231.06402023-01-10
1.07471.07222023-01-11
1.07721.07472023-01-12
1.08141.07782023-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_idfirst_namelast_namemanager_id
5529JackSimmons5125
5238MariaPopovich5329
5329DanJacobsson5125
5009SimoneGudbois5329
5125AlbertKochNULL
5500JackieCarlin5529
5118SteveNicks5952
5012BonniePresley5952
5952HarryRaitt5529
5444SeanElsam5329

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_idemployee_first_nameemployee_last_namedirect_manager_iddirect_manager_first_namedirect_manager_last_namelevel
5529JackSimmons5125AlbertKoch0
5952HarryRaitt5529JackSimmons1
5500JackieCarlin5529JackSimmons1
5012BonniePresley5952HarryRaitt2
5118SteveNicks5952HarryRaitt2

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.