Zurück zur Artikelliste Artikel
24 Leseminuten

Grundlegende SQL-Abfragen online üben: 20 Übungen für Einsteiger

Author's photo
Tihomir Babic

Diese 20 Übungen sind genau das, was Anfänger zum Üben von SQL-Abfragen brauchen. Versuchen Sie, jede einzelne Aufgabe zu lösen, und sehen Sie sich die Lösungen an. Wenn etwas zu klären ist, gibt es Erklärungen zu jeder Lösung.

In diesem Artikel wird weniger geredet als sonst. Stattdessen werden Sie die Antworten auf die SQL-Übungsfragen schreiben. (Keine Sorge, wir haben die Lösungen beigefügt, falls Sie nicht weiterkommen.) Der Sinn des Ganzen ist, Ihnen als Anfänger reichlich Gelegenheit zu geben, SQL-Abfragen zu üben.

Ich habe zwanzig Beispiele aus unserem Grundlegende SQL-Praxis: Führe Abfragen durch! ausgewählt. Wenn Sie am Ende des Artikels das Gefühl haben, dass Sie mehr üben müssen - und das empfehle ich Ihnen von ganzem Herzen -, finden Sie in diesem Kurs fast 100 weitere interaktive SQL-Übungen. Sie behandeln Themen wie die Abfrage einer Tabelle, die Verwendung von JOINs, das Sortieren von Daten mit ORDER BY, das Aggregieren von Daten und die Verwendung von GROUP BY, den Umgang mit NULLs, das Ausführen mathematischer Operationen und das Schreiben von Unterabfragen.

Dies sind alles Themen, die jeder SQL-Anfänger gut kennen sollte, bevor er sich den komplexeren Themen zuwendet. Der beste Weg, etwas über SQL zu lernen, ist, ständig eigenen Code zu schreiben. Auf diese Weise beherrschen Sie die SQL-Syntax und ihre grundlegenden Funktionen, und Sie lernen, Probleme zu lösen. Schließlich geht es bei der Kenntnis von SQL darum, wie man Daten zur Lösung von Problemen nutzen kann.

Und Sie können sogar noch weiter gehen! Wir haben den SQL-Praxis Track und den monatlichen SQL-Praxis Kurs für noch mehr SQL-Abfragepraxis.

Lassen Sie uns also direkt in die SQL-Praxis eintauchen und mit dem Datensatz beginnen.

SQL-Abfrage-Übung

Datensatz

Das Dataset enthält Daten über die Finals von Leichtathletik-Wettbewerben bei verschiedenen Meisterschaften: Olympische Spiele in Rio de Janeiro 2016, IAAF Leichtathletik-Weltmeisterschaften in London 2017 und IAAF Leichtathletik-Weltmeisterschaften in Doha 2019.

Die Daten werden in sechs Tabellen gespeichert: competition, event, discipline, final_result, athlete, und nationality. Das Schema ist unten dargestellt:

Grundlegende SQL-Abfragen online üben

Die Wettbewerbsinformationen werden in der Tabelle competition. Sie hat die folgenden Spalten:

  • id - Die ID des Wettbewerbs und der Primärschlüssel der Tabelle.
  • name - Der Name des Wettkampfs.
  • start_date - Der erste Tag des Wettbewerbs.
  • end_date - Der letzte Tag des Wettbewerbs.
  • year - Das Jahr, in dem der Wettbewerb stattfand.
  • location - Der Ort des Wettkampfs.

Hier sind die Daten aus der Tabelle.

idnamestart_dateend_dateyearlocation
7093747Rio de Janeiro Olympic Games2016-08-122016-08-212016Estádio Olímpico, Rio de Janeiro (BRA)
7093740London IAAF World Championships in Athletics2017-08-042017-08-132017Olympic Stadium, London (GBR)
7125365IAAF World Championships in Athletics2019-09-272019-10-062019Khalifa International Stadium, Doha (QAT)

Die Tabelle discipline enthält Informationen für alle Laufdisziplinen. Sie hat diese Spalten:

  • id - Die ID der Disziplin und der Primärschlüssel der Tabelle.
  • name - Der Name der Disziplin.
  • is_men - TRUE, wenn es sich um eine Männerdisziplin handelt, FALSE, wenn es sich um eine Frauendisziplin handelt.
  • distance - Die Distanz der Disziplin, in Metern.

Dies ist ein Schnappschuss der ersten fünf Zeilen der Daten:

idnameis_mendistance
1Men's 100mTRUE100
2Men's 200mTRUE200
3Men's 400mTRUE400
4Men's 800mTRUE800
5Men's 1500mTRUE1,500

Die nächste Tabelle ist " Ereignis", in der Informationen über jedes einzelne Ereignis gespeichert werden:

  • id - Die ID des Ereignisses und der Primärschlüssel der Tabelle.
  • competition_id - Verknüpft das Ereignis mit einem Wettkampf.
  • discipline_id - Verknüpft das Ereignis mit einer Disziplin.
  • final_date - Wann das Finale dieses Ereignisses stattgefunden hat.
  • wind - Die Windpunkte während des Finales.

Hier sind die ersten fünf Zeilen dieser Tabelle:

idcompetition_iddiscipline_idfinal_datewind
1709374712016-08-140.2
2709374722016-08-18-0.5
3709374732016-08-140
4709374742016-08-150
5709374752016-08-200

