Lösung - WI
Werbung
Datenbanksysteme 2 WS 11/12 Fachbereich Angewandte Informatik Dipl.-Inf. Christian Pape 3. Übung Aufgabe 1: Formulieren Sie die folgenden Ausdrücke in SQL: a.) Erstelle eine 3-spaltige Liste mit Namen. Der in der ersten Spalte genannte Angestellte soll der direkte Vorgesetzte des in der zweiten Spalte angegebenen sein und der in der zweiten Spalte angegebene der direkte Vorgesetzte desjenigen aus der letzten Spalte. SELECT P0.PNAME "Ebene 0", P1.PNAME "Ebene 1", P2.PNAME "Ebene 2" FROM PERS P0, PERS P1, PERS P2 WHERE P0.PNR=P1.MNR AND P1.PNR=P2.MNR Ebene 0 Bigboss Bigboss Bigboss Bigboss Ebene 1 Miniboss Miniboss Gerneboss Gerneboss Ebene 2 Apache Sioux Cherokee Mohican b.) Es sollen die Personalnummern, die Namen und die Personalnummern der jeweiligen Vorgsetzten aller Mitarbeiter ausgegeben werden, die mindestens 30 Jahre alt sind, und zwar aufsteigend sortiert nach der Personalnummer der Vorgesetzten. Bei gleichen Vorgesetzten soll als sekundäres Sortierkriterium der Betrag der Gesamtbezüge (aufsteigend) genommen werden. SELECT PNR, PNAME, MNR FROM PERS WHERE PALTER>=30 AND NOT MNR IS NULL ORDER BY MNR, PGEHALT+PBONUS PNR 2 45 46 47 102 PNAME Adlatus Miniboss Apache Sioux Mohican MNR 1 1 45 45 100 c.) Es sollen Nummern und Starttermine der Projekte ausgegeben werden, deren Manager jünger als 30 Jahre sind. Die Ausgabe soll aufsteigend nach der Abteilungsnummer des Projektleiters sortiert sein. SELECT A.JNR, A.JBEGINN FROM PROJ A WHERE A.JMNR IN (SELECT B.PNR FROM PERS B WHERE B.PALTER<30) ORDER BY (SELECT C.ANR FROM PERS C WHERE C.PNR=A.JMNR) JNR 2 3 1 JBEGINN 01-FEB-94 05-JUN-96 01-SEP-97 Aufgabe 2: Formulieren Sie die folgenden Ausdrücke in SQL: a.) Es ist eine Liste aller Theaterstücke zu erstellen, die neben dem Titel auch die Anzahl der in dem Stück vorkommenden Figuren umfaßt. Die Ausgabe soll absteigend nach der Anzahl sortiert sein. SELECT TITEL, (SELECT COUNT(*) FROM ROLLE WHERE DRAMA.TITEL=ROLLE.TITEL) FIGUREN FROM DRAMA ORDER BY FIGUREN DESC TITEL Faust Maria Stuart Tell Wallenstein Iphigenie Nathan Zerbrochene Krug FIGUREN 3 2 2 2 1 1 0 b.) Für jeden Dichter soll die Anzahl seiner Stücke sowie das maximale, das minimale und das durchschnittliche Alter des Autors bei der Uraufführung seiner Stücke berechnet werden. Diese Ausgabe soll aufsteigend nach Dichternamen sortiert sein. SELECT DRAMA.AUTOR, COUNT(*) STUECKE, AVG(DRAMA.U_JAHR-DICHTER.G_JAHR) ALTERSDURCHSCHNITT, MIN(DRAMA.U_JAHR-DICHTER.G_JAHR) ALTERSMINIMUM, MAX(DRAMA.U_JAHR-DICHTER.G_JAHR) ALTERSMAXIMUM FROM DRAMA, DICHTER WHERE DRAMA.AUTOR=DICHTER.AUTOR GROUP BY DRAMA.AUTOR ORDER BY DRAMA.AUTOR AUTOR Goethe Kleist Lessing Schiller STUECKE 2 1 1 3 ALTERSDURCHSCHNITT 37.5 29 50 42 ALTERSMINIMUM 34 29 50 40 ALTERSMAXIMUM 41 29 50 45 Tabellen Dichter-DB: DICHTER DRAMA ROLLE AUTOR Schiller Goethe Kleist Lessing G_ORT Marbarch Frankfurt Kamenz Frankfurt TITEL Maria Stuart Wallenstein Tell Iphigenie Faust Zerbrochene Krug Nathan FIGUR Faust Mephisto Gretchen Wallenstein Piccolomini Tell Geßler Maria Stuart Elisabeth Iphigenie Nathan SCHAUSPIELER DARSTELLER U_ORT Weimar Jena Weimar Mannheim Frankfurt Weimar Bonn TITEL Faust Faust Faust Wallenstein Wallenstein Tell Tell Maria Stuart Maria Stuart Iphigenie Nathan PNR 1 2 3 4 5 6 7 8 PNR 1 1 2 2 2 2 4 4 4 5 5 G_JAHR 1759 1749 1777 1729 W_ORT Frankfurt Frankfurt Dresden Jena Berlin Frankfurt Marbach Bonn FIGUR Faust Wallenstein Faust Nathan Mephisto Tell Iphigenie Gretchen Maria Stuart Tell Piccolomini U_JAHR 1800 1799 1804 1783 1790 1806 1779 AUTOR Schiller Schiller Schiller Goethe Goethe Kleist Lessing R_GESCHLECHT Held Schurke Opfer Held Schurke Held Schurke Held Schurke Held Held NAME Schön Müller Beck Schiller George Mann Krug Platte A_ORT Jena Frankfurt Fulda Fulda Frankfurt Jena Berlin Kaiserslautern Jena Berlin Kaiserslautern A_JAHR 1991 1992 1990 1991 1992 1993 1991 1993 1992 1991 1992 THEATER Schillertheater Schauspiel Schloßtheater Schloßtheater TAT Schillertheater Theater des Westens Pfalztheater Schillertheater Theater des Westens Pfalztheater Tabellen PERS-DB: PERS ABT PROJ PNR 1 2 45 46 47 100 101 102 300 ANR 45 55 56 100 JNR 1 2 3 4 5 6 MITARB PNAME Bigboss Adlatus Miniboss Apache Sioux Gerneboss Cherokee Mohican Goldon PALTER 50 30 40 40 40 25 25 30 28 PGEHALT 100000 40000 80000 40000 40000 50000 60000 40000 85000 PBONUS 10000 10000 0 10000 15000 0 30000 0 0 MNR 1 1 45 45 1 100 100 1 ANR 56 56 45 45 45 55 55 55 100 ANAME Beschaffung Produktion Leitung Vertrieb JNAME Jahresabschluß NT 4.0 ABC-Analyse ISO 9000 - JNR 1 1 2 2 2 3 3 4 4 5 5 5 5 6 PNR 300 1 100 1 101 100 1 46 1 45 46 47 1 1 JMNR 300 100 100 46 - JBEGINN 01.09.1995 01.02.1994 05.06.1996 01.01.1994 15.06.1996 - ZEIT_ANTEIL 100 20 80 5 80 20 2 100 10 - JENDE 31.07.1996 20.06.1995 - STUNDEN 200 100 200 50 400 10 50 40 - JSITZUNG 10:30 11:00 8:00 14:00 -
