/*
** Aufgabe 1 
**
** Welche Veranstaltungen werden am 5. Mai 2020 gegeben?
**
** titel                  uhrzeit
** ------------------------------
** Der gestiefelte Kater  10:00
** Kabarett 2020          19:30
*/ 

SELECT   st.titel, TO_CHAR(v.start, 'HH24:MI') AS uhrzeit
FROM     stueck st JOIN veranstaltung v USING (st_id)
WHERE    DATE_TRUNC('day', v.start) = DATE '2020-05-05'
ORDER BY uhrzeit
; 

SELECT   st.titel, CAST(v.start AS TIME) AS uhrzeit
FROM     stueck st JOIN veranstaltung v USING (st_id)
WHERE    CAST(v.start AS DATE) = DATE '2020-05-05'
ORDER BY uhrzeit
; 

SELECT   st.titel, v.start::TIME AS uhrzeit
FROM     stueck st JOIN veranstaltung v USING (st_id)
WHERE    v.start::DATE = DATE '2020-05-05'
ORDER BY uhrzeit
; 

/*
WHERE  v.start >= '2020-05-05' AND v.start < '2020-05-06' 
WHERE  v.start BETWEEN '2020-05-05' AND '2020-05-06'
WHERE  DATE_TRUNC('day', start) = '2020-05-05'
WHERE  v.start::DATE = '2020-05-05'    -- Cast auf das Datum (ohne Uhrzeit)
WHERE  CAST(v.start AS DATE) = '2020-05-05'

CAST(start AS TIME) AS uhrzeit
start::TIME
TO_CHAR(start, 'HH24:MI')

-- eher nicht
EXTRACT(hour FROM start) || ':' || EXTRACT(minute FROM start) AS uhrzeit
*/
/*
** Aufgabe 2 
**
** Welche Sitzplätze wurden für die Kindervorstellung 
** „Frau Holle“ am 24. 1. 2020 unter dem Code 'a' 
** reserviert?
**
** Anmerkung
** =========
** 
** Für verschiedene Veranstaltungen werden jeweils
** verschiedene Codes "qr_code" verwendet. Das heißt,
** ein Code kommt in maximal einer Veranstaltung 
** zum Einsatz.
**
** Funktionale Anhängigkeiten: qr_code -> v_id 
**
** In Datenmodell 1 gilt diese funktionale
** Abhängigkeiten nicht, in Datenmodell 3 
** dagegen schon (wg. UNIQUE).
**
** Wenn man diese funktionale Anhängigkeit als
** gegeben voraussetzt, ist das Datenmodell 1
** (im Gegensatz zum Datenmodell 3) nicht in 3NF. 
** Das Datenmodell 1 ist einfacher als das 
** Datenmodell 3: Man spart sich die Unterscheidung
** zwischen "reservierung" und "sitzplatzreservierung".
** Allerdings muss man nun bei allen Anfragen nach
** reservierten Karten auch die Veranstaltung mit
** angeben (was nicht sonderlich dramatisch ist).
** 
** Beachten Sie: Man könnte die fehlende funktionale 
** Abhängigkeit mit Hilfe eines Constraint-Triggers 
** auch für das Datenmodel 1 erzwingen. 
** (Aufgabe für Profis: Formulieren Sie diesen Trigger.)
** 
** Wenn man davon ausgeht, dass die funktionle Abhängigkeit
** mit einem Trigger erzwungen wir oder dass sie bei
** jeder neuen Reservierung strikt beachtet wird,
** kann man diese (un ein paar der folgenden Anfragen)
** einfacher formulieren.
**
** reihe nummer
** ------------
** 1     1
** 1     2
** 1     3
** 1     4
** 1     5
*/ 

SELECT s.reihe, s.nummer
FROM   stueck st JOIN veranstaltung v ON st.st_id = v.st_id
                 JOIN reservierung r  ON v.v_id   =  r.v_id
                 JOIN sitzplatz s     ON r.s_id   = s.s_id
WHERE      r.qr_code = 'a' 
       AND v.start::DATE = TIMESTAMP '2020-01-24'
       AND st.titel = 'Frau Holle' 
;

SELECT s.reihe, s.nummer
FROM   stueck st JOIN veranstaltung v USING (st_id)
                 JOIN reservierung r  USING (v_id) 
                 JOIN sitzplatz s     USING (s_id)
