-- 1. Dla każdego posiłku wypisz ile roznych produktów obejmuje.

SELECT posilek, COUNT(co) ile
FROM obejmuje
GROUP BY posilek;

-- 2. Posortuj posiłki względem malejącej sumarycznej liczby kalorii
--    (wypisz identyfikator posiłku i liczbę kalorii).

SELECT posilek, SUM(obejmuje.ile*dostarcza.ile) kalorie
FROM obejmuje LEFT JOIN dostarcza ON obejmuje.co=dostarcza.co
WHERE dostarcza.czego='energia'     
GROUP BY posilek
ORDER BY kalorie DESC;

-- 3. Wypisz (alfabetycznie) wszystkie pary posiłków,
--    które obejmują co najmniej 3 identyczne produkty.

SELECT A.posilek, B.posilek
FROM obejmuje A, obejmuje B
WHERE A.co=B.co AND A.posilek < B.posilek
GROUP BY A.posilek, B.posilek
HAVING COUNT (DISTINCT A.co) >= 3;

-- 4. Posortuj posiłki (malejaco) względem procentowego zaspokojenia
--    zapotrzebowania na białko u dzieci (wypisz identyfikator
--    posiłku i procent pokrytego zapotrzebowania)

SELECT posilek,
       (100*SUM(obejmuje.ile*dostarcza.ile)/
       (SELECT ile FROM potrzebuje WHERE kto='D' AND czego='bialko')) ile
FROM obejmuje, dostarcza       
WHERE obejmuje.co=dostarcza.co AND dostarcza.czego='bialko'
GROUP BY posilek
ORDER BY ile DESC; 

-- 5. Dla każdego składnika odżywczego wypisz posiłek o minimalnej i
--    maksymalnej jego zawartości. 

WITH zawartosc AS (
  SELECT posilek, czego, sum(obejmuje.ile*dostarcza.ile) ile
  FROM obejmuje LEFT JOIN dostarcza ON obejmuje.co=dostarcza.co
  GROUP BY posilek, czego
),
minmax AS (
  SELECT czego, MIN(ile) min, MAX(ile) max
  FROM zawartosc 
  GROUP BY czego
)
SELECT M.czego skladnik, minZaw.posilek, maxZaw.posilek
FROM minmax M JOIN zawartosc minZaw ON M.czego=minZaw.czego AND minZAw.ile=min
     	      JOIN zawartosc maxZaw ON M.czego=minZaw.czego AND maxZaw.ile=max;


-- 6. Wypisz posiłki, które nie zapewniają 20% zapotrzebowania na
--    choc jeden składnik odżywczy dla choc jednej kategorii osób.

WITH zawartosc AS (
  SELECT posilek, czego, sum(obejmuje.ile*dostarcza.ile) ile
  FROM obejmuje LEFT JOIN dostarcza ON obejmuje.co=dostarcza.co
  GROUP BY posilek, czego
)
SELECT DISTINCT posilek
FROM zawartosc JOIN  potrzebuje ON potrzebuje.czego=zawartosc.czego
WHERE zawartosc.ile < 0.2*potrzebuje.ile;


-- 7.Zbalansowanie posiłku mierzymy różnicą miedzy maksymalnym i
--   minimalnym pokryciem zapotrzebowania na różne składniki
--   odżywcze. Posortuj posiłki według zbalansowania dla dzieci.

WITH zawartosc AS (
  SELECT posilek, czego,
  	 (100*sum(obejmuje.ile*dostarcza.ile)/
	  (SELECT ile FROM potrzebuje
	  WHERE kto='D' AND potrzebuje.czego=dostarcza.czego)) ile
  FROM obejmuje LEFT JOIN dostarcza ON obejmuje.co=dostarcza.co
  GROUP BY posilek, czego
)
SELECT posilek
FROM zawartosc 
GROUP BY posilek
ORDER BY (MAX(ile)-MIN(ile)) ASC;
