SQL | EF Informatik 2022

SQL steht für «Structured Query Language» und ist sozusagen der De-Facto-Standard für Datenbanksprachen bei Relationalen Datenbanken.

# Select

Die SELECT-Anweisung startet eine Abfrage und liefert als Ergebnis Datensätze zurück. Die SELECT-Syntax sieht wie folgt aus (unvollständig, optionale Angaben in eckiger Klammer, mögliche mehrfache Angaben durch ‚+’ gekennzeichnet):

SELECT [DISTINCT] Auswahlliste
 FROM Quelle
 [WHERE Where-Klausel]
 [GROUP BY (Group-by-Attribut)+
  [HAVING Having-Klausel]]
 [ORDER BY (Sortierungsattribut [ASC|DESC])+];

1
2
3
4
5
6

DISTINCT gibt an, dass aus der Ergebnisrelation gleiche Ergebnistupel entfernt werden sollen. Es wird also jeder Datensatz nur einmal ausgegeben, auch wenn er mehrfach in der Tabelle vorkommt.
«Auswahlliste» bestimmt, welche Spalten der Quelle auszugeben sind (* für alle) und ob Aggregatsfunktionen anzuwenden sind. Wie bei allen anderen Aufzählungen werden die einzelnen Elemente mit Komma voneinander getrennt.
«Quelle» gibt an, wo die Daten herkommen. Es können Relationen und Sichten (VIEW) angegeben werden und miteinander als kartesisches Produkt oder als Verbund (JOIN) verknüpft werden. Mit der zusätzlichen Angabe eines Namens können Tupelvariablen besetzt werden, d. h. Relationen für die Abfrage umbenannt werden.
«Where»-Klausel bestimmt Bedingungen, unter denen die Daten ausgegeben werden sollen. In SQL ist hier auch die Angabe von Unterabfragen möglich, so dass SQL streng relational vollständig wird.
«Group-by-Attribut» legt fest, ob unterschiedliche Werte als einzelne Zeilen ausgegeben werden sollen (GROUP BY = Gruppierung) oder aber die Feldwerte der Zeilen durch Addition (SUM), Durchschnitt (AVG), Minimum (MIN), Maximum (MAX) o.ä. zu einem Ergebniswert zusammengefasst werden, der sich dann auf die Gruppierung bezieht.
«Having-Klausel» ist wie die Where-Klausel, nur dass sich die angegebene Bedingung auf das Ergebnis einer Aggregationsfunktion bezieht (z. B. HAVING SUM (Betrag) > 0).
«Sortierungsattribut»: nach ORDER BY werden Attribute angegeben, nach denen sortiert werden soll. ASC gibt dabei aufsteigende (Standard), DESC absteigende Sortierung an.

Aufgabe

Führe die folgenden SQL-Select-Abfragen auf unsere bereits bekannte Film-DB aus und überlege dir wie die Resultate zustande kommen. Übersetze die Anfrage dem Sinn nach in Umgangssprache und notiere dein Ergebnis.

Die SQL-Abfragen kannst du hier (opens new window) ausführen.

SELECT * FROM movies;
SELECT * FROM actors;

1
2

SELECT * FROM movies ORDER BY title;
SELECT * FROM movies ORDER BY title DESC;

1
2

SELECT name FROM actors WHERE sex = "w";
SELECT title FROM movies WHERE year > 2014;

1
2

SELECT name FROM actors WHERE name = "%el%";
SELECT name FROM actors WHERE name LIKE "%el%";
SELECT title FROM movies WHERE title = "Spotlight";
SELECT title FROM movies WHERE title LIKE "Spotlight";

1
2
3
4

SELECT SUM(votes) FROM ratings;
SELECT SUM(votes) as total FROM ratings;

1
2

SELECT sex, COUNT(*) FROM actors
   GROUP BY actors.sex;

1
2

Aufgabe

Schreibe SQL-Select-Abfragen für die folgenden Fragen (siehe Aufgabe Kapitel «Relationale Datenbanksysteme»):

In welchen Filmen hat Michael Keaton mitgespielt?
Findest du ein Zitat aus dem Film mit der höchsten Bewertung (ranking so tief wie möglich)?
Welche Schauspieler haben im Film «Spotlight» mitgespielt?

