/****************************************************************************************
 * Aufgabe 1
 *
 * Welche Unternehmen haben keine E-Mail-Adresse und keine Telefonnummer angegeben?
 * 
 * u_id  name                     branche
 * ===============================================
 * 11    Augsburg Airways         Fluggesellschaft
 * 10    Augsburger Kattunfabrik  Kattunfabrik
 *  9    Bäckerei Bauer           Bäckerei
 *  5    Team23                   Digitalagentur
 *
 ***************************************************************************************/
 
SELECT   u_id, name, branche
FROM     unternehmen
WHERE    email IS NULL AND tel IS NULL
ORDER BY name
;

/****************************************************************************************
 * Aufgabe 2
 *
 * Welche Unternehmen haben nur eine der beiden Kontaktinformationen 
 * Telefonnummer und E-Mail-Adresse angegeben?
 * 
 * u_id  name            branche
 * ====================================
 * 8     Audi               Fahrzeugbau
 * 3     Hochschule Münche  Hochschule
 * 6     MAN                Fahrzeugbau
 *
 ***************************************************************************************/

SELECT   u_id, name, branche
FROM     unternehmen
WHERE    (email IS NULL OR tel IS NULL) AND (email IS NOT NULL OR tel IS NOT NULL) 
ORDER BY name
;
 
SELECT   u_id, name, branche
FROM     unternehmen
WHERE    email IS NULL     AND tel IS NOT NULL 
         OR 
         email IS NOT NULL AND tel IS NULL 
ORDER BY name
;

SELECT u.u_id, u.name, u.branche
FROM   unternehmen u
WHERE  u.tel IS NOT NULL OR u.email IS NOT NULL
EXCEPT 
SELECT u.u_id, u.name, u.branche
FROM   unternehmen u
WHERE  u.tel IS NOT NULL AND u.email IS NOT NULL
;

SELECT u.u_id , u.name, u.branche
FROM   unternehmen u
WHERE  u.email IS NULL AND u.tel IS NOT NULL
UNION
SELECT u.u_id , u.name, u.branche
FROM   unternehmen u
WHERE  u.tel IS NULL AND u.email IS NOT NULL
;

-- Raphael Huber
SELECT u_id, name, branche
FROM   unternehmen u
WHERE  email < tel IS NULL AND NOT (email IS NULL AND tel IS NULL)
ORDER BY u.name
;


/****************************************************************************************
 * Aufgabe 3
 *
 * Welche Unternehmen liegen derzeit nicht in der Region Augsburg, d.h. haben derzeit
 * zumindest eine Zweigstelle, die nicht in Augsburg liegen?
 * 
 * u_id  name                 branche
 * ======================================
 * 8     Audi                 Fahrzeugbau
 * 7     BMW                  Fahrzeugbau
 * 1     Hochschule Augsburg  Hochschule
 * 2     Hochschule Kempten   Hochschule
 * 3     Hochschule München   Hochschule
 * 4     Hochschule Neu-Ulm   Hochschule
 * 6     MAN                  Fahrzeugbau
 *
 ***************************************************************************************/
 
SELECT   DISTINCT u.u_id, u.name, u.branche
FROM     unternehmen u JOIN liegt_in l USING (u_id)
                       JOIN region   r USING (r_id)
WHERE    r.name <> 'Augsburg' AND l.bis IS NULL
ORDER BY u.name
;

SELECT DISTINCT u.u_id, u.name, u.branche
FROM   unternehmen u JOIN liegt_in li USING (u_id)
WHERE      li.r_id IN (SELECT r.r_id
                       FROM region r
                       WHERE name <> 'Augsburg'
                      )
       AND li.bis IS NULL
ORDER BY u.name
;

