Przykładowe zapytania SQL

Przykłady dla Oracle Database: złączenia, zbiory, aktualizacje perspektyw i przechodzenie po hierarchii. Do ćwiczeń wykorzystujemy Las i Ewidencję ludności. Każda sekcja wskazuje potrzebne dane i skrypt. Przykłady tworzące lub zmieniające obiekty należy uruchamiać we własnym schemacie ćwiczeniowym.

Objaśnienia składni i różnic między systemami: Informacje o SQL.

Spis treści

  1. Złączenie zewnętrzne: także gatunki bez ofiar
  2. Operacje na zbiorach i wielozbiorach
  3. UPDATE ze skorelowanym podzapytaniem
  4. Aktualizacja widoku złączeniowego: paśnik i jego las
  5. Aktualizacja widoku: widoczność wiersza a WITH CHECK OPTION
  6. Od najstarszego znanego pokolenia do potomków
  7. Przodkowie wybranej osoby
  8. Najdłuższy łańcuch pokarmowy: co liczy LEVEL?

Złączenie zewnętrzne: także gatunki bez ofiar

Baza: Las (Oracle). Skrypt: outerjoin.sql.

Dla każdego gatunku obliczamy umowny wskaźnik: sumę iloczynów intensywności relacji pokarmowej i masy jednego osobnika ofiary. W danych jak_duzo oznacza intensywność, a nie liczbę zjedzonych osobników; nie jest to więc pomiar kilogramów spożytego mięsa.

SELECT d.nr, d.nazwa,
       COALESCE(SUM(p.jak_duzo * o.wielkosc), 0) AS wskaznik
FROM gatunki d
LEFT JOIN pozeranie p ON p.kto = d.nr
LEFT JOIN gatunki o ON o.nr = p.kogo
GROUP BY d.nr, d.nazwa
ORDER BY wskaznik DESC, d.nr;

Pierwsze LEFT JOIN zachowuje wszystkie gatunki. Drugie również musi zachować wiersz bez ofiary: zastąpienie go złączeniem wewnętrznym odrzuciłoby ten wiersz przed grupowaniem. Dla gatunku bez ofiar SUM daje NULL, z którego COALESCE robi zero. Grupujemy według identyfikatora i nazwy: dwóm gatunkom o tej samej nazwie nie wolno połączyć wyników. W danych przykładowych wilk ma wskaźnik 497, a jeleń 0.

Dawny zapis Oracle d.nr = p.kto(+) oznaczał zachowanie wierszy z lewej strony. Jawne LEFT JOIN … ON od razu pokazuje, którą stronę uzupełniamy wartościami NULL, i działa również w PostgreSQL.

Operacje na zbiorach i wielozbiorach

Dane są zdefiniowane w zapytaniu; przykładu można użyć bez tworzenia tabel. Skrypt: zbiory.sql.

Niech A zawiera liczby 1, 1, 2, 3, 3, 5, a B — 1, 1, 1, 2, 2, 3, 4. W tabeli podano pełne wyniki w kolejności rosnącej. Domyślnie operacje usuwają powtórzenia; z ALL znaczenie ma liczba wystąpień każdej wartości.

Operacje na A i B
OperacjaWynik
A UNION B1, 2, 3, 4, 5
A MINUS B5
A INTERSECT B1, 2, 3
A UNION ALL B1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 5
A MINUS ALL B3, 5
A INTERSECT ALL B1, 1, 2, 3

Na przykład w A są dwie trójki, w B jedna. Bez ALL trójka znika z różnicy; z ALL zostaje jedna kopia. Kolejność wyniku gwarantuje dopiero ORDER BY.

W Oracle 19c tylko UNION z tej trójki przyjmuje ALL. Jak więc pokazać pozostałe operacje? Skrypt numeruje osobno kopie każdej wartości, wykonuje różnicę lub przecięcie na parach (wartość, numer kopii) i pokazuje same wartości. W Oracle od 21c dostępne są rodzime MINUS ALL i INTERSECT ALL. W PostgreSQL różnica nosi nazwę EXCEPT lub EXCEPT ALL.

UPDATE ze skorelowanym podzapytaniem

Baza: Las (Oracle). Skrypt: updskorel.sql.

Dla każdego gatunku poza gatunkiem o najmniejszym numerze ustawiamy rozrodczość i wielkość na średnie odpowiednich cech gatunków o ściśle mniejszych numerach. Gatunek bez poprzednika zachowuje swoje wartości.

UPDATE gatunki g
SET (rozrodczosc, wielkosc) =
    (SELECT AVG(p.rozrodczosc), AVG(p.wielkosc)
     FROM gatunki p
     WHERE p.nr < g.nr)
WHERE EXISTS (SELECT 1 FROM gatunki p WHERE p.nr < g.nr);