# Joins

Zum Zusammenführen von Daten aus mehreren über Fremdschlüssel verbundene Tabellen verwenden wir bevorzugt den JOIN-Befehl.

id	title	year
1	Argo	2012
2	12 Years a Slave	2013
3	Birdman	2014
4	Spotlight	2015
5	Moonlight	2016
6	The Shape of Water	2017

Tabelle der Relation «movies»

id	movie_id	quotetext
1	5	Little: My name is Chiron, people call me little.
2	5	Black: It is what it is.
3	3	Mike Shiner: Popularity is the slutty little cousin of prestige.

Tabelle der Relation «quotes»

Möchte man nun die beiden Tabellen mit SQL verknüpfen, kann man dies mit einem JOIN der beiden Tabellen erreichen:

SELECT *
    FROM movies JOIN quotes
    ON movies.id = quotes.movie_id;

1
2
3

movies.id	movies.title	movies.year	quotes.id	quotes.movie_id	quotes.quotetext
5	Moonlight	2016	1	5	Little: My name is Chiron, people call me little.
5	Moonlight	2016	2	5	Black: It is what it is.
3	Birdman	2014	3	3	Mike Shiner: Popularity is the slutty little cousin of prestige.

Ergebnis der SQL-Abfrage

JOIN erstellt eigentlich eine temporäre Tabelle (eine sogenannte Kreuztabelle) aus der wir dann mit SELECT Datensätze und Attribute auswählen können:

Z.B. Film-Id, -Titel und Zitat aller Filme aus den Jahren 2014 und 2015:

SELECT movies.id, title, quotetext
    FROM movies JOIN quotes
    ON movies.id = quotes.movie_id
    WHERE year = 2014 OR year = 2015;

1
2
3
4

movies.id	movies.title	quotes.quotetext
3	Birdman	Mike Shiner: Popularity is the slutty little cousin of prestige.

Ergebnis der SQL-Abfrage

Wie man sieht fehlt der Film «Spotlight» im Ergebnis. Dies weil es sich beim «normalen» JOIN um einen sogenannten «Inneren Join» handelt.

Inner Join: A JOIN B

Dieser verknüpft die Datensätze der beiden Tabellen. Wenn aber ein Datensatz aus einer Tabelle in der anderen keine Verknüpfung hat, dann wird dieser Datensatz nicht angezeigt. Wenn wir also alle Filme der Jahre 2014 und 2015 haben möchten – inkl. Zitat, falls sie eines haben, sonst halt ohne – dann können wir dies mit einem sogenannten «Linken Join» erreichen:

Left Join: A LEFT JOIN B

SELECT movies.id, title, quotetext
    FROM movies LEFT JOIN quotes
    ON movies.id = quotes.movie_id
    WHERE year = 2014 OR year = 2015;

1
2
3
4

movies.id	movies.title	quotes.quotetext
3	Birdman	Mike Shiner: Popularity is the slutty little cousin of prestige.
4	Spotlight

Ergebnis der SQL-Abfrage

Neben LEFT JOIN existiert natürlich auch ein RIGHT JOIN, welcher alle Datensätze der rechten Tabelle anzeigt, egal ob sie eine Verknüpfung zu einem Datensatz der linken Tabelle haben oder nicht.

Right Join: A RIGHT JOIN B

Aufgabe

Die SQL-Abfragen kannst du hier (opens new window) ausführen.

SELECT movies.id, title, rank
    FROM movies JOIN ratings
    ON movies.id = ratings.movie_id;

1
2
3

SELECT *
    FROM movies
        JOIN movies2actors ON movies.id = movies2actors.movie_id
        JOIN actors ON actors.id = movies2actors.actor_id;

1
2
3
4

SELECT title, COUNT(movies2actors.actor_id) as count_actors
    FROM movies
        JOIN movies2actors ON movies.id = movies2actors.movie_id
    GROUP BY movies.id;

1
2
3
4

SELECT name, COUNT(movies.id) as count_movies
    FROM actors
        JOIN movies2actors ON actors.id = movies2actors.actor_id
        JOIN movies ON movies.id = movies2actors.movie_id
    GROUP BY actors.id
    HAVING COUNT(movies.id) > 1;