/****************************************************************************************
 * Aufgabe 4
 *
 * Welche Unternehmen sind und waren auch noch nicht in Augsburg vertreten
 * (weder durch den Hauptsitz noch durch eine Zweigstelle)? 
 * 
 * u_id  name                 branche
 * ===========================================
 *  8    Audi                 Fahrzeugbau
 * 11    Augsburg Airways     Fluggesellschaft
 *  9    Bäckerei Bauer       Bäckerei
 *  7    BMW                  Fahrzeugbau
 *  2    Hochschule Kempten   Hochschule
 *  3    Hochschule München   Hochschule
 *  4    Hochschule Neu-Ulm   Hochschule
 *
 ***************************************************************************************/
 
SELECT   u_id, name, branche
FROM     unternehmen
EXCEPT
SELECT   u.u_id, u.name, u.branche
FROM     unternehmen u JOIN liegt_in l USING (u_id)
                       JOIN region   r USING (r_id)
WHERE    r.name = 'Augsburg'
ORDER BY name
;

SELECT u_id, name, branche
FROM   unternehmen 
WHERE  u_id NOT IN (SELECT u_id
                    FROM   liegt_in li JOIN region USING (r_id)
                    WHERE  name = 'Augsburg'
                   )
ORDER BY name
;

SELECT u_id, name, branche
FROM   unternehmen u 
WHERE  NOT EXISTS (SELECT *
                   FROM   liegt_in li JOIN region USING (r_id)
                   WHERE  li.u_id = u.u_id AND name = 'Augsburg'
                  )
ORDER BY name
;


/****************************************************************************************
 * Aufgabe 5
 *
 * Schreiben Sie eine View "unternehmen_info", die zu einem Unternehmen sämtliche 
 * Informationen sammelt: Unternehmensdaten und Daten über die Regionen, in denen
 * das Unternehmen liegt. Das heißt, verknüpfen Sie alle vier Tabellen des Modells
 * durch geeignete JOIN-Operationen.
 *
 * Achten Sie darauf, dass wirklich alle Unternehmen ausgegeben werden.
 * 
 * u_id  name                     web                                email                                  tel                branche           von         bis         art                r_id  region        l_id  land
 * ==============================================================================================================================================================================================================================
 *  1    Hochschule Augsburg      https://www.hs-augsburg.de/        info@hs-augsburg.de                    +49 (0)89 1265-0   Hochschule        1670-01-01  NULL        Campus             1     Augsburg      1     Deutschland
 *  1    Hochschule Augsburg      https://www.hs-augsburg.de/        info@hs-augsburg.de                    +49 (0)89 1265-0   Hochschule        2010-01-01  NULL        Tagungszentrum     6     Illertissen   1     Deutschland
 *  2    Hochschule Kempten       https://www.hochschule-kempten.e   post@hs-kempten.de                     +49 (0)831 2523-0  Hochschule        1977-01-01  NULL        Campus             2     Kempten       1     Deutschland
 *  2    Hochschule Kempten       https://www.hochschule-kempten.e   post@hs-kempten.de                     +49 (0)831 2523-0  Hochschule        2010-01-01  NULL        Tagungszentrum     6     Illertissen   1     Deutschland
 *  3    Hochschule München       https://www.hm.edu/                webmaster@hm.edu                       +49 (0)821-5586-0  Hochschule        1823-01-01  NULL        Campus             3     München       1     Deutschland
 *  4    Hochschule Neu-Ulm       https://www.hs-neu-ulm.de/         info@hs-neu-ulm.de                     +49 (0)731/9762-0  Hochschule        1994-01-01  NULL        Campus             4     Ulm           1     Deutschland
 *  4    Hochschule Neu-Ulm       https://www.hs-neu-ulm.de/         info@hs-neu-ulm.de                     +49 (0)731/9762-0  Hochschule        2010-01-01  NULL        Tagungszentrum     6     Illertissen   1     Deutschland
 *  5    Team23                   https://www.team23.de/             NULL                                   NULL               Digitalagentur    1999-05-05  NULL        Zentrale           1     Augsburg      1     Deutschland
 *  6    MAN                      https://www.man.eu/de/             NULL                                   +49 89 36098-0     Fahrzeugbau       1986-01-01  NULL        Zentrale           3     München       1     Deutschland
 *  6    MAN                      https://www.man.eu/de/             NULL                                   +49 89 36098-0     Fahrzeugbau       1840-01-01  NULL        Produktion         1     Augsburg      1     Deutschland
 *  6    MAN                      https://www.man.eu/de/             NULL                                   +49 89 36098-0     Fahrzeugbau       1908-01-01  1968-12-31  Schienenfahrzeuge  8     Nürnberg      1     Deutschland
 *  7    BMW                      https://www.bmwgroup.com/          bmwgroup.customerservice@bmwgroup.com  +49 89 382-0       Fahrzeugbau       1916-01-01  NULL        Zentrale           3     München       1     Deutschland
 *  7    BMW                      https://www.bmwgroup.com/          bmwgroup.customerservice@bmwgroup.com  +49 89 382-0       Fahrzeugbau       NULL        NULL        Vertrieb           7     Salzburg      2     Österreich
 *  8    Audi                     https://www.audi.de/               NULL                                   +49 (0)841 89-0    Fahrzeugbau       1949-01-01  NULL        Zentrale           5     Ingolstadt    1     Deutschland
 *  8    Audi                     https://www.audi.de/               NULL                                   +49 (0)841 89-0    Fahrzeugbau       NULL        NULL        Vertrieb           7     Salzburg      2     Österreich
 *  9    Bäckerei Bauer           https://www.baeckerei-bauer.info/  NULL                                   NULL               Bäckerei          NULL        NULL        NULL               NULL  NULL          NULL  NULL
 * 10    Augsburger Kattunfabrik  https://de.wikipedia.org/wiki/NAK  NULL                                   NULL               Kattunfabrik      1885-01-12  1996-06-30  Zentrale           1     Augsburg      1     Deutschland
 * 11    Augsburg Airways         https://de.wikipedia.org/wiki/IQ   NULL                                   NULL               Fluggesellschaft  1981-01-01  2013-12-31  Zentrale           9     Hallbergmoos  1     Deutschland
 *
 ***************************************************************************************/

