/* =============================================================================
 * =============================================================================
 * Subyqueries in der SELECT-Klausel (= Projektionsliste)
 *
 * Subqueries müssen immer genau ein Ergebnis-Tupel mit genau einem
 * Attribute als Ergebnis liefern.
 * =============================================================================
 * ============================================================================= */

/*******************************************************************************
 * Sammeln von eindeutigen Informationen aus verschiedenen Tabellen.
 * Das folgende Beispiel ist nicht sonderlich sinnvoll.
 *******************************************************************************/

SELECT
  (SELECT h.name        FROM haendler h WHERE h.hnr = 1) as haendler,
  (SELECT w.bezeichnung FROM ware w     WHERE w.wnr = 3) as ware
--FROM dummy
;

/*******************************************************************************
 * Wie viel Prozent des gesamten Warenangebots liefert jeder Händler? 
 * Beachte: Man muss 100.0 an Stelle von 100 schreiben, um eine Float-
 *          Division an Stelle einer Integer-Division zu erzwingen.
 *******************************************************************************/

SELECT   h.hnr, h.name, h.adresse,

         -- absolute Zahl der Waren, die Händler h liefert
         COUNT(DISTINCT l.wnr)
           AS anzahl_waren,

         -- prozentualer Anteil der Waren, die Händler h liefert
         -- (mit Subquery zur Ermittelung der Anzahl aller Waren)
         ROUND(100.0*COUNT(DISTINCT l.wnr)/(SELECT COUNT(*) FROM WARE), 2)
           AS prozent_waren

FROM     haendler h NATURAL LEFT JOIN liefert l
GROUP BY h.hnr, h.name, h.adresse
ORDER BY prozent_waren DESC, hnr
;


/*******************************************************************************
* Jede Ware soll mit einer laufenden Nummer versehen werden, in der
* alphabethischen Reihenfolge der Bezeichnungen:
*******************************************************************************/

SELECT   (SELECT COUNT(*)+1 
          FROM   ware w2 
          WHERE  w2.bezeichnung < w1.bezeichnung
         ) AS nr,
         w1.bezeichnung
FROM     ware w1
ORDER BY nr
;

-- Besser (da performanter): Window-Funktion verwenden:


SELECT   COUNT(*) OVER (ORDER BY bezeichnung) AS nr,
         bezeichnung
FROM     ware
ORDER BY nr
;

-- oder

SELECT   RANK() OVER (ORDER BY bezeichnung) AS nr,
         bezeichnung
FROM     ware
ORDER BY nr
;
/* =============================================================================
 * =============================================================================
 * Subyqueries in der FROM-Klausel 
 * =============================================================================
 * ============================================================================= */

/*******************************************************************************
 * Sinnlose, aber einfaches Beispiel!
 *******************************************************************************/

/* SQL-Standard-konform, aber funktioniert in PostgreSQL nicht */
/*
SELECT hnr
FROM   (SELECT DISTINCT hnr, wnr FROM liefert)
;
*/

/* PostgreSQL: Man muss für eine Subquery in einer From-Klause 
               einen Alias-Namen vergeben! */
SELECT hnr
FROM   (SELECT DISTINCT hnr, wnr FROM liefert) AS dummy
;

/*******************************************************************************
 * Wie viele verschiedene Waren liefern die jeweiligen Händler?
 * Beachten Sie, dass eine Händler ein und dieselbe Ware
 * mehrfach zu unterschiedlichen Konditionen liefern kann!
 *******************************************************************************/

SELECT   hnr, COUNT(*) AS anzahl
FROM     (SELECT DISTINCT hnr, wnr FROM liefert) AS dummy
GROUP BY hnr
ORDER BY hnr
;

/* Das funktioniert allerdings auch ohne Subquery! */

SELECT   hnr, COUNT(DISTINCT wnr) AS anzahl
FROM     liefert
GROUP BY hnr
ORDER BY hnr
;

/* Ohne DISTINCT ist das Ergebnis fehlerhaft! 
   Zum Beispiel liefert Händler 4 ein und dieselbe Ware zu
   unterschiedlichen Preisen und Konditionen. Gefragt war
   aber die Anzahl der UNTERSCHIEDLICHEN Waren, die ein
   Händler liefert.
 */

