26P

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:

Alle auswählen
Projektion
Nach Name Sortiert
Die ersten 5
Selektion

SELECT * 
    FROM legodudes;

--- Projektion: nur bestimmte Attribute anzeigen
SELECT name, beschreibung, bild
    FROM legodudes;

SELECT *
    FROM legodudes
    ORDER BY name;

--- oder explizite Richtung

SELECT *
    FROM legodudes
    ORDER BY name ASC;

--- oder absteigend

SELECT *
    FROM legodudes
    ORDER BY name DESC;

SELECT *
    FROM legodudes
    LIMIT 5;

--- oder kombiniert

SELECT *
    FROM legodudes
    ORDER BY name
    LIMIT 5;

--- nur mit der id 4
SELECT *
    FROM legodudes
    WHERE id=4;

--- nur aus den USA
SELECT *
    FROM legodudes
    WHERE land='USA';

1. Haustiere

Datenbank: legomania; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/legomania

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

Datenbank: legomania; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/legomania

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

Datenbank: legomania; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/legomania

Wo liegt der Unterschied?

Probieren Sie die Abfragen aus
vergleichen Sie die Resultate
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

Datenbank: legomania; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/legomania

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

SSR

5. Aarentemperatur

Datenbank: waters; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/waters

Finden Sie heraus, an welchen Standorten die Aarentemperatur gemessen wird.
Erzeugen Sie eine Tabelle, in welcher nur die Aarentemperatur kurz vor dem Bielersee angezeigt wird. Ordnen Sie die Ausgabe nach dem Erstelldatum des Datensatzes.
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

Datenbank: IMDB; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/imdb

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

Datenbank: legomania; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/legomania

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(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 gross
LOWER(text) schreibt alle Buchstaben klein
MD5(text) berechnet den MD5-Hash eines Textes
... 👉 https://www.postgresql.org/docs/current/functions-string.html

8. IMDB-URLS

Datenbank: IMDB; 👉 https://db-sql.ch/connections/6750d31f-6978-4e24-b044-e2dd9763bd4f/imdb

Welche unterschiedlichen Filmtypen gibt es? Listen Sie alle auf.
Zeigen Sie alle Attribute der ersten 100 Kurzfilme aus dem Jahr 2023 an.
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

CC 4.0

Einstieg SQL

SQL​

Logische Operatoren​

Distinct​

Nach Namen Suchen​

Spalten benennen​

Texte verändern​

CONCAT(text1, text2, ...) oder text1 || text 2​

SUBSTRING(text, start, [length])​

REPLACE(text, old, new)​

SQL