Lösung - WI
Werbung
Fachbereich Angewandte Informatik Dipl.-Inf. Christian Pape Datenbanksysteme 2 WS 11/12 4. Übung Aufgabe 1: Formulieren Sie die folgenden Ausdrücke in SQL: a.) Bestimme das Durchschnittsgehalt der Angestellten, die mehr als ihre Manager verdienen. SELECT AVG(P.PGEHALT) "Durchschnitt" FROM PERS P, PERS M WHERE P.MNR=M.PNR AND P.GEHALT>M.PGEHALT Durchschnitt 60000 b.) Welche Mitarbeiter (PNR, GEHALT) haben ein höheres Gehalt als Sioux und Apache zusammen verdienen? SELECT PNR, PGEHALT FROM PERS WHERE PGEHALT>(SELECT SUM(PGEHALT) FROM PERS WHERE PNAME IN (’Apache’,’Sioux’)) PNR 1 300 PGEHALT 100000 85000 c.) An wie vielen Orten (U_ORT) wurden Dramen uraufgeführt? SELECT COUNT(DISTINCT U_ORT) "Anzahl Uraufführungsorte" FROM DRAMA Uraufführungsorte 5 d.) An welchen Orten wurden mehr als zwei Dramen uraufgeführt? SELECT DISTINCT OUTER.U_ORT FROM DRAMA OUTER WHERE 2<(SELECT COUNT(*) FROM DRAMA INNER WHERE INNER.U_ORT=OUTER.U_ORT) ...oder auch: SELECT U_ORT FROM DRAMA GROUP BY U_ORT HAVING COUNT(*)>2 U_ORT Weimar e.) Welches Drama (Titel, U_JAHR) wurde zuerst aufgeführt? SELECT TITEL, U_JAHR FROM DRAMA WHERE U_JAHR=(SELECT MIN(U_JAHR) FROM DRAMA) TITEL Nathan U_JAHR 1779 Aufgabe 2: Formulieren Sie die folgenden Ausdrücke in SQL: a.) Für alle Projekte, die im Jahr 1994 begonnen wurden, sollen die Zahl der Arbeitsstunden und der durchschnittliche Zeitanteil der daran beteiligten Mitarbeiter bestimmt werden. Mitarbeiter der Abteilung Leitung sollen aber nicht berücksichtigt werden. SELECT JNR, SUM(STUNDEN) ARBEITSSTUNDEN, AVG(ZEIT_ANTEIL) ARBEITSANTEIL FROM MITARB WHERE JNR IN (SELECT JNR FROM PROJ WHERE TO_CHAR(JBEGINN,’YYYY’)=’1994’) AND PNR IN (SELECT PNR FROM PERS WHERE ANR IN (SELECT ANR FROM ABT WHERE ANAME!=’Leitung’)) GROUP BY JNR JNR 2 4 ARBEITSSTUNDEN 300 400 ARBEITSANTEIL 80 100 b.) Das Durchschnittsgehalt pro Abteilung aller Mitarbeiter unter 50 Jahren soll bestimmt werden. Darüber hinaus soll es in der jeweiligen Abteilung mehr als einen Mitarbeiter geben, der diese Bedingung erfüllt. SELECT ANR, AVG(PGEHALT) GEHALTSDURCHSCHNITT FROM PERS WHERE PALTER<50 GROUP BY ANR HAVING COUNT(*)>1 ANR 45 55 GEHALTSDURCHSCHNITT 53333.333 50000 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 -
