Fachliche Fragen mit SQL beantworten

Eine Liste für die Geräteausgabe erstellen

Ein Bildungszentrum verleiht Aufnahmegeräte für Medienprojekte. Am Empfang soll eine Anwendung alle verfügbaren Geräte einer gewählten Kategorie anzeigen. Die Liste soll den Standort enthalten und nach Bezeichnung sortiert sein. Zusätzlich braucht die Leitung eine Auswertung, wie viele Geräte pro Standort erfasst sind.

Die Daten liegen bereits in drei relationalen Tabellen:

• GERAET enthält ein Gerät mit Bezeichnung, Kategorie und Standort.

• STANDORT enthält den Namen eines Ausgabepunkts.

• AUSLEIHE hält fest, wann ein Gerät ausgeliehen und zurückgegeben wurde.

• GERAET: geraet_id, bezeichnung, kategorie, standort_id

• STANDORT: standort_id, name

• AUSLEIHE: ausleihe_id, geraet_id, ausgeliehen_am, zurueckgegeben_am

Die Tabellenstruktur ist für diese Lektion vorgegeben. Du brauchst also kein eigenes Datenmodell zu entwerfen. Du übersetzt stattdessen fachliche Fragen in Abfragen.

Nach dieser Lektion kannst du Daten auswählen, filtern und sortieren. Du kannst zwei Tabellen verbinden, Datensätze zählen und fehlende Werte korrekt prüfen. Außerdem siehst du, wie eine Anwendung externe Eingaben sicher als Parameter übergibt.

SELECT, FROM und WHERE beantworten die erste Frage

Die erste Frage lautet: Welche Audiorekorder gibt es im Bestand?

Eine SQL-Abfrage beschreibt, welche Spalten du sehen willst, aus welcher Tabelle die Daten kommen und welche Zeilen zur Frage passen:

SELECT geraet_id, bezeichnung
FROM GERAET
WHERE kategorie = 'Audiorekorder';

SELECT bestimmt die Spalten des Ergebnisses. Diese Auswahl heißt auch Projektion. FROM nennt die Tabelle. WHERE prüft jede Zeile gegen eine Bedingung. Nur passende Zeilen gelangen in das Ergebnis. Diese Auswahl von Zeilen heißt Selektion.

Die Reihenfolge im geschriebenen Befehl hilft beim Lesen: Ergebnisfelder, Datenquelle, Bedingung. Für die fachliche Kontrolle kannst du andersherum denken:

1. Welche Datenquelle enthält die gesuchte Information?
2. Welche Zeilen passen zur Frage?
3. Welche Spalten braucht die Person im Ergebnis wirklich?

SELECT * liefert alle Spalten. Das ist beim Erkunden einer kleinen Übungstabelle bequem. Für eine Anwendung ist eine ausdrückliche Spaltenliste meist verständlicher. Sie zeigt, welche Daten wirklich gebraucht werden, und verhindert, dass später neu ergänzte Spalten unbemerkt mit ausgegeben werden.

Mehrere Bedingungen verbinden

Die Ausgabe soll nur Audiorekorder am Standort mit der Kennung 2 zeigen. Beide Bedingungen müssen gelten:

SELECT geraet_id, bezeichnung
FROM GERAET
WHERE kategorie = 'Audiorekorder'
  AND standort_id = 2;

Mit AND müssen beide Teilaussagen wahr sein. Mit OR reicht eine davon. Klammern machen deutlich, welche Bedingungen zusammengehören, sobald du AND und OR mischst.

SELECT geraet_id, bezeichnung, kategorie
FROM GERAET
WHERE standort_id = 2
  AND (kategorie = 'Audiorekorder' OR kategorie = 'Mikrofon');

Hier gilt der Standort für beide Kategorien. Ohne Klammern könnte die Datenbank die Verknüpfung anders auswerten, als die fachliche Frage gemeint ist.

