-- 1. Ilu jest producentow oleju z rzepaku?

SELECT COUNT(DISTINCT produkuje.kto) ilu 
FROM produkuje JOIN konsumuje on produkuje.kto = konsumuje.kto  
WHERE produkuje.co='olej' and konsumuje.co='rzepak';

-- 2. Wypisz wszystkich producentow, ktorzy nic nie konsumują.

SELECT DISTINCT produkuje.kto
FROM produkuje LEFT JOIN konsumuje ON produkuje.kto = konsumuje.kto
WHERE konsumuje.kto IS NULL;

(SELECT kto FROM produkuje)
MINUS
(SELECT kto FROM konsumuje);


-- 3. Posortuj zakłady malejąco wg. wielkości odpadów, tzn. różnicy między
--    łączną konsumpcją a łączną produkcją.

SELECT NVL(prod.kto, kons.kto) kto, NVL(kons.ile,0) - NVL(prod.ile,0) odpady
FROM
	(SELECT kto, NVL(SUM(konsumuje.ile),0) ile FROM konsumuje GROUP BY kto) kons
	FULL JOIN
     	(SELECT kto, NVL(SUM(produkuje.ile),0) ile FROM produkuje GROUP BY kto) prod
	ON prod.kto = kons.kto
ORDER BY NVL(kons.ile,0) - NVL(prod.ile,0);


-- 4. Wypisz zakłady wykorzystujące choć jeden surowiec, którego łączna
--    produkcja jest mniejsza niż łączne zapotrzebowanie na niego.

SELECT DISTINCT kto
FROM konsumuje 
WHERE
	(SELECT NVL(SUM(ile),0) FROM produkuje p where p.co = konsumuje.co) <
	(SELECT NVL(SUM(ile),0) FROM konsumuje k where k.co = konsumuje.co);

-- 5. Wypisz wszystkie pary zakładów, które korzystają z tych samych
--    surowców i kazdy cos produkuje.

SELECT pierwszy, drugi
FROM
(SELECT distinct(kto) pierwszy FROM produkuje) JOIN
(SELECT distinct(kto) drugi FROM produkuje) ON pierwszy < drugi
WHERE NOT EXISTS
   (((SELECT co FROM konsumuje WHERE kto=pierwszy) MINUS
     (SELECT co FROM konsumuje WHERE kto=drugi)) UNION ALL
    ((SELECT co FROM konsumuje WHERE kto=drugi) MINUS
     (SELECT co FROM konsumuje WHERE kto=pierwszy)));

-- 6. Wypisz wszystkie towary, które są surowcem (w pewnym zakładzie) dla
--    pewnego swojego surowca (w być moze innym zakładzie). 

WITH wymaga AS (
     SELECT produkuje.co co, konsumuje.co czego
     FROM produkuje JOIN konsumuje ON produkuje.kto=konsumuje.kto
)
SELECT DISTINCT a.co
FROM wymaga a, wymaga b
WHERE a.czego=b.co AND a.co=b.czego;


-- 7. Efektywność produkcji towaru mierzymy stosunkiem wyprodukowanej ilosci tego
--    towaru do łącznej ilości towarów konsumowanych. Wypisz wszystkie zakłady,
--    które w produkcji każdego swojego produktu są mniej efektywne, niż jakis inny
--    zakład (nie koniecznie ten sam dla wszystkich produktów).

WITH
efektywnosc AS (
	SELECT
		kto,
		co,
		ile/(SELECT SUM(ile) FROM konsumuje WHERE konsumuje.kto = produkuje.kto) ile
	FROM produkuje
)
SELECT DISTINCT kto
FROM produkuje
WHERE kto NOT IN
		(SELECT kto
		FROM efektywnosc a
		WHERE ile IS NULL OR
	              -- wpp. porownanie z nullem da falsz, i dobrze bo null to nieskonczonosc
		      ile >= ALL (SELECT b.ile FROM efektywnosc b WHERE b.co=a.co)
		);
