-- ============================================================================
-- queries.sql · Schulbibliothek · Musterlösung
-- ----------------------------------------------------------------------------
-- Vier Use-Case-Anfragen mit dem in schema.sql + testdaten.sql aufgebauten
-- Datenbestand. Jede Anfrage ist ein vollständiges Statement, getrennt durch
-- Kommentar-Header und ausführbar in Reihenfolge.
-- ============================================================================


-- ---------------------------------------------------------------------------
-- USE CASE 1 — «Welche Bücher hat Sara bisher ausgeliehen?»
-- ---------------------------------------------------------------------------
-- Liefert für eine konkrete Schülerin alle Ausleihen mit Titel, Autor und
-- den beiden Daten. Doppel-JOIN über Schueler → Ausleihe → Buch.
SELECT  b.titel,
        b.autor,
        a.ausleih_datum,
        a.rueckgabe_datum
FROM    Schueler s
  JOIN  Ausleihe a ON a.schueler_id = s.id
  JOIN  Buch     b ON b.isbn        = a.isbn
WHERE   s.vorname = 'Sara'
  AND   s.nachname = 'Mueller'
ORDER BY a.ausleih_datum;


-- ---------------------------------------------------------------------------
-- USE CASE 2 — «Welche Bücher sind aktuell verliehen?»
-- ---------------------------------------------------------------------------
-- Nutzt den VIEW AktiveAusleihen — alle Ausleihen ohne Rückgabe-Datum.
-- Sortiert nach Datum, damit überfällige Ausleihen oben stehen.
SELECT  schueler,
        klasse,
        titel,
        ausleih_datum
FROM    AktiveAusleihen
ORDER BY ausleih_datum;


-- ---------------------------------------------------------------------------
-- USE CASE 3 — «Welche Genres werden besonders oft ausgeliehen?»
-- ---------------------------------------------------------------------------
-- Aggregation über die n:m-Beziehung Buch_Genre + Ausleihe.
-- GROUP BY mit HAVING-Filter (Bonus): nur Genres mit ≥ 2 Ausleihen.
-- Beim Zählen wird DISTINCT verwendet, damit ein Buch mit 2 Genres nicht doppelt
-- gezählt wird.
SELECT  g.name                       AS genre,
        COUNT(a.id)                  AS ausleihen_total,
        COUNT(DISTINCT a.schueler_id) AS verschiedene_schueler
FROM    Genre g
  JOIN  Buch_Genre bg ON bg.genre_id = g.id
  JOIN  Buch       b  ON b.isbn      = bg.isbn
  JOIN  Ausleihe   a  ON a.isbn      = b.isbn
GROUP BY g.id, g.name
HAVING  COUNT(a.id) >= 2
ORDER BY ausleihen_total DESC, g.name;


-- ---------------------------------------------------------------------------
-- USE CASE 4 — «Wer sind die drei aktivsten Leser:innen?»
-- ---------------------------------------------------------------------------
-- Aggregation über alle Ausleihen pro Schüler:in; mit LEFT JOIN, damit auch
-- Schüler:innen ohne Ausleihe auftauchen — der HAVING-Filter sortiert sie
-- aber direkt wieder raus.
SELECT  s.vorname || ' ' || s.nachname AS schueler,
        k.bezeichnung                  AS klasse,
        COUNT(a.id)                    AS anzahl_ausleihen
FROM    Schueler s
  LEFT  JOIN Klasse k ON k.id = s.klasse_id
  LEFT  JOIN Ausleihe a ON a.schueler_id = s.id
GROUP BY s.id, s.vorname, s.nachname, k.bezeichnung
HAVING  COUNT(a.id) > 0
ORDER BY anzahl_ausleihen DESC, schueler
LIMIT 3;


-- ---------------------------------------------------------------------------
-- BONUS-QUERY — «Welche Bücher gibt es im Bestand UND welche Genres sind
-- vertreten?» (UNION über zwei unterschiedliche Quellen)
-- ---------------------------------------------------------------------------
-- Demonstriert Mengenoperation: alle Titel + alle Genre-Namen in einer Spalte.
-- Nicht jeder Use Case verlangt UNION, aber die Brand-Guideline des Skripts
-- erlaubt es als Bonus-Demo.
SELECT 'BUCH:  ' || titel AS eintrag FROM Buch
UNION
SELECT 'GENRE: ' || name  AS eintrag FROM Genre
ORDER BY eintrag;
