Datenmodellierung und Datenbanksysteme HTW Berlin Prof. Dr. Ingo Claßen Martin Burghardt Stephan Lorenz Übung 2: Komplexe Abfragen mit SQL (15 Punkte) Das Datenmodell für die Abfrageaufgaben beschreibt Auktionen mit Angeboten und Geboten. Angebot AID ArtikelBez AuktionEndeZeitpunkt AuktionMinPreis Gebot AID=AID GID AID GebotsZeitpunkt GebotsPreis Die Tabellen enthalten folgende Daten: Tabelle Angebot AID ---------1 2 3 4 5 ARTIKELBEZ -----------------------------Harry Potter Band 1 Game of Thrones, Staffel 1 Avengers: Age of Ultron Homeland, Staffel 1 Das Boot AUKTIONENDEZEITPUNKT AUKTIONMINPREIS -------------------- --------------20.03.2015 10:45:00 1 21.03.2015 16:10:00 5 20.03.2015 15:39:00 1 19.03.2015 11:05:00 3 20.03.2015 12:07:00 1 Tabelle Gebot GID AID GEBOTSZEITPUNKT GEBOTSPREIS ---------- ---------- ------------------- ----------10001 1 19.03.2015 12:07:29 1 10002 1 20.03.2015 10:01:05 5 11001 2 08.03.2015 10:17:02 14 11002 2 21.03.2015 16:09:59 15 12001 3 08.03.2015 07:30:33 1 12002 3 09.03.2015 15:12:43 3 12003 3 10.03.2015 13:18:05 7 12004 3 11.03.2015 12:26:56 8 12005 3 12.03.2015 21:08:10 9 12006 3 14.03.2015 23:08:12 10 13001 4 10.03.2015 21:09:09 3 13002 4 11.03.2015 10:12:33 8 13003 4 12.03.2015 19:25:39 12 13004 4 18.03.2015 11:21:00 14 13005 4 19.03.2015 11:04:30 15 14001 5 20.03.2015 10:09:12 1 14002 5 20.03.2015 11:30:21 7 Zur Bearbeitung der Aufgaben stellen Sie bitte mit der Anweisung alter session set current_schema=uauktion; auf das Schema auktion um. Damit ist es dann nicht mehr notwendig, auktion vor jeden Tabellennamen zu schreiben. Nehmen Sie folgende weitere Einstellungen vor: ALTER SESSION SET NLS_TERRITORY=germany; ALTER SESSION SET NLS_LANGUAGE=german; ALTER SESSION SET NLS_DATE_FORMAT=’DD.MM.YYYY HH24:MI:SS’; 2 Aufgabe 1: Abfragen (1 Punkt) Maximaler Gebotspreis aller Gebote. Erwartetes Ergebnis: MAX(GEBOTSPREIS) ---------------15 3 Aufgabe 2: Abfragen (2 Punkte) ARTIKELBEZ aller Angebote, die mehr als 3 Gebote haben. Es soll auch die Anzahl der Gebote mit ausgegeben werden. Erwartetes Ergebnis: ARTIKELBEZ ANZAHLGEBOTE ------------------------------ -----------Avengers: Age of Ultron 6 Homeland, Staffel 1 5 4 Aufgabe 3: Abfragen (2 Punkte) ARTIKELBEZ aller Angebote, mit maximalen AuktionMinPreis. Erwartetes Ergebnis: ARTIKELBEZ -----------------------------Game of Thrones, Staffel 1 5 Aufgabe 4: Abfragen (2 Punkte) ARTIKELBEZ aller Angebote, die die höchsten Preise erzielt haben. Es soll auch der maximale Gebotspreis mit ausgegeben werden. Erwartetes Ergebnis: ARTIKELBEZ MAXGEBOTSPREIS ------------------------------ -------------Game of Thrones, Staffel 1 15 Homeland, Staffel 1 15 6 Aufgabe 5: Abfragen (4 Punkte) ARTIKELBEZ aller Angebote, die die meisten Gebote haben. Die maximale Anzahl der Gebote soll mit ausgegeben werden. Erwartetes Ergebnis: ARTIKELBEZ ANZGEBOTE ------------------------------ ---------Avengers: Age of Ultron 6 7 Aufgabe 2.6 (4 Punkte) Kombinationen von Angeboten, die die gleiche Anzahl von Geboten haben. Jeweils die beiden AID der Kombination und die Anzahl der Gebote sollen ausgegeben werden. Erwartetes Ergebnis: AID1 AID2 ANZGEBOTE ---------- ---------- ---------1 2 2 1 5 2 2 5 2 8