DROP VIEW IF EXISTS unternehmen_info;

CREATE VIEW unternehmen_info
  (u_id, name, web, email, tel, branche, von, bis, art, r_id, region, l_id, land)
AS 
SELECT u.u_id, u.name, u.web, u.email, u.tel, u.branche,
       li.von, li.bis, li.art, r.r_id, r.name AS region, 
       l.l_id, l.name AS land
FROM   unternehmen u LEFT JOIN liegt_in li USING (u_id)
                     LEFT JOIN region   r  USING (r_id)
                     LEFT JOIN land     l  USING (l_id)
;

SELECT   u_id, name, web, email, tel, branche, von, bis, art, r_id, region, l_id, land
FROM     unternehmen_info                    
ORDER BY u_id
;

/****************************************************************************************
 * Aufgabe 6
 *
 * Wie viele derzeit aktive verschiedene Unternehmen wurden für die einzelnen 
 * Regionen erfasst? 
 *
 * (Sie dürfen die zuvor definierte View verwenden, wenn Sie möchten.
 * Auch im Folgenden kann Ihnen diese View noch gute Dienste leisten.)
 * 
 * region       land         anzahl
 * ================================
 * Augsburg     Deutschland  3
 * Illertissen  Deutschland  3
 * Ingolstadt   Deutschland  1
 * Kempten      Deutschland  1
 * München      Deutschland  3
 * Salzburg     Österreich   2
 * Ulm          Deutschland  1
 * NULL         NULL         1
 *
 ***************************************************************************************/

SELECT   region, land, COUNT(DISTINCT u_id) AS anzahl
FROM     unternehmen_info
WHERE    bis IS NULL
GROUP BY region, land
ORDER BY region, land
;

SELECT DISTINCT region, land, 
                COUNT(u_id) OVER (PARTITION BY r_id, l_id) AS anzahl
