/* ** Aufgabe 1 ** ** Welche Veranstaltungen werden am 5. Mai 2023 gegeben? ** ** titel uhrzeit ** ------------------------------ ** Der gestiefelte Kater 10:00 ** Kabarett 2023 19:30 */ SELECT st.titel, TO_CHAR(v.beginn, 'HH24:MI') AS uhrzeit FROM stueck st JOIN veranstaltung v USING (st_id) WHERE DATE_TRUNC('day', v.beginn) = DATE '2023-05-05' ORDER BY uhrzeit ; SELECT st.titel, CAST(v.beginn AS TIME) AS uhrzeit FROM stueck st JOIN veranstaltung v USING (st_id) WHERE CAST(v.beginn AS DATE) = DATE '2023-05-05' ORDER BY uhrzeit ; SELECT st.titel, v.beginn::TIME AS uhrzeit FROM stueck st JOIN veranstaltung v USING (st_id) WHERE v.beginn::DATE = DATE '2023-05-05' ORDER BY uhrzeit ; /* WHERE v.beginn >= '2023-05-05' AND v.beginn < '2023-05-06' WHERE v.beginn BETWEEN '2023-05-05' AND '2023-05-06' WHERE DATE_TRUNC('day', beginn) = '2023-05-05' WHERE v.beginn::DATE = '2023-05-05' -- Cast auf das Datum (ohne Uhrzeit) WHERE CAST(v.beginn AS DATE) = '2023-05-05' CAST(beginn AS TIME) AS uhrzeit beginn::TIME TO_CHAR(beginn, 'HH24:MI') -- eher nicht EXTRACT(hour FROM beginn) || ':' || EXTRACT(minute FROM beginn) AS uhrzeit */ /* ** Aufgabe 2 ** ** Welche Sitzplätze wurden unter dem Passcode 'a' reserviert. ** ** Anmerkung ** ========= ** ** Für verschiedene Reservierunge werden jeweils ** verschiedene Codes "passcode" verwendet (unique). ** Das heißt, ein Passcode kommt in maximal einmal zum Einsatz. ** ** reihe platz ** ----------- ** 1 1 ** 1 2 ** 1 3 ** 1 4 ** 1 5 */ SELECT s.reihe, s.platz FROM reservierung r JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON rs.s_id = s.s_id WHERE r.passcode = 'a' ; SELECT s.reihe, s.platz FROM reservierung r JOIN sitzplatzreservierung rs USING (r_id) JOIN sitzplatz s USING (s_id) WHERE r.passcode = 'a' ; /* ** Aufgabe 3 ** ** Was muss der Kunde für die von ihm unter dem passcode 'a' ** reservierten Plätze (siehe vorherige Aufgabe) bezahlen? ** ** gesamtpreis ** ----------- ** 77.50 ** ** Ü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 2023 bezahlen ** muss. ** ** gesamtpreis ** ----------- ** 46.50 */ -- 24. Januar 2023 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 sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON s.s_id = rs.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 JOIN preisdauer pd ON p.beginn = pd.beginn WHERE r.passcode = 'a' AND v.beginn BETWEEN pd.beginn AND pd.ende ; -- 24. Januar 2023 -- 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 stueck st JOIN veranstaltung v ON st.st_id = v.st_id JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON s.s_id = rs.s_id JOIN preis p ON p.vk_id = v.vk_id AND p.sk_id = s.sk_id JOIN preisdauer pd ON p.beginn = pd.beginn WHERE r.passcode = 'a' AND v.beginn BETWEEN pd.beginn AND pd.ende ; -- 25. Januar 2023 SELECT SUM(p.preis) AS gesamtpreis FROM veranstaltung v JOIN reservierung r USING (v_id) JOIN sitzplatzreservierung rs USING (r_id) JOIN sitzplatz s USING (s_id) JOIN preis p USING (vk_id, sk_id) JOIN preisdauer pd ON p.beginn = pd.beginn WHERE r.passcode = 'abc' AND v.beginn BETWEEN pd.beginn AND pd.ende ; /* ** Aufgabe 4 ** ** Schreiben Sie eine View ** rechnung(beginn, titel, passcode, 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 passcode reservierten ** Karten bezahlen muss. ** ** SELECT * FROM rechnung ORDER BY beginn, passcode; ** ** beginn titel passcode summe ** ----------------------------------------------------------------------- ** 2023-01-24 16:00:00 Frau Holle 2023-01-24 16:00 255.50 ** 2023-01-24 16:00:00 Frau Holle a 77.50 ** 2023-01-25 15:00:00 Frau Holle 2023-01-25 15:00 44.50 ** 2023-01-25 15:00:00 Frau Holle .... 27.00 ** 2023-01-25 15:00:00 Frau Holle abc 46.50 ** 2023-01-25 19:30:00 Kabarett 2023 2023-01-25 19:30 64.00 ** 2023-01-25 19:30:00 Kabarett 2023 ... 84.00 ** 2023-01-25 19:30:00 Kabarett 2023 ... 124.00 ** 2023-01-26 15:00:00 Frau Holle ... 15.50 ** 2023-01-26 15:00:00 Frau Holle ... 62.00 ** 2023-05-05 10:00:00 Der gestiefelte Kater ... 13.50 ** 2023-05-05 19:30:00 Kabarett 2023 ... 15.50 */ DROP VIEW IF EXISTS rechnung CASCADE; CREATE VIEW rechnung(beginn, titel, passcode, summe) AS SELECT v.beginn, st.titel, r.passcode, SUM(p.preis) FROM stueck st JOIN veranstaltung v ON st.st_id = v.st_id JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON s.s_id = rs.s_id JOIN preis p ON p.vk_id = v.vk_id AND p.sk_id = s.sk_id JOIN preisdauer pd ON p.beginn = pd.beginn WHERE v.beginn BETWEEN pd.beginn AND pd.ende GROUP BY v.beginn, st.titel, r.passcode ; SELECT * FROM rechnung ORDER BY beginn, passcode; /* ** Aufgabe 5 ** ** Beantworten Sie die Frage aus Teilaufgabe 3 nochmals. Nehmen Sie diesmal ** die in Aufgabe 4 definierte View zu Hilfe. ** ** summe ** ----- ** 77.50 */ SELECT summe FROM rechnung WHERE passcode = 'a' ; /* ** Aufgabe 6 ** ** Wie viele Karten wurden die Kindervorstellung „Frau Holle“ ** am 24. 1. 2023 direkt an der THEATERKASSE verkauft und wie ** hoch ist die Summe der Verkaufspreise dieser Karten? ** ** anzahl summe ** -------------- ** 21 255.50 ** ** Berechnen Sie diese Werte erst einmal direkt. Erweitern Sie dann ** die View aus Aufgabe 4 so, dass Sie auch diese Aufgabe mit ** damit lösen können. */ SELECT COUNT(*) AS anzahl, SUM(p.preis) AS summe FROM veranstaltung v JOIN reservierung r USING (v_id) JOIN sitzplatzreservierung rs USING (r_id) JOIN sitzplatz s USING (s_id) JOIN preis p USING (vk_id, sk_id) JOIN preisdauer pd ON p.beginn = pd.beginn WHERE r.passcode = '2023-01-24 16:00' AND v.beginn BETWEEN pd.beginn AND pd.ende ; SELECT COUNT(*) AS anzahl, SUM(p.preis) AS summe FROM veranstaltung v JOIN reservierung r USING (v_id) JOIN sitzplatzreservierung rs USING (r_id) JOIN sitzplatz s USING (s_id) JOIN preis p USING (vk_id, sk_id) JOIN preisdauer pd ON p.beginn = pd.beginn WHERE v.beginn = '2023-01-24 16:00' AND r.r_id > 0 AND v.beginn BETWEEN pd.beginn AND pd.ende ; DROP VIEW IF EXISTS rechnung CASCADE; CREATE VIEW rechnung(beginn, titel, passcode, anzahl, summe) AS SELECT v.beginn, st.titel, r.passcode, COUNT(*), SUM(p.preis) FROM stueck st JOIN veranstaltung v ON st.st_id = v.st_id JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON s.s_id = rs.s_id JOIN preis p ON p.vk_id = v.vk_id AND p.sk_id = s.sk_id JOIN preisdauer pd ON p.beginn = pd.beginn WHERE v.beginn BETWEEN pd.beginn AND pd.ende GROUP BY v.beginn, st.titel, r.passcode ; SELECT * FROM rechnung ORDER BY beginn, passcode; SELECT anzahl, summe FROM rechnung WHERE passcode = '2023-01-24 16:00' ; /* ** Aufgabe 7 ** ** Erstellen Sie eine nach "tag" sortierte Übersicht, wie viele Plätze an ** JEDEM TAG im Theater RESERVIERT wurden. (Es geht nur um die Tage, d.h. ** beginn::DATE, 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. 2023 5 ** 25. 01. 2023 12 ** 26. 01. 2023 5 ** 29. 01. 2023 0 ** 01. 02. 2023 0 ** 02. 02. 2023 0 ** 08. 02. 2023 0 ** 05. 05. 2023 2 */ SELECT TO_CHAR(v.beginn::DATE, 'DD. MM. YYYY') AS tag, COUNT(r.r_id) AS anzahl FROM veranstaltung v LEFT JOIN reservierung r ON v.v_id = r.v_id AND r.r_id > 0 LEFT JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id GROUP BY v.beginn::DATE ORDER BY v.beginn::DATE ; /* ohne RIGHT JOIN */ /* -- Problem: Am 26.1.23 gibt es eine Veranstaltung ohne Reservierung und eine mit Reservierung. -- Daher wird dieser Tag doppelt aufgeführt. SELECT TO_CHAR(v.beginn::DATE, 'DD. MM. YYYY') AS tag, COUNT(r.r_id) AS anzahl FROM veranstaltung v JOIN reservierung r ON v.v_id = r.v_id AND r.r_id < 0 JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id GROUP BY v.beginn::DATE UNION SELECT TO_CHAR(v.beginn::DATE, 'DD. MM. YYYY') AS tag, 0 AS anzahl FROM veranstaltung v WHERE 0 = (SELECT COUNT(*) FROM reservierung r JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id WHERE r.r_id < 0 AND r.v_id = v.v_id ) ; SELECT tag, anzahl FROM (SELECT TO_CHAR(v.beginn::DATE, 'DD. MM. YYYY') AS tag, COUNT(r.r_id) AS anzahl FROM veranstaltung v JOIN reservierung r ON v.v_id = r.v_id AND r.r_id < 0 JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id GROUP BY v.beginn::DATE UNION SELECT TO_CHAR(v.beginn::DATE, 'DD. MM. YYYY') AS tag, 0 AS anzahl FROM veranstaltung v WHERE 0 = (SELECT COUNT(*) FROM reservierung r JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id WHERE r.r_id < 0 AND r.v_id = v.v_id ) ) _ ORDER BY SPLIT_PART(tag, '.', 2) || SPLIT_PART(tag, '.', 1) || SPLIT_PART(tag, '.', 1) ; */ /* ** Aufgabe 8 ** ** Schreiben Sie eine View ** ausverkauft(titel, beginn), ** die den Titel und die beginnzeit 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 beginn ** ---------------------------- ** Frau Holle 2023-01-24 16:00 */ DROP VIEW IF EXISTS ausverkauft CASCADE; CREATE VIEW ausverkauft(titel, beginn) AS SELECT st.titel, v.beginn FROM stueck st JOIN veranstaltung v ON st.st_id = v.st_id JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id GROUP BY st.titel, v.beginn HAVING COUNT(*) = (SELECT COUNT(*) FROM sitzplatz) ; SELECT titel, beginn FROM ausverkauft ; /* ** Aufgabe 9 ** ** Kann Familie Kowarschick für das 'Frau Holle' am 25. 1. 2023 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 Sitzplatzplatz um 4 größer ist als die minimale ** Sitzplatzplatz.) ** ** 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 2023' ** am 25. 1. 2023 um 15:00 Uhr überhaupt noch frei sind. ** ** reihe platz 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(beginn, reihe, platz, 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. 2023, 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 Platzplatz ist um 4 größer ** als die andere). ** ** beginn sk_id reihe platz1 platz2 ** ----------------------------------------------------- ** 2023-01-25 15:00:00 2 2 8 12 ** 2023-01-25 15:00:00 2 3 14 18 ** 2023-01-25 15:00:00 2 3 15 19 ** 2023-01-25 15:00:00 2 3 16 20 ** 2023-01-25 15:00:00 3 4 22 26 ** 2023-01-25 19:30:00 2 3 14 18 ** 2023-01-25 19:30:00 2 3 15 19 ** 2023-01-25 19:30:00 2 3 16 20 ** 2023-01-25 19:30:00 3 4 22 26 ** 2023-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: ** ** beginn sk_id reihe platz1 platz2 ** ----------------------------------------------------- ** 2023-01-25 15:00:00 2 2 8 12 ** 2023-01-25 15:00:00 2 3 14 18 ** 2023-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.platz, s.sk_id FROM sitzplatz s WHERE NOT EXISTS (SELECT * FROM veranstaltung v JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id AND rs.s_id = s.s_id WHERE v.beginn = TIMESTAMP '2023-01-25 19:30' ) ; SELECT s.reihe, s.platz, s.sk_id FROM sitzplatz s EXCEPT SELECT s.reihe, s.platz, s.sk_id FROM veranstaltung v JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON s.s_id = rs.s_id WHERE v.beginn = TIMESTAMP '2023-01-25 19:30' ORDER BY reihe, platz ; -------------------------------------------------------------------------- /* Schritt 2 */ DROP VIEW IF EXISTS frei CASCADE; CREATE VIEW frei(beginn, reihe, platz, sk_id) AS SELECT v.beginn, s.reihe, s.platz, s.sk_id FROM veranstaltung v CROSS JOIN sitzplatz s EXCEPT SELECT v.beginn, s.reihe, s.platz, s.sk_id FROM veranstaltung v JOIN reservierung r ON v.v_id = r.v_id JOIN sitzplatzreservierung rs ON r.r_id = rs.r_id JOIN sitzplatz s ON s.s_id = rs.s_id ; SELECT * FROM frei; -------------------------------------------------------------------------- /* Schritt 3 */ SELECT beginn, sk_id, reihe, f1.platz AS platz_min, f2.platz AS platz_max FROM frei f1 JOIN frei f2 USING (beginn, sk_id, reihe) WHERE f1.platz+4 = f2.platz ORDER BY beginn, platz_min ; -------------------------------------------------------------------------- /* Schritt 4 */ SELECT f1.beginn, f1.sk_id, f1.reihe, f1.platz AS platz1, f2.platz AS platz2 FROM frei f1 JOIN frei f2 USING (beginn, sk_id, reihe) WHERE f1.platz+4 = f2.platz AND 5 = (SELECT COUNT(*) FROM frei f WHERE f.beginn = f1.beginn AND f.sk_id = f1.sk_id AND f.reihe = f1.reihe AND f.platz BETWEEN f1.platz AND f2.platz ) ORDER BY beginn, platz1, reihe, sk_id ; -- Allgemeiner SELECT fs.beginn, fs.sk_id, fs.reihe, fs.platz, fe.platz, COUNT(fs.platz) AS anzahl FROM frei fs JOIN frei fe USING (beginn, sk_id, reihe) JOIN frei fm USING (beginn, sk_id, reihe) WHERE fm.platz>=fs.platz AND fm.platz<=fe.platz GROUP BY fs.beginn, fs.sk_id, fs.reihe, fs.platz, fe.platz HAVING COUNT(fs.platz) = fe.platz-fs.platz+1 ; /* Schritt 4 mit Einschränkung auf (eindeutige) Anfangszeit und auf 5 Plätze */ SELECT f1.beginn, f1.sk_id, f1.reihe, f1.platz AS platz1, f2.platz AS platz2 FROM frei f1 JOIN frei f2 USING (beginn, sk_id, reihe) WHERE f1.platz+4 = f2.platz AND 5 = (SELECT COUNT(*) FROM frei f WHERE f.beginn = f1.beginn AND f.sk_id = f1.sk_id AND f.reihe = f1.reihe AND f.platz BETWEEN f1.platz AND f2.platz ) AND beginn = TIMESTAMP '2023-01-25 15:00' ORDER BY platz1, reihe, sk_id ; SELECT fs.beginn, fs.sk_id, fs.reihe, fs.platz, fe.platz, COUNT(fs.platz) AS anzahl FROM frei fs JOIN frei fe USING (beginn, sk_id, reihe) JOIN frei fm USING (beginn, sk_id, reihe) WHERE fm.platz>=fs.platz AND fm.platz<=fe.platz AND beginn = TIMESTAMP '2023-01-25 15:00' GROUP BY fs.beginn, fs.sk_id, fs.reihe, fs.platz, fe.platz HAVING COUNT(fs.platz) = fe.platz-fs.platz+1 AND COUNT(fs.platz) = 5 ; -------------------------------------------------------------------------- -- Für SQL-Freaks -------------------------------------------------------------------------- DROP VIEW IF EXISTS frei2 CASCADE; CREATE VIEW frei2(beginn, sk_id, reihe, platz1, platz2, anzahl) AS SELECT fs.beginn, fs.sk_id, fs.reihe, fs.platz, fe.platz, COUNT(fs.platz) AS anzahl FROM frei fs JOIN frei fe USING (beginn, sk_id, reihe) JOIN frei fm USING (beginn, sk_id, reihe) WHERE fm.platz>=fs.platz AND fm.platz<=fe.platz GROUP BY fs.beginn, fs.sk_id, fs.reihe, fs.platz, fe.platz HAVING COUNT(fs.platz) = fe.platz-fs.platz+1 ; SELECT * FROM frei2; SELECT beginn, sk_id, reihe, platz1, platz2, anzahl FROM frei2 WHERE beginn = TIMESTAMP '2023-01-25 15:00' AND anzahl = 5 ORDER BY beginn, platz1, reihe, sk_id ;