/* 
 * 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
         )
;