Subqueries, Views & Indexe

Worum geht’s?

• Subqueries: Abfragen in Abfragen – z. B. „gib alle Kurse, die Termine haben“.
• Views: gespeicherte Abfragen – wie „virtuelle Tabellen“ für Reports/BI.
• Indexe: Beschleuniger für WHERE/JOIN/ORDER BY (wenn sinnvoll eingesetzt).

Schema (Kontext)

Wir bleiben konsistent zum Lernpfad-Schema aus Kapitel 01–05:

-- kurse(kurs_id, kurs_name)
-- trainer(trainer_id, name)
-- termine(termin_id, kurs_id, trainer_id, start_datum, ort, format, dauer_tage, preis_eur)
-- buchungen(buchung_id, termin_id, name, email, created_at)

Lehr-/Lernziele

• (LZ1) Subqueries mit IN und EXISTS korrekt einsetzen.
• (LZ2) korrelierte Subqueries erkennen und typische Fehler vermeiden.
• (LZ3) Views erstellen/verwenden (CREATE VIEW) und Einsatzzwecke erklären.
• (LZ4) Index-Grundidee verstehen und einfache Indexe anlegen (CREATE INDEX).

Subqueries

Unterabfragen kommen häufig in „Filter-Fragen“ vor: Zeige X, wenn Bedingung in anderer Tabelle erfüllt ist.

-- LZ1: IN-Subquery
-- Alle Kurse, die mindestens einen Termin haben
SELECT k.kurs_id, k.kurs_name
FROM kurse AS k
WHERE k.kurs_id IN (
  SELECT t.kurs_id
  FROM termine AS t
);

-- LZ1: EXISTS eignet sich für Existenztests; bei IN/NOT IN müssen NULL-Regeln bewusst berücksichtigt werden.
SELECT k.kurs_id, k.kurs_name
FROM kurse AS k
WHERE EXISTS (
  SELECT 1
  FROM termine AS t
  WHERE t.kurs_id = k.kurs_id
);

-- LZ2: Korrelierte Subquery: Termine pro Kurs (Alternative zu GROUP BY)
SELECT
  k.kurs_id,
  k.kurs_name,
  (
    SELECT COUNT(*)
    FROM termine AS t
    WHERE t.kurs_id = k.kurs_id
  ) AS anzahl_termine
FROM kurse AS k
ORDER BY anzahl_termine DESC, k.kurs_name ASC;

Views

Views sind gespeicherte Abfragen: gut für Reports, stabile Schnittstellen und weniger Wiederholung.

-- LZ3: View anlegen (Termine + Kurs + optional Trainer)
-- Hinweis: Viele Systeme erlauben kein ORDER BY in der View-Definition.
CREATE VIEW v_termin_report AS
SELECT
  t.termin_id,
  t.start_datum,
  t.ort,
  t.format,
  t.dauer_tage,
  t.preis_eur,
  k.kurs_id,
  k.kurs_name,
  COALESCE(tr.name, '(noch offen)') AS trainer
FROM termine AS t
INNER JOIN kurse AS k
  ON t.kurs_id = k.kurs_id
LEFT JOIN trainer AS tr
  ON t.trainer_id = tr.trainer_id;

-- View nutzen wie eine Tabelle
SELECT kurs_name, start_datum, format, ort, trainer
FROM v_termin_report
WHERE format = 'Praesenz'
ORDER BY start_datum ASC, ort ASC;

-- DB-abhängig:
-- PostgreSQL/MySQL: CREATE OR REPLACE VIEW ...
-- SQLite: ersetzen = DROP + CREATE

DROP VIEW v_termin_report;

Indexe (Performance-Grundlagen)

• Index = „Nachschlage-Struktur“ (meist B-Tree), beschleunigt Suchen/Joinen/Sortieren.
• Indexe kosten Speicher und verlangsamen INSERT/UPDATE/DELETE (weil Index mitgepflegt wird).
• Gute Kandidaten: Spalten in WHERE, JOIN ON, ORDER BY – v. a. bei großen Tabellen.

-- LZ4: Index auf Foreign-Key/Join-Spalte (typisch sehr sinnvoll)
CREATE INDEX idx_termine_kurs_id ON termine(kurs_id);

-- LZ4: Index auf häufige Filter-/Sortierspalte
CREATE INDEX idx_termine_start_datum ON termine(start_datum);

-- Optional: zusammengesetzter Index für typische Query:
-- WHERE format='Online' ORDER BY start_datum
CREATE INDEX idx_termine_format_start_datum ON termine(format, start_datum);

Praxisaufgabe

1. Schreibe eine Subquery mit EXISTS, die alle Kurse liefert, die mindestens einen Präsenz-Termin haben.
2. Erstelle eine View v_praesenztermine (nur Präsenztermine, inkl. Kursname).
3. Lege einen Index an, der deine Filter-/Sortier-Query unterstützt (z. B. termine(format, start_datum)).

-- 1) EXISTS: Kurse mit Präsenz-Terminen
SELECT k.kurs_id, k.kurs_name
FROM kurse AS k
WHERE EXISTS (
  SELECT 1
  FROM termine AS t
  WHERE t.kurs_id = k.kurs_id
    AND t.format = 'Praesenz'
);

-- 2) View: nur Präsenz-Termine (Kursname + Termin-Infos)
CREATE VIEW v_praesenztermine AS
SELECT
  k.kurs_name,
  t.termin_id,
  t.start_datum,
  t.ort,
  t.dauer_tage,
  t.preis_eur
FROM kurse AS k
INNER JOIN termine AS t
  ON t.kurs_id = k.kurs_id
WHERE t.format = 'Praesenz';

-- 3) Index: typische Filter+Sortierung
CREATE INDEX idx_termine_format_start_datum ON termine(format, start_datum);

Kurz-Takeaways

• LZ1: EXISTS eignet sich besonders für Existenztests. Bei IN und insbesondere NOT IN müssen NULL-Werte bewusst berücksichtigt werden.
• LZ2: Korrelierte Subqueries referenzieren die äußere Query – gut für „pro Zeile“ Werte.
• LZ3: Views reduzieren Wiederholung und stabilisieren Report-Abfragen.
• LZ4: Indexe helfen bei großen Daten & selektiven Filtern – kosten aber Schreib-Performance.