Lab05 - Operatory zbiorowe, podzapytania, widoki [ ver. BD1.2025.10.25.003 ]

• Operatory zbiorowe: UNION, INTERSECT, EXCEPT
• Podzapytania - niepowiązane i powiązane
• Podzapytania - operatory ALL, ANY i EXISTS
• Widok (perspektywa, kwerenda)
• Widok modyfikowalny

W ramach operatorów działających na zbiorach wyróżniamy polecenia w języku SQL, które działają na co najmniej dwóch oddzielnych poleceniach SQL. Jeżeli mamy dwa zbiory danych możemy wyróżnić następujące metody wybrania danych:

• dane są w jednym lub drugim zbiorze - operacja sumy, w SQL operator UNION

    SELECT a1, a2, a3 FROM R1
    UNION [ALL]
    SELECT a1, a2, a3 FROM R2;
    

• dane są zarówno w zbiorze pierwszym i drugim - operator przecięcia, w SQL operator INTERSECT

    SELECT a1, a2, a3 FROM R1
    INTERSECT
    SELECT a1, a2, a3 FROM R2;
    

• dane są w zbiorze pierwszym a nie ma ich w zbiorze drugim (i odwrotnie) - operator różnicy, w SQL operator EXCEPT

    SELECT a1, a2, a3 FROM R1
    EXCEPT
    SELECT a1, a2, a3 FROM R2 ;
    

Występujące w poleceniach relacje (tabele) muszą mieć zgodną liczbę atrybutów oraz domeny poszczególnych atrybutów muszą być zgodne.

Przykłady wykorzystania operatorów UNION, EXCEPT i INTERSECT w bazie danych KURS.

Lista wszystkich uczestników i wykładowców - operator UNION.

SELECT imie, nazwisko, 'U' FROM uczestnik
UNION
SELECT imie, nazwisko, 'W' FROM wykladowca 
ORDER BY 3,2 ;

Lista wykładowców, którzy nie prowadzą kursów - operator EXCEPT.

SELECT imie, nazwisko FROM wykladowca
EXCEPT 
SELECT imie, nazwisko 
FROM wykladowca w JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl
ORDER BY 2;

Lista wykładowców, którzy nie prowadzą kursów. Realizacja z wykorzystaniem złączenia LEFT OUTER JOIN

SELECT imie, nazwisko 
FROM wykladowca w LEFT JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl 
WHERE wk.id_wykl is null ;

Lista uczestników, którzy uczestniczyli na 1 i 2 stopniu kursu języka angielskiego, operator INTERSECT

SELECT imie, nazwisko 
FROM uczestnik u JOIN uczest_kurs uk ON u.id_uczestnik = uk.id_uczest
where id_kurs in (1,2 )
INTERSECT
SELECT imie, nazwisko 
FROM uczestnik u JOIN uczest_kurs uk ON u.id_uczestnik = uk.id_uczest
where id_kurs = 3
ORDER BY 2 ;

Termin podzapytanie używamy do określenia kompletnego polecenia SELECT otoczonego nawiasami i zazwyczaj nazywanego aliasem za pomocą klauzuli AS poza nawiasami. Podzapytania możemy użyć w innym poleceniu SELECT, UPDATE, INSERT czy DELETE. W zależności od zwróconego wyniku podzapytania można go wykorzystać w różnych miejscach podstawowego zapytania.

• Podzapytanie tabelaryczne można użyć tam, gdzie można użyć nazwy tabeli lub widoku ale też w procedurze przechowywanej lub funkcji zwracającej tabelę.
• Podzapytanie tabelaryczne z jedną kolumną może użyć tam, gdzie używamy podzapytanie tabelaryczne lub jako lista wartości w predykacie IN.
• Podzapytanie skalarne może być użyć tam, gdzie używamy nazwy kolumny, wyrażenia lub nazwy kolumn.

Kolejnym formalizmem związanym z podzapytaniami są podzapytania skorelowane i nieskorelowane.

• Podzapytanie skorelowane jest wtedy, gdy jakiś warunek wewnątrz podzapytania (w klauzuli WHERE lub HAVING) zależy od wartości w rekordzie aktualnie przetwarzanym przez zapytanie zewnętrzne.
• Podzapytanie nieskorelowane nie jest zależne od zewnętrznej wartości, może być uruchomione jako odrębne zapytanie.