ORDER BY macht die Ausgabe reproduzierbar

Ohne ORDER BY verspricht SQL keine bestimmte Reihenfolge. Eine Anzeige kann heute zufällig passend aussehen und nach einer Änderung anders sortiert sein. Die fachliche Anforderung muss deshalb die gewünschte Sortierung nennen.

SELECT geraet_id, bezeichnung
FROM GERAET
WHERE kategorie = 'Audiorekorder'
ORDER BY bezeichnung ASC, geraet_id ASC;

ASC sortiert aufsteigend, DESC absteigend. Die zweite Sortierspalte löst Gleichstände. Zwei Geräte mit derselben Bezeichnung werden dadurch stabil nach ihrer Kennung angeordnet.

Eine Sortierung verändert nicht, welche Zeilen im Ergebnis stehen. Sie verändert nur ihre Reihenfolge.

Ein JOIN verbindet Angaben aus zwei Tabellen

In GERAET steht nur standort_id. Für die Oberfläche wird aber der verständliche Standortname gebraucht. Ein JOIN verbindet passende Zeilen aus zwei Tabellen über zusammengehörige Schlüssel.

SELECT
  g.geraet_id,
  g.bezeichnung,
  s.name AS standort
FROM GERAET AS g
INNER JOIN STANDORT AS s
  ON s.standort_id = g.standort_id
WHERE g.kategorie = 'Audiorekorder'
ORDER BY g.bezeichnung ASC, g.geraet_id ASC;

g und s sind kurze Tabellennamen für diese Abfrage. Sie machen sichtbar, aus welcher Tabelle eine Spalte kommt. AS standort gibt der Ergebnisspalte einen passenden Namen.

Der INNER JOIN liefert nur Kombinationen, bei denen die Bedingung hinter ON erfüllt ist. s.standort_id = g.standort_id verbindet also jedes Gerät mit seinem Standort. Fehlt zu einem Gerät ein passender Standort, erscheint dieses Gerät nicht im Ergebnis.

Ein LEFT JOIN würde dagegen alle Zeilen der linken Tabelle behalten. Das ist sinnvoll, wenn du auch Geräte ohne passenden Standortdatensatz finden willst. Für die normale Ausgabeliste erwarten wir eine gültige Zuordnung und verwenden deshalb den INNER JOIN.

COUNT und GROUP BY verdichten Zeilen

Die Leitung möchte nicht jedes Gerät sehen, sondern die Anzahl der Geräte pro Standort. COUNT ist eine Aggregatfunktion. Sie verdichtet mehrere Zeilen zu einer Kennzahl.

SELECT
  s.standort_id,
  s.name AS standort,
  COUNT(g.geraet_id) AS anzahl_geraete
FROM STANDORT AS s
INNER JOIN GERAET AS g
  ON g.standort_id = s.standort_id
GROUP BY s.standort_id, s.name
ORDER BY anzahl_geraete DESC, s.name ASC;

GROUP BY bildet Gruppen aus gleichen Werten. Für jede Kombination aus Standortkennung und Standortname entsteht eine Gruppe. COUNT(g.geraet_id) zählt die nicht leeren Gerätekennungen innerhalb dieser Gruppe.

Alle ausgewählten Spalten, die nicht aggregiert werden, stehen hier auch in GROUP BY. Das ist klar lesbar und funktioniert mit streng eingestellten Datenbanksystemen. Manche Systeme erlauben in bestimmten Fällen kürzere Schreibweisen. Für eine portable Grundlage solltest du dich nicht darauf verlassen.

Mit dem INNER JOIN fehlen Standorte ohne Gerät vollständig. Wenn auch diese Standorte mit dem Wert 0 erscheinen sollen, brauchst du einen LEFT JOIN und zählst eine Spalte der rechten Tabelle:

SELECT
  s.standort_id,
  s.name AS standort,
  COUNT(g.geraet_id) AS anzahl_geraete
