/*************************************************************************
 * Division
 *************************************************************************/

-- Alle Händler
SELECT DISTINCT hnr
FROM liefert
;

-- Was wäre, wenn jeder Händler alles liefern würde?
SELECT hnr, wnr
FROM   (SELECT DISTINCT hnr
        FROM liefert
       ) AS alle_haendler
       CROSS JOIN
       ware
;

-- Welche Händler liefern welche Waren nicht?
SELECT hnr, wnr
FROM   (SELECT DISTINCT hnr
        FROM liefert
       ) AS alle_haendler
       CROSS JOIN
       ware
EXCEPT
SELECT hnr, wnr
FROM   liefert
;

-- Welche Händler liefern nicht alle Waren? 
SELECT DISTINCT hnr
FROM (SELECT hnr, wnr
       FROM   (SELECT DISTINCT hnr
               FROM liefert
              ) AS alle_haendler
              CROSS JOIN
              ware
      EXCEPT
      SELECT hnr, wnr
      FROM   liefert
     ) AS wer_liefert_was_nicht
;

-- Welche Händler liefern alle Waren? 
SELECT DISTINCT hnr
FROM liefert
EXCEPT
SELECT DISTINCT hnr
FROM (SELECT hnr, wnr
      FROM   (SELECT DISTINCT hnr
              FROM liefert
             ) AS alle_haendler
             CROSS JOIN
             ware
      EXCEPT
      SELECT hnr, wnr
      FROM   liefert
     ) AS wer_liefert_was_nicht
;


--
-- oder (effizienter) mit zusätzlicher Hilfe der Händler-Tabelle
--

SELECT hnr
FROM haendler
EXCEPT
SELECT hnr
FROM   (SELECT hnr, wnr
        FROM   (SELECT hnr
                FROM haendler
               ) AS alle_haendler
               CROSS JOIN
               ware
       EXCEPT
       SELECT hnr, wnr
       FROM   liefert
      ) AS wer_liefert_was_nicht
;

/*
 * Die obige Lösung basiert auf dem Cross Join und ist damit sehr
 * ineffizient. Effizienter geht es mit dem Exist-Operator 
 * und doppelter Verneinung. 
 */

SELECT DISTINCT l1.hnr
FROM   liefert l1
WHERE  NOT EXISTS
       (SELECT wnr 
        FROM   ware
        EXCEPT
        SELECT l2.wnr 
        FROM   liefert l2
        WHERE  l2.hnr = l1.hnr
       )
;

/* oder noch performanter (da keine Duplikate entfernt werden müssen) */

SELECT h.hnr
FROM   haendler h
WHERE  NOT EXISTS
       (SELECT wnr
        FROM   ware
        EXCEPT
        SELECT l.wnr
        FROM   liefert l
        WHERE  l.hnr = h.hnr
       )
;

/* Alternative mit zweimal NOT EXISTS (doppelte Korrelation: schlecht!) */

SELECT h.hnr
FROM   haendler h
WHERE  NOT EXISTS
       (SELECT *
        FROM   ware w
        WHERE  NOT EXISTS
               (SELECT *
                FROM   liefert l
                WHERE  l.hnr = h.hnr 
                   AND l.wnr = w.wnr
               )
       )
;

/* *********** Alternativen mit Zählen *********** */

SELECT    h.hnr
FROM      haendler h
WHERE     (SELECT COUNT(*) FROM ware)
        = (SELECT COUNT(DISTINCT wnr)
           FROM   liefert l
           WHERE  h.hnr = l.hnr
          )
;

/* Wenn man voraussetzt, dass jede Warennummer mindestens einmal in der
   Tabelle "liefert" vorkommt, d.h., dass es keine Waren gibt, die
   überhaupt kein Händler liefert, funktioniert auch Folgendes:
*/
SELECT    l.hnr
FROM      liefert l
GROUP  BY l.hnr
HAVING    COUNT(DISTINCT wnr) 
        = (SELECT COUNT(*) FROM ware)
;

/* Sonst muss man mit einem Outer-Semi-Join nachhelfen */
SELECT    hnr
FROM      liefert RIGHT JOIN ware USING (wnr)
GROUP  BY hnr
HAVING    COUNT(DISTINCT wnr) 
        = (SELECT COUNT(*) FROM ware)
;

/* *********** Alternative mit Mengenvergleich ***********
  (kein Standard-SQL; funktioniert nicht in PostgreSQL, 
   funktioniert in Transbase)
*/

