-- Kolokwium SQL — Przepisy kulinarne (PostgreSQL, psql). -- Wzorcowe rozwiązania. -- Zadania 1–10: po 1 punkcie. Zadania 11–12 z rekurencji są NIEOBOWIĄZKOWE, po 1 punkcie dodatkowym. \encoding UTF8 \set ON_ERROR_STOP on \pset pager off \pset null '(NULL)' \echo 'Zadanie 1. Składniki sałatki. Wypisać bez powtórzeń nazwy składników wejściowych czynności o nazwie ''mieszanie salatki''. Kolumny: nazwa.' SELECT DISTINCT s.nazwa FROM czynnosci c JOIN przepisy p ON p.id_cz = c.id JOIN skladniki s ON s.id = p.id_skl WHERE c.nazwa = 'mieszanie salatki' AND p.tryb = 'IN' ORDER BY s.nazwa; \echo 'Zadanie 2. Lista zakupów. Wypisać nazwy składników klasy ''podstawowy'', które są wejściem co najmniej jednej czynności, lecz nie są wynikiem żadnej. Jest to lista surowców do kupienia. Kolumny: nazwa.' SELECT s.nazwa FROM skladniki s WHERE s.klasyf = 'podstawowy' AND EXISTS (SELECT 1 FROM przepisy p WHERE p.id_skl = s.id AND p.tryb = 'IN') AND NOT EXISTS (SELECT 1 FROM przepisy p WHERE p.id_skl = s.id AND p.tryb = 'OUT') ORDER BY s.nazwa; \echo 'Zadanie 3. Półprodukty bez przepisu lub odbiorcy. Dla każdego składnika klasy ''posredni'' podać nazwę, liczbę czynności wytwarzających go i liczbę czynności zużywających go. Nie pomijać zer. Raport ma pozwolić dostrzec przerwany przepis: składnik pośredni bez producenta lub bez odbiorcy. Kolumny: nazwa, producenci, odbiorcy.' SELECT s.nazwa, COUNT(p.id_cz) FILTER (WHERE p.tryb = 'OUT') AS producenci, COUNT(p.id_cz) FILTER (WHERE p.tryb = 'IN') AS odbiorcy FROM skladniki s LEFT JOIN przepisy p ON p.id_skl = s.id WHERE s.klasyf = 'posredni' GROUP BY s.id, s.nazwa ORDER BY s.nazwa; \echo 'Zadanie 4. Najwięcej składników wejściowych. Znaleźć czynności o największej liczbie różnych składników wejściowych. Wypisać identyfikator, nazwę i tę liczbę, uwzględnić wszystkie remisy. Czynność bez wejść ma wynik 0. Kolumny: id, nazwa, liczba_wejsc.' WITH ile AS ( SELECT c.id, c.nazwa, COUNT(p.id_skl) AS liczba_wejsc FROM czynnosci c LEFT JOIN przepisy p ON p.id_cz = c.id AND p.tryb = 'IN' GROUP BY c.id, c.nazwa ) SELECT * FROM ile WHERE liczba_wejsc = (SELECT MAX(liczba_wejsc) FROM ile) ORDER BY id; \echo 'Zadanie 5. Klasy składników każdej czynności. Dla każdej czynności i każdej klasy składników występującej w słowniku skladniki wypisać identyfikator i nazwę czynności, klasę oraz liczbę wejść tej klasy. Dla brakujących kombinacji wypisać zero. Kolumny: id, nazwa, klasyf, ile.' WITH klasy AS (SELECT DISTINCT klasyf FROM skladniki), wejscia AS ( SELECT p.id_cz, s.klasyf, COUNT(*) AS ile FROM przepisy p JOIN skladniki s ON s.id = p.id_skl WHERE p.tryb = 'IN' GROUP BY p.id_cz, s.klasyf ) SELECT c.id, c.nazwa, k.klasyf, COALESCE(w.ile, 0) AS ile FROM czynnosci c CROSS JOIN klasy k LEFT JOIN wejscia w ON w.id_cz = c.id AND w.klasyf = k.klasyf ORDER BY c.id, k.klasyf; \echo 'Zadanie 6. Wspólne składniki trzech zup. Wypisać składniki będące wejściem każdej czynności, która produkuje jedną z trzech zup: ''zupa dyniowa'', ''zupa pomidorowa'', ''zupa z makaronem''. Szukać tylko bezpośrednich wejść tych czynności. Jeżeli żadna z zup nie ma przepisu, wynik ma być pusty. Kolumny: nazwa.' WITH gotowanie AS ( SELECT DISTINCT p.id_cz FROM przepisy p JOIN skladniki z ON z.id = p.id_skl WHERE p.tryb = 'OUT' AND z.nazwa IN ('zupa dyniowa','zupa pomidorowa','zupa z makaronem') ) SELECT s.nazwa FROM skladniki s WHERE EXISTS (SELECT 1 FROM gotowanie) AND NOT EXISTS ( SELECT 1 FROM gotowanie g WHERE NOT EXISTS ( SELECT 1 FROM przepisy w WHERE w.id_cz = g.id_cz AND w.tryb = 'IN' AND w.id_skl = s.id ) ) ORDER BY s.nazwa; \echo 'Zadanie 7. Dwa kroki od zupy dyniowej. Wypisać bez powtórzeń nazwy składników klasy ''podstawowy'' potrzebnych do przygotowania produktu ''zupa dyniowa'' bezpośrednio albo za pośrednictwem jednego półproduktu. Uwzględnić wszystkie dostępne przepisy. Nie rozwijać zależności dalej niż o dwa kroki. Kolumny: nazwa.' WITH zaleznosc AS ( SELECT DISTINCT o.id_skl AS produkt, i.id_skl AS wejscie FROM przepisy o JOIN przepisy i ON i.id_cz = o.id_cz WHERE o.tryb = 'OUT' AND i.tryb = 'IN' ), pierwszy AS ( SELECT z.wejscie AS id FROM zaleznosc z JOIN skladniki s ON s.id = z.produkt WHERE s.nazwa = 'zupa dyniowa' ), dwa_poziomy AS ( SELECT id FROM pierwszy UNION SELECT z.wejscie FROM pierwszy p JOIN zaleznosc z ON z.produkt = p.id ) SELECT s.nazwa FROM dwa_poziomy d JOIN skladniki s ON s.id = d.id WHERE s.klasyf = 'podstawowy' ORDER BY s.nazwa; \echo 'Zadanie 8. Najczęściej używana jednostka. Wypisać jednostkę miary występującą w największej liczbie różnych czynności, obok liczbę tych czynności. Jednostka liczy się raz na czynność, niezależnie od tego, czy dotyczy wejść, wyjść, czy obu. Uwzględnić remisy i tylko jednostki faktycznie użyte w przepisach. Kolumny: jm, czynnosci.' WITH ile AS ( SELECT s.jm, COUNT(DISTINCT p.id_cz) AS czynnosci FROM przepisy p JOIN skladniki s ON s.id = p.id_skl GROUP BY s.jm ) SELECT * FROM ile WHERE czynnosci = (SELECT MAX(czynnosci) FROM ile) ORDER BY jm; \echo 'Zadanie 9. Przepisy różniące się jednym składnikiem. Znaleźć pary czynności o podobnej liście surowców: obie mają co najmniej jeden składnik wejściowy, a wejścia pierwszej są dokładnie wejściami drugiej powiększonymi o jeden składnik. Wypisać identyfikatory i nazwy obu czynności (bogatszą najpierw) oraz nazwę dodatkowego składnika. Nie porównywać ilości. Kolumny: id_bogatszej, bogatsza, id_ubozszej, ubozsza, dodatkowy.' WITH wej AS (SELECT id_cz, id_skl FROM przepisy WHERE tryb = 'IN'), ile AS (SELECT id_cz, COUNT(*) AS n FROM wej GROUP BY id_cz) SELECT a.id AS id_bogatszej, a.nazwa AS bogatsza, b.id AS id_ubozszej, b.nazwa AS ubozsza, s.nazwa AS dodatkowy FROM ile x JOIN ile y ON x.n = y.n + 1 JOIN czynnosci a ON a.id = x.id_cz JOIN czynnosci b ON b.id = y.id_cz JOIN wej w ON w.id_cz = a.id JOIN skladniki s ON s.id = w.id_skl WHERE NOT EXISTS (SELECT 1 FROM wej v WHERE v.id_cz = b.id AND v.id_skl = w.id_skl) AND NOT EXISTS ( SELECT id_skl FROM wej WHERE id_cz = b.id EXCEPT SELECT id_skl FROM wej WHERE id_cz = a.id ) ORDER BY a.id, b.id; \echo 'Zadanie 10. Ubytek masy. Znaleźć czynności, dla których wszystkie wejścia i wyjścia mierzy się w kg, a łączna masa produktów jest mniejsza od łącznej masy wejść. Wymagać co najmniej jednego wejścia i jednego wyjścia. Wypisać identyfikator, nazwę i ubytek masy w kg, od największego ubytku. Kolumny: id, nazwa, ubytek_kg.' SELECT c.id, c.nazwa, SUM(p.il_skl) FILTER (WHERE p.tryb = 'IN') - SUM(p.il_skl) FILTER (WHERE p.tryb = 'OUT') AS ubytek_kg FROM czynnosci c JOIN przepisy p ON p.id_cz = c.id JOIN skladniki s ON s.id = p.id_skl GROUP BY c.id, c.nazwa HAVING BOOL_AND(s.jm = 'kg') AND SUM(p.il_skl) FILTER (WHERE p.tryb = 'IN') > SUM(p.il_skl) FILTER (WHERE p.tryb = 'OUT') ORDER BY ubytek_kg DESC, c.id; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Zupa dyniowa od podstaw. Rozwiązać zadanie o składnikach produktu ''zupa dyniowa'' bez ograniczenia głębokości. Idąc od produktu do wejść, uwzględnić wszystkie możliwe przepisy. Każdy osiągalny składnik klasy ''podstawowy'' wypisać raz. Zapytanie musi się zakończyć także w razie cyklu. Kolumny: nazwa.' WITH RECURSIVE zaleznosc AS ( SELECT DISTINCT o.id_skl AS produkt, i.id_skl AS wejscie FROM przepisy o JOIN przepisy i ON i.id_cz = o.id_cz WHERE o.tryb = 'OUT' AND i.tryb = 'IN' ), potrzebne(id) AS ( SELECT id FROM skladniki WHERE nazwa = 'zupa dyniowa' UNION SELECT z.wejscie FROM potrzebne p JOIN zaleznosc z ON z.produkt = p.id ) SELECT s.nazwa FROM potrzebne p JOIN skladniki s ON s.id = p.id WHERE s.klasyf = 'podstawowy' AND s.nazwa <> 'zupa dyniowa' ORDER BY s.nazwa; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Co zależy od ziarna pszenicy?. Restauracja nie może kupić składnika ''ziarno pszenicy''. Idąc od wejść ku produktom, znaleźć składniki klasy ''koncowy'', których przynajmniej jeden łańcuch przygotowania korzysta z tego ziarna. Dla każdego podać nazwę i najmniejszą liczbę kroków od ziarna. Nie powtarzać składnika na jednej ścieżce; inne warianty przepisu mogą nadal być dostępne. Kolumny: nazwa, kroki.' WITH RECURSIVE zaleznosc AS ( SELECT DISTINCT o.id_skl AS produkt, i.id_skl AS wejscie FROM przepisy o JOIN przepisy i ON i.id_cz = o.id_cz WHERE o.tryb = 'OUT' AND i.tryb = 'IN' ), szlak(id, droga) AS ( SELECT id, ARRAY[id::integer] FROM skladniki WHERE nazwa = 'ziarno pszenicy' UNION ALL SELECT z.produkt, s.droga || z.produkt::integer FROM szlak s JOIN zaleznosc z ON z.wejscie = s.id WHERE NOT (z.produkt::integer = ANY(s.droga)) ) SELECT s.nazwa, MIN(cardinality(t.droga) - 1) AS kroki FROM szlak t JOIN skladniki s ON s.id = t.id WHERE s.klasyf = 'koncowy' GROUP BY s.id, s.nazwa ORDER BY kroki, s.nazwa;