/* 
 * a) Geben Sie die wesentlichen Daten der Fakultät für Informatik (Kürzel: I) aus:
 *    Kürzel, Name, E-Mail-Adresse, Telefon.
 */

SELECT f_kuerzel, f_name, f_email, f_tel
FROM   fakultaet
WHERE  f_kuerzel='I'
;


/*
 * b) Geben Sie die wesentlichen Daten der Fakultät für Informatik (Kürzel: I) aus:
 *    Kürzel, Name, E-Mail-Adresse, Telefon, Dekan.
 */

SELECT f_kuerzel, f_name, f_email, f_tel, l.l_id, l_name AS dekan -- nicht l_id, sondern l.l_id!!!
FROM   fakultaet f,
       amt_fk a,
       amtbeschreibung ab,
       lehrender l
WHERE  f.f_id = a.f_id AND a.ab_id = ab.ab_id AND a.l_id = l.l_id AND -- JOINs 
       f_kuerzel='I' AND ab_bez_maennlich = 'Dekan'
;

SELECT f_kuerzel, f_name, f_email, f_tel, l.l_id, l_name AS dekan -- nicht l_id, sondern l.l_id!!!
FROM        fakultaet       f 
       JOIN amt_fk          a  ON f.f_id   = a.f_id
       JOIN amtbeschreibung ab ON ab.ab_id = a.ab_id
       JOIN lehrender       l  ON l.l_id   = a.l_id
WHERE  f_kuerzel='I' AND ab_bez_maennlich = 'Dekan'
;

SELECT f_kuerzel, f_name, f_email, f_tel, l_id, l_name AS dekan
FROM        fakultaet 
       JOIN amt_fk          USING (f_id)
       JOIN amtbeschreibung USING (ab_id)
       JOIN lehrender       USING (l_id)
WHERE  f_kuerzel='I' AND ab_bez_maennlich = 'Dekan'
;

SELECT f_kuerzel, f_name, f_email, f_tel, l_kuerzel AS dekan_kuerzel, 
       l_anrede || ' ' || l_titel || ' ' || l_name AS dekan
FROM        fakultaet       f
       JOIN amt_fk          a  ON f.f_id   = a.f_id
       JOIN amtbeschreibung ab ON ab.ab_id = a.ab_id
       JOIN lehrender       l  ON l.l_id   = a.l_id
WHERE  f_kuerzel='I' AND ab_bez_maennlich = 'Dekan'
;

CREATE OR REPLACE VIEW fakultaet_amt
AS
SELECT a.f_id,
       l.l_id, l.l_kuerzel, l.l_anrede, l.l_titel, l.l_name, l.l_email, l.l_zugeordnet_zu, l.l_status,
       p.p_dienstzimmer, p.p_sprechstunde,
       ab.ab_id, ab.ab_bez_weiblich, ab.ab_bez_maennlich, ab.ab_bez_plural,
       CASE
         WHEN l_anrede = 'Frau'
         THEN ab_bez_weiblich
         ELSE ab_bez_maennlich
       END AS ab_bez
FROM        amt_fk          a
       JOIN lehrender       l  ON a.l_id  = l.l_id
       JOIN professor       p  ON p.l_id  = l.l_id
       JOIN amtbeschreibung ab ON a.ab_id = ab.ab_id
;

SELECT f_kuerzel, f_name, f_email, f_tel, l_kuerzel AS dekan_kuerzel, 
       l_anrede || ' ' || l_titel || ' ' || l_name AS dekan
FROM   fakultaet f JOIN fakultaet_amt fa ON f.f_id = fa.f_id
WHERE  f.f_kuerzel='I' AND fa.ab_bez_maennlich = 'Dekan'
;

/*
 * c) Was passiert, wenn Sie das Kürzel einer Fakultät eingeben, für die
 *    bislang kein Dekan erfasst wurde.
 */

Leeres Ergebnis!

/*
 * d) Geben Sie die wesentlichen Daten ALLER Fakultäten aus:
 *    Kürzel, Name, E-Mail-Adresse, Telefon, Dekan (sofern dieser existiert).
 */

-- funktioniert nicht
SELECT f_kuerzel, f_name, f_email, f_tel, l_name AS dekan
FROM             fakultaet       f
       LEFT JOIN amt_fk          a  ON f.f_id   = a.f_id
       LEFT JOIN amtbeschreibung ab ON ab.ab_id = a.ab_id
       LEFT JOIN lehrender       l  ON l.l_id   = a.l_id
WHERE  ab_bez_maennlich = 'Dekan'
;

-- funktioniert
SELECT f_kuerzel, f_name, f_email, f_tel, l_name AS dekan
FROM             fakultaet       f
       LEFT JOIN amt_fk          a  ON f.f_id   = a.f_id
       LEFT JOIN amtbeschreibung ab ON ab.ab_id = a.ab_id
       LEFT JOIN lehrender       l  ON l.l_id   = a.l_id