WHERE      r.qr_code = 'a' 
       AND v.start::DATE = TIMESTAMP '2020-01-24'
       AND st.titel = 'Frau Holle' 
;


-- Eigentlich ist diese Lösung falsch, sofern die 
-- funktionale Anhängigkeit qr_code -> v_id nicht 
-- gilt.
-- 
-- „Frau Holle“ könnte am 24. 1. zweimal gegeben
-- werden, einmal am Vormittag als Sondervorstellung
-- und einmal am Nachmittag als Kindervorstellung.
--
-- Falls nun qr_code -> v_id nicht gilt, könnte für
-- beide Veranstaltungen dieselbe Reservierungsnummer
-- vergeben worden sein. Das heißt aber, dass die
-- obige Anfrage die unter Code 'a' reservierten 
-- Sitzplätze von beiden Veranstaltungen gemeinsam
-- ausgeben würde. Das ist aber schlecht, da die 
-- Reservierungen für unterschiedliche Veranstaltungen
-- üblicherweise von unterschiedlichen Personen vorgenommen
-- werden. Und diejenige, die die Karten für die 
-- Nachmittagsveranstaltung reserviert hat braucht
-- nicht die Sitze der Vormittagsreservierung und
-- möchte diese auch sicher nicht bezahlen.
-- Korrekterweise müsste man also zur Reservierungs-
-- nummer die genauer Startzeit mit angeben.
-- Dann ist es aber nicht mehr notwendig, den
-- Titel der Veranstaltung anzugeben, da dieser durch
-- die Startzeit eindeutig festgelegt ist. 

SELECT s.reihe, s.nummer
FROM        veranstaltung v
       JOIN reservierung r  USING (v_id) 
       JOIN sitzplatz s     USING (s_id)
WHERE      r.qr_code = 'a' 
       AND v.start = TIMESTAMP '2020-01-24 16:00'
;

-- Alternativ könnte man nach der Kindervorstellung
-- suchen (diese gibt es nur einmal pro Tag; auch das
-- ist wieder eine Intrgritätsbedingung, die vom
-- Model nicht garantiert wird, wohl aber von der
-- Augsburger Puppenkiste).

SELECT s.reihe, s.nummer
FROM        veranstaltung v
       JOIN reservierung r  USING (v_id) 
       JOIN sitzplatz s     USING (s_id)
WHERE      r.qr_code = 'a' 
       AND v.vk_id = 1
;
-- Dass sich hinter der vk_id 1 die Kinderveranstaltung
-- verbirgt, wird sich (hoffentlich) niemals ändern.
-- Siehe auch: Anmerkungen zu Datenmodell 3 unter
--   https://kowa.hs-augsburg.de/mmdb/mmdb-beispiele/theater-datenbank/

-- Man beachte, dass sich das Ergebnis in beiden Fällen 
-- nicht ändert, da es keine Vormittagsvorstellung 
-- „Frau Holle“ gibt.
-- ABER: Die Aufgabe lautete ja, dass die Anfrage für 
-- jeden erlaubten Datenbankinhalt korrekt funktionieren muss.

-- Wenn man die (sehr sinnvolle) Integritätsbedingung
-- qr_code -> v_id als gegeben ansieht, kann man auch noch
-- auf die Angabe der Startzeit verzichten, da dann unter dem
-- Code 'a' immer nur die Reservierung einer Person für
-- eine Veranstaltung zu finden ist.

SELECT s.reihe, s.nummer
FROM   reservierung r JOIN sitzplatz s USING (s_id)
WHERE  r.qr_code = 'a' 
;

-- Constraint-Trigger: qr_code -> v_id
DROP TRIGGER  IF EXISTS check_qr_code_trigger ON reservierung;
DROP FUNCTION IF EXISTS check_qr_code_function();

CREATE FUNCTION check_qr_code_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    IF (    NEW.qr_code IS NOT NULL 
        AND (SELECT COUNT(DISTINCT v_id) 
             FROM   reservierung 
             WHERE  qr_code = NEW.qr_code
            ) > 1
       ) 
    THEN
      RAISE EXCEPTION 
         'Ein QR-Code darf nur für eine Veranstaltung vergeben werden. v_id: %, s_id: %, qr_code: %', 
         NEW.v_id, NEW.s_id, NEW.qr_code;
    END IF;
    RETURN NULL;
  END
$$
;

CREATE CONSTRAINT TRIGGER check_qr_code_trigger
AFTER INSERT OR UPDATE 
ON reservierung
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
  EXECUTE PROCEDURE check_qr_code_function();

