-- Rozwiazania zadan z klasowki 2017/18

-- 1. Jaki jest łączny miesięczny koszt utrzymania wszystkich mieszkańców zoo?

SELECT sum(koszt)
FROM zwierze;

-- 2. Wypisz zwierzęta, których koszt utrzymania nie jest pokryty przez datki na nie.

SELECT *
FROM zwierze
WHERE koszt > (SELECT NVL(SUM(ile),0) FROM datek WHERE komu=imie);

SELECT imie  
FROM zwierze LEFT JOIN datek ON imie = komu
GROUP BY imie, koszt
HAVING NVL(SUM(ile),0) < koszt;

-- 3. Dla kazdego gatunku wypisz srednia laczna kwote wplat na przedstawiciela;
--    posortuj malejaco wg. tej sredniej. 

SELECT gatunek, SUM(ile)/COUNT(DISTINCT imie) srednia
FROM zwierze LEFT JOIN datek ON imie=komu
GROUP BY gatunek
ORDER BY srednia DESC;

-- 4. Wypisz sponsorow, którzy miesięcznie wpłacają najwięcej.

WITH ranking AS (
     SELECT id, NVL(SUM(ile),0) suma
     FROM sponsor LEFT JOIN datek ON id=kto
     GROUP BY id
)
SELECT id
FROM ranking
WHERE suma = (SELECT MAX(suma) FROM ranking);

-- 5. Wypisz wszystkie kontrakty, które można by rozwiązać
--    przy zachowaniu pokrycia kosztów danego zwierzaka.

SELECT *
FROM datek 
WHERE
	(SELECT koszt FROM zwierze WHERE komu=imie)
		<=
	(SELECT SUM(ile) FROM datek inne
	WHERE datek.kto <> inne.kto AND datek.komu=inne.komu); 

-- 6. Dla każdego sponsora, dla ktorego istnieje zwierze
--    jego ulubionego gatunku nie otrzymujace żadnego datku,
--    wypisz liczbe takich zwierzat.

WITH opuszczone AS (
     SELECT imie, gatunek
     FROM zwierze LEFT JOIN datek on imie=komu
     WHERE komu IS NULL
)
SELECT id, COUNT(imie)
FROM sponsor LEFT JOIN opuszczone ON ulubiony=gatunek 
GROUP BY id
HAVING COUNT(imie) > 0;


-- 7. Wypisz pary osob (posortowane wg id), ktore sponsoruja zwierzeta dokladnie tych samych gatunkow 

WITH spon_gat AS (
     SELECT id, gatunek
     FROM sponsor
     	  LEFT JOIN datek ON id=kto
     	  LEFT JOIN zwierze ON komu=imie
)
SELECT sponsor1.id, sponsor2.id
FROM sponsor sponsor1, sponsor sponsor2
WHERE sponsor1.id < sponsor2.id
AND NOT EXISTS (
    (SELECT gatunek FROM spon_gat WHERE id=sponsor1.id)
    MINUS
    (SELECT gatunek FROM spon_gat WHERE id=sponsor2.id)
) AND NOT EXISTS (
    (SELECT gatunek FROM spon_gat WHERE id=sponsor1.id)
    MINUS
    (SELECT gatunek FROM spon_gat WHERE id=sponsor2.id)
);