Die Daten zu jedem Athleten befinden sich in der Tabelle athlete:

  • id - Die ID des Athleten und der Primärschlüssel der Tabelle.
  • first_name - Der Vorname des Athleten.
  • last_name - Der Nachname des Athleten.
  • nationality_id - Die Nationalität des Athleten.
  • birth_date - Das Geburtsdatum des Athleten.

Dies sind die ersten fünf Zeilen:

idfirst_namelast_namenationality_idbirth_date
14201847UsainBOLT11986-08-21
14238562JustinGATLIN21982-02-10
14535607AndréDE GRASSE31994-11-10
14201842YohanBLAKE11989-12-26

Die Tabelle nationality Tabelle enthält Länderinformationen:

  • id - Die ID des Landes und den Primärschlüssel der Tabelle.
  • country_name - Der Name des Landes.
  • country_abbr - Die aus drei Buchstaben bestehende Abkürzung des Landes.

Hier ist ein fünfzeiliger Schnappschuss dieser Tabelle:

idcountry_namecountry_abbr
1JamaicaJAM
2United StatesUSA
3CanadaCAN
4South AfricaRSA
5Côte d’IvoireCIV

Die letzte Tabelle ist final_result. Sie enthält Informationen über die Teilnehmer und ihre Ergebnisse bei einer bestimmten Veranstaltung:

  • event_id - Die Veranstaltungs-ID.
  • athlete_id - Die des Athleten
  • result - Die Zeit/Punktzahl des Athleten (kann NULL sein).
  • place - Die vom Athleten erreichte Platzierung (kann NULL sein).
  • is_dsq - TRUE wenn eineDisqualifikationerfolgte.
  • is_dnf - TRUE, wenn der Athlet den Lauf nicht beendet hat.
  • is_dns - TRUE, wenn der Athlet den Lauf nicht begonnen hat.

Hier ist der Schnappschuss:

event_idathlete_idresultplaceis_dsqis_dnsis_dnf
1142018470:00:101FALSEFALSEFALSE
1142385620:00:102FALSEFALSEFALSE
1145356070:00:103FALSEFALSEFALSE
1142018420:00:104FALSEFALSEFALSE
1144177630:00:105FALSEFALSEFALSE

Nachdem Sie nun einen guten Blick auf den Datensatz geworfen haben, beginnen wir mit der Übung der grundlegenden SQL-Abfragen! Für alle Übungen werden Sie SQL-Kenntnisse benötigen. Stellen Sie also sicher, dass Sie alle grundlegenden Elemente einer SQL-Abfrage kennen.

Übung #1: Anzeigen der Enddaten aller Ereignisse und der Windpunkte

Übung: Finden Sie die Endtermine aller Ereignisse und die Windpunkte.

Lösung:

SELECT
  final_date,
  wind
FROM event;

Erläuterung: Die Daten, die Sie benötigen, befinden sich in der Tabelle Ereignis. Daraus müssen Sie zwei Spalten auswählen: final_date und wind. Dazu schreiben Sie die erste Spalte in die Anweisung SELECT. Danach schreiben Sie den Namen der zweiten Spalte und trennen die Spaltennamen durch ein Komma.

Schließlich referenzieren Sie die Tabelle in der FROM -Klausel.

Ausgabe:

final_datewind
2016-08-140.2
2016-08-18-0.5
2016-08-140
2016-08-150
2016-08-200

Übung #2: Zeige alle Endrunden, bei denen der Wind über 0,5 Punkte lag

Übung: Zeigen Sie alle Finaldaten an, bei denen der Wind stärker als 0,5 Punkte war.

Lösung:

SELECT final_date
FROM event
WHERE wind > 0.5;

Erläuterung: Wählen Sie zunächst die Spalte final_date aus der Tabelle event. Damit erhalten Sie eine Liste aller Endrunden. Sie benötigen jedoch nicht die gesamte Liste, sondern nur die Endrunden, bei denen der Wind stärker als 0,5 war.

Daher müssen Sie die Daten mit der Klausel WHERE filtern. Darin geben Sie den Namen der Spalte an, die Sie filtern möchten; in diesem Fall ist es die Spalte wind. Um den Wind über 0,5 zu erhalten, verwenden Sie den logischen Operator "größer als" (>).

Ausgabe:

final_date
2017-08-11
2019-09-28
2019-10-02

Übung #3: Zeige alle Daten für alle Marathons

Übung: Zeigen Sie die Disziplindaten für alle Marathons an.

Lösung: 

SELECT *
FROM discipline	
WHERE name LIKE '%Marathon%';

Erläuterung: Um alle Spalten auszuwählen, müssen Sie deren Namen nicht explizit schreiben. Es gibt ein Kürzel für "alle Spalten", das Sternchen (*). Anstelle der Spaltennamen setzen Sie einfach ein Sternchen in SELECT ein.

Wenn Sie dann Daten aus der Tabelle disciplinebenötigen, verweisen Sie auf diese in FROM.

Schließlich müssen Sie die Daten filtern. Verwenden Sie WHERE und den LIKE-Operator. Dieser Operator durchsucht die Textdaten in der Spalte und gibt alle Zeilen zurück, die den Text in der Bedingung WHERE enthalten. Mit anderen Worten: Die Bedingung sucht nach dem Wort "Marathon". Sie müssen das Wort in einfache Anführungszeichen setzen.

