/* * 1. Geben Sie die wesentlichen Daten der Fakultät für Informatik (Kürzel: I) aus: * Kürzel, Name, E-Mail-Adresse, Telefon. * * fk_kuerzel fk_name fk_email fk_tel * ====================================================================== * I Informatik inf@informatik.fh-augsburg.de 0821 5586-3450 */ SELECT fk_kuerzel, fk_name, fk_email, fk_tel FROM fakultaet WHERE fk_kuerzel='I' ; /* * 2. Geben Sie die wesentlichen Daten ALLER Fakultäten aus: * Kürzel, Name, E-Mail-Adresse, Telefon, Dekan Id und Name.N * * fk_kuerzel fk_name fk_email fk_tel dekan_id dekan_name * ================================================================================================================================ * G Gestaltung gestaltung@hs-augsburg.de 0821 5586-3401 8 Daniel Rothaug * I Informatik inf@informatik.fh-augsburg.de 0821 5586-3450 11 Jürgen Scholz * AB Architektur und Bauingenieurwesen architektur@hs-augsburg.de 0821 5586-3102 NULL NULL * B Elektrotechnik fb-e@rz.fh-augsburg.de 0821 5586-3360 NULL NULL * M Maschinenbau fm@hs-augsburg.de 0821 5586-3150 NULL NULL * GN Geistes- und Naturwissenschaften NULL 0821 5586-3301 NULL NULL * W Wirtschaft sekretariat@wirtschaft.hs-augsburg.de 0821 5598-2902 NULL NULL */ SELECT fk_kuerzel, fk_name, fk_email, fk_tel, lp_id AS dekan_id, lp_name AS dekan_name FROM fakultaet LEFT JOIN amt_fk USING (fk_id) LEFT JOIN lehrperson USING (lp_id) LEFT JOIN amtsbezeichnung USING (ab_id) WHERE (ab_maennlich = 'Dekan') IS NOT FALSE -- oder -- WHERE ab_maennlich = 'Dekan' OR ab_maennlich IS NULL ; SELECT fk_kuerzel, fk_name, fk_email, fk_tel, lp_id, lp_name FROM fakultaet LEFT JOIN (amt_fk JOIN lehrperson USING (lp_id) JOIN (SELECT ab_id FROM amtsbezeichnung WHERE ab_maennlich = 'Dekan' ) AS ab USING (ab_id) ) AS d USING (fk_id) ; /* * 3. Welche Ämter übt Prof. Kowarschick wie oft aus? * (Achten Sie auch auf die korrekte Amtsbezeichnung!) * * amtsbezeichnung anzahl * ================================ * Praktikantenbeauftragter 3 * PK-Vorsitzender 2 * Web-Beauftragter 1 */ SELECT CASE WHEN lp_anrede = 'Frau' THEN ab_weiblich ELSE ab_maennlich END AS amtsbezeichnung, COUNT(*) as anzahl FROM lehrperson JOIN (SELECT lp_id, ab_id FROM amt_fk UNION ALL SELECT lp_id, ab_id FROM amt_stg ) amt USING (lp_id) JOIN amtsbezeichnung USING (ab_id) WHERE lp_kuerzel = 'wk' GROUP BY amtsbezeichnung ; SELECT CASE WHEN lp_anrede = 'Frau' THEN ab_weiblich ELSE ab_maennlich END AS amtsbezeichnung, SUM(anzahl) AS anzahl FROM (SELECT ab_weiblich, ab_maennlich, COUNT(*) AS anzahl, lp_anrede FROM fakultaet f JOIN amt_fk a USING (fk_id) JOIN lehrperson l USING (lp_id) JOIN amtsbezeichnung ab USING (ab_id) WHERE lp_kuerzel = 'wk' GROUP BY ab_weiblich, ab_maennlich, lp_anrede UNION SELECT ab_weiblich, ab_maennlich, COUNT(*) AS anzahl, lp_anrede FROM studiengang s JOIN amt_stg a USING (stg_id) JOIN lehrperson l USING (lp_id) JOIN amtsbezeichnung ab USING (ab_id) WHERE lp_kuerzel = 'wk' GROUP BY ab_weiblich, ab_maennlich, lp_anrede ) AS dummy GROUP BY ab_weiblich, ab_maennlich, lp_anrede ; /* * 4. Ein Amtsträger ist ein Professor, der entweder ein Amt direkt * an der Fakultät oder an einem Studiengang, der der Fakultät * zugeordnet ist, ausübt. * * Schreiben Sie eine View "v_amtstraeger", die für die einzelnen Fakultäten für * alle Amtsträger folgende Informationen bereitstellt: * Daten der Faktultät: * fk_id, fk_kuerzel,, fk_name, fk_email, fk_tel, * Daten eines Amtsträgers (NULL, sofern nicht vorhanden): * lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, * p_dienstzimmer, p_sprechstunde * ab_id, ab_plural, ab * Daten des Studiengangs (NULL, wenn es sich um ein Fakultätsamt handelt) * stg_id, stg_kuerzel, stg_name, stg_abschluss, stg_titel * (stg_zugeorndet_zu ist bereits in fk_id enthalten) * * Das zusätzlich Attribut "ab" soll die geschlechtsspezifische Amtsbezeichung * des jeweiligen Amtsträgers/der jeweiligen Amtsträgerin enthalten. * Das Attribut "ab_plural" wird gespeichert, um geschlechtsneutral alle * Amtsträger eines bestimmten Amtes aus der View selektieren zu können. * * Schreiben Sie zunächst zwei Views zwei Views "v_amtstraeger_fk" und "v_amtstraeger_stg" * die jeweils nur die Fakultäts-Amtsträger bzw. die Studiengangs-Amtsträger enthält. * Die zweite dieser beiden Views, enthält dieselben Attribute wie "v_amtstraeger", * in der ersten werden die Studiengangsattribute dagegen nicht ausgegeben. * * Definieren Sie anschließend "v_amtstraeger" mit Hilfer dieser beiden Views. */ DROP VIEW IF EXISTS v_amtstraeger_fk CASCADE; CREATE VIEW v_amtstraeger_fk AS SELECT f.fk_id, fk_kuerzel, fk_name, fk_email, fk_tel, lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, p_dienstzimmer, p_sprechstunde, ab_id, ab_plural, (CASE WHEN lp_anrede = 'Frau' THEN ab_weiblich ELSE ab_maennlich END ) AS ab FROM fakultaet f JOIN lehrperson l ON (f.fk_id=l.lp_zugeordnet_zu) JOIN professor p USING (lp_id) JOIN amt_fk a USING (lp_id) JOIN amtsbezeichnung ab USING (ab_id) ; SELECT * FROM v_amtstraeger_fk ORDER BY lp_id, ab_id, fk_id ; DROP VIEW IF EXISTS v_amtstraeger_stg CASCADE; CREATE VIEW v_amtstraeger_stg AS SELECT f.fk_id, fk_kuerzel, fk_name, fk_email, fk_tel, lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, p_dienstzimmer, p_sprechstunde, ab_id, ab_plural, (CASE WHEN lp_anrede = 'Frau' THEN ab_weiblich ELSE ab_maennlich END ) AS ab, stg_id, stg_kuerzel, stg_name, stg_abschluss, stg_titel FROM lehrperson l JOIN professor p USING (lp_id) JOIN amt_stg a USING (lp_id) JOIN amtsbezeichnung ab USING (ab_id) JOIN studiengang s USING (stg_id) JOIN fakultaet f ON (f.fk_id=s.stg_zugeordnet_zu) ; SELECT * FROM v_amtstraeger_stg ORDER BY lp_id, ab_id, fk_id ; DROP VIEW IF EXISTS v_amtstraeger; CREATE VIEW v_amtstraeger AS SELECT fk_id, fk_kuerzel, fk_name, fk_email, fk_tel, lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, p_dienstzimmer, p_sprechstunde, ab_id, ab_plural, ab, NULL AS stg_id, NULL AS stg_kuerzel, NULL AS stg_name, NULL AS stg_abschluss, NULL AS stg_titel FROM v_amtstraeger_fk UNION SELECT fk_id, fk_kuerzel, fk_name, fk_email, fk_tel, lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, p_dienstzimmer, p_sprechstunde, ab_id, ab_plural, ab, stg_id, stg_kuerzel, stg_name, stg_abschluss, stg_titel FROM v_amtstraeger_stg ; SELECT * FROM v_amtstraeger ORDER BY fk_id, lp_id, ab_id ; /* * 5. Lösen Sie die zweite Aufgabe mit Hilfe der View "v_fakultaet". * * fk_kuerzel fk_name fk_email fk_tel dekan_id dekan_name * =============================================================================================================================== * G Gestaltung gestaltung@hs-augsburg.de 0821 5586-3401 8 Daniel Rothaug * I Informatik inf@informatik.fh-augsburg.de 0821 5586-3450 11 Jürgen Scholz * AB Architektur und Bauingenieurwesen architektur@hs-augsburg.de 0821 5586-3102 NULL NULL * B Elektrotechnik fb-e@rz.fh-augsburg.de 0821 5586-3360 NULL NULL * M Maschinenbau fm@hs-augsburg.de 0821 5586-3150 NULL NULL * GN Geistes- und Naturwissenschaften NULL 0821 5586-3301 NULL NULL * W Wirtschaft sekretariat@wirtschaft.hs-augsburg.de 0821 5598-2902 NULL NULL */ SELECT f.fk_kuerzel, f.fk_name, f.fk_email, f.fk_tel, lp_id, lp_name FROM fakultaet f LEFT JOIN v_amtstraeger_fk USING (fk_id) WHERE (ab_plural = 'Dekane') IS NOT FALSE ; /* * 6. Für welche Fakultäten wurde bislang * keine E-Mail-Adressen erfasst? * * fk_kuerzel fk_name * =========?================================== * GN Geistes- und Naturwissenschaften */ SELECT fk_kuerzel, fk_name FROM fakultaet WHERE fk_email IS NULL ; /* * 7. Welche Fakultäten haben bislang keine Frauenbeauftragte gewählt? * * fk_kuerzel fk_name * ============================================= * GN Geistes- und Naturwissenschaften * G Gestaltung * B Elektrotechnik * M Maschinenbau * AB Architektur und Bauingenieurwesen * W Wirtschaft */ SELECT fk_kuerzel, fk_name FROM fakultaet EXCEPT SELECT fk_kuerzel, fk_name FROM v_amtstraeger_fk WHERE ab_plural = 'Frauenbeauftragte' ; /* * 8. In welchen Fakultäten sind alle Ämter besetzt? * * fk_kuerzel fk_name * ====================== * I Informatik */ SELECT fk_kuerzel, fk_name FROM v_amtstraeger GROUP BY fk_id, fk_kuerzel, fk_name HAVING COUNT(DISTINCT ab_id) = (SELECT COUNT(*) FROM amtsbezeichnung) ; /* * 9. Wie viele Studiengänge gibt es? * * anz_studiengaenge * ================= * 6 */ SELECT COUNT(*) AS anz_stuediengaenge FROM studiengang; /* * 10. Erstellen Sie das Vorlesungsverzeichnis von Wolfgang Kowarschick. * * lp_kuerzel lp_titel stg_kuerzel stg_name stg_abschluss stg_titel * ======================================================================================================== * i4.DATA Multimedia-Datenbanken ... ME Mechatronik Bachelor BEng * g4.MM Multimedia-Anwendungen/... ME Mechatronik Bachelor BEng * PROJEKT Medienprojekt IMS Interaktive Mediensysteme Bachelor MA */ SELECT lp_kuerzel, lp_kuerzel, lp_titel, stg_kuerzel, stg_name, stg_abschluss, stg_titel FROM lehrperson JOIN lp_haelt_lv USING (lp_id) JOIN lehrveranstaltung USING (lv_id) JOIN studiengang ON (lv_zugeordnet_zu = stg_id) WHERE lp_kuerzel = 'wk' ; /* * 11. Die Veranstaltungen i4.DATA, g4.MM und PROJEKT, an denen Prof. Kowarschick * als lehrperson beteiligt ist, sind falschen Studiengängen zugeordnet worden. * Diese Fächer werden im Studiengang IAM unterrichtet. * Verbessern Sie diese Fehler mit Hilfe einer Update-Anweisung, * in der Sie keine Integerzahlen (IDs) direkt eingeben! * * lv_kuerzel lv_titel stg_kuerzel stg_name stg_abschluss stg_titel * ================================================================================================= * i4.DATA Multimedia-Datenbanken ... IAM Interaktive Medien Bachelor BA * g4.MM Multimedia-Anwendungen/... IAM Interaktive Medien Bachelor BA * PROJEKT Medienprojekt IAM Interaktive Medien Bachelor BA */ UPDATE lehrveranstaltung SET lv_zugeordnet_zu = (SELECT stg_id FROM studiengang WHERE stg_kuerzel ='IAM' ) WHERE lv_kuerzel IN ('i4.DATA', 'g4.MM', 'PROJEKT') ; UPDATE lehrveranstaltung SET lv_zugeordnet_zu = stg_id FROM studiengang WHERE stg_kuerzel = 'IAM' AND lv_kuerzel IN ('i4.DATA', 'g4.MM', 'PROJEKT') ; SELECT lv_kuerzel, lv_kuerzel, lv_titel, stg_kuerzel, stg_name, stg_abschluss, stg_titel FROM lehrperson JOIN lp_haelt_lv USING (lp_id) JOIN lehrveranstaltung USING (lv_id) JOIN studiengang ON (lv_zugeordnet_zu = stg_id) WHERE lv_kuerzel = 'wk' ; /* * 12. Welcher Dozent hält die meisten Lehrveranstaltungen? * * lp_kuerzel lp_anrede lp_titel lp_name lp_email lp_status anzahlp_lv * ================================================================================================== * wk Herr Prof. Dr. Wolfgang Kowarschick kowa@hs-augsburg.de Professor 3 */ SELECT lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_status, COUNT(*) AS anzahlp_lv FROM lehrperson l JOIN lp_haelt_lv lv USING (lp_id) GROUP BY lp_id HAVING COUNT (*) >=ALL (SELECT COUNT(*) FROM lp_haelt_lv GROUP BY lp_id ) ; SELECT lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_status FROM lehrperson l JOIN lp_haelt_lv lv USING (lp_id) GROUP BY lp_id HAVING COUNT (*) = (SELECT MAX(anzahlp_lv) FROM (SELECT COUNT(*) AS anzahlp_lv FROM lp_haelt_lv GROUP BY lp_id ) AS dummy ) ; -- Hier gibt es diverse weitere Lösungen /* * 13. Welcher Dozent übt die meisten unterschiedlichen Ämter aus? * (Wenn jemand beispielsweise PK-Vorsitzender mehrerer Studiengänge ist, * zählt dies nur als ein Amt.) * * lp_kuerzel lp_anrede lp_titel lp_name lp_email lp_status anzahlp_lv * =================================================================================================== * wk Herr Prof. Dr. Wolfgang Kowarschick kowa@hs-augsburg.de Professor 3 */ SELECT lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_status, COUNT (DISTINCT ab_id) AS anzahlp_aemter FROM v_amtstraeger GROUP BY lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_status HAVING COUNT (DISTINCT ab_id) >=ALL (SELECT COUNT (DISTINCT ab_id) FROM v_amtstraeger GROUP BY lp_id ) ; -- Analog mit MAX -- Hier gibt es zahlreiche andere Lösungen. /* * 14. Geben Sie in eine Tabelle aus, die aufzeigt, welche Studiengänge * den einzelnen Fakultäten zugeordnet sind und an welchen sie * beteiligt sind. * * fk_kuerzel fk_name stg_kuerzel stg_name stg_abschluss art * ============================================================================================= * E Elektrotechnik E Elektrotechnik Bachelor zugeordnet * E Elektrotechnik ME Mechatronik Bachelor zugeordnet * G Gestaltung IAM Interaktive Medien Bachelor zugeordnet * G Gestaltung IMS Interaktive Mediensysteme Master zugeordnet * I Informatik IAM Interaktive Medien Bachelor beteiligt * I Informatik IMS Interaktive Mediensysteme Master beteiligt * I Informatik ME Mechatronik Bachelor beteiligt * I Informatik IN Informatik Bachelor zugeordnet * I Informatik I Informatik Master zugeordnet * M Maschinenbau E Elektrotechnik Bachelor beteiligt * M Maschinenbau ME Mechatronik Bachelor beteiligt */ SELECT fk_kuerzel, fk_name, stg_kuerzel, stg_name, stg_abschluss, 'zugeordnet' AS art FROM fakultaet JOIN studiengang ON (stg_zugeordnet_zu = fk_id) UNION SELECT fk_kuerzel, fk_name, stg_kuerzel, stg_name, stg_abschluss, 'beteiligt' FROM fakultaet JOIN fk_beteiligt_stg USING (fk_id) JOIN studiengang USING (stg_id) ORDER BY fk_name, art, stg_name ; /* * 15. Welche Lehrveranstaltungen werden für alle Studiengänge angeboten? * Lösen Sie dis Anfrage auf zwei Arten, einmal mit COUNT und einmal * mit EXISTS. * * lv_id lv_kuerzel lv_titel * ============================== * 4 W.AKT Aktzeichnen */ SELECT lv_id, lv_kuerzel, lv_titel FROM lehrveranstaltung JOIN stg_zugeordnet_lv USING (lv_id) JOIN studiengang USING (stg_id) GROUP BY lv_id HAVING COUNT(*) = (SELECT COUNT(*) FROM studiengang) ; SELECT lv_id, lv_kuerzel, lv_titel FROM lehrveranstaltung lv1 WHERE NOT EXISTS (SELECT stg_id FROM studiengang EXCEPT SELECT stg_id FROM stg_zugeordnet_lv l WHERE lv_id = lv1.lv_id ) ;