/*
** Aufgabe 3 
**
** Was muss der Kunde für die von ihm unter dem Code 'a' 
** reservierten Plätze (siehe vorherige Aufgabe) bezahlen?
**
** gesamtpreis
** -----------
**       70.00
**
** Überprüfen Sie Ihre Lösung, indem Sie ermitteln, was ein
** (vermutlich anderer) Kunde für die von ihm unter dem 
** 'abc' reservierten Plätze am 25. Januar 2020 bezahlen 
** muss.
**
** gesamtpreis
** -----------
**       42.00
*/ 

-- 24. Januar 2020
SELECT SUM(p.preis) AS gesamtpreis
FROM        stueck st 
       JOIN veranstaltung            v ON st.st_id = v.st_id
       JOIN reservierung             r ON v.v_id   = r.v_id 
       JOIN sitzplatz                s ON s.s_id   = r.s_id 
       JOIN veranstaltungskategorie vk ON vk.vk_id = v.vk_id
       JOIN sitzplatzkategorie      sk ON sk.sk_id = s.sk_id
       JOIN preis         p ON p.vk_id  = vk.vk_id AND p.sk_id = sk.sk_id
WHERE      r.qr_code = 'a' 
       -- Die Anmerkung zu Aufgabe 2 gilt auch hier.
       -- „Frau Holle“ könnte am 24. 1. 2020 mehrfach gegeben werden
       AND v.start = TIMESTAMP '2020-01-24' 
       AND st.titel = 'Frau Holle' 
;

-- 24. Januar 2020
-- Die obige Anfrage kann vereinfacht werden, da die vk_id
-- und die sk_id shon in den Tabellen "veranstaltung" und 
-- "sitzplatz" enthalten sind.
SELECT SUM(p.preis) AS gesamtpreis
FROM        veranstaltung v
       JOIN reservierung  r ON v.v_id   = r.v_id 
       JOIN sitzplatz     s ON s.s_id   = r.s_id 
       JOIN preis         p ON p.vk_id  = v.vk_id AND p.sk_id = s.sk_id
WHERE      r.qr_code = 'a' 
       -- „Frau Holle“ wird über die genaue Startzeit identifiziert.
       AND v.start = TIMESTAMP '2020-01-24 16:00'
;

SELECT SUM(p.preis) AS gesamtpreis
FROM        veranstaltung v
       JOIN reservierung  r USING (v_id) 
       JOIN sitzplatz     s USING (s_id)
       JOIN preis         p USING (vk_id, sk_id) 
WHERE      r.qr_code = 'a' 
       -- „Frau Holle“ wird über die genaue Startzeit identifiziert.
       AND v.start = TIMESTAMP '2020-01-24 16:00'
;

-- Falls qr_code -> v_id gilt, vereinfacht sich 
-- die Anfrage wieder.
-- Hier kann man auf den Join mit der Veranstaltung
-- allerdings nicht verzichten, da der Preis eines Sitzplatzes
-- von der Veranstaltungskategorie abhängt. 
SELECT SUM(p.preis) AS gesamtpreis
FROM        veranstaltung v
       JOIN reservierung  r USING (v_id) 
       JOIN sitzplatz     s USING (s_id)
       JOIN preis         p USING (vk_id, sk_id) 
WHERE  r.qr_code = 'a' 
;


-- 25. Januar 2020
SELECT SUM(p.preis) AS gesamtpreis
FROM        veranstaltung v
       JOIN reservierung  r USING (v_id) 
       JOIN sitzplatz     s USING (s_id)
       JOIN preis         p USING (vk_id, sk_id) 
WHERE  r.qr_code = 'abc'
;