WHERE  ab_bez_maennlich = 'Dekan' OR ab_bez_maennlich IS NULL
;

-- funktioniert nicht; es werden alle Aemter und nicht nur der Dekan angezeigt
SELECT f_kuerzel, f_name, f_email, f_tel, l_name AS dekan
FROM             fakultaet       f        
       LEFT JOIN amt_fk          a  ON f.f_id   = a.f_id
       LEFT JOIN lehrender       l  ON l.l_id   = a.l_id
       LEFT JOIN amtbeschreibung ab ON (a.ab_id = ab.ab_id AND ab.ab_bez_maennlich = 'Dekan')
;

-- Grund: Die anderen Aemter werden zwar ausgeblendet, aber durch einen Left Join
--        wieder als Null-Werte eingefügt:
SELECT   f_kuerzel, f_name, f_email, f_tel, l_name AS dekan, ab.ab_bez_maennlich as amt,
         'KEINE SELEKTION' AS typ
FROM               fakultaet       f        
         LEFT JOIN amt_fk          a  ON f.f_id   = a.f_id
         LEFT JOIN lehrender       l  ON l.l_id   = a.l_id
         LEFT JOIN amtbeschreibung ab ON (a.ab_id = ab.ab_id)
WHERE    l_name IS NOT NULL
UNION
SELECT   f_kuerzel, f_name, f_email, f_tel, l_name AS dekan, ab.ab_bez_maennlich as amt,
         'selektion: dekan' AS typ
FROM               fakultaet       f        
         LEFT JOIN amt_fk          a  ON f.f_id   = a.f_id
         LEFT JOIN lehrender       l  ON l.l_id   = a.l_id
         LEFT JOIN amtbeschreibung ab ON (a.ab_id = ab.ab_id AND ab.ab_bez_maennlich = 'Dekan')
WHERE    l_name IS NOT NULL
ORDER BY f_name, dekan, typ
;

-- funktioniert
SELECT f_kuerzel, f_name, f_email, f_tel, l_name AS dekan
FROM   fakultaet f         
       LEFT JOIN
       (     amt_fk          a  
        JOIN lehrender       l  ON l.l_id   = a.l_id 
        JOIN amtbeschreibung ab ON (a.ab_id = ab.ab_id AND ab.ab_bez_maennlich = 'Dekan')
       ) AS af
       ON f.f_id = af.f_id
;

-- funktioniert auch (mit Hilfe der View)
SELECT f.f_kuerzel, f.f_name, f.f_email, f.f_tel, 
       f1.l_name AS "Dekan"
FROM             fakultaet f 
       LEFT JOIN fakultaet_amt f1 ON (f.f_id = f1.f_id AND f1.ab_bez_weiblich = 'Dekanin')
;


-- Verallgemeinerung: 3 verschiedene Ämter
SELECT f.f_kuerzel, f.f_name, f.f_email, f.f_tel, 
       f1.l_name AS "Dekan",
       f2.l_name AS "Frauenbeauftragte",
       f3.l_name AS "Web-Beauftragter"
FROM             fakultaet f 
       LEFT JOIN fakultaet_amt f1 ON (f.f_id = f1.f_id AND f1.ab_bez_weiblich   = 'Dekanin')
       LEFT JOIN fakultaet_amt f2 ON (f.f_id = f2.f_id AND f2.ab_bez_weiblich   = 'Frauenbeauftragte')
       LEFT JOIN fakultaet_amt f3 ON (f.f_id = f3.f_id AND f3.ab_bez_maennlich  = 'Web-Beauftragter')
;

/*
 * e) Geben Sie folgende Daten zu Professor Kowarschick (Kürzel: kowarschick_wolfgang)
 *    aus: Kürzel, Anrede, Titel, Name, E-Mail-Adresse, Dienstzimmer und Sprechstunde.
 */

SELECT l_kuerzel, l_anrede, l_titel, l_name, l_email, p_dienstzimmer, p_sprechstunde
FROM   lehrender l JOIN professor p ON l.l_id = p.l_id
WHERE  l_kuerzel = 'kowarschick_wolfgang'
;

/*
 * f) Welche Lehrveranstaltungen hält der Lehrende mit dem Kürzel 'kowarschick_wolfgang'?
 */

SELECT lv.lv_id, lv.lv_kuerzel, lv.lv_titel
FROM        lehrender         l
       JOIN lehr_haelt_lv     h  ON l.l_id  = h.l_id
       JOIN lehrveranstaltung lv ON h.lv_id = lv.lv_id
WHERE  l_kuerzel = 'kowarschick_wolfgang'
;

/*
 * g) Für welche Lehrenden (einschließlich der Professoren) wurden bislang
 *    keine E-Mail-Adressen erfasst?
 */