SELECT   hnr, COUNT(*) AS anzahl
FROM     (SELECT hnr, wnr FROM liefert) AS dummy
GROUP BY hnr
ORDER BY hnr
;

SELECT   hnr, COUNT(wnr) AS anzahl
FROM     liefert
GROUP BY hnr
ORDER BY hnr
; 

/* Es geht auch ohne GROUP BY mit einer Subquery in der FROM-Klausel. */
SELECT  hnr, 
       (SELECT COUNT(DISTINCT wnr) 
        FROM   liefert l 
        WHERE  l.hnr = h.hnr
       ) AS anzahl
FROM   haendler h
;

/*******************************************************************************
 * Welche Waren liefert Maier aus Königsbrunn billiger als seine Kollegen?
 *******************************************************************************/

SELECT DISTINCT w.wnr, w.typ, w.bezeichnung
FROM   haendler h  NATURAL JOIN 
       liefert  l1 NATURAL JOIN 
       ware     w
where  name = 'Maier' AND adresse like 'Kö%'
       AND preis <= ALL (SELECT preis 
                         FROM   liefert l2 
                         WHERE  l1.wnr = l2.wnr
                        )
;

/*******************************************************************************
 * Welche Waren liefert Maier aus Königsbrunn ECHT billiger als seine Kollegen?
 *******************************************************************************/

SELECT DISTINCT w.wnr, w.typ, w.bezeichnung
FROM   haendler h  NATURAL JOIN 
       liefert  l1 NATURAL JOIN 
       ware     w
where  name = 'Maier' AND adresse like 'Kö%'
       AND preis < ALL (SELECT preis 
                        FROM   liefert l2 
                        WHERE  l1.wnr = l2.wnr AND l2.hnr <> h.hnr
                       )
;
/*******************************************************************************
 * Der maximale Durchschnittspreis der Ware mit Warennummer 1
 *******************************************************************************/

/* Durchschnittspreis der Ware 3 je Händler */

SELECT   hnr, AVG(preis)
FROM     liefert
WHERE    wnr = 3
GROUP BY hnr
;

/* Das Maximum der Durchschnittspreise der einzelnen Händler */

SELECT MAX(avg_preis)
FROM   (SELECT   AVG(preis)
        FROM     liefert
        WHERE    wnr = 3
        GROUP BY hnr
       )
       AS p(avg_preis)
;

/* Folgende Anfrage ist kein korrektes SQL */

/*
SELECT   MAX(AVG(preis))
FROM     liefert
WHERE    wnr = 1
GROUP BY hnr
;
*/

/* Welcher Händler liefert Ware 3 zum maximalen Durchschnittspreis? */

SELECT MAX(avg_preis),
       (SELECT   hnr
        FROM     liefert l
        WHERE    wnr = 3
        GROUP BY hnr
        HAVING   AVG(l.preis) = MAX(liefert_avg.avg_preis)) AS hnr
FROM   (SELECT   hnr, AVG(preis) AS avg_preis
        FROM     liefert
        WHERE    wnr = 3
        GROUP BY hnr) liefert_avg
;

/* Diese Anfrage funktioniert nur, wenn es genau einen Händler gibt,
   der Ware 3 zum maximalen Durchschnittspreis liefert.
   Wenn es mehrere Händler gibt, die die Ware 3 zum maximalen Durchschnittspreis 
   liefern, läuft die Anfrage auf einen Fehler!!!!. */

/* korrekte Version */

SELECT  hnr, avg_preis
FROM    (SELECT   hnr, AVG(preis) AS avg_preis
         FROM     liefert
         WHERE    wnr = 3
         GROUP BY hnr) liefert_avg
WHERE   avg_preis = (SELECT MAX(avg_preis)
                     FROM   (SELECT   hnr, AVG(preis) AS avg_preis
                             FROM     liefert
                             WHERE    wnr = 3
                             GROUP BY hnr
                            ) liefert_avg  
                    )
;

/* Mit einer View kann man diese Anfrage einfacher formulieren. */

DROP VIEW IF EXISTS liefert_avg;