/*
** Aufgabe 4 
**
** Schreiben Sie eine View 
**   rechnung(start, titel, qr_code, summe),
** mit der man für jeden Kunden den Gesamtpreis berechnen kann, 
** den der Kunde für die von ihm an einem bestimmten Tag, zu einer
** bestimmten Uhrzeit unter einen bestimmten Code reservierten 
** Karten bezahlen muss.
**
** SELECT * FROM rechnung ORDER BY start, qr_code;
**
** start                titel                  qr_code               summe
** -----------------------------------------------------------------------
** 2020-01-24 16:00:00  Frau Holle             a                     70.00
** 2020-01-24 16:00:00  Frau Holle             NULL                 241.50
** 2020-01-25 15:00:00  Frau Holle             2fsödgjn2vmösdngsdd   25.00
** 2020-01-25 15:00:00  Frau Holle             abc                   42.00
** 2020-01-25 15:00:00  Frau Holle             NULL                  40.50
** 2020-01-25 19:30:00  Kabarett 2020          §DFGBDFGDFGsdfsdfsd   76.50
** 2020-01-25 19:30:00  Kabarett 2020          üergji§$%dfg34FGDFG  112.50
** 2020-01-25 19:30:00  Kabarett 2020          NULL                  58.00
** 2020-01-26 15:00:00  Frau Holle             trüiu erü43tpknpdfg   56.00
** 2020-01-26 15:00:00  Frau Holle             wrzlbrmpft            14.00
** 2020-05-05 10:00:00  Der gestiefelte Kater  --__::__::__::__::_   12.50
** 2020-05-05 19:30:00  Kabarett 2020          rtw§$%tdfDFHDÖÜERdg   14.00
*/

DROP VIEW IF EXISTS rechnung CASCADE;

CREATE VIEW rechnung(start, titel, qr_code, summe) AS 
SELECT   v.start, st.titel, r.qr_code, SUM(p.preis)
FROM          stueck st 
         JOIN veranstaltung v USING (st_id)
         JOIN reservierung  r USING (v_id) 
         JOIN sitzplatz     s USING (s_id)
         JOIN preis         p USING (vk_id, sk_id) 
GROUP BY v.start, st.titel, r.qr_code
;

SELECT * FROM rechnung ORDER BY start, qr_code;


/*
** Aufgabe 5 
**
** Beantworten Sie die Frage aus Teilaufgabe 3 nochmals. Nehmen Sie diesmal
** die in Aufgabe 4 definierte View zu Hilfe.
**
** summe
** -----
** 70.00
*/

SELECT summe
FROM   rechnung
WHERE  qr_code = 'a' 
--  -- Die Anmerkungen zu Aufgabe 2 gelten auch hier:
--     AND DATE_TRUNC('day', start) = TIMESTAMP '2020-01-24'
--     AND titel = 'Frau Holle' 
--  -- oder besser:
--     AND start = TIMESTAMP '2020-01-24 16:00'
--  -- oder noch besser (bei qr_code -> v_id):
--  -- keine weitere Bedingung
;

/*
** Aufgabe 6
**
** Wie viele Karten wurden die Kindervorstellung „Frau Holle“ 
** am 24. 1. 2020 direkt an der  THEATERKASSE verkauft und wie 
** hoch ist die Summe der Verkaufspreise dieser Karten?
**
** anzahl     summe
** --------------
** 21      241.50
**
** Berechnen Sie diese Werte erst einmal direkt. Erweitern Sie dann
** die View aus Aufgabe 4 so, dass Sie auch diese Aufgabe mit 
** damitt lösen können.
*/

SELECT COUNT(*) AS anzahl, SUM(p.preis) AS summe
FROM        veranstaltung v 
       JOIN reservierung  r USING (v_id) 
       JOIN sitzplatz     s USING (s_id)
       JOIN preis         p USING (vk_id, sk_id) 
WHERE      qr_code IS NULL 
   -- „Frau Holle“ wird über die genaue Startzeit identifiziert.
       AND v.start = TIMESTAMP '2020-01-24 16:00'
;

DROP VIEW IF EXISTS rechnung CASCADE;

CREATE VIEW rechnung(start, titel, qr_code, anzahl, summe) AS 
SELECT   v.start, st.titel, r.qr_code, COUNT(*), SUM(p.preis)
FROM          stueck st 
         JOIN veranstaltung v USING (st_id)
         JOIN reservierung  r USING (v_id) 
         JOIN sitzplatz     s USING (s_id)
         JOIN preis         p USING (vk_id, sk_id) 
GROUP BY v.start, st.titel, r.qr_code
;

SELECT anzahl, summe 
FROM   rechnung 
WHERE      qr_code IS NULL 
   -- „Frau Holle“ wird über die genaue Startzeit identifiziert.
       AND start = TIMESTAMP '2020-01-24 16:00'
;

-- Die Einschränkung auf „Frau Holle“ ist zwingend notwendig,
-- da der "qr_code" den Wert NULL in sehr vielen Veranstaltungen
-- annehmen kann (und i. Allg. auch annimmt).

