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

-- 1. Ile krajów kupuje towar, który produkuje.

SELECT count(distinct sprzedaje.komu) ile
FROM sprzedaje, produkuje
WHERE sprzedaje.komu = produkuje.kto
      AND sprzedaje.co = produkuje.co
      AND sprzedaje.ile > 0
      AND produkuje.ile > 0;

-- 2. Wypisz wszystkie pary krajów, między którymi zachodzi przepływ jakiegoś towaru w obie strony. Nie wypisuj dwukrotnie tej samej pary w różnych kolejnosciach.

SELECT DISTINCT s.kto, s.komu
FROM sprzedaje s, sprzedaje p
WHERE s.kto = p.komu AND s.komu = p.kto
      AND s.co = p.co AND s.ile > 0
      AND p.ile > 0 AND s.kto < s.komu;

-- 3. Wypisz producentów niezerowej ilości węgla w kolejności malejącego łącznego eksportu węgla. 

SELECT produkuje.kto, NVL(sum(sprzedaje.ile),0) razem
FROM produkuje LEFT JOIN sprzedaje
     ON produkuje.kto = sprzedaje.kto
     AND sprzedaje.co = 'wegiel'
WHERE produkuje.co = 'wegiel' AND produkuje.ile > 0
GROUP BY produkuje.kto
ORDER BY razem DESC;

-- 4. Wypisz wszystkie kraje, które sprzedają jakiegoś towaru więcej niż go produkują.

SELECT DISTINCT sprzedaje.kto
FROM sprzedaje LEFT JOIN produkuje
     ON sprzedaje.kto = produkuje.kto
     AND sprzedaje.co = produkuje.co
GROUP BY sprzedaje.kto, sprzedaje.co, produkuje.ile
HAVING sum(sprzedaje.ile) > NVL(produkuje.ile,0);

-- 5. Dla każdego towaru wypisz jego największych importerow. W przypadku remisow wypisz wszystkich.

WITH importer AS (
     SELECT komu AS kraj, co, sum(ile) AS ile
     FROM sprzedaje
     GROUP BY komu, co
)
SELECT co, kraj
FROM importer i
WHERE i.ile = (SELECT MAX(ile)
      	      FROM importer j
	      WHERE j.co=i.co)
ORDER BY co;


-- 6. Dla każdego eksportera A i eksportowanego przez niego towaru T wypisz kraj B, który jest następny na liście głównych eksporterów towaru T. Jeśli takiego nie ma, wypisz null (puste pole). Jeśli jest kilku ex-aequo, wypisz wszystkich. 

WITH eksport AS (
  SELECT kto, co, SUM(ile) AS ile
  FROM sprzedaje
  GROUP BY kto, co
)
SELECT e.kto, e.co, f.kto
FROM eksport e LEFT JOIN eksport f
     ON e.co = f.co AND e.ile > f.ile
     AND e.kto <> f.kto
     AND NOT EXISTS
		(SELECT *
		FROM eksport g
		WHERE e.co = g.co
		      AND e.ile > g.ile
		      AND g.ile > f.ile)
ORDER BY e.co, e.kto; 

-- 7. Posortuj kraje malejąco wg. konsumpcji węgla, przy czym za konsumpcję danego towaru w danym kraju uznajemy sumę produkcji i importu pomniejszoną o eksport.

WITH
eksporterzy AS (
  SELECT kto eksporter, sum(ile) eksport
  FROM sprzedaje
  WHERE co = 'wegiel'
  GROUP BY kto
),
importerzy AS (
  SELECT komu importer, sum(ile) import
  FROM sprzedaje
  WHERE co = 'wegiel'
  GROUP BY komu
),
producenci AS (
  SELECT kto producent, ile produkcja
  FROM produkuje
  WHERE co='wegiel'
)
SELECT nvl(importer, nvl(eksporter,producent)) kraj,
       nvl(produkcja,0) + NVL(import,0) - NVL(eksport,0) ile
FROM (eksporterzy
     FULL JOIN importerzy ON eksporter = importer)
     FULL JOIN producenci ON eksporter = producent
     	       		     OR importer = producent
ORDER BY nvl(produkcja, 0) + NVL(import,0) - NVL(eksport,0) DESC;

WITH kraje AS
(SELECT kto FROM produkuje UNION SELECT kto
	FROM sprzedaje UNION SELECT komu kto FROM sprzedaje)
SELECT kraje.kto kraj,
(SELECT NVL(SUM(ile), 0) ile
	FROM sprzedaje WHERE co = 'wegiel' AND kto = kraje.kto) eksport,
(SELECT NVL(SUM(ile), 0) ile
	FROM sprzedaje WHERE co = 'wegiel' AND komu = kraje.kto) import,
(SELECT NVL(SUM(ile), 0) ile
	FROM produkuje WHERE co = 'wegiel' AND kto = kraje.kto) produkcja
FROM kraje
ORDER BY produkcja - eksport + import;