Sie kennen jedoch nicht den genauen Namen der Disziplin; Sie wissen nur, dass er dieses Wort enthalten muss. Es kann an beliebiger Stelle im Namen der Disziplin stehen: am Anfang, in der Mitte oder am Ende. Um eine beliebige Stelle in der Zeichenkette zu suchen, setzen Sie den Operator modulo (%) vor und hinter das gesuchte Wort.

Ausgabe:

idnameis_mendistance
8Men's MarathonTRUE42,195
16Women's MarathonFALSE42,195

Übung #4: Alle Endergebnisse für Nicht-Plazierungsläufer anzeigen

Übung: Zeigen Sie alle Daten für die Endergebnisse der Läufer an, die sich nicht platziert haben.

Lösung:

SELECT *
FROM final_result
WHERE place IS NULL;

Erläuterung: Sie benötigen alle Spalten, also verwenden Sie ein Sternchen in SELECT und verweisen auf die Tabelle final_result in FROM.

Sie müssen nur die Ergebnisse anzeigen, bei denen die Läufer ohne Platzierung geblieben sind. Auch dieses Mal verwenden Sie WHERE und filtern nach der Spalte place. Wenn ein Läufer ohne Platzierung endet, dann ist die Spalte place leer (d.h. NULL). Sie benötigen den Operator IS NULL nach dem Spaltennamen, um alle diese Zeilen zurückzugeben.

Bevor Sie den IS NULL Operator verwenden, sollten Sie wissen, was NULL in SQL bedeutet .

Ausgabe:

event_idathlete_idresultplaceis_dsqis_dnsis_dnf
614464221NULLNULLTRUEFALSEFALSE
714530623NULLNULLFALSEFALSETRUE
714573513NULLNULLFALSEFALSETRUE
814167397NULLNULLFALSEFALSETRUE
814177784NULLNULLFALSEFALSETRUE

Übung Nr. 5: Alle Ergebnisdaten für nicht gestartete Läufer anzeigen

Übung: Zeigen Sie alle Ergebnisdaten von Läufern an, die den Lauf gar nicht gestartet haben.

Lösung: 

SELECT *
FROM final_result
WHERE is_dns IS TRUE;

Erläuterung: Markieren Sie alle Spalten in der Tabelle final_result mit einem Sternchen aus und referenzieren Sie die Tabelle in FROM.

Dann verwenden Sie WHERE und filtern die Spalte nach is_dns. Wenn der Läufer nicht am Rennen teilgenommen hat, wird diese Spalte den Wert TRUE haben. Sie müssen also den Operator IS TRUE nach dem Spaltennamen verwenden.

Ausgabe: Hier ist die gesamte Ausgabe:

event_idathlete_idresultplaceis_dsqis_dnsis_dnf
1414451797NULLNULLFALSETRUEFALSE
1614296979NULLNULLFALSETRUEFALSE
1914176330NULLNULLFALSETRUEFALSE
2214367867NULLNULLFALSETRUEFALSE
2414219653NULLNULLFALSETRUEFALSE
2414225279NULLNULLFALSETRUEFALSE
3214751813NULLNULLFALSETRUEFALSE
4114291986NULLNULLFALSETRUEFALSE

Übung Nr. 6: Namen für Männer-Disziplinenläufe unter 500 Meter anzeigen

Übung: Zeigen Sie nur die Namen der Männer-Disziplinen an, bei denen die zu laufende Distanz weniger als 500 Meter beträgt.

Lösung:

SELECT name
FROM discipline
WHERE is_men IS TRUE
  AND distance < 500;

Erläuterung: Wählen Sie zunächst den Spaltennamen aus der Tabelle discipline.

Sie müssen die Daten erneut filtern - diesmal durch zwei Bedingungen in WHERE.

Die erste Bedingung ist, dass es sich um eine männliche Disziplin handelt. Sie müssen also die Spalte is_men mit dem Operator IS TRUE filtern. Dann fügen Sie die zweite Bedingung hinzu: Die Werte in der Spalte distance müssen unter 500 liegen. Für diese Bedingung wird der Operator kleiner als (<) verwendet. Da beide Bedingungen erfüllt sein müssen, trennen Sie die Bedingungen mit dem Operator AND.

Ausgabe:

name
Men's 100m
Men's 200m
Men's 400m

Übung #7: Sortieren von Ländernamen und Abkürzungen

Übung: Zeigen Sie alle Ländernamen und Abkürzungen an. Sortieren Sie die Ausgabe alphabetisch nach Ländernamen.

Lösung:

SELECT
  country_name,
  country_abbr
FROM nationality
ORDER BY country_name ASC;

Erklären Sie: Wählen Sie den Ländernamen und seine Abkürzung aus der Tabelle nationality.

Um die Ausgabe zu sortieren, verwenden Sie die Klausel ORDER BY. Sie wollen nach Ländernamen sortieren, also schreiben Sie country_name in ORDER BY. Die Ausgabe soll alphabetisch sortiert werden, also verwenden Sie das Schlüsselwort ASC (aufsteigend) nach dem Spaltennamen.

Ausgabe: Hier sind die ersten fünf Zeilen der Ausgabe:

country_namecountry_abbr
AfghanistanAFG
AlgeriaALG
American SamoaASA
AndorraAND
AngolaANG

Übung Nr. 8: Sortieren der Vor- und Nachnamen der Athleten

