IMDb mit SQL
Die 👉 IMDb (Akronym für Internet Movie Database) ist eine Online-Datenbank zu Filmen, Fernsehserien, Videoproduktionen, Computerspielen und Personen, die daran mitgewirkt haben. Im Dezember 2023 gab es Einträge zu über 17,8 Millionen Titeln. Sie verteilen sich unter anderem auf über 667.000 verschiedene Spielfilmproduktionen, über 285.000 Videofilme, über 143.000 Fernsehfilme, mehr als 254.000 Fernsehserien, fast 8 Millionen Serienepisoden und mehr als 7,2 Millionen Podcast-Episoden. Zudem sind über 13,1 Millionen Film- und Fernsehschaffende aufgeführt. -- Wikipedia1
Nach Namen Suchen
Mit dem Schlüsselwort LIKE kann nach einem bestimmten Muster gesucht werden. % steht dabei für beliebige Zeichen.
Beispiel: Es sollen alle Personen ausgegeben werden, deren Name mit Georg beginnt.
SELECT * FROM persons WHERE name LIKE 'Georg%';
... oder alle Personen, deren Name ein grosses Z enthält.
SELECT * FROM persons WHERE name LIKE '%Z%';
Der LIKE Befehl beachtet die Gross- und Kleinschreibung! Damit man nicht (wie in Python) zuerst den zu durchsuchenden Text in kleine Buchstaben transformieren muss, gibt es das insensitive Like: ILIKE. Damit wird die Gross- und Kleinschreibung ignoriert.
SELECT * FROM persons WHERE name ILIKE '%z%';
gibt alle Personen mit einem grossen- oder kleinen z im Namen zurück.
Ohne ILIKE
ILIKEOhne den Befehl ILIKE könnte die Abfrage so aussehen:
SELECT * FROM persons WHERE name LIKE '%z%' OR name LIKE '%Z%';
oder
SELECT * FROM persons WHERE LOWER(name) LIKE '%z%' OR name LIKE '%Z%';
Listen Sie alle Personen auf, die
- mit Ihren Namen beginnen? (Achtung: Beim Namen
Johnsoll der NameJohnnynicht ausgegeben werden) - mit Ihrem Namen enden?
- ihren Namen enthalten?
Spalten benennen
Die Spaltennamen lassen sich bei der Abfrage verändern oder neu definieren:
SELECT name as Spitzname, tierart as Tier, lieblingsfutter as Frisst
FROM haustiere
- Wo liegt der Unterschied zwischen den beiden Queries? Führen Sie beide Queries aus und halten Sie den Unterschied fest.
- Funktioniert es auch mit einfachen Anführungszeichen
'?
SELECT name as Spitzname, tierart as Tier, lieblingsfutter as Frisst
FROM haustiere;
SELECT name as "Spitzname", tierart as "Tier", lieblingsfutter as "Frisst"
FROM haustiere;
Texte verändern
CONCAT(text1, text2, ...) oder text1 || text 2
Mit dem Schlüsselwort CONCAT können Texte zusammengefügt werden:
SELECT CONCAT('Hallo ', name) FROM persons;
-- oder äquivaltent
SELECT 'Hallo ' || name FROM persons;
⭐ Substring und Replace
Substring und ReplaceSUBSTRING(text, start, [length])
Mit dem Schlüsselwort SUBSTRING können Textausschnitte ausgegeben werden, wobei der start Index angibt, welches der erste Buchstaben ist. Achtung - hier hat der erste Buchstabe im Gegensatz zu Python den Index 1! Optional kann mit dem Parameter length die Länge des Ausschnitts angegeben werden.
SELECT SUBSTRING('Hallo', 2); -- > 'allo'
SELECT SUBSTRING('Hallo', 2, 3); -- > 'all'
SELECT SUBSTRING('Hallo', 1, 3); -- > 'Hal'
-- oder mit Spalteninhalten
SELECT SUBSTRING(name, 1, 5) FROM persons;
REPLACE(text, old, new)
Mit dem Schlüsselwort REPLACE können Textteile ersetzt werden
-- "Reto Holz" wird zu "Reto_Holz"
SELECT REPLACE(name, ' ', '_') FROM persons;
Weitere Operatoren und Funktionen
UPPER(text)schreibt alle Buchstaben grossLOWER(text)schreibt alle Buchstaben kleinMD5(text)berechnet den MD5-Hash eines Textes- ... 👉 https://www.postgresql.org/docs/current/functions-string.html
- Welche unterschiedlichen Filmtypen gibt es? Listen Sie alle auf.
- Zeigen Sie alle Attribute der ersten
100Kurzfilme aus dem Jahr 2026 an. - Die angezeigte ID (bspw.
tt31013067) findet man auch in der URL der IMDB-Seite. Erstellen Sie eine Abfrage, welche nebst allen Attributen noch zusätzlich dieURLfür die ersten 100 Kurzfilme ausgibt, die im Jahr 2026 erschienen sind.
Die URL hat folgendes Format:https://www.imdb.com/title/<ID>/
Aggregierte Abfragen
Oft sollen Ergebnisse in einer zusammengefassten Form ausgegeben werden. So ist bspw. oft von Interesse, wie viele Datensätze in einer Tabelle vorhanden sind, oder wie hoch der Durchschnittswert einer Spalte ist. Hierfür gibt es die sogenannten aggregierten Abfragen.
- COUNT
COUNT(*)Was macht diese Abfrage?SELECT COUNT(*) FROM legodudes;Laden...
Die Aggregationsfunktionen lassen sich nun auch als verschachtelte WHERE-Klauseln verwenden, bspw. um alle Legodudes auszugeben, die die höchste Lieblingszahl haben:
SELECT *
FROM legodudes
WHERE lieblingszahl = (SELECT MAX(lieblingszahl) FROM legodudes);
In der WHERE-Klausel können auch verschachtelte Abfragen verwendet werden, sofern diese nur einen einzigen Wert zurückgeben.
Geben Sie den jüngsten Legodude aus, indem Sie eine verschachtelte Unterabfrage verwenden.
Group By
Oft interessiert aber nicht nur die Anzahl der Datensätze, sondern die Anzahl der Datensätze pro Kategorie.
Hierfür gibt es den GROUP BY-Befehl.
GROUP BY
Mit GROUP BY lassen sich Datensätze nach einer oder mehrerer Spalten gruppieren. Die Aggregatfunktionen werden dann auf die einzelnen Gruppen angewendet.
SELECT land, COUNT(*)
FROM legodudes
GROUP BY land;
/* land | count
------------+-------
Irland | 1
Österreich | 1
Schweden | 1
USA | 3
Schweiz | 1 */
Für jede Spalte, die nicht in der GROUP BY-Klausel steht, muss eine Aggregatfunktion verwendet werden!
In welchem Jahr (start_year) wurden wie viele Spielfilme (movies) veröffentlicht? Erstellen Sie eine Tabelle, welche die Anzahl der Spielfilme pro Jahr anzeigt.
Zusatz:
- Ordnen Sie die Tabelle nach dem Startjahr absteigend.
- Zeigen Sie nur Einträge an, bei welchen das Startjahr bekannt (= nicht
NULL) ist. - Beschriften Sie die Spalten mit
JahrundAnzahl Spielfilme.