/*
SELECT   h.hnr
FROM     haendler h
WHERE    (SELECT l.wnr FROM liefert l WHERE l.hnr = h.hnr)
         =
         (SELECT w.wnr FROM ware w)
;
*/

/* Mengen-Vergelich in Standard-SQL:
   A Teilmenge von B und B Teilmenge von A
   <=>
   NOT EXISTS
   (SELECT * FROM A EXCEPT SELECT * FROM B)
   AND
   NOT EXISTS
   (SELECT * FROM B EXCEPT SELECT * FROM A)

Da (SELECT l.wnr FROM liefert l) stets eine Teilmenge
von (SELECT w.wnr FROM ware w) ist, kann man hier
auf diesen Teilmengentest verzichten.
*/
SELECT   h.hnr
FROM     haendler h
WHERE    NOT EXISTS
         ((SELECT w.wnr FROM ware w)
          EXCEPT
          (SELECT l.wnr FROM liefert l WHERE l.hnr = h.hnr)
         );
/* Diese Lösung wurde schon weiter oben aufgeführt. Hier wurde
   sie nur anders begründet. */ 

/*************************************************************************
 * Welcher Händler liefert welche Waren am günstigsten?
 *************************************************************************/

/* Liste alle Händler und ihre Waren auf, für die es keinen 
   anderen Händler gibt, der dieselbe Ware günstiger verkauft.
*/

SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  NOT EXISTS
       (SELECT h2.hnr
        FROM   haendler h2 JOIN liefert l2 USING (hnr)
        WHERE  l1.wnr = l2.wnr AND l2.preis < l1.preis
       )
;

SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  l1.preis <=ALL (SELECT l2.preis
                       FROM   liefert l2
                       WHERE  l2.wnr = l1.wnr
                      )
;

SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  l1.preis <= (SELECT MIN(l2.preis)
                    FROM   liefert l2
                    WHERE  l2.wnr = l1.wnr
                   )
;

/*************************************************************************
 * Welcher Händler liefert welche Ware (echt) billiger als alle anderen? 
 *************************************************************************/

/* Liste alle Händler und ihre Waren auf, für die es keinen 
   anderen Händler gibt, der dieselbe Ware für denselben Preis
   oder sogar noch günstiger verkauft.
*/

SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  NOT EXISTS
       (SELECT h2.hnr
        FROM   haendler h2 JOIN liefert l2 USING (hnr)
        WHERE  h1.hnr <> h2.hnr AND l1.wnr = l2.wnr AND l2.preis <= l1.preis
       )
;

SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  l1.preis <ALL (SELECT l2.preis
                      FROM   liefert l2
                      WHERE  l2.hnr <> h1.hnr and l2.wnr = l1.wnr
                     )
;

/* Die Eieruhr wird nur von einem Lieferanten geliefert.
   Daher existiert das Minimum der Preise der anderen Händler nicht.
   Folglich wird die Eieruhr bei dieser Lösung nicht aufgelistet!
 */

SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  l1.preis < (SELECT MIN(l2.preis)
                   FROM   liefert l2
                   WHERE  l2.hnr <> h1.hnr and l2.wnr = l1.wnr
                  )
;

/* Workaround (hässlich) */
SELECT DISTINCT h1.hnr, h1.name, h1.adresse, w1.wnr, w1.typ, w1.bezeichnung
FROM   haendler h1  JOIN liefert l1 USING (hnr)
                    JOIN ware w1    USING (wnr)
WHERE  l1.preis < (SELECT MIN(l2.preis)
                   FROM   liefert l2
                   WHERE  l2.hnr <> h1.hnr and l2.wnr = l1.wnr
                  )
       OR
       (SELECT COUNT(*)
        FROM   liefert l2
        WHERE  l2.hnr <> h1.hnr and l2.wnr = l1.wnr
       )
       = 0
;


/*************************************************************************
 * Welcher Händler liefert das reichhaltigste Angebot
 * (d.h. mehr als die Anderen; 
 *  genauer: nicht weniger als irgendein Anderer)
 *************************************************************************/

SELECT h.hnr
FROM   haendler h
WHERE  NOT EXISTS
       (SELECT   l.hnr
        FROM     liefert l
        GROUP BY l.hnr
        HAVING   COUNT(DISTINCT l.wnr) 
                 >
                 (SELECT COUNT(DISTINCT l2.wnr)
                  FROM   liefert l2
                  WHERE  h.hnr = l2.hnr
                 )
       )
