Zum Hauptinhalt springen
26e

Einstieg SQL

db-sql.ch

Erstellen Sie unter https://db-sql.ch mit Ihrer Schul-E-Mail-Adresse einen Account. Sie erhalten anschliessend eine E-Mail mit einem Link, um Ihr Konto zu aktivieren.

Sobald Sie dies erledigt haben, können Sie auf die für Sie freigegebenen Datenbankverbindungen zugreifen.

SQL

Eine SQL-Abfrage ist vom Aufbau her immer gleich:

SELECT
[DISTINCT]
Auswahlliste
FROM Quelle [Alias-Name]
[WHERE Where-Klausel]
[ [GROUP BY {Group-by-Attribut}] +
[HAVING Having-Klausel]]
[ORDER BY {Sortierungsattribut [ASC|DESC]}
[LIMIT {Datensatz-Anzahl}];

wobei alles, was in eckigen Klammern steht, optional ist.

DISTINCT

Jeder Datensatz wird nur einmal ausgegeben, auch wenn er mehrfach in der Tabelle vorkommt.

Auswahlliste

Bestimmt, welche Attribute (Spalten) der Quelle angezeigt werden sollen (* für alle) und ob Aggregationsfunktionen angewendet werden sollen. Aufgezählte Elemente sind mit einem Komma voneinander getrennt.

Quelle [Alias-Name]

Spezifiziert, wo die Daten herkommen. Es können Relationen angegeben werden und miteinander als kartesisches Produkt oder als Verbund (JOIN) verknüpft werden. Durch die Angabe eines Alias-Namen können die Relationen für die Abfrage umbenannt werden.

Where-Klausel

Bestimmt die Bedingungen, unter denen die Daten ausgegeben werden sollen. Einzelne Bedingungen können mit OR oder AND verknüpft werden.

Group-by-Attribut

Legt fest, ob unterschiedliche Werte als einzelne Zeilen ausgegeben werden sollen oder ob alle Attributwerte aggregiert (bspw. aufsummiert (SUM), gemittelt (AVG), kleinster Wert (MIN), grösster Wert (MAX),...) zu einem einzelnen Ergebniswert zusammengefasst werden.

Having-by-Attribut

Ist wie die WHERE-Klausel, nur dass sich die angegebenen Bedingungen auf das Ergebnis einer Aggregationsfunktion beziehen.

Sortierungsattribut

Nach dem ORDER BY Statement werden Attribute angegeben, nach denen sortiert werden soll. Für eine aufsteigende Reihenfolge (1, 2, 3, ...) wird ASC spezifiziert, für eine absteigende Reihenfolge DESC (99, 80, 13, ...).

LIMIT

Gibt an wie viele Datensätze angezeigt werden sollen. Besonders hilfreich bei grossen Datensätzen, oder wenn nur eine gewisse Anzahl der grössten/kleinsten Attributwerte gesucht werden.

Einige Beispiele:

SELECT * 
FROM legodudes;
1. Haustiere

Fragen Sie alle Haustiere ab, wobei nur die Tierart, der Name und das Bild (in dieser Reihenfolge) ausgegeben werden sollen. Die Resultate sollen zudem alphabetisch absteigend nach der Tierart sortiert sein.

SSR
2. Nur Hunde

Fragen Sie bei den Haustieren alle Hunde ab, wobei alle Attribute ausgegeben werden sollen.

SSR

Logische Operatoren

Mit den logischen Operatoren AND, OR und NOT können mehrere Bedingungen miteinander verknüpft werden.

Zwei Bedingungen, beide müssen erfüllt sein
SELECT * FROM haustiere
WHERE lieblingsfutter='Körner' AND tierart='Hamster';
Zwei Bedingungen, die Eine oder die Andere muss erfüllt sein
SELECT * FROM haustiere
WHERE lieblingsfutter='Körner'
OR lieblingsfutter='Fisch';
Kombinierte Bedingung - die Klammerung gibt an, was zusammengehört.
SELECT * FROM haustiere
WHERE
NOT lieblingsfutter='Körner' AND (tierart='Hund' OR tierart='Katze');
Beachte
  • Das NOT bezieht sich jeweils nur auf den direkt folgenden Ausdruck.

  • Spalten-Namen müssen bei jeden Vergleich erneut angegeben werden.

:::

3. Klammerung

Wo liegt der Unterschied?

  1. Probieren Sie die Abfragen aus

  2. vergleichen Sie die Resultate

  3. Erklären Sie das Verhalten

SELECT * FROM haustiere
WHERE (NOT lieblingsfutter='Körner' AND tierart='Hund') OR tierart='Hamster';
SELECT * FROM haustiere
WHERE NOT lieblingsfutter='Körner' AND (tierart='Hund' OR tierart='Hamster');

Distinct

Mit dem Schlüsselwort DISTINCT kann angegeben werden, dass jeder Datensatz nur einmal ausgegeben werden soll, auch wenn er mehrfach in der Tabelle vorkommt.

Beispiel: Es sollen alle Tierarten ausgegeben werden, die in der Tabelle haustiere vorkommen.

SELECT DISTINCT tierart FROM haustiere;
4. Distinct

Geben Sie alle Länder in welchen Legodudes wohnen in alphabetisch absteigender Reihenfolge aus.

SSR
5. Aarentemperatur
  1. Finden Sie heraus, an welchen Standorten die Aarentemperatur gemessen wird.

  2. Erzeugen Sie eine Tabelle, in welcher nur die Aarentemperatur kurz vor dem Bielersee angezeigt wird. Ordnen Sie die Ausgabe nach dem Erstelldatum des Datensatzes.

  3. Erstellen Sie ein Liniendiagramm mit db-sql und fügen Sie das Bild als Antwort unten ein.

SSR

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%';
Gross- und Kleinschreibung

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

Ohne 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%';
6. Nach Namen Suchen

Listen Sie alle Personen auf, die

  • mit Ihren Namen beginnen? (Achtung: Beim Namen John soll der Name Johnny nicht ausgegeben werden)

  • mit Ihrem Namen enden?

  • ihren Namen enthalten?

SSR

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
7. Unterschied finden
  1. Wo liegt der Unterschied zwischen den beiden Queries? Führen Sie beide Queries aus und halten Sie den Unterschied fest.

  2. 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(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

8. IMDB-URLS
  1. Welche unterschiedlichen Filmtypen gibt es? Listen Sie alle auf.

  2. Zeigen Sie alle Attribute der ersten 100 Kurzfilme aus dem Jahr 2023 an.

  3. Die angezeigte ID (bspw. tt8773344) findet man auch in der URL der IMDB-Seite. Erstellen Sie eine Abfrage, welche nebst allen Attributen noch zusätzlich die URL für die ersten 100 Kurzfilme ausgibt, die im Jahr 2023 erschienen sind.
    Die URL hat folgendes Format: https://www.imdb.com/title/<ID>/

SSR