-- 1. Nazwy skladnikow wejsciowych dla czynnosci "totalne mieszanie".

-- wersja A
SELECT s.nazwa
FROM skladniki s, czynnosci c, przepisy p
WHERE s.id = p.id_skl
  AND c.id = p.id_cz
  AND p.tryb = 'IN'
  AND c.nazwa = 'totalne mieszanie';

-- wersja B
SELECT s.nazwa
FROM skladniki s JOIN przepisy p  ON (s.id = p.id_skl)
                 JOIN czynnosci c ON (c.id = p.id_cz)
WHERE p.tryb = 'IN'
  AND c.nazwa = 'totalne mieszanie';

-- 2. Nazwy skladnikow podstawowych, ktore da sie wytworzyc jakas czynnoscia.

-- wersja A
SELECT DISTINCT s.nazwa
FROM skladniki s, przepisy p
WHERE s.id = p.id_skl
  AND s.klasyf = 'podstawowy'
  AND p.tryb = 'OUT';

-- wersja B
SELECT DISTINCT s.nazwa
FROM skladniki s JOIN przepisy p ON (s.id = p.id_skl)
WHERE s.klasyf = 'podstawowy'
  AND p.tryb = 'OUT';


-- 3. Nazwy skladnikow posrednich, ktorych nie da sie wytworzyc zadna 
--    czynnoscia, lub ktorych nie konsumuje zadna czynnosc. 

-- wersja A 
SELECT s.nazwa
FROM skladniki s LEFT JOIN przepisy p ON (p.id_skl = s.id)
WHERE s.klasyf = 'posredni'
GROUP BY s.nazwa
HAVING COUNT (DISTINCT p.tryb) != 2;

-- wersja B 
SELECT s.nazwa
FROM skladniki s 
WHERE s.klasyf = 'posredni'
  AND (NOT EXISTS (SELECT 1 
                  FROM przepisy p
                  WHERE p.id_skl = s.id AND p.tryb = 'IN')
       OR
       NOT EXISTS (SELECT 1 
                  FROM przepisy p
                  WHERE p.id_skl = s.id AND p.tryb = 'OUT')
       );

-- 4. Nazwy czynnosci majace najwieksza liczbe skladnikow wejsciowych.

SELECT c.nazwa
FROM przepisy p, czynnosci c
WHERE p.id_cz = c.id
  AND p.tryb = 'IN'
GROUP BY c.id, c.nazwa   
HAVING COUNT(*) >= ALL 
   (SELECT COUNT(*) 
    FROM przepisy p1
    WHERE p1.tryb = 'IN'
    GROUP BY p1.id_cz);


-- 5. Dla kazdej nazwy czynnosci liczbe skladnikow wejsciowych w rozbiciu na
-- klasyf(ikacje).

SELECT c.nazwa, s.klasyf, COUNT(*)
FROM czynnosci c, przepisy p, skladniki s
WHERE c.id = p.id_cz
  AND s.id = p.id_skl
  AND p.tryb = 'IN'
GROUP BY c.id, c.nazwa, s.klasyf;
    
-- 6. Nazwy skladnikow, ktore wystepuja w kazdej czynnosci (jako IN lub OUT).

-- wersja A
SELECT s.nazwa
  FROM skladniki s, przepisy p
  WHERE s.id = p.id_skl
  GROUP BY s.id, s.nazwa
  HAVING COUNT(DISTINCT p.id_cz) = (SELECT COUNT(*) FROM czynnosci c);
  
--wersja B
SELECT s.nazwa
  FROM skladniki s
  WHERE NOT EXISTS (
     SELECT 1 
     FROM czynnosci c
     WHERE NOT EXISTS (
        SELECT 1
        FROM przepisy p
        WHERE p.id_skl = s.id
          AND p.id_cz = c.id
     )
  );