;

SELECT h.hnr
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       >= ALL
       (SELECT   COUNT(DISTINCT l.wnr)
        FROM     liefert l
        GROUP BY l.hnr
       )
;

SELECT h.hnr
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       = 
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;

/* Nun soll zusätzlich die maximale Anzahl ausgegeben werden. */

SELECT h.hnr,
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       ) AS anzahl
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       =
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;

-- Ohne Code-Duplikation (mit einer temporären View)
WITH max_anzahl(anzahl)
AS   (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
     )

SELECT h.hnr,
       (SELECT anzahl FROM max_anzahl) AS anzahl
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       =
       (SELECT anzahl FROM max_anzahl)
;

-- Im folgenden Beispiel ist die Elimination der doppelten Subquery
-- schwieriger, da es sich um eine korrelierte Subquery handelt.

SELECT h.hnr,
       (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       ) AS anzahl
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       =
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;

-- Aber es geht mit einem kleinen Trick.
WITH hnr_anzahl(hnr, anzahl)
AS   (SELECT   hnr, COUNT(DISTINCT wnr)
      FROM     liefert
      GROUP BY hnr
     )

SELECT h.hnr,
       (SELECT anzahl FROM hnr_anzahl ha WHERE ha.hnr = h.hnr) AS anzahl
FROM   haendler h
WHERE  (SELECT anzahl FROM hnr_anzahl ha WHERE ha.hnr = h.hnr)
       =
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;

-- Hier wäre eine Lateral View noch besser geeignet,
-- da auf die "Parameterübergabe" via "WHERE ha.hnr = h.hnr"
-- verzichtet werden kann.
-- Leider werden Lateral Views von PostgreSQL noch nicht unterstützt.

/*
SELECT h.hnr,
       (SELECT anzahl FROM hnr_anzahl) AS anzahl
FROM   haendler h, 
       LATERAL (SELECT COUNT(DISTINCT l.wnr)
                FROM   liefert l
                WHERE  h.hnr = l.hnr
               ) AS hnr_anzahl(anzahl)
WHERE  (SELECT anzahl FROM hnr_anzahl)
       =
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;
*/

-- Die Zähllösung bring noch einen weitern Vorteil mit sich.
-- Man kann auch Anfragen der Art "Wer liefert midestens 80%
-- des Sortiments" beantwirten.

WITH hnr_anzahl(hnr, anzahl)
AS   (SELECT   hnr, COUNT(DISTINCT wnr)
      FROM     liefert
      GROUP BY hnr
     )

SELECT h.hnr,
       (SELECT anzahl FROM hnr_anzahl ha WHERE ha.hnr = h.hnr) AS anzahl
FROM   haendler h
WHERE  (SELECT anzahl FROM hnr_anzahl ha WHERE ha.hnr = h.hnr)
       >=
       0.8 * 
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;

/* Die Beantwortung der Frag "Wer liefert ECHT am meisten", 
   d.h. "Wer liefert wirklich mehr als alle anderen"
   ist etwas komplizierter, da ein Händler in diesem Fall
   nicht mir sich selbst verglichen werden darf. 
*/

SELECT h.hnr
FROM   haendler h
WHERE  NOT EXISTS
       (SELECT   l.hnr
        FROM     liefert l
        WHERE    l.hnr <> h.hnr
        GROUP BY l.hnr
        HAVING   COUNT(DISTINCT l.wnr)
                 >=
                 (SELECT COUNT(DISTINCT l2.wnr)
                  FROM   liefert l2
                  WHERE  h.hnr = l2.hnr
                 )
       )
;

SELECT h.hnr
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT l.wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       > 
       (SELECT MAX(anzahl)
        FROM   (SELECT   COUNT(DISTINCT l.wnr) AS anzahl
                FROM     liefert l
                WHERE    l.hnr <> h.hnr
                GROUP BY l.hnr
               ) AS postgres_will_hier_einen_alias
       )
;

SELECT h.hnr
FROM   haendler h
WHERE  (SELECT COUNT(DISTINCT wnr)
        FROM   liefert l
        WHERE  h.hnr = l.hnr
       )
       > ALL
       (SELECT   COUNT(DISTINCT wnr)
        FROM     liefert l
        WHERE    l.hnr <> h.hnr
        GROUP BY l.hnr
       )
;
