-- Kolokwium SQL — Agenci (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. Długie odpowiedzi z października. Wypisać odpowiedzi wygenerowane w październiku 2026 roku, których długość wynosi co najmniej 1000 tokenów. Uwzględnić także odpowiedzi bez oceny. Kolumny: id_odpowiedzi, data_wygenerowania, liczba_tokenow, ocena. Sortowanie: data_wygenerowania, id_odpowiedzi — rosnąco.' SELECT id_odpowiedzi, data_wygenerowania, liczba_tokenow, ocena FROM odpowiedz WHERE data_wygenerowania >= DATE '2026-10-01' AND data_wygenerowania < DATE '2026-11-01' AND liczba_tokenow >= 1000 ORDER BY data_wygenerowania, id_odpowiedzi; \echo 'Zadanie 2. Autorzy plików SQL. Wypisać artefakty o formacie sql wraz z identyfikatorem i nazwą agenta, który je wytworzył, oraz rozmiarem w bajtach. Autorem artefaktu jest autor odpowiedzi, w której artefakt powstał. Każdy artefakt wypisać raz. Kolumny: id_artefaktu, nazwa_artefaktu, id_agenta, nazwa_agenta, rozmiar_bajtow. Sortowanie: id_artefaktu — rosnąco.' SELECT f.id_artefaktu, f.nazwa AS nazwa_artefaktu, a.id_agenta, a.nazwa AS nazwa_agenta, f.rozmiar_bajtow FROM artefakt f JOIN odpowiedz o ON o.id_odpowiedzi = f.id_odpowiedzi JOIN agent a ON a.id_agenta = o.id_agenta WHERE f.format = 'sql' ORDER BY f.id_artefaktu; \echo 'Zadanie 3. Wspólny koordynator. Wypisać wszystkie pary różnych agentów mających tego samego bezpośredniego koordynatora. Agentów bez koordynatora pominąć. Każdą parę wypisać tylko raz: pierwszy agent ma mieć mniejszy identyfikator. Równe nazwy nie oznaczają tego samego agenta. Kolumny: id_koordynatora, id_pierwszego, nazwa_pierwszego, id_drugiego, nazwa_drugiego. Sortowanie: id_koordynatora, id_pierwszego, id_drugiego — rosnąco.' SELECT a.id_koordynatora, a.id_agenta AS id_pierwszego, a.nazwa AS nazwa_pierwszego, b.id_agenta AS id_drugiego, b.nazwa AS nazwa_drugiego FROM agent a JOIN agent b ON b.id_koordynatora = a.id_koordynatora AND a.id_agenta < b.id_agenta ORDER BY a.id_koordynatora, a.id_agenta, b.id_agenta; \echo 'Zadanie 4. Liczba promptów w konwersacjach. Dla każdej konwersacji podać liczbę jej promptów. Uwzględnić również konwersacje bez promptów, podając dla nich 0. Liczyć wszystkie prompty, niezależnie od tego, czy otrzymały odpowiedź. Kolumny: id_konwersacji, tytul, liczba_promptow. Sortowanie: id_konwersacji — rosnąco.' SELECT k.id_konwersacji, k.tytul, COUNT(p.id_promptu) AS liczba_promptow FROM konwersacja k LEFT JOIN prompt p ON p.id_konwersacji = k.id_konwersacji GROUP BY k.id_konwersacji, k.tytul ORDER BY k.id_konwersacji; \echo 'Zadanie 5. Dobrze oceniani agenci. Wypisać agentów, którzy mają co najmniej trzy ocenione odpowiedzi, a średnia ocen ich odpowiedzi wynosi co najmniej 4. Odpowiedzi bez oceny pominąć przy obliczaniu liczby ocen i średniej. Każda oceniona odpowiedź ma taką samą wagę. Kolumny: id_agenta, nazwa, liczba_ocen, srednia_ocen. Sortowanie: id_agenta — rosnąco.' SELECT a.id_agenta, a.nazwa, COUNT(o.ocena) AS liczba_ocen, ROUND(AVG(o.ocena), 2) AS srednia_ocen FROM agent a JOIN odpowiedz o ON o.id_agenta = a.id_agenta GROUP BY a.id_agenta, a.nazwa HAVING COUNT(o.ocena) >= 3 AND AVG(o.ocena) >= 4 ORDER BY a.id_agenta; \echo 'Zadanie 6. Agenci bez artefaktów. Wypisać agentów, którzy nie wytworzyli żadnego artefaktu. Uwzględnić zarówno agentów bez odpowiedzi, jak i tych, których żadna odpowiedź nie wytworzyła artefaktu. Kolumny: id_agenta, nazwa. Sortowanie: id_agenta — rosnąco.' SELECT a.id_agenta, a.nazwa FROM agent a WHERE NOT EXISTS ( SELECT 1 FROM odpowiedz o JOIN artefakt f ON f.id_odpowiedzi = o.id_odpowiedzi WHERE o.id_agenta = a.id_agenta ) ORDER BY a.id_agenta; \echo 'Zadanie 7. Odpowiedzi dłuższe od średniej. Wypisać odpowiedzi o liczbie tokenów większej od średniej liczby tokenów wszystkich odpowiedzi w tej samej konwersacji. Do średniej wliczyć także badaną odpowiedź oraz odpowiedzi bez oceny. Uwzględnić odpowiedzi na wszystkie prompty tej konwersacji. Kolumny: id_odpowiedzi, id_konwersacji, liczba_tokenow. Sortowanie: id_odpowiedzi — rosnąco.' SELECT o.id_odpowiedzi, p.id_konwersacji, o.liczba_tokenow FROM odpowiedz o JOIN prompt p ON p.id_promptu = o.id_promptu WHERE o.liczba_tokenow > ( SELECT AVG(o2.liczba_tokenow) FROM odpowiedz o2 JOIN prompt p2 ON p2.id_promptu = o2.id_promptu WHERE p2.id_konwersacji = p.id_konwersacji ) ORDER BY o.id_odpowiedzi; \echo 'Zadanie 8. Dorobek agentów. Dla każdego agenta podać liczbę jego odpowiedzi, liczbę wytworzonych przez niego artefaktów oraz sumę rozmiarów tych artefaktów w bajtach. Liczyć wszystkie odpowiedzi, również bez artefaktów i bez ocen. Każdą odpowiedź oraz każdy artefakt liczyć tylko raz. Uwzględnić wszystkich agentów; brak odpowiedzi lub artefaktów oznacza odpowiednie liczniki i sumę równe 0. Kolumny: id_agenta, nazwa, liczba_odpowiedzi, liczba_artefaktow, suma_bajtow. Sortowanie: id_agenta — rosnąco.' SELECT a.id_agenta, a.nazwa, COUNT(DISTINCT o.id_odpowiedzi) AS liczba_odpowiedzi, COUNT(f.id_artefaktu) AS liczba_artefaktow, COALESCE(SUM(f.rozmiar_bajtow), 0) AS suma_bajtow FROM agent a LEFT JOIN odpowiedz o ON o.id_agenta = a.id_agenta LEFT JOIN artefakt f ON f.id_odpowiedzi = o.id_odpowiedzi GROUP BY a.id_agenta, a.nazwa ORDER BY a.id_agenta; \echo 'Zadanie 9. Najaktywniejsi w konwersacjach. Dla każdej konwersacji, w której istnieje co najmniej jedna odpowiedź, wypisać agentów będących autorami największej liczby odpowiedzi w tej konwersacji. Uwzględnić wszystkich remisujących. Liczyć odpowiedzi, a nie różne prompty: wielokrotne odpowiedzi agenta na ten sam prompt liczą się oddzielnie. Odpowiedzi bez oceny także uwzględnić. Kolumny: id_konwersacji, tytul, id_agenta, nazwa_agenta, liczba_odpowiedzi. Sortowanie: id_konwersacji, id_agenta — rosnąco.' SELECT k.id_konwersacji, k.tytul, a.id_agenta, a.nazwa AS nazwa_agenta, COUNT(*) AS liczba_odpowiedzi FROM konwersacja k JOIN prompt p ON p.id_konwersacji = k.id_konwersacji JOIN odpowiedz o ON o.id_promptu = p.id_promptu JOIN agent a ON a.id_agenta = o.id_agenta GROUP BY k.id_konwersacji, k.tytul, a.id_agenta, a.nazwa HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM odpowiedz o2 JOIN prompt p2 ON p2.id_promptu = o2.id_promptu WHERE p2.id_konwersacji = k.id_konwersacji GROUP BY o2.id_agenta ) ORDER BY k.id_konwersacji, a.id_agenta; \echo 'Zadanie 10. Odpowiedź na każdy prompt. Wypisać agentów, którzy udzielili co najmniej jednej odpowiedzi na każdy prompt konwersacji o identyfikatorze 2. Ocena odpowiedzi nie ma znaczenia. Wielokrotne odpowiedzi na jeden prompt nie zastępują odpowiedzi na inny. Jeżeli konwersacja 2 nie ma żadnego promptu, warunek spełniają wszyscy agenci; dotyczy to także sytuacji, gdy taka konwersacja nie istnieje. Kolumny: id_agenta, nazwa. Sortowanie: id_agenta — rosnąco.' SELECT a.id_agenta, a.nazwa FROM agent a WHERE NOT EXISTS ( SELECT 1 FROM prompt p WHERE p.id_konwersacji = 2 AND NOT EXISTS ( SELECT 1 FROM odpowiedz o WHERE o.id_promptu = p.id_promptu AND o.id_agenta = a.id_agenta ) ) ORDER BY a.id_agenta; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Podwładni koordynatora. Korzystając z WITH RECURSIVE, wypisać wszystkich bezpośrednich i pośrednich podwładnych agenta o identyfikatorze 1. Nie wypisywać samego agenta 1. Dla każdego podwładnego podać odległość od agenta 1 w liczbie krawędzi hierarchii: bezpośredni podwładny ma poziom 1. Agentów należących do innych drzew pominąć. Hierarchia nie zawiera cykli. Kolumny: id_agenta, nazwa, poziom. Sortowanie: poziom, id_agenta — rosnąco.' WITH RECURSIVE podwladni (id_agenta, poziom) AS ( SELECT id_agenta, 1 FROM agent WHERE id_koordynatora = 1 UNION ALL SELECT a.id_agenta, p.poziom + 1 FROM agent a JOIN podwladni p ON a.id_koordynatora = p.id_agenta ) SELECT a.id_agenta, a.nazwa, p.poziom FROM podwladni p JOIN agent a ON a.id_agenta = p.id_agenta ORDER BY p.poziom, a.id_agenta; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Wszystkie wymagane artefakty. Korzystając z WITH RECURSIVE, wypisać wszystkie artefakty wymagane bezpośrednio lub pośrednio przez artefakt o identyfikatorze 31. Każdy wymagany artefakt wypisać dokładnie raz, nawet gdy prowadzi do niego kilka ścieżek. Nie wypisywać artefaktu 31. Wiersz zaleznosc(A, B) oznacza, że A wymaga B. Graf nie zawiera cykli; artefakt bez zależności daje pusty wynik. Kolumny: id_artefaktu, nazwa, format. Sortowanie: id_artefaktu — rosnąco.' WITH RECURSIVE wymagane (id_artefaktu) AS ( SELECT id_wymaganego FROM zaleznosc WHERE id_artefaktu = 31 UNION SELECT z.id_wymaganego FROM zaleznosc z JOIN wymagane w ON z.id_artefaktu = w.id_artefaktu ) SELECT f.id_artefaktu, f.nazwa, f.format FROM wymagane w JOIN artefakt f ON f.id_artefaktu = w.id_artefaktu ORDER BY f.id_artefaktu;