-- =============================================================================
-- =============================================================================
-- 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 KOWA.Haendler.haendler h WHERE h.hnr = 1) as haendler,
  (SELECT w.bezeichnung FROM KOWA.Haendler.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 KOWA.Haendler.ware), 2)
           AS prozent_waren

FROM     KOWA.Haendler.haendler h LEFT JOIN KOWA.Haendler.liefert l USING (hnr)
GROUP BY h.hnr, h.name, h.adresse
ORDER BY prozent_waren DESC, h.hnr
;


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

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

-- Besser (da performanter): Window-Funktion verwenden.
-- Werden von virtuoso leider nicht unterstützt.


-- SELECT   COUNT(*) OVER (ORDER BY bezeichnung) AS nr,
--          bezeichnung
-- FROM     KOWA.Haendler.ware
-- ORDER BY nr
-- ;

-- oder

-- SELECT   RANK() OVER (ORDER BY bezeichnung) AS nr,
--          bezeichnung
-- FROM     KOWA.Haendler.ware
-- ORDER BY nr
-- ;

-- =============================================================================
-- =============================================================================
-- Subyqueries in der FROM-Klausel 
-- =============================================================================
-- =============================================================================

--******************************************************************************
-- Sinnloses, aber einfaches Beispiel!
--******************************************************************************

-- SQL-Standard-konform, aber funktioniert in Virtuoso nicht

-- SELECT hnr
-- FROM   (SELECT DISTINCT hnr, wnr FROM KOWA.Haendler.liefert)
-- ;

-- PostgreSQL: Man muss für eine Subquery in einer From-Klause 
--             einen Alias-Namen vergeben!
SELECT hnr
FROM   (SELECT DISTINCT hnr, wnr FROM KOWA.Haendler.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 KOWA.Haendler.liefert) AS dummy
GROUP BY hnr
ORDER BY hnr
;

-- Das funktioniert allerdings auch ohne Subquery!

SELECT   hnr, COUNT(DISTINCT wnr) AS anzahl
FROM     KOWA.Haendler.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 KOWA.Haendler.liefert) AS dummy
GROUP BY hnr
ORDER BY hnr
;

SELECT   hnr, COUNT(wnr) AS anzahl
FROM     KOWA.Haendler.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   KOWA.Haendler.liefert l 
        WHERE  l.hnr = h.hnr
       ) AS anzahl
FROM   KOWA.Haendler.haendler h
;

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

SELECT DISTINCT w.wnr, w.typ, w.bezeichnung
FROM   KOWA.Haendler.haendler h JOIN KOWA.Haendler.liefert l1 USING (hnr)
                                JOIN KOWA.Haendler.ware    w  USING (wnr)
where  name = 'Maier' AND adresse like 'Kö%'
       AND preis <= ALL (SELECT preis 
                         FROM    KOWA.Haendler.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   KOWA.Haendler.haendler h JOIN KOWA.Haendler.liefert l1 USING (hnr)
                                JOIN KOWA.Haendler.ware    w  USING (wnr)
where  name = 'Maier' AND adresse like 'Kö%'
       AND preis < ALL (SELECT preis 
                        FROM   KOWA.Haendler.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     KOWA.Haendler.liefert
WHERE    wnr = 3
GROUP BY hnr
;

-- Das Maximum der Durchschnittspreise der einzelnen Händler

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

-- Folgende Anfrage ist kein korrektes SQL

-- SELECT   MAX(AVG(preis))
-- FROM     KOWA.Haendler.liefert
-- WHERE    wnr = 1
-- GROUP BY hnr
-- ;

-- Welcher Händler liefert Ware 3 zum maximalen Durchschnittspreis?


SELECT  hnr, avg_preis
FROM    (SELECT   hnr, AVG(preis) AS avg_preis
         FROM     KOWA.Haendler.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     KOWA.Haendler.liefert
                             WHERE    wnr = 3
                             GROUP BY hnr
                            ) liefert_avg  
                    )
;

-- Mit einer View kann man diese Anfrage einfacher formulieren.

DROP VIEW KOWA.Haendler.liefert_avg;

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

SELECT * FROM KOWA.Haendler.liefert_avg;

SELECT   hnr, avg_preis
FROM     KOWA.Haendler.liefert_avg
WHERE        wnr = 3
         AND avg_preis = (SELECT MAX(avg_preis)
                          FROM   KOWA.Haendler.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            KOWA.Haendler.haendler h, KOWA.Haendler.liefert l
WHERE           h.hnr = l.hnr AND l.wnr = 3
;

SELECT DISTINCT h.hnr, h.name
FROM            KOWA.Haendler.haendler h JOIN KOWA.Haendler.liefert l USING (hnr)
WHERE           l.wnr = 3
;

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

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

-- auch nicht besser
SELECT h.hnr, h.name
FROM   KOWA.Haendler.haendler h
WHERE  EXISTS
       (SELECT *
        FROM   KOWA.Haendler.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            KOWA.Haendler.liefert l
WHERE           l.wnr IN
                (SELECT l.wnr
                 FROM   KOWA.Haendler.liefert l
                 WHERE  l.hnr = 3
                )
;

--******************************************************************************
-- explizite Aufzählung einer Menge
--******************************************************************************

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

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


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

-- zu viele Tupel (falsche Duplikate)
SELECT name
FROM   KOWA.Haendler.haendler JOIN KOWA.Haendler.liefert USING (hnr)
                              JOIN KOWA.Haendler.ware USING (wnr)
WHERE  typ = 'CPU'
;

-- zu wenige Tupel (richtige Duplikate fehlen)
SELECT DISTINCT name
FROM   KOWA.Haendler.haendler JOIN KOWA.Haendler.liefert USING (hnr)
                              JOIN KOWA.Haendler.ware USING (wnr)
WHERE  typ = 'CPU'
;

-- richtige Antwort (Duplikate, aber keine falschen Duplikate)
SELECT name
FROM   KOWA.Haendler.haendler
WHERE  hnr IN
       (SELECT hnr
        FROM   KOWA.Haendler.liefert JOIN KOWA.Haendler.ware USING (wnr)
        WHERE  typ = 'CPU'
      )
;

-- Es funktioniert auch mit einem Join; aber ohne Subquery geht es
-- trotzdem nicht.

SELECT name
FROM   (SELECT DISTINCT name, h.hnr -- JOIN ... USING ist 
                                    -- in Virtuoso kein echter 
                                    -- NATURAL JOIN
        FROM   KOWA.Haendler.haendler h JOIN KOWA.Haendler.liefert USING (hnr)
                                        JOIN KOWA.Haendler.ware USING (wnr)
        WHERE  typ = 'CPU'
       ) AS dummy
;

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