Bez EXISTS wartości obu średnich dla pustego zbioru byłyby NULL, a obie kolumny mają ograniczenie NOT NULL. Z kolei <= oznaczałoby inne zadanie: gatunek uczestniczyłby we własnej średniej. Jedna instrukcja czyta cechy sprzed tej aktualizacji — wynik wcześniejszego wiersza nie zasila średniej następnego. W danych ćwiczeniowych dla gatunku nr 3 masy 180 i 35 dają 107,5 kg. Skrypt pokazuje wyniki i wycofuje swoją zmianę do punktu przywracania.

Aktualizacja widoku złączeniowego: paśnik i jego las

Baza: Las (Oracle). Skrypt jest przeznaczony do osobnej sesji SQL*Plus w schemacie ćwiczeniowym. Skrypt: joinupd.sql.

Każdy paśnik należy do jednego lasu, ale ten sam las może mieć kilka paśników. Złączenie zachowuje zatem klucz tabeli pasniki, niekoniecznie tabeli lasy. Dodajemy paśnik przez widok, podając numer istniejącego lasu; samo dodanie paśnika nie tworzy lasu.

CREATE VIEW bd_demo_pasniki_v AS
    SELECT p.nr, p.karma, p.wielkosc, p.nr_lasu,
           l.nazwa AS nazwa_lasu, l.wielkosc AS wielkosc_lasu,
           l.rodzaj AS rodzaj_lasu
    FROM pasniki p
    JOIN lasy l ON l.nr = p.nr_lasu;

Czy przez taki widok można zmienić powierzchnię lasu? W Oracle 19c natywny UPDATE widoku wymaga tu zmiany po stronie z zachowanym kluczem. Od Oracle 21c zmiana po drugiej stronie też może być dozwolona, jeśli modyfikuje jedną tabelę i każdy jej wiersz jest aktualizowany najwyżej raz.

W skrypcie zamiast polegać na różnicach między wersjami definiujemy INSTEAD OF UPDATE: dla wskazanego paśnika zapisuje nową powierzchnię w jego lesie. Próba ustawienia innego pola jest jawnie odrzucana. Pokazowa zmiana wskazuje tylko jeden paśnik; nową powierzchnię widać jednak przy wszystkich paśnikach tego lasu. Jeśli celem jest jedna zmiana na las niezależnie od liczby paśników, wygodniej aktualizować wprost tabelę lasy.

Wstawienie i pokazową zmianę wycofujemy przed usunięciem widoku. Samo tworzenie i usuwanie obiektów to DDL, dlatego przed uruchomieniem skryptu proszę zakończyć inne transakcje.

Aktualizacja widoku: widoczność wiersza a WITH CHECK OPTION

Pamiętnik: przykład tworzy własną tabelę i widoki w osobnej sesji ćwiczeniowej Oracle. Skrypt: viewupd.sql.

Prosty widok może udostępniać do zapisu tylko numer i treść wpisu. Pozostałe wymagane kolumny tabeli otrzymują wartości domyślne: USER dla podpisu i SYSDATE dla daty. Poniższe dwa widoki pokazują te same wiersze, ale inaczej obsługują zapis:

CREATE VIEW bd_demo_widoczne AS
    SELECT nr, tresc FROM bd_demo_pamietnik WHERE nr > 0;
CREATE VIEW bd_demo_dodatnie AS
    SELECT nr, tresc FROM bd_demo_pamietnik WHERE nr > 0
    WITH CHECK OPTION;

Wstawienie numeru −1 przez pierwszy widok powiedzie się: wiersz trafi do tabeli, chociaż nie będzie widoczny przez ten widok. Drugi widok odrzuci takie wstawienie (ORA-01402), jak również zmianę widocznego numeru na niedodatni. Sprawdza to skrypt, w którym oczekiwany błąd jest przechwycony.

Ostatni widok ogranicza odczyt do wierszy spełniających podpis = USER. Aby takie ograniczenie miało znaczenie dla innego konta, udzielamy mu praw do odpowiedniego widoku, a nie równocześnie do całej tabeli lub widoku z szerszym dostępem. Skrypt nie nadaje praw PUBLIC. Po demonstracji wycofuje przykładowe wpisy i usuwa własne obiekty.

Od najstarszego znanego pokolenia do potomków

Zestaw: Ewidencja ludności (Oracle, wariant archiwalny). Skrypt downtree.sql.

Proszę wypisać ścieżki rozpoczynające się od osób bez zapisanych rodziców, a następnie przejść w stronę ich dzieci, wnuków i dalszych potomków.

SELECT CONNECT_BY_ROOT id AS id_korzenia, LEVEL AS poziom,
       id, imie, nazwisko, id_ojca, id_matki,
       SYS_CONNECT_BY_PATH(TO_CHAR(id), '/') AS sciezka
