-- Rozwiazania zadan z klasowki 2018/19, wersja A


-- 1. Wypisz zwięrzęta, które urodziły się wcześniej niż któryś z ich opiekunów został zatrudniony.

SELECT DISTINCT zwrznum, gatunek
FROM zwierzeta NATURAL JOIN opieka NATURAL JOIN opiekunowie
WHERE urodzony < zatrudnienie
ORDER BY zwrznum ASC;


-- 2. Dla każdego z opiekunów wypisz ilość mięsa, które musi dostarczyć swoim podopiecznym. (W przypadku gdy zwierzę ma więcej niż jednego opiekuna zakładamy, że dzielą się obowiązkami po równo.)

WITH zoo AS (
  SELECT DISTINCT o.*, z.*
  FROM opiekunowie o
       LEFT JOIN opieka ON opieka.opknnum = o.opknnum
       LEFT JOIN zwierzeta z ON opieka.zwrznum = z.zwrznum 
) 
SELECT
  opknnum,
  nazwisko,
  SUM(miesozernosc * dziennespozycie/(
    SELECT count(DISTINCT opknnum)
    FROM opieka
    WHERE zwrznum = zoo.zwrznum
  )) ile_miesa
FROM zoo
GROUP BY opknnum, nazwisko
ORDER BY opknnum;


-- 3. Okazuje się że opieka nad całkowicie mięsożernymi zwierzętami jest niebiezpieczna. Napisz zapytanie wyliczające podwyżkę dla każdego opiekuna w wysokości tylu procent jego pensji, ile procent z jego podopiecznych jest całkowicie mięsożerne.

WITH zoo AS (
  SELECT DISTINCT o.*, z.*
  FROM opiekunowie o
       LEFT JOIN opieka ON opieka.opknnum = o.opknnum
       LEFT JOIN zwierzeta z ON opieka.zwrznum = z.zwrznum 
) 
SELECT
  opknnum,
  nazwisko,
  pensja / count(*) * sum(
    CASE
	WHEN miesozernosc = 1 THEN 1
ELSE 0
    END
  ) podwyzka
FROM zoo
GROUP BY opknnum, nazwisko, pensja
ORDER BY opknnum;

-- 4.Kowalski zginął w nieszczęśliwym wypadku. Zwierzęta, których był jedynym opiekunem, muszą mieć przypisanego nowego opiekuna. Aby uniknąć wypadków w przyszłości, kierownictwo zdecydowało się każdemu "osieroconemu" zwierzęciu przypisać najbardziej doświadczonego opiekuna. Napisz zapytanie, które dla każdego zwierzęcia X, które było pod wyłączną opieką Kowalskiego, wypisuje opiekuna najstarszego stażem spośród tych, którzy już zajmują jakimś zwierzęciem tego samego gatunku co X. Dla pozostałych zwierząt wypisz null (czyli puste miejsce).


WITH doswiadczeni AS (
  SELECT DISTINCT
    gatunek, opknnum, nazwisko,
    dense_rank() over (PARTITION BY gatunek
	 	      ORDER BY zatrudnienie) rank
  FROM zwierzeta NATURAL JOIN opieka NATURAL JOIN opiekunowie
  WHERE nazwisko <> 'Kowalski'
),
osierocone AS (
  SELECT DISTINCT zwrznum
  FROM opieka NATURAL JOIN opiekunowie
  GROUP BY zwrznum
  HAVING count(*) = 1 and min(nazwisko) = 'Kowalski'
)
SELECT z.zwrznum, z.gatunek, d.opknnum, d.nazwisko
FROM zwierzeta z
     LEFT JOIN osierocone o ON z.zwrznum = o.zwrznum
     LEFT JOIN doswiadczeni d ON z.gatunek = d.gatunek
     	       		AND rank = 1
			AND o.zwrznum IS NOT NULL;