Podzapytania można też rozpatrywać ze względu na miejsce wykorzystania.

• Podzapytanie w miejscu listy tabel, określa źródło danych.
• Podzapytanie w miejscu warunku, staje się częscią kryteriów selekcji zarówno dla klauzuli WHERE jak i HAVING.
• Podzapytanie w miejscu listy atrybutów tworzy najczęściej wartość obliczaną.

Przykłady wykorzystania podzapytań w bazie danych KURS.

Podzapytanie tabelaryczne zwracające tabelę. Podzapytanie nieskorelowane.

SELECT i, n 
FROM  ( select imie i, nazwisko n FROM uczestnik ) AS u
ORDER BY n ;

Podzapytanie zwracające listę (tabela z jedną kolumną). Imię i nazwisko uczestnika kursu o numerach id=1 i grupy id=1. Podzapytanie nieskorelowane.

SELECT imie, nazwisko FROM uczestnik
WHERE id_uczestnik IN ( SELECT id_uczest 
FROM uczest_kurs WHERE id_kurs=1 ) 
ORDER BY 2;

Podzapytanie zwracające skalar (pojedynczą wartość). Podzapytanie nieskorelowane.

SELECT
(SELECT COUNT(*) FROM uczestnik ) AS l_uczest,
(SELECT COUNT(*) FROM wykladowca ) AS l_wykl,
(SELECT COUNT(*) FROM kurs ) AS l_kurs,
(SELECT SUM(oplata) FROM uczest_kurs ) AS sum_oplata ;

Lista kursów i wartości zgregowanych. Podzapytanie skorelowane.

SELECT ko.opis, 
( SELECT COUNT(*) FROM uczest_kurs uk 
  WHERE uk.id_kurs=k.id_kurs ) AS l_ucz,
( SELECT SUM(oplata) FROM uczest_kurs uk
  WHERE uk.id_kurs=k.id_kurs ) AS s_opl
FROM kurs k JOIN kurs_opis ko ON k.id_nazwa = ko.id_kurs;

Lista kursów dla których suma opłat jest większa od wartości A. Podzapytanie skorelowane.

SELECT ko.opis 
FROM kurs k JOIN kurs_opis ko ON k.id_nazwa = ko.id_kurs 
WHERE 
( SELECT SUM(oplata) FROM uczest_kurs uk
  WHERE uk.id_kurs=k.id_kurs )
> 5500;

Lista kursów dla których suma opłat jest większa od wartości A. Realizacja w jednym zapytaniu.

SELECT ko.opis, k.id_kurs, k.id_grupa
FROM kurs k JOIN kurs_opis ko ON k.id_nazwa = ko.id_kurs
JOIN uczest_kurs uk ON uk.id_kurs=k.id_kurs 
GROUP BY ko.opis, k.id_kurs, k.id_grupa
HAVING SUM(uk.oplata) > 5500;

Operatory EXISTS, ALL i ANY ( SOME ) wykorzystuje się do analizy danych zwróconych przez podzapytanie (zapytanie wewnętrzne).

• Predykat EXISTS zwraca wartość true jeśli podzapytanie zwróciło jakiekolwiek dane, jeśli nie zwraca wartość false.

  SELECT  { atrybuty }  FROM relacja1 R1
  WHERE EXISTS 
  ( SELECT 1 FROM relacja2 R2 WHERE R1.fk = R2.id ) ;
    

  SELECT  { atrybuty }  FROM relacja1 R1
  WHERE NOT EXISTS 
  ( SELECT 1 FROM relacja2 R2 WHERE R1.fk = R2.id ) ;
    

• Predykaty ANY (SOME) działają na wektorze wartości zwróconym przez podzapytanie. Wartość bieżącego wiersza jest porównywana z każdą wartością podzapytania w oparciu o operator porównania. Jeżeli jedno zwraca wartość prawdę, to całe porównanie jest prawdą.

  SELECT  { atrybuty }  FROM relacja1 R1
  WHERE {atrybut} {operator porównania} 
  ( SELECT {R2.atrybut} FROM relacja2 R2 WHERE R1.fk = R2.id ) ;
    

• Predykat ALL działa na wektorze wartości zwróconym przez podzapytanie. Działa podobnie jak ANY, jednak jeżeli jedno porównanie da fałsz, to cała klauzula przyjmie wartość fałsz.

  SELECT  { atrybuty }  FROM relacja1 R1
  WHERE {atrybut} {operator porównania} 
  ( SELECT {R2.atrybut} FROM relacja2 R2 WHERE R1.fk = R2.id ) ;
    

