-- Kolokwium SQL — Lasy — równowaga ekosystemu (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. Populacja i najliczniejsze stanowisko. Dla każdego gatunku podać łączną liczbę osobników we wszystkich lasach oraz numer i nazwę lasu, w którym jest ich najwięcej. Remis rozstrzyga najmniejszy numer lasu. Dla gatunku bez wystąpień podać 0 oraz NULL w kolumnach lasu. Kolumny: nr, nazwa, razem, nr_lasu, las.' WITH stat AS ( SELECT w.*, SUM(ilosc) OVER (PARTITION BY nr_gatunku) AS razem, ROW_NUMBER() OVER (PARTITION BY nr_gatunku ORDER BY ilosc DESC, nr_lasu) AS miejsce FROM wystapienia w ) SELECT g.nr, g.nazwa, COALESCE(s.razem, 0) AS razem, l.nr AS nr_lasu, l.nazwa AS las FROM gatunki g LEFT JOIN stat s ON s.nr_gatunku = g.nr AND s.miejsce = 1 LEFT JOIN lasy l ON l.nr = s.nr_lasu ORDER BY g.nr; \echo 'Zadanie 2. Gatunki u podstawy sieci. Wypisać gatunki na dole sieci pokarmowej, czyli takie, które według tabeli pozeranie nie polują na żaden inny gatunek. Kolumny: nr, nazwa.' SELECT g.nr, g.nazwa FROM gatunki g WHERE NOT EXISTS (SELECT 1 FROM pozeranie p WHERE p.kto = g.nr) ORDER BY g.nr; \echo 'Zadanie 3. Drapieżnik lżejszy od ofiary. Wypisać pary drapieżnik–ofiara, w których przeciętny osobnik gatunku drapieżnika jest lżejszy od przeciętnego osobnika gatunku ofiary. Kolumny: nr_drapieznika, drapieznik, nr_ofiary, ofiara.' SELECT d.nr AS nr_drapieznika, d.nazwa AS drapieznik, o.nr AS nr_ofiary, o.nazwa AS ofiara FROM pozeranie p JOIN gatunki d ON d.nr = p.kto JOIN gatunki o ON o.nr = p.kogo WHERE d.wielkosc < o.wielkosc ORDER BY d.nr, o.nr; \echo 'Zadanie 4. Lasy bez naturalnego wroga. Wypisać pary las–gatunek, dla których gatunek występuje w lesie, ale nie występuje w nim żaden jego bezpośredni naturalny wróg. Kolumny: nr_lasu, las, nr_gatunku, gatunek.' SELECT l.nr AS nr_lasu, l.nazwa AS las, g.nr AS nr_gatunku, g.nazwa AS gatunek FROM wystapienia w JOIN lasy l ON l.nr = w.nr_lasu JOIN gatunki g ON g.nr = w.nr_gatunku WHERE NOT EXISTS ( SELECT 1 FROM pozeranie p JOIN wystapienia d ON d.nr_gatunku = p.kto WHERE p.kogo = g.nr AND d.nr_lasu = l.nr ) ORDER BY l.nr, g.nr; \echo 'Zadanie 5. Spotkania przy paśniku. Przy których paśnikach drapieżnik może zastać swoją ofiarę? Wypisać bez powtórzeń numery paśników, gdy w tym samym lesie występują oba gatunki, a karma w paśniku odpowiada ofierze. Drapieżnik nie musi jeść tej karmy. Kolumny: nr.' SELECT DISTINCT f.nr FROM pasniki f JOIN wystapienia wd ON wd.nr_lasu = f.nr_lasu JOIN gatunki d ON d.nr = wd.nr_gatunku JOIN pozeranie p ON p.kto = d.nr JOIN gatunki o ON o.nr = p.kogo AND o.karma = f.karma JOIN wystapienia wo ON wo.nr_lasu = f.nr_lasu AND wo.nr_gatunku = o.nr ORDER BY f.nr; \echo 'Zadanie 6. Masa zwierząt na kilometr kwadratowy. Dla lasów, w których szacowana masa wszystkich zwierząt przypadająca na km² przekracza 10 kg/km², podać numer, nazwę i tę wartość. Próg sprawdzić przed zaokrągleniem; wynik wyświetlić do dwóch miejsc po przecinku. Kolumny: nr, nazwa, kg_na_km2.' SELECT l.nr, l.nazwa, ROUND(SUM(w.ilosc * g.wielkosc) / l.wielkosc, 2) AS kg_na_km2 FROM lasy l JOIN wystapienia w ON w.nr_lasu = l.nr JOIN gatunki g ON g.nr = w.nr_gatunku GROUP BY l.nr, l.nazwa, l.wielkosc HAVING SUM(w.ilosc * g.wielkosc) > 10 * l.wielkosc ORDER BY l.nr; \echo 'Zadanie 7. Zmiana populacji po roku. W prostym modelu bez polowań połowa osobników początkowej populacji to samice. Dla każdej pary las–obecny gatunek przyjąć, że przy n osobnikach spodziewana liczba urodzeń to n × 0,5 × rozrodczosc, a zgonów n × smiertelnosc. Dla zmian o co najmniej 5% w górę lub w dół podać las, gatunek, początkową liczebność i przewidywaną liczebność po roku. Nie zaokrąglać wartości oczekiwanych. Kolumny: nr_lasu, las, nr_gatunku, gatunek, obecnie, po_roku.' WITH prognoza AS ( SELECT l.nr AS nr_lasu, l.nazwa AS las, g.nr AS nr_gatunku, g.nazwa AS gatunek, w.ilosc AS obecnie, w.ilosc * (1 + 0.5 * g.rozrodczosc - g.smiertelnosc) AS po_roku FROM wystapienia w JOIN lasy l ON l.nr = w.nr_lasu JOIN gatunki g ON g.nr = w.nr_gatunku ) SELECT * FROM prognoza WHERE ABS(po_roku - obecnie) >= 0.05 * obecnie ORDER BY nr_lasu, nr_gatunku; \echo 'Zadanie 8. Gatunki bez odpowiedniego paśnika. Wypisać lasy, w których występuje przynajmniej jeden gatunek o znanej karmie, dla którego w tym lesie nie ma paśnika z odpowiednią karmą. Kolumny: nr, nazwa.' SELECT DISTINCT l.nr, l.nazwa FROM wystapienia w JOIN lasy l ON l.nr = w.nr_lasu JOIN gatunki g ON g.nr = w.nr_gatunku WHERE g.karma IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM pasniki f WHERE f.nr_lasu = l.nr AND f.karma = g.karma ) ORDER BY l.nr; \echo 'Zadanie 9. Czy wystarczy karmy?. Na jeden dzień przyjąć rację karmy równą 2% masy osobnika i założyć, że każdy paśnik jest pełny. Dla każdej pary las–rodzaj karmy, dla której zapotrzebowanie przewyższa podaż, wypisać osobny wiersz: numer i nazwę lasu, rodzaj karmy i brakującą ilość w kg. Sumować tylko osobniki o znanej karmie; brak stosownego paśnika oznacza podaż zerową. Kolumny: nr, nazwa, karma, niedobor_kg.' WITH potrzeby AS ( SELECT w.nr_lasu, g.karma, SUM(0.02 * w.ilosc * g.wielkosc) AS kg FROM wystapienia w JOIN gatunki g ON g.nr = w.nr_gatunku WHERE g.karma IS NOT NULL GROUP BY w.nr_lasu, g.karma ), zapas AS ( SELECT nr_lasu, karma, SUM(wielkosc) AS kg FROM pasniki GROUP BY nr_lasu, karma ) SELECT l.nr, l.nazwa, p.karma, p.kg - COALESCE(z.kg, 0) AS niedobor_kg FROM potrzeby p JOIN lasy l ON l.nr = p.nr_lasu LEFT JOIN zapas z ON z.nr_lasu = p.nr_lasu AND z.karma = p.karma WHERE p.kg > COALESCE(z.kg, 0) ORDER BY l.nr, p.karma; \echo 'Zadanie 10. Łańcuchy z jedną relacją. Policzyć łańcuchy złożone z dokładnie dwóch gatunków (jedna relacja „zjada”), w których drapieżnik sam nie jest niczyją ofiarą, a ofiara nie poluje na żaden gatunek. Rozpatrywać pełną sieć, bez podziału na lasy. Kolumny: liczba_lancuchow.' SELECT COUNT(*) AS liczba_lancuchow FROM pozeranie p WHERE NOT EXISTS (SELECT 1 FROM pozeranie q WHERE q.kogo = p.kto) AND NOT EXISTS (SELECT 1 FROM pozeranie q WHERE q.kto = p.kogo); \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Od wilka do dalszych ofiar. Dla wilka (nr 4) wypisać gatunki osiągalne po jednej lub kilku relacjach „zjada”, bez podziału na lasy. Każdy gatunek podać raz wraz z najmniejszą liczbą relacji potrzebnych do dotarcia do niego. Wilka pominąć, także gdyby w danych powstał cykl. Kolumny: nr, nazwa, odleglosc.' WITH RECURSIVE szlak(nr, droga) AS ( SELECT g.nr, ARRAY[g.nr] FROM gatunki g WHERE g.nr = 4 UNION ALL SELECT p.kogo, s.droga || p.kogo FROM szlak s JOIN pozeranie p ON p.kto = s.nr WHERE NOT (p.kogo = ANY(s.droga)) ) SELECT g.nr, g.nazwa, MIN(cardinality(s.droga) - 1) AS odleglosc FROM szlak s JOIN gatunki g ON g.nr = s.nr WHERE g.nr <> 4 GROUP BY g.nr, g.nazwa ORDER BY odleglosc, g.nr; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Najdłuższe łańcuchy w każdym lesie. W każdym lesie znaleźć wszystkie najdłuższe łańcuchy „zjada” złożone wyłącznie z gatunków obecnych w tym lesie. Nie powtarzać gatunku na ścieżce. Wypisać las, tablicę numerów gatunków od drapieżnika do ostatniej ofiary oraz liczbę relacji. Gatunek bez połączeń tworzy łańcuch długości zero; las bez gatunków pominąć. Kolumny: nr, nazwa, droga, liczba_relacji.' WITH RECURSIVE lancuch(nr_lasu, ostatni, droga) AS ( SELECT nr_lasu, nr_gatunku, ARRAY[nr_gatunku] FROM wystapienia UNION ALL SELECT s.nr_lasu, p.kogo, s.droga || p.kogo FROM lancuch s JOIN pozeranie p ON p.kto = s.ostatni JOIN wystapienia w ON w.nr_lasu = s.nr_lasu AND w.nr_gatunku = p.kogo WHERE NOT (p.kogo = ANY(s.droga)) ), ranking AS ( SELECT *, MAX(cardinality(droga)) OVER (PARTITION BY nr_lasu) AS najwiecej FROM lancuch ) SELECT l.nr, l.nazwa, r.droga, cardinality(r.droga)-1 AS liczba_relacji FROM ranking r JOIN lasy l ON l.nr = r.nr_lasu WHERE cardinality(r.droga) = r.najwiecej ORDER BY l.nr, r.droga;