WITH doswiadczeni AS (
  SELECT DISTINCT
    gatunek, opknnum, nazwisko,
    dense_rank() over (PARTITION BY gatunek
	 	      ORDER BY zatrudnienie) rank
  FROM zwierzeta NATURAL JOIN opieka NATURAL JOIN opiekunowie
  WHERE nazwisko <> 'Kowalski'
)
SELECT z.zwrznum, z.gatunek, d.opknnum, d.nazwisko
FROM zwierzeta z LEFT JOIN doswiadczeni d
     ON z.gatunek = d.gatunek AND rank = 1 AND zwrznum in (
       	SELECT zwrznum
       	FROM opieka NATURAL JOIN opiekunowie
       	GROUP BY zwrznum
       	HAVING count(DISTINCT opknum) = 1
	       AND min(nazwisko) = 'Kowalski'
     );

WITH doswiadczeni AS (
  SELECT DISTINCT
    gatunek, opknnum, nazwisko,
    dense_rank() over (PARTITION BY gatunek
	 	      ORDER BY zatrudnienie) rank
  FROM zwierzeta NATURAL JOIN opieka NATURAL JOIN opiekunowie
  WHERE nazwisko <> 'Kowalski'
)
SELECT z.zwrznum, z.gatunek, d.opknnum, d.nazwisko
FROM zwierzeta z LEFT JOIN doswiadczeni d
  ON z.gatunek = d.gatunek AND rank = 1
    AND 1 = (SELECT count(DISTINCT opknnum)
    	     FROM opieka WHERE zwrznum=z.zwrznum)
    AND 'Kowalski' in (SELECT nazwisko
	               FROM opieka NATURAL JOIN opiekunowie
	               WHERE zwrznum=z.zwrznum);

-- 5. Zbliża się okres godowy w ZOO i kierownictwo zastanawia się nad zatrudnieniem nowej położnej. Dla każdego gatunku wypisz 5 najcięższych samic spośród tych, które mają co najmniej 3 lata.

SELECT zwrznum, gatunek, waga FROM (
  SELECT zwierzeta.*,
  	 rank() over (PARTITION BY gatunek
       		      ORDER BY waga DESC, zwrznum) pozycja 
  FROM zwierzeta
  WHERE plec = 'Samica'
  	AND urodzony <= add_months(SYSDATE, -3*12)
)
WHERE pozycja <= 5
ORDER BY gatunek, pozycja;



-- 6. Odszkodowanie za śmierć Kowalskiego okazało się zbyt duże dla ZOO, trzeba będzie przyciąć koszty. Wypisz wszystkie zwierzęta w kolejności miesięcznych kosztów ich utrzymania. Załóż że pensja opiekunów rozkłada się równomiernie pomiędzy zwierzęta, kilogram mięsa kosztuje 5zł, natomiast kilogram roślin 1zł.

WITH opiekaIle AS (
  SELECT DISTINCT
    zwrznum,
    opknnum,
    (SELECT count(DISTINCT zwrznum)
     FROM opieka o
     WHERE o.opknnum = opieka.opknnum) ile
  FROM opieka
  ORDER BY zwrznum, opknnum
)
SELECT
  zwierzeta.*,
  (4 * miesozernosc + 1) * dziennespozycie * 30 + (
    SELECT NVL(sum(pensja/ile),0)
    FROM opiekaIle NATURAL JOIN opiekunowie
    WHERE zwrznum=zwierzeta.zwrznum
   ) koszt
FROM zwierzeta
ORDER BY koszt; 


-- 7. Niektórzy pracownicy mają swoich pupilków. Wypisz dla każdego pracownika zwierzę, którym się zajmuje najdłużej, sposród tych których jest jedynym opiekunem.

WITH jedyni AS (
  SELECT zwrznum, min(opknnum) opknnum
  FROM opieka
  GROUP BY zwrznum
  HAVING count(distinct opknnum) = 1
), zoo AS (
SELECT o.opknnum, o.nazwisko, o.zatrudnienie,
       z.zwrznum, z.gatunek, z.urodzony,
       greatest(zatrudnienie, urodzony) kiedy
FROM opiekunowie o LEFT JOIN jedyni j ON o.opknnum = j.opknnum
     		   LEFT JOIN zwierzeta z ON j.zwrznum = z.zwrznum
)
SELECT opknnum, nazwisko, zatrudnienie, zwrznum, gatunek, urodzony
FROM 
  (SELECT zoo.*, rank() over (PARTITION BY opknnum
      	   	             ORDER BY kiedy, zwrznum) rank
  FROM zoo)
WHERE rank <=1;