FROM osoba
START WITH id_ojca IS NULL AND id_matki IS NULL
CONNECT BY NOCYCLE (PRIOR id = id_ojca OR PRIOR id = id_matki)
ORDER SIBLINGS BY id;

PRIOR id to identyfikator osoby z poprzedniego wiersza ścieżki. Następny wiersz jest jej dzieckiem, jeżeli ma ten identyfikator w kolumnie ojca lub matki. Poziom 1 to osoba początkowa. Dwa pola NULL oznaczają brak informacji o rodzicach w bazie.

Wynik zawiera po jednym wierszu na ścieżkę, a nie na osobę. Dziecko dwóch osób początkowych zostanie wypisane dwa razy, z różnymi ścieżkami. W danych przykładowych ze 154 osób otrzymamy 213 wierszy (w tym 109 korzeni). NOCYCLE zabezpiecza przed zapętleniem na osiągalnej ścieżce, ale nie usuwa prawidłowych powtórzeń ani nie sprawdza całej bazy.

Przodkowie wybranej osoby

Zestaw: Ewidencja ludności (Oracle, wariant archiwalny). Skrypt uptree.sql.

Proszę wypisać każdego znanego przodka Tomasza Jankiewicza dokładnie raz, podając najmniejszą liczbę kroków od niego do tego przodka. Identyfikator Tomasza w zestawie to 1111.

WITH przodkowie AS (
  SELECT id, imie, nazwisko, LEVEL - 1 AS pokolenia
  FROM osoba
  START WITH id = 1111
  CONNECT BY NOCYCLE (PRIOR id_ojca = id OR PRIOR id_matki = id)
)
SELECT id, imie, nazwisko, MIN(pokolenia) AS odleglosc
FROM przodkowie
WHERE pokolenia > 0 AND id <> 1111
GROUP BY id, imie, nazwisko
ORDER BY odleglosc, id;

Tym razem to poprzedni wiersz wskazuje rodzica: PRIOR id_ojca = id lub PRIOR id_matki = id. Osobę początkową identyfikuje klucz, a nie para imię–nazwisko. Poziom 1 w hierarchii oznacza tutaj zero kroków; osoba początkowa nie należy do wyniku.

W danych przykładowych otrzymamy 8 różnych przodków: 2 rodziców, 2 znanych dziadków i 4 znanych pradziadków. Gdyby do któregoś przodka prowadziły dwie ścieżki, grupowanie po osobie i MIN wybrałyby krótszą z nich.

Najdłuższy łańcuch pokarmowy: co liczy LEVEL?

Zestaw: Las (Oracle). Skrypt hierarchy.sql.

Proszę znaleźć wszystkie gatunki drapieżników rozpoczynające najdłuższy łańcuch w danych. Długość mierzymy liczbą relacji „zjada”; pojedyncza relacja ma długość 1. Zakładamy, że sieć pokarmowa nie zawiera cykli.

WITH lancuchy AS (
  SELECT CONNECT_BY_ROOT kto AS szczyt, LEVEL AS dlugosc
  FROM pozeranie
  CONNECT BY PRIOR kogo = kto
)
SELECT DISTINCT g.nr, g.nazwa, l.dlugosc
FROM lancuchy l
JOIN gatunki g ON g.nr = l.szczyt
WHERE l.dlugosc = (SELECT MAX(dlugosc) FROM lancuchy)
ORDER BY g.nr;

Wierszem hierarchii jest teraz krawędź (kto, kogo). Bez START WITH każda krawędź może rozpoczynać ścieżkę. PRIOR kogo = kto oznacza, że ofiara z poprzedniego ogniwa staje się drapieżnikiem w następnym. CONNECT_BY_ROOT kto zachowuje identyfikator pierwszego drapieżnika.

Przykład: wilk → lis → zając to trzy gatunki i dwa ogniwa. W danych zestawu są trzy gatunki rozpoczynające łańcuch długości 2: 4 wilk, 8 rys, 15 puchacz. Istnieje sześć takich łańcuchów; DISTINCT eliminuje powtórzenia drapieżników, zachowując remisy. Gdy tabela pozeranie jest pusta, wynik jest pusty.

Ograniczenie kto <> kogo wyklucza samopętlę, ale nie cykl z kilku gatunków. Celowo nie używamy tu NOCYCLE: jeśli pojawi się cykl, Oracle zgłosi ORA-01436. Ucięcie przejścia nie rozwiązuje problemu definicji: przy dopuszczeniu wielokrotnego przejścia przez cykl nie ma najdłuższego skończonego łańcucha.

Oracle: zapytania hierarchiczne i operator PRIOR.

Sprawdź poprawność XHTML 1.0 Strict


SQL — strona główna