Übung: Zeigen Sie den Vor- und Nachnamen eines jeden Sportlers an. Sortieren Sie die Ausgabe absteigend nach dem Vornamen des Athleten. Wenn mehrere Athleten den gleichen Namen haben, zeigen Sie deren Nachnamen absteigend sortiert an.

Lösung:

SELECT
  first_name,
  last_name
FROM athlete
ORDER BY first_name DESC, last_name DESC;

Erläuterung: Wählen Sie den Vor- und Nachnamen aus der Tabelle athlete.

Fügen Sie dann die Klausel ORDER BY hinzu. Sortieren Sie zunächst absteigend nach dem Vornamen und fügen Sie DESC nach dem Spaltennamen hinzu. Die zweite Sortierbedingung sortiert nach dem Nachnamen, ebenfalls in absteigender Reihenfolge. Auch hier schreiben Sie den Spaltennamen und fügen DESC hinzu. Die Bedingungen müssen durch ein Komma getrennt werden.

Ausgabe: Hier sind die ersten fünf Zeilen der Ausgabe:

first_namelast_name
ZsófiaERDÉLYI
ZouhairAWAD
ZoeyCLARK
ZoeHOBBS
ZoeBUCKMAN

Übung #9: Sortieren der Endergebnisse über drei Stunden

Übung: Zeigen Sie für alle Endergebnisse die Zeiten an, die mindestens drei Stunden betragen. Sortieren Sie die Zeilen nach dem Ergebnis in absteigender Reihenfolge.

Lösung: 

SELECT result
FROM final_result
WHERE result >= INTERVAL '3 hours'
ORDER BY result DESC;

Erläuterung: Wählen Sie die Spalte result aus der Tabelle final_result.

Verwenden Sie dann WHERE, um die Ergebnisse zu finden, die unter drei Stunden liegen. Sie können die Operatoren "größer als oder gleich" (>=) und INTERVAL verwenden.

Die Daten in der Ergebnisspalte sind als Zeitangabe formatiert. Daher müssen Sie INTERVAL verwenden, um den spezifischen Teil (Intervall) dieser Daten zu ermitteln. In diesem Fall sind es drei Stunden. Schreiben Sie einfach "3 Stunden" nach INTERVAL.

Schließlich sortieren Sie die Ausgabe absteigend nach dem Ergebnis.

Ausgabe: Hier sind die ersten fünf Zeilen der Ausgabe:

result
3:20:20
3:16:11
3:15:18
3:11:31
3:11:05

Übung #10: Zeige die Namen und Orte der Top 3 Athleten

Übung: Zeigen Sie für jeden Athleten, der jemals auf dem Podium stand (d.h. unter den Top 3 war), seinen Vor- und Nachnamen sowie seinen Platz an.

Lösung:

SELECT
  a.last_name,
  a.first_name,
  fin.place
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place <= 3;

Erläuterung: In dieser Übung müssen Sie Daten aus zwei Tabellen verwenden: athlete und final_result. Beginnen wir also die Erklärung mit der FROM Klausel.

Sie referenzieren die athlete und geben ihr den Alias 'a', damit Sie den vollen Namen der Tabelle nicht an anderer Stelle in der Abfrage schreiben müssen. Um auch Daten aus einer anderen Tabelle zu erhalten, müssen Sie die Tabellen verbinden. In diesem Fall verwenden Sie JOIN, das nur die übereinstimmenden Zeilen aus beiden Tabellen zurückgibt. Dazu verweisen Sie einfach auf die Tabelle final_result in JOIN referenzieren und den Alias 'fin' hinzufügen.

Als nächstes müssen Sie die Verknüpfungsbedingung mit dem Schlüsselwort ON angeben. Die Tabellen werden über gemeinsame Spalten verbunden: id from athlete und athlete_id von final_result. Sie suchen nach Zeilen, in denen die Werte in diesen beiden Spalten gleich sind, also setzen Sie ein Gleichheitszeichen (=) zwischen die beiden Spalten. Stellen Sie vor jeden Spaltennamen den Tabellenalias, gefolgt von einem Punkt, damit die Datenbank weiß, in welcher Tabelle sich die Spalte befindet.

Nachdem Sie nun die Tabellen verbunden haben, können Sie die Spalten auswählen. Setzen Sie vor jeden Spaltennamen den Tabellenalias aus demselben Grund wie oben beschrieben. Jetzt haben Sie die Vor- und Nachnamen der Athleten und deren Orte.

Als letzten Schritt filtern Sie die Daten einfach mit WHERE und der Spalte place. Sie suchen nach Podiumsplatzierungen, also müssen die Werte gleich oder kleiner als drei sein. Verwenden Sie den Operator "kleiner als oder gleich" (<=).

Für diese SQL-Abfragepraxis müssen Sie SQL-JOINs kennen. Wenn Sie immer noch unsicher sind, wie sie funktionieren, schauen Sie sich diese SQL-JOINs Übungsfragen an, bevor Sie zu anderen Übungen übergehen.

Ausgabe: Hier sind die ersten fünf Zeilen der Ausgabe:

last_namefirst_nameplace
BOLTUsain3
BOLTUsain1
BOLTUsain1
GATLINJustin2
GATLINJustin1

Übung #11: Alle Marathons mit dem Namen des Wettkampfs, dem Jahr des Wettkampfs und dem Namen der Disziplin anzeigen

