/*
** Aufgabe 1 
**
** Welche Veranstaltungen werden am 24.12.2011 gegeben?
*/ 

SELECT v.titel
FROM   veranstaltung v
WHERE  DATE_TRUNC('day', v.start) = DATE '2011-12-24'
; 

/*
** Aufgabe 2 
**
** Welche Sitzplaetze wurden für Peter und der Wolf am 24.12.2011 um 14:00 
** unter der Kennnummer 1 reserviert?
*/ 

SELECT s.reihe, s.nummer
FROM   veranstaltung v JOIN reservierung r ON v.v_id = r.v_id 
                       JOIN sitzplatz s    ON r.s_id = s.s_id
WHERE      r.ticketnummer = 1 
       AND v.start = TIMESTAMP '2011-12-24 14:00'
       AND v.titel = 'Peter und der Wolf'
;

/*
** Aufgabe 3 
**
** Was muss der Kunde fuer die von ihm unter der Nummer 1 reservierten Plaetze 
** (siehe vorherige Aufgabe) bezahlen?
*/ 

SELECT sum(p.preis)
FROM   veranstaltung v, reservierung r, 
       sitzplatz s, preis p 
WHERE      v.v_id = r.v_id 
       AND r.s_id = s.s_id
       AND v.vk_id = p.vk_id 
       AND p.sk_id = s.sk_id
       AND r.ticketnummer = 1 
       AND v.start = TIMESTAMP '2011-12-24 14:00'
       AND v.titel = 'Peter und der Wolf'
;


/*
** Aufgabe 4 
**
** Schreiben Sie eine View 
**   rechnung(start, titel, ticketnummer, preis),
** mit der man fuer jeden Kunden den Gesamtpreis berechen kann, den der Kunde
** fuer die von ihm an einem bestimmten Tag, unter einer bestimmten
** Kennnummer reservierten Karten bezahlen muss.
*/

DROP VIEW IF EXISTS rechnung CASCADE;

CREATE VIEW rechnung(start, titel, ticketnummer, preis) AS 
SELECT v.start, v.titel, r.ticketnummer, sum(p.preis)
FROM   veranstaltung v, reservierung r, 
       sitzplatz s, preis p
WHERE      v.v_id = r.v_id 
       AND r.s_id = s.s_id
       AND v.vk_id = p.vk_id 
       AND p.sk_id = s.sk_id
GROUP BY r.ticketnummer, v.start, v.titel
;

SELECT * FROM rechnung;

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

SELECT preis 
FROM   rechnung
WHERE       ticketnummer = 1 
       AND start = TIMESTAMP '2011-12-24 14:00'
       AND titel = 'Peter und der Wolf'
;

/*
** Aufgabe 6
**
**  Erstellen Sie eine nach start sortierte Uebersicht, wie viele Plaetze an
**  jedem Tag im Theater reserviert wurden.  Achten Sie darauf, dass auch
**  diejenigen Tage ausgegeben werden, an denen zwar eine Veranstaltung
**  stattfindet, aber (bislang) keine Plaetze reserviert wurden.
*/

SELECT   v.start, COUNT(*) AS anzahl
FROM     veranstaltung v JOIN reservierung USING (v_id)
WHERE    ticketnummer > 0
GROUP BY v.start

UNION

SELECT   v.start, 0 AS anzahl
FROM     veranstaltung v 
WHERE    0 = (SELECT COUNT(*)
              FROM reservierung r
              WHERE ticketnummer > 0 AND r.v_id = v.v_id)

ORDER BY start;


/* mit right join */
SELECT   v.start, COUNT(r.ticketnummer)
FROM     reservierung r RIGHT JOIN veranstaltung v
           ON r.v_id = v.v_id AND ticketnummer > 0
GROUP BY v.start, v.titel
;

/*
** Aufgabe 7 
**
** 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 verfuegbaren Plaetze
** reserviert wurden.
*/

DROP VIEW IF EXISTS ausverkauft CASCADE;

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

SELECT * FROM ausverkauft;

/*
** Aufgabe 8
**
** Welche Veranstaltungen waren ausverkauft?
*/ 

SELECT titel, start FROM ausverkauft;