FROM            unternehmen_info       
WHERE    bis IS NULL
ORDER BY region, land
;

-- Bessere Lösung (Vanessa Spiegel): 
-- Liefert auch ein Ergebnis  für Halbergmoos und Nürnberg (anzahlz = 0)
SELECT   r.name AS region, c.name AS land, 
         (SELECT COUNT(DISTINCT u_id)
          FROM   liegt_in l 
          WHERE  r.r_id = l.r_id AND l.bis IS NULL
         )  AS anzahl
FROM     region r JOIN land c USING (l_id)
ORDER BY region, land
;

/****************************************************************************************
 * Aufgabe 7
 *
 * Für welche Regionen wurden die meisten Unternehmen erfasst? 
 * 
 * region       land         anzahl
 * ================================
 * Augsburg     Deutschland  4
 *
 ***************************************************************************************/

/*
= (SELECT MAX(...) FROM (SELECT ...) AS dummy)
entspricht
>= ALL (SELECT ...)
*/

SELECT   region, land, COUNT(DISTINCT u_id) AS anzahl
FROM     unternehmen_info
GROUP BY region, land
HAVING   COUNT(DISTINCT u_id) >= ALL (SELECT COUNT(DISTINCT u_id) 
                                      FROM unternehmen_info 
                                      GROUP BY region, land
                                     ) 
ORDER BY region, land
;

SELECT region, land, anzahl
FROM (SELECT region, land, 
             COUNT(u_id) AS anzahl, 
             MAX(COUNT(*)) OVER () AS max_anzahl
      FROM unternehmen_info
      GROUP BY region, land
     )
     AS dummy
WHERE anzahl = max_anzahl
;

WITH region_anzahl(region,land,anzahl)
AS
(SELECT   region, land, COUNT(*) AS anzahl
 FROM     unternehmen_info
 GROUP BY region, land
)
SELECT   region, land, anzahl
FROM     region_anzahl
WHERE    anzahl = (SELECT MAX(anzahl)FROM region_anzahl)
ORDER BY region
;

CREATE VIEW region_anzahl
AS
SELECT   region, land, COUNT(*) AS anzahl
FROM     unternehmen_info
GROUP BY region, land
;

SELECT region, land, anzahl
FROM   region_anzahl
WHERE  anzahl = (SELECT MAX(anzahl) FROM region_anzahl)
;

SELECT region, land, anzahl
FROM   region_anzahl
WHERE  anzahl >= ALL (SELECT anzahl FROM region_anzahl)
;


-- Beispiel für sinnvollen CROSS JOIN. Performance?
-- Das heißt, kommt Optimierer damit zurecht?
WITH unternehmen_anzahl(region, land, anzahl) AS
(SELECT   region, land, COUNT(DISTINCT u_id)
 FROM     unternehmen_info
 GROUP BY region, land
)
SELECT   u1.region, u1.land, u1.anzahl
FROM     unternehmen_anzahl u1, unternehmen_anzahl u2
GROUP BY u1.region, u1.land, u1.anzahl
HAVING   u1.anzahl >= MAX(u2.anzahl)
ORDER BY u1.region
; 
 
 
WITH region_unternehmen(region, land, anzahl)
AS   
   (SELECT DISTINCT region, land, COUNT(u_id) OVER (PARTITION BY region)
    FROM   unternehmen_info       -- COUNT (DISTINCT u_id) ist nicht implementiertbv cvc
   )
SELECT region, land, anzahl
FROM   region_unternehmen
WHERE  anzahl >= ALL (SELECT anzahl FROM   region_unternehmen)
;

SELECT   region, land, COUNT(u_id) as anzahl
FROM     unternehmen_info,
         (SELECT MAX(anzahl) AS max_anzahl
          FROM   (SELECT COUNT(DISTINCT u_id) AS anzahl 
                  FROM unternehmen_info 
                  GROUP BY region, land
                 ) AS dummy1
         ) AS dummy2