Übung: Zeigen Sie alle Marathons, den Namen (benennen Sie diese Spalte wettkampf_name) und das Jahr des Wettkampfs sowie den Namen der Disziplin (benennen Sie diese Spalte disziplin_name).

Lösung:

SELECT
  c.name AS competition_name,
  c.year,
  d.name AS discipline_name
FROM competition c
JOIN event e
  ON e.competition_id = c.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE d.name LIKE '%Marathon%';

Erläuterung: Diese Übung zeigt, wie man mehrere Tabellen verknüpft. Das Prinzip ist dasselbe wie bei zwei Tabellen. Sie fügen einfach weitere JOINs und die Verknüpfungsbedingungen hinzu.

In diesem Fall verknüpfen Sie die Tabellen competition und event Tabellen, wobei e.competition_id gleich c.id column ist.

Dann müssen Sie die Tabelle discipline Tabelle zur Verknüpfungskette hinzufügen. Schreiben Sie erneut JOIN und verweisen Sie auf die Tabelle discipline. Fügen Sie die Verknüpfungsbedingung hinzu: Die Spalte discipline_id aus der Tabelle event muss gleich der Spalte id aus der Tabelle discipline Tabelle.

Wählen Sie nun die gewünschten Spalten aus und vergessen Sie nicht, den Tabellenalias vor jede Spalte zu setzen. Geben Sie competition_name und discipline_name mit dem Schlüsselwort AS die in der Anleitung beschriebenen Spaltennamen.

Filtern Sie schließlich die Ergebnisse so, dass nur die Marathondisziplinen angezeigt werden.

Ausgabe:

competition_nameyeardiscipline_name
Rio de Janeiro Olympic Games2016Men's Marathon
Rio de Janeiro Olympic Games2016Women's Marathon
London IAAF World Championships in Athletics2017Men's Marathon
London IAAF World Championships in Athletics2017Women's Marathon
IAAF World Championships in Athletics2019Men's Marathon

Übung #12: Zeige Mo Farahs Ergebnisse für alle Disziplinen

Übung: Zeigen Sie die Ergebnisse von Mo Farah (Athleten-ID von 14189197) für alle Disziplinen an. Zeigen Sie NULL an, wenn er noch nie an einer bestimmten Disziplin teilgenommen hat. Zeigen Sie die Namen, Daten, Orte und Ergebnisse aller männlichen Disziplinen.

Lösung:

SELECT
  d.name AS discipline_name,
  e.final_date,
  fin.place,
  fin.result
FROM discipline d
LEFT JOIN event e
  ON e.discipline_id = d.id
LEFT JOIN final_result fin
  ON fin.event_id = e.id
 AND athlete_id = 14189197
WHERE is_men IS TRUE;

Erläuterung: Verbinden Sie die Tabellen discipline und event über die Spalten discipline_id und id. Sie müssen LEFT JOIN verwenden. Diese Art der Verknüpfung liefert alle Zeilen aus der ersten (linken) Tabelle und nur die passenden Zeilen aus der zweiten (rechten) Tabelle. Wenn es keine übereinstimmenden Zeilen gibt, lauten die Werte NULL. Dies ist ideal für diese Übung, da Sie alle Disziplinen anzeigen und NULLs verwenden müssen, wenn Mo Farah nie an der Disziplin teilgenommen hat.

Die nächste Verknüpfung ist ebenfalls eine LEFT JOIN. Sie verbindet die Tabelle event mit der Tabelle final_result. Die erste Verknüpfungsbedingung hier verbindet die Tabellen über die Spalten event_id und id. Sie müssen auch die zweite Bedingung einschließen, indem Sie das Schlüsselwort AND hinzufügen. Diese zweite Bedingung sucht nur nach den Daten von Mo Farah, d. h. nach dem Athleten mit der ID 14189197.

Als letzten Schritt verwenden Sie WHERE, um nur die Disziplinen der Männer zu finden.

Ausgabe:

discipline_namefinal_dateplaceresult
Men's 5000m2016-08-2010:13:03
Men's 10,000m2016-08-1310:27:05
Men's 5000m2017-08-1220:13:33
Men's 10,000m2017-08-0410:26:50
Men's 800m2017-08-08NULLNULL
Men's Marathon2019-10-05NULLNULL
Men's 100m2017-08-05NULLNULL

Übung #13: Zeigen Sie die Namen der Wettbewerbe und die Anzahl der Wettkämpfe an

Aufgabe: Geben Sie die Namen aller Wettkämpfe und die Anzahl der Disziplinen für jeden Wettkampf an.

Lösung: 

SELECT
  c.name AS competition_name,
  COUNT(*) AS events_held
FROM competition c
JOIN event e
  ON e.competition_id = c.id
GROUP BY c.name;

Erläuterung: Zeigen Sie zunächst den Spaltennamen aus der Tabelle competition und benennen Sie die Spalte in competition_name um.

Verwenden Sie dann die Aggregatfunktion COUNT(*), um die Anzahl der durchgeführten Veranstaltungen zu zählen. Die Funktion COUNT() mit einem Sternchen zählt alle Zeilen der Ausgabe, einschließlich der NULL-Zeilen. Der besseren Lesbarkeit halber geben wir der resultierenden Spalte den Alias events_held.

Die Tabellen, die wir verbinden, sind competition und event. Um schließlich die Anzahl der Ereignisse pro Wettbewerb zu erhalten, müssen Sie den Namen des Wettbewerbs unter GROUP BY eingeben.