SELECT l_kuerzel, l_name
FROM   lehrender 
WHERE  l_email IS NULL
;

/*
 * h) Welche Professoren üben welche Ämter für die Fakultät für Informatik aus.
 *    Sorgen Sie dafür, dass jeweils die Amtsbezeichnung ausgegeben wird, die
 *    dem Geschlecht des jeweiligen Amtsinhabers/der jeweiligen Amtsinhaberin
 *    entspricht.
 */

SELECT l_kuerzel, l_name,
       CASE 
         WHEN l_anrede = 'Frau'
         THEN ab_bez_weiblich
         ELSE ab_bez_maennlich
       END AS amt
FROM        fakultaet       f  
       JOIN amt_fk          a  ON f.f_id  = a.f_id
       JOIN lehrender       l  ON a.l_id  = l.l_id
       JOIN professor       p  ON p.l_id  = l.l_id
       JOIN amtbeschreibung ab ON a.ab_id = ab.ab_id
WHERE  f_kuerzel = 'I'
;

-- Mit der View fakultaet_amt:
SELECT l_anrede || ' ' || l_titel || ' ' || l_name AS professor,
       ab_bez AS amt
FROM   fakultaet JOIN fakultaet_amt USING (f_id)
WHERE  f_kuerzel = 'I'
;

/*
 * i) Welche Ämter übt Prof. Kowarschick wo aus? 
 *    (Achten Sie auch auf die korrekte Amtsbezeichnungen!)
 */

-- Aemter an der Fakultaet
SELECT CASE
         WHEN l_anrede = 'Frau'
         THEN ab_bez_weiblich
         ELSE ab_bez_maennlich
       END AS amtsbezeichnung,
       'Fakultät' AS kategorie, f_kuerzel AS kuerzel, 'Fakultät für ' || f_name AS name
FROM        fakultaet       f  
       JOIN amt_fk          a  ON f.f_id  = a.f_id
       JOIN lehrender       l  ON a.l_id  = l.l_id
       JOIN amtbeschreibung ab ON a.ab_id = ab.ab_id
WHERE  l_kuerzel = 'kowarschick_wolfgang'
UNION
-- Aemter an den Studiengaengen
SELECT CASE
         WHEN l_anrede = 'Frau'
         THEN ab_bez_weiblich
         ELSE ab_bez_maennlich
       END AS amtsbezeichnung,
       'Studiengang' AS kategorie, s_kuerzel AS kuerzel, 'Studiengang ' || s_name AS name
FROM        studiengang     s
       JOIN amt_stg         a  ON s.s_id  = a.s_id
       JOIN lehrender       l  ON a.l_id  = l.l_id
       JOIN amtbeschreibung ab ON a.ab_id = ab.ab_id
WHERE  l_kuerzel = 'kowarschick_wolfgang'
;

-- Kürzer mit den beiden Views fakuultaet_amt und studiengang_amt
CREATE OR REPLACE VIEW studiengang_amt
AS
SELECT a.s_id,
       l.l_id, l.l_kuerzel, l.l_anrede, l.l_titel, l.l_name, l.l_email, l.l_zugeordnet_zu, l.l_status,
       p.p_dienstzimmer, p.p_sprechstunde,
       ab.ab_id, ab.ab_bez_weiblich, ab.ab_bez_maennlich, ab.ab_bez_plural,
       CASE 
         WHEN l.l_anrede = 'Frau'
         THEN ab.ab_bez_weiblich
         ELSE ab.ab_bez_maennlich
       END AS ab_bez
FROM        amt_stg         a
       JOIN lehrender       l  ON a.l_id  = l.l_id
       JOIN professor       p  ON p.l_id  = l.l_id
       JOIN amtbeschreibung ab ON a.ab_id = ab.ab_id
;

SELECT ab_bez AS amtsbezeichnung,
       'Fakultät' AS kategorie, f_kuerzel AS kuerzel, 'Fakultät für ' || f_name AS name
FROM   fakultaet f JOIN fakultaet_amt fa ON f.f_id = fa.f_id
WHERE  l_kuerzel = 'kowarschick_wolfgang'
UNION
SELECT ab_bez AS amtsbezeichnung,
       'Studiengang' AS kategorie, s_kuerzel AS kuerzel, 'Studiengang ' || s_name AS name
FROM   studiengang s JOIN studiengang_amt sa ON s.s_id = sa.s_id
WHERE  l_kuerzel = 'kowarschick_wolfgang'
;



/*
 * Überlegen Sie sich mindestens zwei weitere Fragen, die Sie 
 * verwenden können, wenn Sie entsprechende Web-Seiten mit Hilfe
 * dieser Datenbank dynamische erzeugen.
 *
 * Formulieren Sie diese Anfragen sowohl auf Deutsch, als auch -- nach
 * Möglichkeit -- in SQL.
 */