1
2
3
4
5
6

SELECT actors.name, AVG(rank)
    FROM movies
        JOIN movies2actors ON movies.id = movies2actors.movie_id
        JOIN actors ON actors.id = movies2actors.actor_id
        JOIN ratings ON movies.id = ratings.movie_id
    GROUP BY actors.id
    ORDER BY AVG(rank);

1
2
3
4
5
6
7

# weitere Befehle

Hinweis

Diese weiteren Befehle sind nicht Inhalt unseres Kurses. Sie zeigen aber, was alles möglich ist mit SQL.

Testen kann man sie z.B. auf https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all (opens new window)

# Datenmanipulation

Zum Einfügen, Ändern und Löschen von Datensätzen, resp. zum Leeren von ganzen Tabellen.

INSERT INTO Relation [( Attribut+ )] VALUES ( ( Konstante+ ) )+
INSERT INTO Relation [( Attribut+ )] SFW-Block
UPDATE Relation SET (Attribut=Ausdruck)+ [WHERE Where-Klausel]
DELETE FROM Relation [WHERE Where-Klausel]
TRUNCATE Relation

1
2
3
4
5

Mit INSERT können explizit konstruierte Tupel oder die Ergebnisse eines SFW-Blocks (SELECT-FROM-WHERE) in eine Relation eingefügt werden. Dabei kann jeweils mehr als eine Zeile verarbeitet werden.
Der Ausdruck aus der UPDATE-Anweisung kann insbesondere auch auf das zu manipulierende Attribut Bezug nehmen.
Wird bei DELETE die WHERE-Klausel weggelassen, werden alle Tupel gelöscht, aber nicht das Relationsschema.
Die TRUNCATE-Anweisung leert eine Tabelle vollständig und, was den Hauptunterschied zu DELETE FROM Table ist, setzt auch jegliche Indizes und Auto-Inkrement-Werte auf die Standardwerte.

# Definition des Datenbankschemas

Die SQL-Befehle zur Definition des Datenbankschemas werden wir nur der Vollständigkeit halber kurz anschauen. Heutzutage werden meistens grafische Tools wie z.B. phpMyAdmin (mySQL-DB) oder pgAdmin (PostgreSQL-DB) verwendet, um die Struktur einer Relation anzupassen oder eine neue Relation zu definieren.

CREATE TABLE 'lagerbestand' (
  'notebooks_id' varchar(20) NOT NULL,
  'bestand' int(11) NOT NULL,
  'lieferung' date default NULL,
  PRIMARY KEY  ('notebooks_id')
)

1
2
3
4
5
6

Weitere SQL-Befehle dieser Kategorie sind z.B. ALTER um eine vorhandene Relation abzuändern oder DROP um eine Relation zu löschen.

# Rechteverwaltung

Mit den Befehlen GRANT und REVOKE lassen sich Zugriffsrechte auf die Datenbankobjekte einer Datenbank verteilen resp. wieder entfernen.

GRANT SELECT, UPDATE ON TABLE lagerbestand TO group_lager
REVOKE INSERT ON TABLE notebooks FROM PUBLIC

1
2

Allerdings wird auch diese Aufgabe heute meist über grafische Tools erledigt.

# theTVDB

Im Projekt werden wir mit der Datenbank «TheTVDB» arbeiten. Vorerst wollen wir sie aber etwas kennen lernen.

Nachfolgend aufgezeichnet ist das komplette Diagramm der Datenbank inkl. Attributen und Verknüpfungen.

Aufgabe

Versuche für die folgenden Teilaufgaben je ein SQL-SELECT-Abfrage zusammenzustellen um die Antwort zu erhalten.
(Wenn möglich alles in einer Abfrage!)

Die SQL-Abfragen kannst du hier (opens new window) ausführen.