Ausgabe:

competition_nameevents_held
IAAF World Championships in Athletics15
Rio de Janeiro Olympic Games16
London IAAF World Championships in Athletics16

Übung #14: Zeige die beliebtesten Sportlernamen

Übung: Zeigen Sie die beliebtesten Sportlernamen. Namen sind beliebt, wenn sie von mindestens fünf Athleten benutzt werden. Geben Sie neben dem Namen auch die Anzahl der Athleten mit diesem Namen an. Sortieren Sie die Ergebnisse so, dass die populärsten Namen an erster Stelle stehen.

Lösung:

SELECT
  first_name,
  COUNT(*) AS name_count
FROM athlete
GROUP BY first_name
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC;

Erläuterung: Wählen Sie zunächst die Vornamen aus und zählen Sie sie mit COUNT(*). Anschließend gruppieren Sie nach dem Vornamen des Sportlers. Jetzt haben Sie alle Namen und deren Anzahl.

Es sollen aber nur die Namen angezeigt werden, deren Anzahl größer als fünf ist. Dies erreichen Sie mit der HAVING Klausel. Sie hat den gleichen Zweck wie WHERE, aber HAVING wird zum Filtern aggregierter Daten verwendet.

Schließlich sortieren Sie die Ausgabe nach der Anzahl der Namen vom höchsten zum niedrigsten. Sie können nicht einfach den Spaltennamen name_count in ORDER BY schreiben, da die Sortierung vor der Aggregation erfolgt; SQL erkennt den Spaltennamen nicht. Kopieren Sie stattdessen COUNT(*) und sortieren Sie absteigend.

Diese Übung zeigt ein typisches SQL-Problem, das das Filtern von Daten mit einer Aggregatfunktion erfordert.

Ausgabe:

first_namename_count
David9
Daniel7
Michael7
Jessica6
Alex6
Sarah5
Diana5
Jonathan5
Emmanuel5
Isaac5
Julian5
Anna5

Übung #15: Zeige jedes Land und die Anzahl der Athleten, die ohne Platzierung ins Ziel gekommen sind

Übung: Zeigen Sie alle Länder mit der Anzahl ihrer Athleten, die ohne Platzierung ins Ziel gekommen sind. Zeigen Sie 0 an, wenn es keine gab. Sortieren Sie die Ausgabe absteigend nach der Anzahl der Athleten und aufsteigend nach dem Ländernamen.

Lösung:

SELECT
  n.country_name,
  COUNT(fin.athlete_id) AS athletes_no
FROM nationality n
LEFT JOIN athlete a
  ON n.id = a.nationality_id
LEFT JOIN final_result fin
  ON a.id = fin.athlete_id
 AND fin.place IS NULL
GROUP BY n.country_name
ORDER BY
  COUNT(fin.athlete_id) DESC,
  n.country_name ASC;

Erläuterung: Sie müssen alle Zeilen der Tabelle nationality Tabelle behalten, also müssen Sie sie mit der LEFT JOIN athlete Tabelle. Das tun Sie, indem Sie id mit nationality_id gleichsetzen. Dann LEFT JOIN eine andere Tabelle, bei der id aus der athlete Tabelle gleich athlete_id aus der final_result Tabelle entspricht.

Da Sie alle Nationalitätenzeilen benötigen, können Sie die Bedingung IS NULL nicht in WHERE verwenden. Es gibt eine Lösung: Verschieben Sie sie in die ON Klausel, und Sie erhalten alle Werte, deren Ort NULL ist.

Jetzt können Sie die Spalte country_name auswählen. Verwenden Sie außerdem die Funktion COUNT() für die Spalte athlete_id, um die Anzahl der Sportler zu ermitteln, die ohne Platzierung ins Ziel gekommen sind. Die Funktion COUNT(*) kann hier nicht verwendet werden, da sie f gezählt hätte, und Sie brauchen die Anzahl der konkreten Athleten.

Um den Zählwert nach Land zu erhalten, gruppieren Sie die Ausgabe nach Ländernamen.

Schließlich sortieren Sie die Ausgabe nach der Anzahl der Athleten absteigend und nach dem Ländernamen aufsteigend.

Ausgabe: Hier sind die ersten fünf Zeilen der Ausgabe:

country_nameathletes_no
Bahrain8
Ethiopia6
Turkey6
Kenya5
South Africa5

Übung Nr. 16: Berechnen Sie die durchschnittliche Geschwindigkeit für jeden Lauf

Übung: Berechnen Sie das durchschnittliche Tempo für jeden Lauf und zeigen Sie es in der Spalte average_pace an.

Lösung:

SELECT fin.result / (d.distance * 1.0 / 1000) AS average_pace
FROM event e
JOIN discipline d
  ON e.discipline_id = d.id
JOIN final_result fin
  ON fin.event_id = e.id;

Erläuterung: Um die durchschnittliche Pace pro Lauf zu erhalten, müssen Sie das Ergebnis durch die Distanz dividieren. Dies geschieht mit der obigen Abfrage, allerdings mit zwei Änderungen.

Zunächst müssen Sie die Entfernung mit 1,0 multiplizieren. Damit konvertieren Sie die Entfernung in eine Dezimalzahl. Andernfalls könnte die Division ein anderes Ergebnis liefern, da das Ergebnis durch eine ganze Zahl geteilt wird. Die zweite Änderung besteht darin, dass Sie die Entfernung durch 1.000 teilen. Auf diese Weise konvertieren Sie die Entfernung von Metern in Kilometer.