/*
** Aufgabe 7
**
** Erstellen Sie eine nach "start" sortierte Übersicht, wie viele Plätze an
** JEDEM TAG im Theater RESERVIERT wurden. (Es geht nur um die Tage, nicht
** um die einzelnen Veranstaltungen. Außerdem sind nur echte Reservierungen 
** gefragt, keine Barverkäufe.) 
**
** Achten Sie darauf, dass auch diejenigen Tage ausgegeben werden, an denen 
** zwar eine Veranstaltung stattfindet, aber (bislang) keine Plätze 
** reserviert wurden.
**
** SQL-Profis sollten diese Aufgabe zweimal lösen, einmal mit und einmal 
** ohne Outer Join.
**
** tag           anzahl
** --------------------
** 24. 01. 2020   5
** 25. 01. 2020  12
** 26. 01. 2020   5
** 29. 01. 2020   0
** 01. 02. 2020   0
** 02. 02. 2020   0
** 08. 02. 2020   0
** 05. 05. 2020   2
*/

/* mit RIGHT JOIN */
SELECT   TO_CHAR(v.start::DATE, 'DD. MM. YYYY') AS tag, 
         COUNT(r.qr_code) AS anzahl
FROM     reservierung r RIGHT JOIN veranstaltung v  USING(v_id)
GROUP BY v.start::DATE
ORDER BY TO_CHAR(v.start::DATE, 'YYYY-MM-DD')
;

/* ohne RIGHT JOIN */
SELECT   TO_CHAR(v.start::DATE, 'DD. MM. YYYY') AS tag, 
         COUNT(r.qr_code) AS anzahl
FROM     reservierung r JOIN veranstaltung v  USING(v_id)
GROUP BY v.start::DATE

UNION

SELECT   TO_CHAR(v.start::DATE, 'DD. MM. YYYY') AS tag,
         0 AS anzahl
FROM     veranstaltung v
WHERE    0 = (SELECT COUNT(*)
              FROM   reservierung r
              WHERE  r.v_id = v.v_id)

-- Sortierung nach tag ist hier nicht möglich!
;

/*
** Aufgabe 8 
**
** Schreiben Sie eine View
**   ausverkauft(titel, start),
** die den Titel und die Startzeit derjenigen Veranstaltungen ausgibt,
** die ausverkauft sind oder waren. 
** Ein Veranstaltung gilt als ausverkauft, wenn ALLE verfügbaren Plätze
** reserviert wurden.
**
** Welche Veranstaltungen waren ausverkauft?
**
** titel       start
** ----------------------------
** Frau Holle  2020-01-24 16:00
*/

DROP VIEW IF EXISTS ausverkauft CASCADE;

CREATE VIEW ausverkauft(titel, start) AS
SELECT   st.titel, v.start
FROM     stueck st JOIN veranstaltung v USING (st_id)
                   JOIN reservierung  r USING (v_id) 
GROUP BY st.titel, v.start
HAVING   COUNT(*) = (SELECT COUNT(*) FROM sitzplatz)
;

SELECT titel, start FROM ausverkauft
;