-- 7. Nazwy skladnikow bazowych potrzebnych do ugotowania "zupy z hanoi"
-- a) zakladajac, ze zagniezdzenie tego przepisu wynosi 2, tj. czynnosci
--   prowadzace do wytworzenia "zupy z hanoi" korzystaja ze skladnikow
--   wytworzonych przez inne czynnosci nizszego szczebla. [ Wypisac nalezy
--   takze skladniki czynnosci drugiego poziomu. ]

SELECT DISTINCT wy.nazwa
  FROM skladniki zh, przepisy p1_out, przepisy p1_in,
       skladniki wy, przepisy p2_out, przepisy p2_in
  WHERE zh.nazwa = 'zupy z Hanoi'
    AND p1_out.id_skl = zh.id        AND p1_out.tryb = 'OUT'
    AND p1_out.id_cz  = p1_in.id_cz  AND p1_in.tryb = 'IN'
    AND p2_out.id_skl = p1_in.id_skl AND p2_out.tryb = 'OUT'
    AND p2_out.id_cz  = p2_in.id_cz  AND p2_in.tryb = 'IN'
    AND wy.id = p2_in.id_skl;

-- b) (NIEOBOWIAZKOWE) bez zadnego zalozenia o maksymalnej glebokosci 
--   drzewa przepisu.

WITH RECURSIVE bazowe (id) AS
  (SELECT s.id
   FROM skladniki s
   WHERE s.nazwa = 'zupy z Hanoi'
   UNION
   SELECT p_in.id_skl
   FROM bazowe b, przepisy p_out, przepisy p_in
   WHERE b.id = p_out.id_skl      AND p_out.tryb = 'OUT'
     AND p_out.id_cz = p_in.id_cz AND p_in.tryb  = 'IN')  
SELECT b.id, s.nazwa
FROM bazowe b, skladniki s
WHERE b.id = s.id;

-- 8. Jednostke miary, ktora wystepuje w najwiekszej liczbie !czynnosci!.

SELECT s.jm
FROM skladniki s, przepisy p
WHERE s.id = p.id_skl
GROUP BY s.jm
HAVING COUNT(DISTINCT p.id_cz) >= ALL
    (SELECT COUNT(DISTINCT p1.id_cz)
     FROM skladniki s1, przepisy p1
     WHERE s1.id = p1.id_skl
     GROUP BY s1.jm);

-- 9. Pary nazw czynnosci, ktore wytwarzaja ten sam skladnik wynikowy, ale 
--    roznia sie co najmniej jednym skladnikiem wejsciowym.

SELECT DISTINCT c1.nazwa, c2.nazwa
FROM czynnosci c1, przepisy p1_out, przepisy p1_in,
     czynnosci c2, przepisy p2_out, przepisy p2_in
WHERE c1.id = p1_out.id_cz AND p1_out.tryb = 'OUT'
  AND c1.id = p1_in.id_cz  AND p1_in.tryb  = 'IN'
  AND c2.id = p2_out.id_cz AND p2_out.tryb = 'OUT'
  AND c2.id = p2_in.id_cz  AND p2_in.tryb  = 'IN'
  AND p1_out.id_skl = p2_out.id_skl
  AND p1_in.id_skl != p2_in.id_skl
  AND c1.id < c2.id;
  
-- 10. Nazwy czynnosci korzystajaych wylacznie ze skladnikow mierzonych w kg,
--     ktore powoduja powstanie wyniku o wiekszej masie, niz laczna masa 
--     skladnikow wejsciowych.

SELECT c.nazwa
FROM czynnosci c
WHERE NOT EXISTS (SELECT 1 FROM przepisy p, skladniki s
                  WHERE p.id_cz = c.id 
                    AND s.id = p.id_skl
                    AND s.jm != 'kg')
  AND (SELECT SUM(p_in.il_skl) 
       FROM przepisy p_in
       WHERE p_in.id_cz = c.id
         AND p_in.tryb = 'IN')
      <
      (SELECT SUM(p_out.il_skl) 
       FROM przepisy p_out
       WHERE p_out.id_cz = c.id
         AND p_out.tryb = 'OUT');