Da Sie nun die Berechnung haben, geben Sie dieser Spalte den Alias average_pace.

Der Rest der Abfrage entspricht dem, was Sie bereits in früheren Beispielen gesehen haben: Sie verknüpfen die Tabelle event mit der Tabelle discipline und dann mit der Tabelle final_result.

Ausgabe: Hier sind die ersten fünf Zeilen der Ausgabe:

average_pace
0:01:38
0:01:39
0:01:39
0:01:39
0:01:39

Beispiel #17: Alle überdurchschnittlich schnellen Zeiten für 1.500-Meter-Läufe finden

Übung: Geben Sie die Zeiten für alle 1.500-Meter-Läufe aus. Zeigen Sie nur Zeiten an, die schneller sind als die Durchschnittszeit für diesen Lauf.

Lösung:

SELECT fin.result
FROM final_result fin
JOIN event e
  ON fin.event_id = e.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE distance = 1500
  AND fin.result < (
    SELECT AVG(fin.result)
    FROM final_result fin
    JOIN event e
      ON fin.event_id = e.id
    JOIN discipline d
      ON e.discipline_id = d.id
    WHERE distance = 1500
  );

Erläuterung: Um diese Aufgabe zu lösen, müssen Sie SQL-Unterabfragen kennen. Ihre grundlegende Definition ist, dass es sich um Abfragen innerhalb einer Hauptabfrage handelt. Schauen wir uns an, wie das funktioniert!

Wählen Sie die Ergebnisspalte aus der Tabelle final_result. Dann JOIN die Tabelle mit event und dann mit der discipline Tabelle.

Danach müssen Sie in WHERE zwei Bedingungen festlegen. Die erste wählt nur Strecken aus, die gleich 1.500 sind.

Die zweite sucht nach Daten, bei denen das Ergebnis unter dem Gesamtdurchschnitt für 1.500-Meter-Läufe liegt. Um den Durchschnitt zu berechnen, verwenden Sie eine Unterabfrage auf folgende Weise.

Schreiben Sie in die Klammern nach dem Vergleichsoperator eine weitere SELECT -Anweisung (d. h. eine Unterabfrage). Verwenden Sie darin die Aggregatfunktion AVG(), um das durchschnittliche Ergebnis zu berechnen. Der Rest der Abfrage ist derselbe wie die Hauptabfrage; Sie verknüpfen dieselben Tabellen und verwenden dieselbe Filterungsbedingung in WHERE.

Ausgabe: Hier sind die ersten paar Zeilen der Ausgabe:

result
0:03:51
0:03:51
0:03:51
0:03:51
0:03:51
0:03:50
0:03:50
0:03:51

Übung #18: Finden Sie alle Athleten, die an mindestens zwei Wettkämpfen teilgenommen haben

Übung: Geben Sie eine Liste der Athleten aus, die an zwei oder mehr Wettkämpfen eines beliebigen Wettbewerbs teilgenommen haben. Zeigen Sie nur die Vor- und Nachnamen an.

Lösung:

SELECT
  first_name,
  last_name
FROM athlete
WHERE id IN (
  SELECT fin.athlete_id
  FROM event e
  JOIN final_result fin
    ON fin.event_id = e.id
  GROUP BY e.competition_id, fin.athlete_id
  HAVING COUNT(*) >= 2
);

Erläuterung: Wählen Sie zunächst den Vor- und den Nachnamen aus der Tabelle athlete.

Dann verwenden Sie WHERE, um eine Bedingung aufzustellen. Wir verwenden wieder eine Unterabfrage, um die Daten zurückzugeben, die wir vergleichen wollen, diesmal mit der Spalte id. Im vorherigen Beispiel haben wir jedoch den Operator "kleiner als" (<) verwendet, weil die Unterabfrage nur einen Wert zurückgegeben hat. Diesmal verwenden wir den Operator IN, der alle von der Unterabfrage zurückgegebenen Werte durchgeht und diejenigen zurückgibt, die die Bedingung erfüllen.

Die Bedingung ist, dass die Athleten an mindestens zwei Veranstaltungen innerhalb eines Wettkampfs teilnehmen. Um diese Sportler zu finden, wählen Sie die Spalte athlete_id aus und verknüpfen die Tabellen event und final_result. Gruppieren Sie dann die Ergebnisse nach den Wettkampf- und Athleten-IDs. Dieses Beispiel zeigt, dass Sie die Ausgabe nach der Spalte gruppieren können, die nicht in SELECT enthalten ist. Allerdings müssen alle Spalten, die in SELECT erscheinen, auch in GROUP BY enthalten sein.

Verwenden Sie schließlich HAVING, um die Daten zu filtern. Zählen Sie die Anzahl der Zeilen mit COUNT(*). Auf diese Weise zählen Sie, wie oft jeder Sportler vorkommt. Legen Sie die Bedingung so fest, dass nur die Athleten zurückgegeben werden, deren Anzahl gleich oder größer als zwei ist.

Ausgabe: Hier ist der Schnappschuss der Ausgabe.

first_namelast_name
UsainBOLT
AndréDE GRASSE
AaronBROWN
LaShawnMERRITT
WaydeVAN NIEKERK