/*
** Aufgabe 9
**
** Kann Familie Kowarschick für das 'Frau Holle' am 25. 1. 2020 um 15:00 Uhr
** noch fünf benachbarte Sitzplätze derselben Kategorie reservieren? 
** (Fünf Sitzplätze sind benachbart, wenn sie zur selben Reihe
** gehören und die maximale Sitzplatznummer um 4 größer ist als die minimale
** Sitzplatznummer.) 
** 
** Geben Sie jeweils die entsprechende Reihe, den Sitzplatz mit der
** niedrigsten Nummer sowie den Sitzplatz mit der höchsten Nummer aus.
**
** Schritt 1:
**   Berechnen Sie zunächst, welche Sitzplätze für das 'Kabarett 2020' 
**   am 25. 1. 2020 um 15:00 Uhr überhaupt noch frei sind. 
**
**   reihe  nummer  sk_id
**   --------------------
**   2       8      2
**   2       9      2
**   2      10      2
**   2      11      2
**   2      12      2
**   3      13      3
**   3      14      2
**   3      15      2
**   3      16      2
**   3      17      2
**   3      18      2
**   3      20      2
**   3      21      3
**   4      22      3
**   4      23      3
**   4      24      3
**   4      25      3
**   4      26      3
**
** Schritt 2:
**   Definieren Sie eine View
**     frei(start, reihe, nummer, sk_id)
**   mit der Sie Fragen gemäß Schritt 1 beantworten können.
**   Achtung: In dieser View wird ein echtes kartesisches Produkt (CROSS JOIN) 
**   zweier Tabellen benötigt. Diese kartesische Produkt führt aber nicht
**   zu Performanzproblemen, wenn man mit ihr nur Fragen zu bestimmten
**   Veranstaltungen beantwortet. (Fragen der Art "Welche freien Sitzplätze
**   gibt es für beliebige Veranstaltungen?" sind auch nicht sonderlich sinnvoll.) 
**
**   Die View gibt für die Beispielsdatenbank 236 Tupel aus. Für den
**   25. 1. 2020, 15:00 sind die genau die oben angegebenen Tupel.
**   
** Schritt 3:
**   Finden Sie nun mit Hilfe der zuvor definierten View zwei Plätze derselben 
**   Kategorie, die in derselben Reihe liegen und zwischen denen sich drei 
**   weitere Plätze befinden (d.h., die eine Platznummer ist um 4 größer 
**   als die andere).
**
**   start                sk_id  reihe  nummer_min  nummer_max
**   ---------------------------------------------------------
**   2020-01-25 15:00:00  2      2       8          12
**   2020-01-25 15:00:00  2      3      14          18
**   2020-01-25 15:00:00  2      3      15          19
**   2020-01-25 15:00:00  2      3      16          20
**   2020-01-25 15:00:00  3      4      22          26
**   2020-01-25 19:30:00  2      3      14          18
**   2020-01-25 19:30:00  2      3      15          19
**   2020-01-25 19:30:00  2      3      16          20
**   2020-01-25 19:30:00  3      4      22          26
**   2020-01-26 15:00:00  ...    ...    ...         ...
**   ...                  ...    ...    ...         ...
** 
** Schritt 4:
**   Erweitern Sie diese vorherige Anfrage. Zählen Sie, wie viele Plätze zwischen 
**   den beiden zuvor ermittelten Plätzen noch frei sind, und testen Sie,
**   ob es sich um genügend Plätze handelt.
**
** Für den 25. Januar, 15:00 sind noch folgende Plätze frei:
**
**   start                sk_id  reihe  nummer_min  nummer_max
**   ---------------------------------------------------------
**   2020-01-25 15:00:00  2      2       8          12
**   2020-01-25 15:00:00  2      3      14          18
**   2020-01-25 15:00:00  3      4      22          26
**
** Für SQL-Freaks:
**   Schreiben Sie eine View, mit der man allgemein n freie, benachbarte 
**   Sitzplätze ermitteln kann. Die View gibt die Anzahl der freien Plätze
**   zusätzlich aus. Für die obige Anfrage liefert die View folgendes 
**   Ergebnis. Insgesamt liefert die View 773 Ergebnistupel mit bis zu 
**   7 freien benachbarten Plätzen.
*/

/* Schritt 1 */

SELECT s.reihe, s.nummer, s.sk_id
FROM   sitzplatz s
WHERE  NOT EXISTS (SELECT * 
                   FROM   reservierung r JOIN veranstaltung v USING (v_id)
                   WHERE      r.s_id = s.s_id
                          AND v.start =  TIMESTAMP '2020-01-25 15:00'
--                        AND st.titel = 'Frau Holle'
--  --                    unnötig, da die Startzeit das Stück eindeutig 
--  --                    identifiziert:  v.start --> st.title (sofern
--  --                    veranstaltung und stueck via st_id gejoint werden)
                  )
;

SELECT   s.reihe, s.nummer, s.sk_id
FROM     sitzplatz s

EXCEPT

SELECT   s.reihe, s.nummer, s.sk_id
FROM     sitzplatz s JOIN reservierung r USING (s_id) 
                     JOIN veranstaltung v USING (v_id)
WHERE    v.start =  TIMESTAMP '2020-01-25 19:30'

ORDER BY reihe, nummer
;

--------------------------------------------------------------------------
/* Schritt 2 */

DROP VIEW IF EXISTS frei CASCADE;

CREATE VIEW frei(start, reihe, nummer, sk_id) 
AS
SELECT v.start, s.reihe, s.nummer, s.sk_id
FROM   veranstaltung v CROSS JOIN sitzplatz s 
EXCEPT
SELECT v.start, s.reihe, s.nummer, s.sk_id
FROM   sitzplatz s JOIN reservierung r USING (s_id) 
                   JOIN veranstaltung v USING (v_id)