GROUP BY region, land, max_anzahl
HAVING   COUNT(DISTINCT u_id) =  max_anzahl;


/****************************************************************************************
 * Aufgabe 8
 *
 * Fügen Sie für Digitalagentur 'Team23' die E-Mail-Adresse 'info@team23.de' 
 * und die Telefonnummer '+49 821/650 719 - 0' ein. Sorgen Sie dafür, dass
 * die Update-Operation auch dann korrekt funktionieren würde, falls es in 
 * Hamburg einen Friseur mit demselben Firmennamen geben sollte und dieser  
 * schon in der Datenbank eingetragen wäre. Verwenden Sie in Ihrer Operation 
 * NICHT den Vergleich "u_id = 5".
* 
 * u_id  name    web                     email           tel                  branche
 * =========================================================================================
 * 5     Team23  https://www.team23.de/  info@team23.de  +49 821/650 719 - 0  Digitalagentur
 *
 ***************************************************************************************/

UPDATE unternehmen
SET    email = 'info@team23.de', tel = '+49 821/650 719 - 0'
WHERE  name = 'Team23' AND branche = 'Digitalagentur';
;

-- Testabfrage 
SELECT u_id, name, web, email, tel, branche
FROM   unternehmen
WHERE  name = 'Team23' AND branche = 'Digitalagentur';

-- Bastian: Die Aufgabenstellung wurde wörtlich genommen :-)
UPDATE unternehmen
SET    email = 'info@team23.de', tel = '+49 (0)821 / 650 719 - 0'
WHERE  u_id IN (SELECT u_id
                FROM unternehmen_info
                WHERE name = 'Team23' AND NOT (region = 'Hamburg' AND branche = 'Friseur')
               )
;

/****************************************************************************************
 * Aufgabe 9
 *
 * Welches ist der älteste Unternehmensstandort? Geben Sie den Standort, das Unternehmen
 * und das Datum aus, seit dem das Unternehmen an diesem Standort tätig ist.
 * 
 * region    land         name                 branche     seit
 * ==================================================================
 * Augsburg  Deutschland  Hochschule Augsburg  Hochschule  1670-01-01
 *
 ***************************************************************************************/

SELECT region, land, name, branche, von AS seit
FROM   unternehmen_info
WHERE  von <= (SELECT MIN(von) FROM liegt_in)
;

SELECT region, land, name, branche, von AS seit    
FROM   unternehmen_info
WHERE  von <= ALL (SELECT von FROM liegt_in WHERE von IS NOT NULL)
;

-- Man beachte den Unterschied der obigen beiden Lösungen: 
-- Wenn die Liste NULL-Werte enthält, wird trotzdem ein Minimum berechnet,
-- <= ALL liefert dagegen NULL als Ergebnis. Im lettenen Fall müssen daher die
-- NULL-Werte ausgeblendet werden.

SELECT region, land, name, branche, von AS seit
FROM   unternehmen_info
WHERE  von <= (SELECT MIN(von) FROM unternehmen_info)
;

SELECT region, land, name, branche, von AS seit
FROM   unternehmen_info
WHERE  von = (SElECT MIN(von) FROM unternehmen_info)
;

SELECT  rang, region, land, name, branche, seit
FROM    (SELECT COUNT(*) OVER (ORDER BY von) AS rang, 
                region, land, name, branche, von AS seit
         FROM   unternehmen_info
        ) AS platzierung 
WHERE   rang = 1
; 

SELECT DISTINCT r.name AS region, l.name AS land , u.name, u.branche, li.von AS seit
FROM   unternehmen u JOIN liegt_in li USING (u_id)
                     JOIN region    r USING (r_id)
                     JOIN land      l USING (l_id),
       (SELECT MIN(von) FROM liegt_in) AS dummy(min_anzahl)
WHERE  li.von = min_anzahl
;