Verknüpfe die Serie Lost (id: 73739) über die drei Tabellen tvseries, tvseasons und tvepisodes mit einem doppelten JOIN.
Welche TV-Serie hat am meisten Hits?
In welchen Serien hat Michael Keaton und in welchen Rachel McAdams mitgespielt?
Welche Serien laufen am Montag Abend zwischen 18 und 20 Uhr?
Welche TV-Serie hat die meisten Seasons?
Welche TV-Serie hat die meisten Episoden?
Finde eine deiner Lieblingsserien und versuche so viel Information wie möglich zu erhalten (Genre, Bilder, Seasons, Episoden, Schauspieler)

zu a)

SELECT * FROM tvseries
JOIN tvseasons ON tvseries.id = tvseasons.seriesid
JOIN tvepisodes ON tvseasons.id = tvepisodes.seasonid
WHERE tvseries.id = 73739

1
2
3
4

Beim 3-Fach-Join müssen wir darauf achten, dass wir dir richtigen Fremdschlüssel mit den dazugehörigen Primärschlüssel verbinden.
Die Serie «Lost» erhalten wir am einfachsten mit deren id, welche wir ja kennen.

zu b)

SELECT seriesname FROM tvseries
ORDER BY hits DESC
LIMIT 1

1
2
3

Wir müssen absteigend sortieren und limitieren dann das Ergebnis aufs einen einzelnen Datensatz.

zu c)

SELECT tvseries.seriesname, seriesactors.name FROM tvseries
JOIN seriesactors ON tvseries.id = seriesactors.seriesid
WHERE seriesactors.name = "Michael Keaton" OR seriesactors.name = "Rachel McAdams"

1
2
3

Wir müssen die beiden Tabellen verknüpfen. Anschliessend machen wir im WHERE ein logisches Oder.

zu d)

Der Wochentag können wir relativ einfach einbauen. Aber es gibt Serien die auch täglich ausgestrahlt werden.
Bei der Uhrzeit wird es schwierig! Unsere Datenbank speichert die Zeit nicht als Zahl, sondern als Text ab – und sogar nicht immer im selben Format. Eine mögliche aber unschöne Lösung ist unten abgebildet – mit dem Prozentzeichen wird die Uhrzeit auf einen speziellen Beginn (18 oder 19 Uhr) oder auf ein spezielles Ende (PM) hin überprüft:

SELECT seriesname, airstime, airsdayofweek FROM  tvseries
WHERE (airsdayofweek = "Monday" OR airsdayofweek = "Daily")
AND (
   airstime LIKE "18:%"
OR airstime LIKE "19:%"
OR (airstime LIKE "6:%" AND airstime LIKE "%PM")
OR (airstime LIKE "7:%" AND airstime LIKE "%PM")
)

1
2
3
4
5
6
7
8

zu e)

Wir «joinen» die beiden Tabellen und gruppieren sie nach TV-Serie. Pro Gruppe zählen wir mit dem Count die Einträge – das gibt die Anzahl Seasons. Wenn man die absteigend sortiert und das Ergebnis auf den obersten Datensatz limitiert, erhalten wir die Serie mit den meisten Seasons.

SELECT seriesname, COUNT(*) as seasons FROM tvseries
JOIN tvseasons ON tvseries.id = tvseasons.seriesid
GROUP BY tvseries.id
ORDER BY seasons DESC
LIMIT 1

1
2
3
4
5

zu f)

Wir gehen analog zur vorherigen Aufgabe vor.

Wir können die Tabelle tvseries über die «Zwischentabelle» tvseasons mit der Tabelle tvepisodes verknüpfen:

SELECT seriesname, COUNT(*) as episodes FROM tvseries
JOIN tvseasons ON tvseries.id = tvseasons.seriesid
JOIN tvepisodes ON tvseasons.id = tvepisodes.seasonid
GROUP BY tvseries.id
ORDER BY episodes DESC
LIMIT 1

1
2
3
4
5
6

Oder wir machen die Verknüpfung direkt von tvseasons zu tvepisodes:

SELECT seriesname, COUNT(*) as episodes FROM tvseries
JOIN tvepisodes ON tvseries.id = tvepisodes.seriesid
GROUP BY tvseries.id
ORDER BY episodes DESC
LIMIT 1

1
2
3
4
5

zu g)

Z.B die TV-Serie «Lost». Siehe dazu das Kapitel Struktur «theTVDB» im Skript «Projekt: WebApp».

← Relationales Datenbanksystem