/*
** Aufgabe 9
**
** Kann Familie Kowarschick für den Freischützen am 5.1.2011 um 18:00 Uhr
** noch fuenf benachbarte Sitzplaetze reservieren? 
** (Fuenf Sitzplaetze sind benachbart, wenn sie zur selben Reihe
** gehoeren und die maximale Sitzplatznummer um 4 groesser ist als die minimale
** Sitzplatznummer.) 
** 
** Geben Sie jeweils die entsprechende Reihe, den Sitzplatz mit der
** niedrigsten Nummer sowie den Sitzplatz mit der hoechsten Nummer aus.
**
** Schritt 1:
**   Berechnen Sie zunaechst, welche Sitzplaetze für den Freischützen am 5.1.2011 
**   um 18:00 Uhr ueberhaupt noch frei sind. Definieren Sie dazu eine entsprechende View:
**     frei(titel, start, sk_id, reihe, nummer)
** 
** Schritt 2:
**   Finden Sie nun mit Hilfe der zuvor definierten View zwei Plaetze,
**   die in derselben Reihe liegen und zwischen denen sich drei weitere Plaetze
**   befinden (d.h., die eine Platznummer ist um 4 groesser als die ANDere).
**
** Schritt 3:
**   Erweitern Sie diese Anfrage. Zaehlen Sie, wieviele Plaetze zwischen 
**   den be_iden zuvor ermittelten Plaetzen noch frei sind, und testen Sie,
**   ob es sich um genuegend Plaetze handelt.
**
** Schritt 4:
**   Schreiben Sie eine View, mit der man allgemein n (n = 2, 3, ... oder 10)
**   freie, benachtbarte Sitzplaetze ermitteln kann.
*/

/* 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.titel = 'Der Freischütz'
                          AND v.start =  TIMESTAMP '2011-01-05 18:00'
                  )
;

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.titel = 'Der Freischütz'
       AND v.start =  TIMESTAMP '2011-01-05 18:00'
;

DROP VIEW IF EXISTS frei CASCADE;

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

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

SELECT f1.titel, f1.start, f1.sk_id, f1.reihe, f1.nummer, f2.nummer
FROM   frei f1 JOIN frei f2 USING (titel, start, sk_id, reihe)
WHERE  f1.nummer+4 = f2.nummer
;

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

SELECT f1.titel, f1.start, f1.sk_id, f1.reihe, f1.nummer, f2.nummer
FROM   frei f1 JOIN frei f2 USING (titel, start, sk_id, reihe)
WHERE      f1.nummer+4 = f2.nummer
       AND 5 = (SELECT COUNT(*) 
                FROM  frei f
                WHERE     f.titel = f1.titel
                      AND 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
               )
;

SELECT f1.titel, f1.start, f1.sk_id, f1.reihe, f1.nummer, f2.nummer
FROM   frei f1 JOIN frei f2 USING (titel, start, sk_id, reihe)
               JOIN frei f  USING (titel, start, sk_id, reihe)
WHERE      f1.nummer+4 = f2.nummer
       AND f.nummer BETWEEN f1.nummer AND f2.nummer
GROUP BY f1.titel, f1.start, f1.sk_id, f1.reihe, f1.nummer, f2.nummer
HAVING   COUNT(*) = 5
;

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

DROP TABLE IF EXISTS anzahlplaetze CASCADE;
CREATE TABLE anzahlplaetze
(
  anzahl integer NOT NULL,
  PRIMARY KEY (anzahl)
);

INSERT INTO anzahlplaetze VALUES ( 1);
INSERT INTO anzahlplaetze VALUES ( 2);
INSERT INTO anzahlplaetze VALUES ( 3);
INSERT INTO anzahlplaetze VALUES ( 4);
INSERT INTO anzahlplaetze VALUES ( 5);
INSERT INTO anzahlplaetze VALUES ( 6);
INSERT INTO anzahlplaetze VALUES ( 7);
INSERT INTO anzahlplaetze VALUES ( 8);
INSERT INTO anzahlplaetze VALUES ( 9);
INSERT INTO anzahlplaetze VALUES (10);

DROP VIEW IF EXISTS frei2 CASCADE;

CREATE VIEW frei2(titel, start, sk_id, reihe, platz1, platz2, anzahl) AS
SELECT f1.titel, f1.start, f1.sk_id, f1.reihe, f1.nummer, f2.nummer, a.anzahl
FROM   frei f1 JOIN frei f2 USING (titel, start, sk_id, reihe)
               JOIN frei f  USING (titel, start, sk_id, reihe),
       anzahlplaetze a
WHERE      f1.nummer+a.anzahl-1 = f2.nummer
       AND f.nummer BETWEEN f1.nummer AND f2.nummer
GROUP BY f1.titel, f1.start, f1.sk_id, f1.reihe, f1.nummer, f2.nummer, a.anzahl
HAVING   COUNT(*) = a.anzahl
;

SELECT * FROM frei2;

SELECT * 
FROM   frei2
WHERE      titel  = 'Der Freischütz'
       AND start  =  TIMESTAMP '2011-01-05 18:00'
       AND anzahl = 5
;

DROP VIEW IF EXISTS frei3 CASCADE;

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

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

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

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

/* Diese be_iden Views liefern dasselbe Ergebnis: */
(SELECT * FROM frei2) EXCEPT  (SELECT * FROM frei3)
UNION
(SELECT * FROM frei3) EXCEPT  (SELECT * FROM frei2);

SELECT * 
FROM   frei2
WHERE      titel  = 'Der Freischütz'
       AND start  =  TIMESTAMP '2011-01-05 18:00'
     AND anzahl = 5
;

SELECT * 
FROM   frei3
WHERE      titel  = 'Der Freischütz'
       AND start  =  TIMESTAMP '2011-01-05 18:00'
       AND anzahl = 5
;