CREATE VIEW liefert_avg
AS
SELECT   hnr, wnr, AVG(preis) AS avg_preis, AVG(lieferzeit) AS avg_lieferzeit
FROM     liefert
GROUP BY hnr, wnr
;

SELECT * FROM liefert_avg;

SELECT   hnr, avg_preis
FROM     liefert_avg
WHERE        wnr = 3
         AND avg_preis = (SELECT MAX(avg_preis)
                          FROM   liefert_avg
                          WHERE  wnr = 3
                         )
;

/* =============================================================================
 * =============================================================================
 * Subyqueries in der WHERE-/HAVING-Klausel 
 * =============================================================================
 * ============================================================================= */

/*******************************************************************************
 * Das IN-Prädikat:
 * Welcher Händler liefert den Artikel mit der Warennumer 3?
 *******************************************************************************/

/* als Semin-Join (DISTINCT ist notwendig!) */
SELECT DISTINCT h.hnr, h.name
FROM            haendler h, liefert l
WHERE           h.hnr = l.hnr AND l.wnr = 3
;

SELECT DISTINCT h.hnr, h.name
FROM            haendler h NATURAL JOIN liefert l
WHERE           l.wnr = 3
;

/* mit nicht-korrelierter Subquery */
SELECT h.hnr, h.name
FROM   haendler h
WHERE  h.hnr IN
       (SELECT l.hnr
        FROM   liefert l
        WHERE  l.wnr = 3
      )
;

/* weniger performant, da korrelierte Subquery */
SELECT h.hnr, h.name
FROM   haendler h
WHERE  3 IN
       (SELECT l.wnr
        FROM   liefert l
        WHERE  l.hnr = h.hnr
      )
;

/* auch nicht besser */
SELECT h.hnr, h.name
FROM   haendler h
WHERE  EXISTS
       (SELECT *
        FROM   liefert l
        WHERE  l.hnr = h.hnr AND l.wnr = 3
      )
;

/*******************************************************************************
 * Welche Händler liefern zumindest einige derjenigen Waren, die Händler 3
 * liefert?
 *******************************************************************************/

SELECT DISTINCT l.hnr
FROM            liefert l
WHERE           l.wnr IN
                (SELECT l.wnr
                 FROM   liefert l
                 WHERE  l.hnr = 3
                )
;

/*******************************************************************************
 * explizite Aufzählung einer Menge
 *******************************************************************************/

SELECT * 
FROM   ware
WHERE  typ IN ('CPU', 'RAM', 'Sonstiges')
;

SELECT *
FROM   ware
WHERE  typ = 'CPU' OR typ = 'RAM' or typ = 'Sonstiges'
;

/*******************************************************************************
 * Das In-Prädikat mit Tupeln.
 *******************************************************************************/

SELECT ROW ('Maier', 'Königsbrunn') IN
       (SELECT name, adresse FROM haendler) as "Ergebnis"
--FROM dummy
;


/*******************************************************************************
 * Falsche Duplikate vermeiden!
 * Wie lauten die Namen der Händler, die CPUs liefern?
 *******************************************************************************/

/* zu viele Tupel (falsche Duplikate) */
SELECT name
FROM   haendler NATURAL JOIN liefert NATURAL JOIN ware
WHERE  typ = 'CPU'
;

/* zu wenige Tupel (richtige Duplikate fehlen) */
SELECT DISTINCT name
FROM   haendler NATURAL JOIN liefert NATURAL JOIN ware
WHERE  typ = 'CPU'
;

/* richtige Antwort (Duplikate, aber keine falschen Duplikate) */
SELECT name
FROM   haendler
WHERE  hnr IN
       (SELECT hnr
        FROM   liefert NATURAL JOIN ware
        WHERE  typ = 'CPU'
      )
;

/* Es funktioniert auch mit einem Join; aber ohne Subquery geht es
   trotzdem nicht.
 */
SELECT name
FROM   (SELECT DISTINCT name, hnr
        FROM   haendler NATURAL JOIN liefert NATURAL JOIN ware
        WHERE  typ = 'CPU'
       ) AS dummy
;

/*******************************************************************************
 * Das Match-Pädikat: Wird von PostgreSQL nicht unterstützt.
 *******************************************************************************/

