Przejście do drugiej części wykładu

Kwerenda sparametryzowana

Czasami jest wygodnie mieć kwerendę uzależnioną od parametru  np. od nazwiska osoby, nazwy firmy itp. Oto metoda tworzenia takiej kwerendy w widoku projekt:

Odpowiada temu instrukcja SQL:

Przy obliczaniu wyniku zapytania wyświetla się okienko dialogowe do którego użytkownik wpisuje wartość parametru:

W rezultacie otrzymujemy listę towarów zakupionych przez klienta o identyfikatorze 1:

 

Podzapytania

Czytelnik z pewnością zauważył brak istotnej cechy, która jest typowa dla języków programowania – mianowicie zagnieżdżania instrukcji – zgodnego ze strukturalnym podejściem do rozwiązywania problemów. Według tej metody rozwiązywania problemów: dzielimy nasz problem na podproblemy, rozwiązujemy podproblemy, a następnie używając ich rozwiązań konstruujemy rozwiązanie całego problemu. Język SQL nazywa się strukturalnym językiem zapytań więc ma też możliwość podejścia strukturalnego. Rozważmy problem, w którym łatwo zidentyfikować podproblem.

Wyznacz towar, który ma najwyższą cenę.

Podproblemem jest tutaj "wyznacz najwyższą cenę towaru". Zapytanie SELECT Max(Cena) FROM Towary - oblicza najwyższą cenę zapisaną w kolumnie Cena tabeli Towary. Oto rozwiązanie całego problemu:

Jako wynik dostajemy:

Wreszcie odczytujemy zapytanie w widoku SQL:

Warunek Towary.Cena=(SELECT MAX(Cena) FROM Towary) występujący w klauzuli WHERE głównego zapytania określa, że interesują nas towary, których cena jest równa cenie najdroższego towaru.

Teraz zadanie do rozwiązania dla czytelnika:

Wyznacz towar, którego ilość jest największa na zamówieniu.

 

Zauważmy, że w podzapytaniu nie skorzystaliśmy z nazw kolumn wprowadzonych w głównym zapytaniu. Takie podzapytanie nazywamy zwykłym - zbiór wynikowych wierszy podzapytania nie zmienia się i nie zależy od wierszy w głównym zapytaniu. Podzapytanie nazywamy skorelowanym jeśli zbiór wyników podzapytania zależy od wartości występujących w wierszach w głównym zapytaniu.

Rozważmy teraz problem:

Dla każdego zamówienia wyznacz nazwę najdroższego towaru na tym zamówieniu.

Interesuje więc nas następujący wynik:

Rozwiązujemy postawiony problem zakładając na chwilę, że umiemy rozwiązać podproblem - znalezienia maksymalnej ceny wśród towarów występujących na fakturze o danym numerze Faktury.Id_faktury. Zaznaczony na czerwono numer pojawia się w wierszu głównego zapytania i następnie jest przekazywany i używany przez podzapytanie. Służy więc do korelacji głównego zapytania z podzapytaniem.

SELECT Faktury.Id_faktury, Towary.Nazwa, Towary.cena
FROM Towary INNER JOIN (Faktury INNER JOIN Pozycje ON Faktury.Id_faktury =        Pozycje.Id_faktury) ON Towary.Id_towaru = Pozycje.Id_towaru
WHERE
    Towary.Cena=<MAX Towary.Cena na fakturze o numerze Faktury.Id_faktury>
ORDER BY Faktury.Id_faktury;

 Rozwiązujemy teraz wyróżniony przez nas podproblem.

SELECT MAX(Towary.Cena)
FROM Towary INNER JOIN Pozycje ON Towary.Id_towaru = Pozycje.Id_towaru
WHERE Pozycje.Id_faktury = Faktury.Id_faktury;

Składając razem oba rozwiązania otrzymujemy rozwiązanie wyjściowego problemu.

