-- Kolokwium SQL — Redakcja (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 artykuły październikowe. Wypisać artykuły opublikowane w październiku 2026 roku, które mają co najmniej 1000 słów. Kolumny: id_artykulu, tytul, data_publikacji, liczba_slow. Sortowanie: data_publikacji, id_artykulu.' -- Przedział dat obejmuje cały październik. Szkice mają datę NULL, więc nie przechodzą -- warunku WHERE. SELECT a.id_artykulu, a.tytul, a.data_publikacji, a.liczba_slow FROM artykul a WHERE a.data_publikacji >= DATE '2026-10-01' AND a.data_publikacji < DATE '2026-11-01' AND a.liczba_slow >= 1000 ORDER BY a.data_publikacji, a.id_artykulu; \echo 'Zadanie 2. Warszawscy wydawcy. Wypisać opublikowane artykuły wydane przez redakcje z Warszawy. Dla każdego artykułu podać autora oraz redakcję, która go opublikowała. Kolumny: id_artykulu, tytul, id_autora, imie, nazwisko, id_redakcji, nazwa_redakcji. Sortowanie: id_artykulu.' -- Łączymy redakcję wydającą artykuł, czyli artykul.id_redakcji. Redakcja zatrudniająca -- autora może być inna. SELECT a.id_artykulu, a.tytul, a.id_autora, d.imie, d.nazwisko, r.id_redakcji, r.nazwa AS nazwa_redakcji FROM artykul a JOIN dziennikarz d ON d.id_dziennikarza = a.id_autora JOIN redakcja r ON r.id_redakcji = a.id_redakcji WHERE a.data_publikacji IS NOT NULL AND r.miasto = 'Warszawa' ORDER BY a.id_artykulu; \echo 'Zadanie 3. Pary dziennikarzy z tej samej redakcji. Wypisać wszystkie pary różnych dziennikarzy zatrudnionych w tej samej redakcji. Każdą parę wypisać tylko raz: jako pierwszą osobę podać tę o mniejszym identyfikatorze. Kolumny: id_redakcji, nazwa_redakcji, id_pierwszego, imie_pierwszego, nazwisko_pierwszego, id_drugiego, imie_drugiego, nazwisko_drugiego. Sortowanie: id_redakcji, id_pierwszego, id_drugiego.' -- Samozłączenie porównuje dwie osoby. Nierówność identyfikatorów usuwa pary osoby z nią -- samą i odwrócone powtórzenia par. SELECT r.id_redakcji, r.nazwa AS nazwa_redakcji, d1.id_dziennikarza AS id_pierwszego, d1.imie AS imie_pierwszego, d1.nazwisko AS nazwisko_pierwszego, d2.id_dziennikarza AS id_drugiego, d2.imie AS imie_drugiego, d2.nazwisko AS nazwisko_drugiego FROM dziennikarz d1 JOIN dziennikarz d2 ON d2.id_redakcji = d1.id_redakcji AND d1.id_dziennikarza < d2.id_dziennikarza JOIN redakcja r ON r.id_redakcji = d1.id_redakcji ORDER BY r.id_redakcji, d1.id_dziennikarza, d2.id_dziennikarza; \echo 'Zadanie 4. Liczba publikacji redakcji. Dla każdej redakcji podać liczbę opublikowanych przez nią artykułów. Uwzględnić również redakcje bez opublikowanych artykułów; dla nich podać 0. Kolumny: id_redakcji, nazwa_redakcji, liczba_artykulow. Sortowanie: id_redakcji.' -- Warunek publikacji jest w ON, aby zachować redakcje bez publikacji. -- COUNT(a.id_artykulu) pomija NULL po niedopasowanym LEFT JOIN. SELECT r.id_redakcji, r.nazwa AS nazwa_redakcji, COUNT(a.id_artykulu) AS liczba_artykulow FROM redakcja r LEFT JOIN artykul a ON a.id_redakcji = r.id_redakcji AND a.data_publikacji IS NOT NULL GROUP BY r.id_redakcji, r.nazwa ORDER BY r.id_redakcji; \echo 'Zadanie 5. Wysoko ocenione artykuły. Znaleźć opublikowane artykuły, którym wystawiono co najmniej dwie oceny, a średnia tych ocen wynosi co najmniej 4. Recenzje bez wystawionej oceny pominąć przy obliczaniu liczby i średniej ocen. Kolumny: id_artykulu, tytul, liczba_ocen, srednia_ocen. Sortowanie: id_artykulu.' -- COUNT(ocena) i AVG(ocena) pomijają recenzje bez oceny. HAVING sprawdza niezaokrągloną -- średnią; zaokrąglenie dotyczy tylko prezentacji wyniku. SELECT a.id_artykulu, a.tytul, COUNT(r.ocena) AS liczba_ocen, ROUND(AVG(r.ocena), 2) AS srednia_ocen FROM artykul a JOIN recenzja r ON r.id_artykulu = a.id_artykulu WHERE a.data_publikacji IS NOT NULL GROUP BY a.id_artykulu, a.tytul HAVING COUNT(r.ocena) >= 2 AND AVG(r.ocena) >= 4 ORDER BY a.id_artykulu; \echo 'Zadanie 6. Dziennikarze bez publikacji. Wypisać dziennikarzy, którzy nie napisali żadnego opublikowanego artykułu. Uwzględnić zarówno osoby bez jakiegokolwiek artykułu, jak i autorów samych szkiców. Kolumny: id_dziennikarza, imie, nazwisko. Sortowanie: id_dziennikarza.' -- NOT EXISTS sprawdza brak publikacji, a nie brak dowolnego artykułu. Autor samych -- szkiców również spełnia warunek. SELECT d.id_dziennikarza, d.imie, d.nazwisko FROM dziennikarz d WHERE NOT EXISTS ( SELECT 1 FROM artykul a WHERE a.id_autora = d.id_dziennikarza AND a.data_publikacji IS NOT NULL ) ORDER BY d.id_dziennikarza; \echo 'Zadanie 7. Artykuły dłuższe od średniej redakcji. Znaleźć opublikowane artykuły, których liczba słów jest większa od średniej liczby słów wszystkich opublikowanych artykułów tej samej redakcji wydającej. Do średniej wliczyć także badany artykuł; szkice pominąć. Kolumny: id_artykulu, tytul, id_redakcji, liczba_slow. Sortowanie: id_artykulu.' -- Podzapytanie jest skorelowane z redakcją wydającą badany artykuł. Średnia uwzględnia -- także ten artykuł, ale nie uwzględnia szkiców. SELECT a.id_artykulu, a.tytul, a.id_redakcji, a.liczba_slow FROM artykul a WHERE a.data_publikacji IS NOT NULL AND a.liczba_slow > ( SELECT AVG(b.liczba_slow) FROM artykul b WHERE b.id_redakcji = a.id_redakcji AND b.data_publikacji IS NOT NULL ) ORDER BY a.id_artykulu; \echo 'Zadanie 8. Dorobek każdego dziennikarza. Dla każdego dziennikarza podać liczbę jego opublikowanych artykułów, liczbę ocen wystawionych tym artykułom oraz średnią wszystkich tych ocen. Chodzi o oceny otrzymane jako autor, a nie wystawione jako recenzent. Obliczyć średnią wszystkich ocen, nie średnią średnich poszczególnych artykułów. Uwzględnić osoby bez publikacji i bez ocen: brak artykułów lub ocen oznacza odpowiedni licznik równy 0, a brak ocen — średnią NULL. Kolumny: id_dziennikarza, imie, nazwisko, liczba_artykulow, liczba_ocen, srednia_ocen. Sortowanie: id_dziennikarza.' -- Jeden artykuł może mieć wiele recenzji, dlatego liczymy DISTINCT identyfikatorów -- artykułów. Każda wystawiona ocena waży tak samo w AVG. LEFT JOIN zachowuje osoby bez -- publikacji; brak ocen daje AVG równe NULL. SELECT d.id_dziennikarza, d.imie, d.nazwisko, COUNT(DISTINCT a.id_artykulu) AS liczba_artykulow, COUNT(r.ocena) AS liczba_ocen, ROUND(AVG(r.ocena), 2) AS srednia_ocen FROM dziennikarz d LEFT JOIN artykul a ON a.id_autora = d.id_dziennikarza AND a.data_publikacji IS NOT NULL LEFT JOIN recenzja r ON r.id_artykulu = a.id_artykulu GROUP BY d.id_dziennikarza, d.imie, d.nazwisko ORDER BY d.id_dziennikarza; \echo 'Zadanie 9. Najczęściej publikowani autorzy. Dla każdej redakcji, która opublikowała co najmniej jeden artykuł, znaleźć autorów największej liczby opublikowanych przez nią artykułów. Wypisać wszystkich autorów remisujących na pierwszym miejscu. Miejsce zatrudnienia autora nie ma znaczenia. Kolumny: id_redakcji, nazwa_redakcji, id_autora, imie, nazwisko, liczba_artykulow. Sortowanie: id_redakcji, id_autora.' -- Porównujemy liczbę publikacji autora z liczebnościami grup wszystkich autorów tej -- samej redakcji. >= ALL zachowuje wszystkie remisy. Redakcje bez publikacji nie tworzą -- grup w zapytaniu zewnętrznym. SELECT a.id_redakcji, r.nazwa AS nazwa_redakcji, a.id_autora, d.imie, d.nazwisko, COUNT(*) AS liczba_artykulow FROM artykul a JOIN redakcja r ON r.id_redakcji = a.id_redakcji JOIN dziennikarz d ON d.id_dziennikarza = a.id_autora WHERE a.data_publikacji IS NOT NULL GROUP BY a.id_redakcji, r.nazwa, a.id_autora, d.imie, d.nazwisko HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM artykul b WHERE b.id_redakcji = a.id_redakcji AND b.data_publikacji IS NOT NULL GROUP BY b.id_autora ) ORDER BY a.id_redakcji, a.id_autora; \echo 'Zadanie 10. Wszystkie publikacje Latarni. Wypisać dziennikarzy, którzy wystawili ocenę każdemu opublikowanemu artykułowi redakcji o nazwie Latarnia. Recenzja z oceną NULL nie wystarcza; szkice pominąć. Jeżeli nie ma żadnego takiego opublikowanego artykułu (także gdy nie ma redakcji Latarnia), warunek spełniają wszyscy dziennikarze. Kolumny: id_dziennikarza, imie, nazwisko. Sortowanie: id_dziennikarza.' -- Nie może istnieć publikacja Latarni, której dana osoba nie oceniła. Recenzja z oceną -- NULL nie wystarcza. Gdy zbiór publikacji Latarni jest pusty, zewnętrzne NOT EXISTS -- jest prawdziwe dla każdego dziennikarza. SELECT d.id_dziennikarza, d.imie, d.nazwisko FROM dziennikarz d WHERE NOT EXISTS ( SELECT 1 FROM artykul a JOIN redakcja r ON r.id_redakcji = a.id_redakcji WHERE r.nazwa = 'Latarnia' AND a.data_publikacji IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM recenzja rec WHERE rec.id_artykulu = a.id_artykulu AND rec.id_recenzenta = d.id_dziennikarza AND rec.ocena IS NOT NULL ) ) ORDER BY d.id_dziennikarza; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Sieć recenzentów. Krok od A do B jest możliwy, jeśli B wystawił ocenę opublikowanemu artykułowi autorstwa A. Zaczynając od dziennikarza nr 1, znaleźć wszystkich innych dziennikarzy osiągalnych w jednym lub wielu krokach. Każdą osobę podać raz: identyfikator, imię i nazwisko, rosnąco według identyfikatora. Kolumny: id_dziennikarza, imie, nazwisko.' WITH RECURSIVE krawedzie(autor, recenzent) AS ( SELECT DISTINCT a.id_autora, r.id_recenzenta FROM artykul a JOIN recenzja r USING (id_artykulu) WHERE a.data_publikacji IS NOT NULL AND r.ocena IS NOT NULL ), osiagalni(id) AS ( SELECT recenzent FROM krawedzie WHERE autor = 1 UNION SELECT k.recenzent FROM osiagalni o JOIN krawedzie k ON k.autor = o.id ) SELECT d.id_dziennikarza, d.imie, d.nazwisko FROM osiagalni o JOIN dziennikarz d ON d.id_dziennikarza = o.id WHERE o.id <> 1 ORDER BY d.id_dziennikarza; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Wzajemna osiągalność w sieci recenzji. Krok od A do B jest możliwy, jeśli B wystawił ocenę opublikowanemu artykułowi autorstwa A. Znaleźć pary różnych osób, dla których istnieją ścieżki w obie strony, każda długości co najmniej jeden. Podać mniejszy i większy identyfikator; każdą parę wypisać raz, rosnąco według obu kolumn. Kolumny: mniejszy_id, wiekszy_id.' WITH RECURSIVE krawedzie(autor, recenzent) AS ( SELECT DISTINCT a.id_autora, r.id_recenzenta FROM artykul a JOIN recenzja r USING (id_artykulu) WHERE a.data_publikacji IS NOT NULL AND r.ocena IS NOT NULL ), drogi(poczatek, koniec) AS ( SELECT autor, recenzent FROM krawedzie UNION SELECT d.poczatek, k.recenzent FROM drogi d JOIN krawedzie k ON k.autor = d.koniec ) SELECT a.poczatek AS mniejszy_id, a.koniec AS wiekszy_id FROM drogi a JOIN drogi b ON b.poczatek = a.koniec AND b.koniec = a.poczatek WHERE a.poczatek < a.koniec ORDER BY mniejszy_id, wiekszy_id;