FROM STANDORT AS s
LEFT JOIN GERAET AS g
  ON g.standort_id = s.standort_id
GROUP BY s.standort_id, s.name
ORDER BY anzahl_geraete DESC, s.name ASC;

COUNT(*) würde auch die erhaltene Standortzeile zählen, obwohl kein Gerät zugeordnet ist. COUNT(g.geraet_id) ignoriert dagegen den fehlenden Wert auf der rechten Seite und liefert für diese Gruppe 0.

NULL bedeutet: Der Wert fehlt

In AUSLEIHE.zurueckgegeben_am steht NULL, solange ein Gerät noch nicht zurückgegeben wurde. NULL ist weder die Zahl null noch ein leerer Text. Es bedeutet, dass an dieser Stelle kein Wert vorliegt.

Darum funktioniert dieser Vergleich nicht wie gewünscht:

-- Nicht verwenden: NULL wird nicht mit = verglichen.
SELECT ausleihe_id, geraet_id
FROM AUSLEIHE
WHERE zurueckgegeben_am = NULL;

Für fehlende Werte gibt es IS NULL und IS NOT NULL:

SELECT ausleihe_id, geraet_id, ausgeliehen_am
FROM AUSLEIHE
WHERE zurueckgegeben_am IS NULL
ORDER BY ausgeliehen_am ASC, ausleihe_id ASC;

Die Abfrage zeigt offene Ausleihen. Die älteste Ausleihe steht zuerst. Auch hier löst die Kennung mögliche Gleichstände beim Datum auf.

Eingaben gehören als Parameter in die Abfrage

Die Kategorie kommt später aus einem Formular. Wenn eine Anwendung diese Eingabe per Zeichenkettenverkettung direkt in SQL einbaut, kann die Eingabe die Struktur des Befehls verändern. Dieses Sicherheitsproblem heißt SQL-Injection.

So sollte die Anwendung die Abfrage nicht zusammensetzen:

// Unsicher: Externe Eingabe wird Teil des SQL-Texts.
const sql = "SELECT geraet_id, bezeichnung FROM GERAET WHERE kategorie = '" + kategorie + "'"
const result = await db.query(sql)

Bei einer parametrisierten Abfrage bleiben SQL-Code und Daten getrennt. Die Anwendung übergibt zuerst einen festen Befehl mit Platzhalter und danach den Wert:

const sql = `
  SELECT geraet_id, bezeichnung
  FROM GERAET
  WHERE kategorie = ?
  ORDER BY bezeichnung ASC, geraet_id ASC
`

const result = await db.query(sql, [kategorie])

Das Beispiel zeigt das Prinzip einer Bibliothek mit ? als Platzhalter. Andere Treiber verwenden etwa $1 oder benannte Parameter wie :kategorie. Entscheidend ist nicht das Zeichen. Der Datenbanktreiber bindet den Wert getrennt an den vorbereiteten SQL-Befehl.

Parameter ersetzen Werte, aber normalerweise keine SQL-Bestandteile wie Spaltennamen oder ASC und DESC. Für solche auswählbaren Strukturelemente legst du in der Anwendung eine feste Liste erlaubter Varianten an.

Übertrage den Ablauf auf eine neue Auswertung

SELECT
  a.ausleihe_id,
  g.bezeichnung,
  s.name AS standort,
  a.ausgeliehen_am
FROM AUSLEIHE AS a
INNER JOIN GERAET AS g
  ON g.geraet_id = a.geraet_id
INNER JOIN STANDORT AS s
  ON s.standort_id = g.standort_id
WHERE a.zurueckgegeben_am IS NULL
  AND s.standort_id = ?
ORDER BY a.ausgeliehen_am ASC, a.ausleihe_id ASC;

Was du aus der Abfrage mitnimmst