SELECT Faktury.Id_faktury, Towary.Nazwa, Towary.Cena
FROM Towary INNER JOIN (Faktury INNER JOIN Pozycje ON Faktury.Id_faktury = Pozycje.Id_faktury) ON Towary.Id_towaru = Pozycje.Id_towaru
WHERE 
Towary.Cena=
   (SELECT MAX(Towary.Cena)
    FROM Towary INNER JOIN Pozycje ON Towary.Id_towaru = Pozycje.Id_towaru
    WHERE Pozycje.Id_faktury=Faktury.Id_faktury)
ORDER BY Faktury.Id_faktury;

Oto postać tekstowa otrzymanego zapytania do przekopiowania do okienka tekstowego w MS Access.

Reasumując, otrzymane podzapytanie jest skorelowane, ponieważ wielkość określona w głównym zapytaniu - Faktury.Id_faktury, jest używana w podzapytaniu i wpływa na jego wynik.

Teraz zadanie do rozwiązania dla czytelnika:

 
Wyznacz fakturę, której sumaryczna wartość jest największa.

Podzapytania mogą występować tylko po prawej stronie operatorów relacyjnych i muszą zwracać pojedynczą wartość z wyjątkiem operatorów:

Oto przykład zastosowania operatora IN do wyznaczenia pracowników, którzy przyjęli co najmiej jedną fakturę:

SELECT Imie, Nazwisko
FROM Pracownicy
WHERE Id_pracownika IN (SELECT Id_pracownika FROM Faktury);

Wydaje się, że podobnie przy pomocy operatora NOT IN można znaleźć pracowników, którzy nie przyjęli żadnej faktury.

SELECT Imie, Nazwisko
FROM Pracownicy
WHERE Id_pracownika NOT IN (SELECT Id_pracownika FROM Faktury);

Spotyka nas jednak tutaj niespodzianka - wynik jest pusty, chociaż w naszej bazie danych mamy pracowników, którzy nie przyjęli żadnego zamówienia.

Bierze to się stąd, że o żadnej wartości nie da się stwierdzić, że jest różna od NULL! A wśród faktur znajdują się faktury, dla których nie został określony żaden pracownik je wypisujący – w tym przypadku został wpisany NULL w polu Faktury.Id_pracownika.

Operatory EXISTS i NOT EXISTS - sprawdzają czy podzapytanie daje pusty zbiór wyników czy nie, np.
     EXISTS(SELECT "x" FROM Pracownicy WHERE Stanowisko="Dyrektor")
"istnieje co najmniej jeden pracownik zatrudniony na stanowisku dyrektora". Dla wyniku nie jest istotne co napiszemy na liście SELECT w ramach predykatu EXISTS – najprostsza obliczeniowo jest wartość stała taka jak "x".

Pierwszy przykład dotyczy EXISTS:

Znaleźć pracowników, którzy przyjęli co najmniej jedną fakturę.

Oto rozwiązanie:

SELECT Imie, Nazwisko
FROM Pracownicy
WHERE EXISTS (SELECT "x" FROM Faktury 
              WHERE Faktury.Id_pracownika=Pracownicy.Id_pracownika);

Teraz rozwiążemy problem, którego poprzednio nie udało się rozwiązać za pomocą operatora NOT IN:

Znajdź pracowników, którzy nie przyjęli żadnej faktury.

Oto rozwiązanie korzystające z operatora NOT EXISTS:

SELECT Imie, Nazwisko
FROM Pracownicy
WHERE NOT EXISTS (SELECT "x" FROM Faktury
                  WHERE Faktury.Id_pracownika=Pracownicy.Id_pracownika);

Tym razem wynik jest poprawny:

Na zakończenie tego wykładu rozważmy problem.

Wyznacz klientów, którzy jednocześnie są pracownikami firmy.

Przydałby tu się operator przecięcia (części wspólnej) wyników dwóch zapytań skierowanych odpowiednio do tabel Klienci i Pracownicy. Jednak takiego operatora MS Access 2000 nie wprowadza – chociaż występuje on w Standardzie języka SQL - pod nazwą INTERSECT. Zamiast niego użyjemy  podzapytania i operatora EXISTS.

