/*
** Olympiade (Teil 3)
*/

/*
** Aufgabe a 
**
** Erstellen Sie ein Teilnehmer-Verzeichnis. Neben den persoenlichen Daten 
** der Teilnehmer sollen auch die Sportarten aufgefuehrt werden, 
** in denen diese antreten.   
**
** Geben Sie in diesem Verzeichnis auch diejenigen Sportler aus,
** die noch in keinem Wettbewerb gemeldet sind.
*/ 

-- LEFT JOIN (oder vergleichbarer UNION) ist wichtig
SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land, w.sportart
FROM            sportler s LEFT JOIN teilnahme t ON s.s_id=t.s_id
                           LEFT JOIN wettkampf w ON w.w_id=t.w_id
ORDER BY        name, vorname
;

SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land, w.sportart
FROM            sportler s LEFT JOIN teilnahme t USING (s_id)
                           LEFT JOIN wettkampf w USING (w_id)
ORDER BY        name, vorname
;

-- Outer Join explizit
SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land, w.sportart
FROM            sportler s JOIN teilnahme t USING (s_id)
                           JOIN wettkampf w USING (w_id)
UNION
SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land, NULL
FROM            sportler s 
WHERE           NOT EXISTS 
                (SELECT * FROM teilnahme t WHERE t.s_id = s.s_id)
ORDER BY        name, vorname
;

--
-- Mit Listen-Aggregation
--
-- http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html
CREATE OR REPLACE FUNCTION array_append_distinct(anyarray, anyelement) RETURNS anyarray AS 
$____$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '{}'
   WHEN $1 IS NULL
       THEN ARRAY[$2]
   WHEN $2 IS NULL
       THEN $1
   WHEN $2 = ANY($1)
       THEN $1
   ELSE
       array_append($1,$2)
END;
$____$
LANGUAGE SQL IMMUTABLE;

-- http://www.postgresql.org/docs/9.0/interactive/sql-createaggregate.html
DROP AGGREGATE IF EXISTS array_accumulate (anyelement);

CREATE AGGREGATE array_accumulate (anyelement)
(
   SFUNC = array_append_distinct,
   STYPE = anyarray
); 

SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, array_accumulate(w.sportart) 
FROM     sportler s LEFT JOIN teilnahme t USING (s_id)
                    LEFT JOIN wettkampf w USING (w_id)
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land; 

/*
** Aufgabe b 
**
** Welche Sportlerinnen erhielten Gold, Silber und Bronze im 
** Schwimmwettbewerb '100m Brust' der Frauen?
*/ 

SELECT DISTINCT t.platz, s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM            wettkampf w, sportler s, teilnahme t
WHERE           w.w_id=t.w_id AND t.s_id=s.s_id AND
                w.sportart ='Schwimmen' AND w.wettkampfart = '100m Brust' AND w.geschlecht='w' AND
                t.platz BETWEEN 1 AND 3
ORDER BY        platz
;

SELECT DISTINCT t.platz, s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t USING (s_id)
                           JOIN wettkampf w USING (w_id)
WHERE           sportart ='Schwimmen' AND wettkampfart = '100m Brust' AND s.geschlecht='w' AND
                platz BETWEEN 1 AND 3
ORDER BY        platz
;


/*
** Aufgabe c 
**
** Welche Plaetze belegt Sandra Völker bislang wie oft?
*/

SELECT   t.s_id, t.platz, COUNT(*) AS anzahl
FROM     sportler s JOIN teilnahme t USING (s_id)
WHERE    s.name='Völker' AND s.vorname='Sandra'
GROUP BY t.platz, t.s_id
;

/* Etwas komplizierter Variante, aber dieselbe Idee */

SELECT   s_id, platz, COUNT(*) AS anzahl
FROM     (SELECT s_id, platz
          FROM   sportler s JOIN teilnahme t USING (s_id)
          WHERE  s.name='Völker' AND s.vorname='Sandra'
         ) AS VoelkerPlaetze
GROUP BY s_id, platz
;


/*
** Aufgabe d
**
** Welche Sportler/-innen haben mehrere Medaillien gewonnen?
*/

-- medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, COUNT(*) AS medaillenanzahl
FROM     sportler s, teilnahme t
WHERE    s.s_id=t.s_id AND t.platz BETWEEN 1 AND 3
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
HAVING   COUNT(*)>1
ORDER BY name, vorname
;

SELECT s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s
WHERE  (SELECT COUNT(t.s_id) 
        FROM   teilnahme t 
        WHERE  t.s_id=s.s_id AND t.platz BETWEEN 1 AND 3
       )
       > 1
;


/*
** Aufgabe e
**
** Welche Sportler/-innen haben drei oder mehr Gold-Medaillien gewonnen?
*/

-- medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, COUNT(*) AS medaillenanzahl
FROM     sportler s, teilnahme t
WHERE    s.s_id=t.s_id AND t.platz=1
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
HAVING   COUNT(*)>=3
ORDER BY name, vorname
;

SELECT s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s
WHERE  (SELECT COUNT(t.s_id) 
        FROM   teilnahme t 
        WHERE  t.s_id=s.s_id AND t.platz=1
       )
       >= 3
;


/*
** Aufgabe f
**
** Welche Sportlerin (d.h. welche Schwimmerin) hat ALLE
** Schwimmwettbewerbe der w gewonnen?
**
** Anmerkung: SELECT COUNT(*) FROM tabelle
**            wird von PostgreSQL stets durch einen kompletten Scan der Tabelle
**            ermittelt.
*/

-- medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, COUNT(*) AS medaillenanzahl
FROM     sportler s JOIN teilnahme t USING (s_id)
                    JOIN wettkampf w USING (w_id)
WHERE    t.platz=1 AND w.sportart='Schwimmen' AND w.geschlecht = 'w'
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
HAVING   COUNT(*) = (SELECT COUNT(*) 
                     FROM   wettkampf w
                     WHERE  w.sportart='Schwimmen' AND w.geschlecht = 'w'
                    )
ORDER BY name, vorname
;

SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s JOIN teilnahme t USING (s_id)
                  JOIN wettkampf w USING (w_id)
WHERE  t.platz=1 AND w.sportart='Schwimmen' AND w.geschlecht = 'w'
       AND
       NOT EXISTS 
       (SELECT w2.w_id
        FROM   wettkampf w2
        WHERE  w2.sportart='Schwimmen' AND w2.geschlecht = 'w'
        EXCEPT
        SELECT t2.w_id 
        FROM   teilnahme t2
        WHERE  t2.s_id = s.s_id AND t2.platz=1 
       )
;

/* alle, bei denen sie mitgemacht hat  (war aber nicht gefragt)*/
SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s JOIN teilnahme t USING (s_id)
                  JOIN wettkampf w USING (w_id)
WHERE  t.platz=1 AND w.sportart='Schwimmen' AND w.geschlecht = 'w'
       AND
       NOT EXISTS 
       (SELECT * 
        FROM   teilnahme t2 JOIN wettkampf w2 USING (w_id)
        WHERE  s.s_id=t2.s_id AND
               w2.sportart='Schwimmen' AND w.geschlecht = 'w' AND t2.platz<>1
       )
;


/*
** Aufgabe g
**
** Welche Sportler/-innen haben die meisten Gold-Medaillien gewonnen?
*/

-- medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, COUNT(*) AS medaillenanzahl
FROM     sportler s, teilnahme t
WHERE    s.s_id=t.s_id AND t.platz=1
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
HAVING   COUNT(*) >=ALL (SELECT   COUNT(*)
                         FROM     teilnahme t
                         WHERE    t.platz=1
                         GROUP BY t.s_id
                        )
ORDER BY name, vorname
;

-- medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, COUNT(*) AS medaillenanzahl
FROM     sportler s, teilnahme t
WHERE    s.s_id=t.s_id AND t.platz=1
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
HAVING   COUNT(*) = (SELECT MAX(anzahl) AS MAX
                     FROM
                       (SELECT   COUNT(*)
                        FROM     teilnahme t
                        WHERE    t.platz=1
                        GROUP BY t.s_id
                       ) AS aux1(anzahl)
                     )
ORDER BY name, vorname
;

SELECT s_id, name, vorname, geburtstag, land, medaillenanzahl
FROM   (
        SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land,
                 COUNT(*) AS medaillenanzahl,
                 MAX(COUNT(*)) OVER () AS max
        FROM     sportler s, teilnahme t
        WHERE    s.s_id=t.s_id AND t.platz=1
        GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
        ORDER BY name, vorname
       ) AS dummy
WHERE  medaillenanzahl = max
;

/* Schmutziger Trick, funktioniert nur, wenn es genau einen Teilnehmer
   gibt, der die meisten Medaillen gewonnen hat.
   FETCH ... ONLY ist ein Hilfsmittel,
   um die Anzahl der Ausgaben (z.B. fuer Web-Seiten) zu begrenzen.
*/
SELECT   s.s_id, s.name, s.vorname, s.geburtstag, s.land, COUNT(*) AS medaillenanzahl
FROM     sportler s, teilnahme t
WHERE    s.s_id=t.s_id AND t.platz=1
GROUP BY s.s_id, s.name, s.vorname, s.geburtstag, s.land
ORDER BY medaillenanzahl DESC, name, vorname
FETCH FIRST 1 ROW ONLY
;