;

SELECT * FROM frei;

--------------------------------------------------------------------------
/* Schritt 3 */

SELECT   start, sk_id, reihe, f1.nummer AS nummer_min, f2.nummer AS nummer_max
FROM     frei f1 JOIN frei f2 USING (start, sk_id, reihe)
WHERE    f1.nummer+4 = f2.nummer
ORDER BY start, nummer_min
;

--------------------------------------------------------------------------
/* Schritt 4 */

SELECT f1.start, f1.sk_id, f1.reihe, 
       f1.nummer AS platz1, f2.nummer AS platz2
FROM   frei f1 JOIN frei f2 USING (start, sk_id, reihe)
WHERE      f1.nummer+4 = f2.nummer
       AND 5 = (SELECT COUNT(*) 
                FROM  frei f
                WHERE     f.start = f1.start
                      AND f.sk_id = f1.sk_id
                      AND f.reihe = f1.reihe AND
                      f.nummer BETWEEN f1.nummer AND f2.nummer
               )
ORDER BY start, platz1, reihe, sk_id
;

-- Allgemeiner
SELECT   fs.start, fs.sk_id, fs.reihe,
         fs.nummer, fe.nummer,
         COUNT(fs.nummer) AS anzahl

FROM     frei fs JOIN frei fe USING (start, sk_id, reihe)
                 JOIN frei fm USING (start, sk_id, reihe)

WHERE        fm.nummer>=fs.nummer
         AND fm.nummer<=fe.nummer

GROUP BY fs.start, fs.sk_id, fs.reihe, fs.nummer, fe.nummer
HAVING       COUNT(fs.nummer) = fe.nummer-fs.nummer+1 
;

/* Schritt 4  mit Einschränkung auf (eindeutige) Startzeit und auf 5 Plätze */

SELECT f1.start, f1.sk_id, f1.reihe, 
       f1.nummer AS platz1, f2.nummer AS platz2
FROM   frei f1 JOIN frei f2 USING (start, sk_id, reihe)
WHERE      f1.nummer+4 = f2.nummer
       AND 5 = (SELECT COUNT(*) 
                FROM  frei f
                WHERE     f.start = f1.start
                      AND f.sk_id = f1.sk_id
                      AND f.reihe = f1.reihe AND
                      f.nummer BETWEEN f1.nummer AND f2.nummer
               )
       AND start  =  TIMESTAMP '2020-01-25 15:00'
ORDER BY platz1, reihe, sk_id
;

SELECT   fs.start, fs.sk_id, fs.reihe,
         fs.nummer, fe.nummer,
         COUNT(fs.nummer) AS anzahl

FROM     frei fs JOIN frei fe USING (start, sk_id, reihe)
                 JOIN frei fm USING (start, sk_id, reihe)

WHERE        fm.nummer>=fs.nummer
         AND fm.nummer<=fe.nummer
         AND start  =  TIMESTAMP '2020-01-25 15:00'

GROUP BY fs.start, fs.sk_id, fs.reihe, fs.nummer, fe.nummer
HAVING       COUNT(fs.nummer) = fe.nummer-fs.nummer+1 
         AND COUNT(fs.nummer) = 5
;

--------------------------------------------------------------------------
-- Für SQL-Freaks
--------------------------------------------------------------------------

DROP VIEW IF EXISTS frei2 CASCADE;

CREATE VIEW frei2(start, sk_id, reihe, platz1, platz2, anzahl) AS
SELECT fs.start, fs.sk_id, fs.reihe,
       fs.nummer, fe.nummer,
       COUNT(fs.nummer) AS anzahl

FROM frei fs JOIN frei fe USING (start, sk_id, reihe)
             JOIN frei fm USING (start, sk_id, reihe)

WHERE     fm.nummer>=fs.nummer
      AND fm.nummer<=fe.nummer

GROUP BY fs.start, fs.sk_id, fs.reihe, fs.nummer, fe.nummer
HAVING COUNT(fs.nummer) = fe.nummer-fs.nummer+1
;

SELECT * FROM frei2;

SELECT start, sk_id, reihe, platz1, platz2, anzahl 
FROM   frei2
WHERE      start  =  TIMESTAMP '2020-01-25 15:00'
       AND anzahl = 5
ORDER BY start, platz1, reihe, sk_id
;