Übung Nr. 19: Läufer anzeigen, die nur als Erste ins Ziel gekommen sind

Übung: Zeigen Sie alle Läufer an, die nie auf einem anderen Platz als dem ersten ins Ziel gekommen sind; für sie hat nie ein Platz gefehlt. Zeigen Sie drei Spalten an: id, first_name, und last_name.

Lösung:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1

EXCEPT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place != 1 OR fin.place IS NULL;

Erläuterung: Für diese Lösung müssen Sie den EXCEPT set-Operator verwenden. Die Set-Operatoren werden verwendet, um die Werte aus zwei oder mehr Abfragen zurückzugeben. EXCEPT gibt alle eindeutigen Datensätze aus der ersten Abfrage zurück , mit Ausnahme der jenigen, die von der zweiten Abfrage zurückgegeben werden.

Die erste Abfrage in der Lösung sucht nach den Sportlern, die den ersten Platz belegt haben. Um diese Werte zu erhalten, wählen Sie die erforderlichen Spalten aus der Tabelle athlete. Verknüpfen Sie dann die Tabelle mit der Tabelle final_result. Danach stellen Sie die Bedingung in WHERE so ein, dass nur die ersten Plätze gefunden werden.

Geben Sie nun das Schlüsselwort EXCEPT ein und folgen Sie ihm mit der zweiten Abfrage.

Die zweite Abfrage ist fast die gleiche wie die erste. Der einzige Unterschied sind zwei Bedingungen in WHERE.

Die erste Bedingung gibt alle Plätze zurück, die nicht der erste sind, indem sie den Operator "nicht gleich" (!=) verwendet. Die zweite Bedingung sucht nach den Platzierungen, die nichtNULL sind, d. h., die Platzierung hat für diesen Athleten nie gefehlt. Die Bedingungen werden mit ODER verknüpft, da eine dieser Bedingungen wahr sein muss; der Athlet kann nicht unter dem ersten Platz landen und gleichzeitig gar nicht ins Ziel kommen.

Beachten Sie, dass die Mengenoperatoren nur dann funktionieren, wenn beide Abfragen die gleiche Anzahl von Spalten desselben Datentyps enthalten.

Ausgabe:

idfirst_namelast_name
14590785Elijah MotoneiMANANGOI
14208194EliudKIPCHOGE
14603138DonavanBRAZIER
14289014Jemima JelagatSUMGONG
14536762NoahLYLES
14377814LelisaDESISA
14209691DavidRUDISHA
14431159HalimahNAKAAYI

Übung #20: Finde alle Athleten, die nicht gestartet sind und mindestens einmal gewonnen haben

Übung: Geben Sie die Athleten aus, die bei mindestens einem Rennen nicht gestartet sind und mindestens ein Rennen gewonnen haben. Zeigen Sie drei Spalten an: id, first_name, und last_name.

Lösung:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.is_dns IS TRUE

INTERSECT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1;

Erläuterung: In dieser Übung wird ein weiterer Mengenoperator verwendet. Diesmal ist es INTERSECT, der alle Werte zurückgibt, die in beiden Abfragen gleich sind.

Die erste Abfrage in der Lösung listet die IDs sowie die Vor- und Nachnamen der Athleten auf. Die Tabellen athlete und final_result werden über die Spalten id und athlete_id aus den Tabellen verbunden.

Die Bedingung in WHERE sucht nach Zeilen mit dem Wert TRUE in der Spalte is_dns, d. h. in der Spalte, die anzeigt, ob der Athlet an dem Rennen teilgenommen hat.

Wie im vorherigen Beispiel schreiben Sie den Set-Operator und dann die zweite Abfrage.

Die zweite Abfrage ist die gleiche wie die erste, mit Ausnahme von WHERE. Die Filterungsbedingung findet die Athleten, die als erste ins Ziel gekommen sind.

Zusammen geben diese beiden Abfragen die Athleten aus, die nicht mindestens einmal am Start waren, aber auch mindestens einmal als Erste ins Ziel kamen.

Ausgabe:

idfirst_namelast_name
14291986DafneSCHIPPERS

Von der grundlegenden SQL-Abfragepraxis zum SQL-Meister

Irgendwo muss man ja anfangen. Diese 20 grundlegenden SQL-Abfrageübungen sind ideal, um eine Grundlage zu schaffen, bevor Sie fortgeschrittenere Konzepte lernen.

Sie haben viel gelernt, als Sie geübt haben, Abfragen zu schreiben, die WHERE, ORDER BY, JOINs, GROUP BY und HAVING verwenden. Ich habe Ihnen auch mehrere Beispiele für den Umgang mit NULLs, Berechnungen, das Schreiben von Unterabfragen und die Verwendung von Mengenoperatoren gezeigt. Die Abfragen in diesem Artikel stammen aus unserem Grundlegende SQL-Praxis: Führe Abfragen durch! Dort finden Sie weitere grundlegende SQL-Übungen. Und wenn Sie noch mehr üben wollen, schauen Sie sich unseren SQL-Praxis Track, der 9 SQL-Übungskurse für Anfänger enthält.

Fügen Sie 20 Beispiele für grundlegende SQL-Abfragen und 10 SQL-Übungen für Anfänger hinzu, und Sie werden mit einem mittleren Niveau an SQL-Kenntnissen ausgestattet sein.

Tags:

Das könnte Sie auch interessieren