SELECT Klienci.Imie, Klienci.Nazwisko
FROM Klienci
WHERE 
EXISTS(SELECT "x" FROM Pracownicy 
    WHERE Pracownicy.Imie=Klienci.Imie AND Pracownicy.Nazwisko=Klienci.Nazwisko);

Na naszej przykładowej bazie danych otrzymamy w wyniku pusty zbiór wierszy. Nie ma klientów, którzy by byli zatrudnieni w firmie. Nie ma problemu konfliktu interesów! Oczywiście wątpliwość może budzić test na tożsamość osoby za pomocą imienia i nazwiska – ale w naszej bazie danych nie postaraliśmy się o przechowywanie bardziej wiarygodnej identyfikacji osoby jak numer PESEL.

Podobnie w MS Access 2000 nie ma operatora EXCEPT, przy pomocy którego można byłoby odejmować wyniki dwóch zapytań. Zamiast niego można użyć podzapytania i operatora NOT EXISTS. Proponujemy czytelnikowi rozwiązanie zadania:

Wyznacz klientów, którzy nie są pracownikami firmy.

 

Podsumowanie

W wykładzie 9 czytelnik poznał podstawy języka SQL dostępu do baz danych. Język ten stanowi międzynarodowy standard, do którego stosują się wszyscy producenci relacyjnych i obiektowo-relacyjnych baz danych.

Omawiane były następujące instrukcje tego języka: wstawianie nowego wiersza do tabeli – INSERT, usuwanie wiersza z tabeli – DELETE, modyfikacja wartości w wierszu w tabeli – UPDATE oraz wyszukiwianie danych w tabelach bazy danych – SELECT.

Instrukcja SELECT czyli zapytanie do bazy danych może składać się z kilku części nazywanych klauzulami. Klauzula SELECT określa jakie wartości mają być sprowadzone z bazy danych, klauzula FROM – z jakich tabel, klauzula WHERE określa warunki jakie muszą spełniać sprowadzane dane, klauzula GROUP BY określa podział na grupy, którego ma dokonać system bazodanowy przed sprowadzeniem danych, klauzula HAVING określa warunki jakie muszą spełniać sprowadzane grupy, klauzula ORDER BY określa porządek wyświetlania sprowadzanych wierszy.

Dodatkowymi dwiema konstrukcjami stosowanymi przy budowie zapytań jest łączenie wyników dwóch zapytań operatorem sumowania UNION oraz zagnieżdżanie jednego zapytania w drugim (jako podzapytania).

Omawianie konstrukcji języka SQL będzie kontynuowane w ramach innego przedmiotu, mianowicie „Systemów baz danych”.
 

Słownik

SQL - (ang. Structured Query Language - Strukturalny Język Zapytań) język stanowiący interfejs do relacyjnej bazy danych. Jest międzynarodowym standardem, do którego stosują się wszyscy producenci relacyjnych i obiektowo-relacyjnych systemów baz danych.
SELECT - instrukcja języka SQL służąca do wydobywania danych z bazy danych. Określa:

operatory w SQL - IS [NOT] NULL, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, [NOT] EXISTS, UNION, DISTINCT, DISTINCTROW.
INSERT - instrukcja języka SQL służąca do wprowadzania danych do bazy danych.
DELETE - instrukcja języka SQL służąca do usuwania danych z bazy danych.
UPDATE - instrukcja języka SQL służąca do aktualizacji danych w bazie danych.
UNION - operator sumowania wyników zapytań.
złączenia w SQL - mogą być dokonane przy pomocy specjalnych operatorów na tabelach: INNER JOIN, LEFT JOIN, RIGHT JOIN.
GROUP BY - klauzula instrukcji SELECT służąca do grupowania danych.
zapytanie sparametryzowane - zapytanie wewnątrz którego występują parametry, których wartości na ogół podaje użytkownik przed realizacją zapytania.
podzapytanie - wystąpienie jednego zapytania wewnątrz drugiego. Podzapytanie jest albo proste albo skorelowane z głównym zapytaniem.
 

Zadania

Strona przygotowana przez Lecha Banachowskiego - 05/10/03 .