Eine belastbare SQL-Abfrage beginnt mit einer fachlichen Frage. FROM und JOIN legen die benötigten Datenquellen fest. WHERE filtert Zeilen. SELECT bestimmt, welche Spalten im Ergebnis gebraucht werden. GROUP BY und Aggregatfunktionen verdichten mehrere Zeilen. ORDER BY macht die Reihenfolge ausdrücklich und bei Bedarf mit einer zweiten Spalte eindeutig.

NULL steht für einen fehlenden Wert und wird mit IS NULL oder IS NOT NULL geprüft. Externe Eingaben werden als Parameter gebunden. So bleibt die Abfrage nicht nur fachlich nachvollziehbar, sondern auch sicher in eine Anwendung integrierbar.

Quellenbasis und Abgrenzung

Der KMK-Rahmenlehrplan ordnet das Realisieren von Anwendungsfunktionalität dem Lernfeld 11a zu. Die Berufsbildposition B1 nennt den Datenaustausch und das Durchführen komplexer Abfragen. Der aktuelle FIAE-Prüfungskatalog führt im Prüfungsbereich “Entwicklung und Umsetzung von Algorithmen” Projektion, Selektion, Sortierung, Gruppierung, Abfragen über mehrere Tabellen und Aggregatfunktionen auf. Er beschreibt die Themenbreite, garantiert aber keine konkrete Aufgabe in einem Prüfungstermin.

Eine Beispielprüfung aus Sommer 2023 wurde nur verwendet, um wiederkehrende Aufgabenformen zu erkennen: fachliche Bedingungen, mehrere Tabellen, Aggregation, Sortierung und fehlende Werte. Das Geräteausgabe-Szenario, die Tabellen, Werte, Fragen und Lösungen dieser Lektion sind neu. Die Sicherheitsempfehlung zu parametrisierten Abfragen folgt dem OWASP SQL Injection Prevention Cheat Sheet.

Die SQL-Beispiele verwenden weitgehend herstellerunabhängige Standardsyntax. Datenbanksysteme unterscheiden sich unter anderem bei Datumsfunktionen, Groß- und Kleinschreibung, Begrenzung von Ergebnismengen und Platzhaltern. Diese Lektion behandelt lesende Abfragen mit SELECT. Datenmodellierung, INSERT, UPDATE, DELETE, Schemaänderungen, Unterabfragen, Transaktionen, Rechte, Indizes und Leistungsoptimierung gehören nicht zu dieser Einführung.

Belegmatrix

• Spalten und Zeilen auswählen: LF11a und B1 bilden den Handlungsbezug. Berufsverordnung und FIAE-Prüfungskatalog tragen Datenabfragen, Projektion und Selektion. Einstieg, Erklärung und erstes Quiz machen die Auswahl an einer neuen Bestandsfrage beobachtbar.
• Ergebnisse eindeutig sortieren: Der FIAE-Prüfungskatalog nennt das Sortieren von Abfragen. Erklärung und Bausteinaufgabe verbinden ORDER BY mit einer konkreten Anforderung und einem aufgelösten Gleichstand.
• Tabellen verbinden: B1 trägt komplexe Abfragen. Prüfungskatalog und abstrahiertes Aufgabenmuster tragen Abfragen über mehrere Tabellen. Geräteszenario, Erklärung und zweites Quiz prüfen die passende JOIN-Art.
• Daten verdichten: Der FIAE-Prüfungskatalog nennt Gruppierung und Aggregatfunktionen. Die Standortauswertung und das Quiz zeigen den Unterschied zwischen vorhandenen Detailzeilen und einer Kennzahl pro Gruppe.
• Fehlende und externe Werte behandeln: Das abstrahierte Aufgabenmuster trägt die Prüfung auf fehlende Werte. OWASP trägt die Trennung von SQL-Code und Eingabedaten durch parametrisierte Abfragen. Erklärung, Quiz und Transferaufgabe verbinden beide Aspekte in einer neuen Anwendungssituation.