Przykłady wykorzystania predykatów: ANY, ALL i EXISTS w bazie danych KURS.

Predykat ANY. Lista uczestników, którzy byli co najmniej na jednym kursie.

SELECT u.imie, u.nazwisko FROM uczestnik u
WHERE u.id_uczestnik = ANY ( SELECT id_uczest FROM uczest_kurs )
ORDER BY 2 ;

Prydykat ALL. Lista uczestników, którzy otrzymali tylko określoną ocenę końcową.

SELECT u.imie, u.nazwisko FROM uczestnik u
WHERE 5 = ALL ( SELECT ocena FROM uczest_kurs uk 
WHERE uk.id_uczest = u.id_uczestnik  )
ORDER BY 2;

Predykat EXISTS. Podać wykładowców, którzy mają zajęcia i jakie prowadzą przedmioty.

SELECT w.imie, w.nazwisko, ko.opis 
FROM wykladowca w, kurs_opis ko
WHERE EXISTS ( SELECT 1 FROM wykl_kurs wk JOIN kurs k
ON wk.id_kurs = k.id_kurs 
WHERE wk.id_wykl = w.id_wykladowca 
      AND ko.id_kurs=k.id_nazwa) 
ORDER BY 2;

Predykat NOT EXISTS. Podać wykładowców, którzy nie prowadzą zajęć.

SELECT w.imie, w.nazwisko 
FROM wykladowca w 
WHERE NOT EXISTS ( SELECT 1 FROM wykl_kurs wk 
WHERE wk.id_wykl = w.id_wykladowca ) 
ORDER BY 2;

Widok jest wynikiem realizacji jednej lub wielu operacji na tabelach w bazie danych tworzących nową dynamiczną tabelę wynikową. Widok jest tabelą wirtualną, która nie musi fizycznie istnieć w bazie danych (w przeciwieństwie do widoków zmaterializowanych). Jest wyliczana na żądanie użytkownika. Dla użytkownika widok wygląda jak zwykła tabela. Widok tworzymy w następujących sytuacjach:

• pozwalają uprościć złożone zapytania - brak konieczności pisania długich poleceń SELECT;
• umożliwiają wielokrotne użycia.zdefiniowanego zapytania;
• umożliwiają zmianę formatowania danych, np. wykorzystanie funkcji CAST;
• ukrywanie efektów normalizacji - łączenie tabel z wykorzystaniem JOIN;
• możemy tworzyć kolumny obliczeniowe;
• ograniczenie dostępu do danych - usuwanie poprzez selekcję i projekcję chronionych danych;
• tworzenie warstwy abstrakcji - przykrywanie orginalnej struktury bazy danych, zmiana nazw.

Widok tworzymy poleceniem CREATE VIEW.

CREATE VIEW 
	<nazwa perspektywy> [<nazwa kolumny>,...] 
	AS
	<zapytanie SELECT definiujące perspektywę> 
	[ WITH [CASCADED|LOCAL]
	  CHECK OPTION]

Widok jest modyfikowalny, jeżeli istnieje możliwość przeniesienia każdego zmodyfikowanego wiersza czy kolumny z perspektywy do tabeli bazowej. Standard ISO narzuca na widoki modyfikowalne ograniczenia:

• nie występuje opcja DISTINCT,
• każdy atrybut w poleceniu definiującym widok jest atrybutem z tabeli źródłowej ( nie ma stałych, wyrażeń, czy funkcji agregujących,
• w klauzuli FROM okrelona jest tylko jedna tabela źródłowa (wykluczone są złączenia, sumy, przecięcia czy różnica),
• klauzula WHERE nie zawiera żądnego podzapytania,
• nie występuję klauzula GROUP BY i HAVING.

W ramach polecenia tworzącego widok występują dodatkowe parametry związane z modyfikacją danych poprzez widok.

• WITH CHECK OPTION -
• WITH LOCAL CHECK OPTION -
• WITH CASCADED CHECK OTION -

1. Wikipedia - Join (SQL)
2. Microsoft - Structured Query Language (SQL)
3. PostgreSQL Documentation, The SQL Language
4. Database Design, Adrienne Watt (rozdz.15 i 16)