/****************************************************************************************
 * Aufgabe 10
 *
 * Berechnen Sie für jede Region und jedes Land wie viele Unternehmen dafür erfasst
 * wurden und tragen Sie diese Werte in die Attribute "anz_unt" der Tabellen
 * "region" und "land" ein.
 *
 * region
 * r_id  l_id  name         anz_unt
 * ================================
 * 1     1     Augsburg      4
 * 2     1     Kempten       1
 * 3     1     München       3
 * 4     1     Ulm           1
 * 5     1     Ingolstadt    1
 * 6     1     Illertissen   3
 * 7     2     Salzburg      2
 * 8     1     Nürnberg      1
 * 9     1     Hallbergmoos  1
 *
 * 
 * land
 * l_id  name         anz_unt
 * ==========================
 * 1     Deutschland  15
 * 2     Österreich    2
 *
 ***************************************************************************************/
 
UPDATE region
SET    anz_unt =
       (SELECT COUNT(*) 
        FROM   unternehmen_info u
        WHERE  u.r_id = region.r_id
       )
;

UPDATE region r
SET    anz_unt = anz
FROM   (SELECT   r_id, COUNT(u_id) AS anz
        FROM     unternehmen_info
        GROUP BY r_id
       ) AS dummy
WHERE r.r_id= dummy.r_id
;

SELECT r_id, l_id, name, anz_unt
FROM   region
;

UPDATE land l
SET    anz_unt =
       (SELECT COUNT(*) 
        FROM   unternehmen_info u
        WHERE  u.l_id = l.l_id              
       )
;

SELECT l_id, name, anz_unt
FROM   land
;

/****************************************************************************************
 * Bonusaufgabe
 *
 * Wenn Sie diese Aufgabe lösen, wird Ihre Studienarbeit um 0,3 besser bewertet.
 *
 * Sorgen Sie mit Hilfe von Triggern dafür, dass die Attribute "anz_unt" in den beiden
 * Tabellen "region" und "land" stets korrekt aktualisiert werden, wenn irgendwelche 
 * einschlägigen Modifikationen am Datenbestand vorgenommen werden, d.h., wenn 
 * beispielsweise eine neue liegt_in-Beziehung eingefügt wird. 
 * 
 ***************************************************************************************/
 
DROP TRIGGER IF EXISTS trigger_liegt_in ON region CASCADE;
DROP FUNCTION IF EXISTS function_liegt_in() CASCADE;

CREATE FUNCTION function_liegt_in () RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
    BEGIN
       -- Verbesserungswürdig:
       -- Bei jeder Änderung der Tabelel liegt_in werden
       -- derzeit ALLE Regionen und ALLE Länder aktualisiert.
       -- Es reicht, die betroffenen Regionen und Länder zu aktualisieren.
       UPDATE region
       SET    anz_unt =
              (SELECT COUNT(*)
               FROM   unternehmen_info u
               WHERE  u.r_id = region.r_id
              )
       ;
       UPDATE land l
       SET    anz_unt =
              (SELECT COUNT(*)
               FROM   unternehmen_info u
               WHERE  u.l_id = l.l_id
              )
       ;
       RETURN NULL;
    END
$$
;


CREATE TRIGGER trigger_liegt_in 
AFTER INSERT OR UPDATE OR DELETE ON liegt_in
EXECUTE PROCEDURE function_liegt_in()
;

-- Theoretisch müsste auch noch der Fall berücksichtigt werden, dass eine Region einem anderen
-- Land zugeordnet wird (UPDATE region SET l_id ...).
-- Da dieser Fall sehr selten auftriftt, wird hierfür jetzt kein Trigger definiert.

INSERT INTO liegt_in(u_id, r_id, von, bis, art)
VALUES
(1, 2, DATE '2015-01-01', NULL, 'Technologie-Zentrum')
;

SELECT * from region;
SELECT * FROM land;

UPDATE liegt_in
SET    r_id = 3 
WHERE  u_id = 1 AND r_id = 2
;

SELECT * from region;
SELECT * FROM land;

DELETE
FROM   liegt_in
WHERE  u_id = 1 AND r_id = 3
;