PRZYKŁADY ZAPYTAŃ W JĘZYKU SQL

Opracował: Piotr Parewicz Instytut Informatyki PW

Materiał dydaktyczny przeznaczony dla studentów kursów

BAZY DANYCH i WPROWADZENIE DO BAZ DANYCH

SCHEMAT BAZY DANYCH DO PRZYKŁADÓW ZAPYTAŃ

KLIENCI

Id_klienta	Nazwa_firmy	Adres	Miasto	Nr_tel	Nr_faksu

ZAMÓWIENIA

Nr_zam	Data_zam	Id_klienta	Wartość_zam

POZYCJE_ZAMÓWIEŃ

Nr_zam	Nr_towaru	Ilość_zam

TOWARY

Nr_towaru	Rodzaj_towaru	Nazwa_producenta	Model	Cena_sprzed	Ilość_w_mag

DOSTAWCY

Id_dostawcy	Nazwa_firmy	Adres	Miasto	Kraj

OFERTY_DOSTAWCÓW

Id_dostawcy	Nr_towaru	Cena

DOSTAWY

Nr_dostawy	Id_dostawcy	Data_zam	Data_przyjęcia	Nr_towaru	Ilość

PRZYKŁADY ZAPYTAŃ (POLECEŃ) W JĘZYKU SQL

CZĘŚĆ 1 - DOSTĘP DO DANYCH Z JEDNEJ TABELI

Wybór wierszy spełniających zadany warunek i kolumn wskazanych nazwą

1.1 Podać wykaz klientów z Krakowa: dla każdego klienta podać jego identyfikator oraz nazwę firmy.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE Miasto = `Kraków' ;

1.2 Podać wykaz wszystkich modeli namiotów. Dla każdego modelu podać numer katalogowy, nazwę producenta i oznaczenie modelu.

SELECT Nr_towaru, Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru = 'Namiot' ;

1.3 Podać wykaz zamówień złożonych przez klienta o identyfikatorze 1123. Dla każdego zamówienia podać numer zamówienia, datę i wartość zamówienia

SELECT Nr_zam, Data_zam, Wartość_zam

FROM Zamówienie

WHERE Id_klienta = 1123 ;

1.4 Podać nazwę firmy i miasto dla klienta o identyfikatorze 2575.

SELECT Nazwa_firmy, Miasto

FROM Klienci

WHERE Id_klienta = 2575 ;

To polecenie zwraca tabelę jednowierszową-przewidywanie takie wynika z faktu, że kolumna Id_klienta jest kluczem

1.5 Podać wykaz dostawców z Austrii. Podać pełne dane każdego dostawcy.

SELECT *

FROM Dostawcy

WHERE Kraj = `Austria' ;

Symbol * zastępuje listę wszystkich kolumn

1.6 Podać wykaz wszystkich klientów: dla każdego klienta podać identyfikator nazwę firmy i kraj.

SELECT Id_klienta, Nazwa_firmy, Kraj

FROM Klienci ;

Brak warunku wyboru wierszy - polecenie zwraca wszystkie wiersze tabeli

1.7 Podać pełne dane dla wszystkich klientów

SELECT *

FROM Klienci ;

Polecenie zwraca całą tabelę

1.8 Podać wykaz zamówień o wartości przekraczającej 20 000 złotych. Dla każdego takiego zamówienia podać jego numer, identyfikator klienta i wartość zamówienia.

SELECT Nr_zam, Id_klienta, Wartość_zam

FROM Zamówienia

WHERE Wartość_zam > 20000 ;

1.9 Podać wykaz dostawców zagranicznych. Dla każdego dostawcy podać nazwę firmy, miasto i kraj.

SELECT Nazwa_firmy, Miasto, Kraj

FROM Dostawcy

WHERE Kraj <> 'Polska' ;

1.10 Podać wykaz wszystkich zamówień złożonych między 15/04/2004 a 30/04/2004. Dla każdego zamówienia podać numer, datę i wartość.

SELECT Nr_zam, Data_zam, Wartość_zam

FROM Zamówienie

WHERE Data_zam BETWEEN '15/04/2004' AND '30/04/2004';

Użycie słowa kluczowego DISTINCT

1.11 Podać wykaz krajów i miast, w których mieszczą się firmy dostawców.

SELECT DISTINCT Miasto, Kraj

FROM Dostawcy ;

Przykłady złożonych warunków wyboru wierszy

1.12 Podać wykaz modeli plecaków produkcji firmy PRS. Dla każdego modelu podać numer katalogowy, oznaczenie modelu i cenę.

SELECT Nr_towaru, Model, Cena_sprzed

FROM Towary

WHERE Rodzaj_towaru = 'Plecak' AND Nazwa_producenta = 'PRS' ;

1.13 Podać wykaz wszystkich artykułów produkcji firm TWX i XYZ. Dla każdego artykułu podać rodzaj, nazwę producenta i oznaczenie modelu.

SELECT Rodzaj_towaru, Nazwa_producenta, Model

FROM Towary

WHERE Nazwa_producenta = 'TWX' OR Nazwa_producenta = 'XYZ' ;

1.14 Podać wykaz wszystkich modeli śpiworów o cenach mieszczących się w granicach od 100 do 400 złotych. Dla każdego modelu podać numer katalogowy, nazwę producenta, oznaczenie modelu i cenę.

SELECT Nr_towaru, Nazwa_producenta, Model, Cena_sprzed

FROM Towary

WHERE Rodzaj_towaru = 'Śpiwór' AND Cena_sprzed BETWEEN 100 AND 400 ;

1.15 Podać wykaz wszystkich zamówień o wartości przekraczającej 50 000 złotych, złożonych między 15/04/2004 a 30/04/2004. Dla każdego zamówienia podać numer, datę i wartość.

SELECT Nr_zam, Data_zam, Wartość_zam

FROM Zamówienie

WHERE Data_zam BETWEEN '15-APR-2004' AND '30-APR-2004' AND Wartość_zam > 50000;

1.16 Dla każdego modelu namiotu produkcji firmy UWZ lub QRT lub XYZ, którego zapas w magazynie jest mniejszy niż 100 sztuk, podać numer katalogowy, markę i oznaczenie modelu.

SELECT Nr_towaru, Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru =

'Namiot'

AND(Nazwa_producenta = 'UWZ'

OR Nazwa_producenta = 'QRT'

OR Nazwa_producenta = 'XYZ'

)

AND Ilość_w_mag < 100 ;

Użycie operatora IN

1.17 Polecenie z zad. 1.16 można zapisać w następującej postaci, korzystając z operatora IN:

SELECT Nr_towaru, Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru = 'Namiot'

AND Nazwa_producenta IN ('UWZ', 'QRT', 'XYZ')

AND Ilość_w_mag < 100 ;

Użycie operatora LIKE

1.18 Podać wykaz modeli śpiworów o oznaczeniu rozpoczynającym się od słowa “Borsuk”. Dla każdego modelu podać numer katalogowy i nazwę modelu.

SELECT Nr_towaru, Model

FROM Towary

WHERE Rodzaj_towaru = 'Śpiwór' AND Model LIKE `Borsuk%' ;

Przykłady wyrażeń w sekcji WHERE

1.19 Podać wykaz modeli plecaków, takich że wartość zapasów danego modelu przekracza 20 000 złotych. Dla każdego modelu podać numer katalogowy, nazwę producenta i nazwę modelu.

SELECT Nr_towaru, Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru = 'Plecak'

AND Ilość_w_mag * Cena_sprzed > 20000 ;

1.20 Podać wykaz dostaw przyjętych w okresie od 15/04/2004 do 30/04/2004, dla których czas oczekiwania przekroczył 20 dni. Dla każdej dostawy podać numer dostawy i identyfikator dostawcy.

SELECT Nr_dostawy, Id_dostawcy

FROM Dostawy

WHERE Data_przyjęcia BETWEEN '15/04/2004' AND '30/04/2004'

AND Data_przyjęcia - Data_zam > 20 ;

1.21 Podać wykaz dostaw sprzed 2 miesięcy lub wcześniejszych. Dla każdej dostawy podać numer dostawy, identyfikator dostawcy, numer katalogowy towaru i datę przyjęcia

SELECT Nr_dostawy, Id_dostawcy, Nr_towaru, Data_przyjęcia

FROM Dostawy

WHERE Months_between (Sysdate - Data_przyjęcia) > 2 ;

Użyto funkcji wbudowanych Sysdate oraz Months_between

1.22 Podać wykaz zamówień złożonych w pażdzierniku 2003,o wartości przekraczającej 20 000 złotych. Dla każdego takiego zamówienia podać jego numer, identyfikator klienta i wartość zamówienia.

SELECT Nr_zam, Id_klienta, Wartość_zam

FROM Zamówienia

WHERE Wartość_zam > 20000 AND Trunc (Data_zam, `MM') = `01/10/2003' ;

Użyto funkcji wbudowanej Trunc

Uporządkowanie wierszy wyniku za pomocą specyfikacji ORDER BY

1.23 Podać wykaz klientów z Poznania. Dla każdego klienta podać identyfikator, nazwę firmy i adres. Uporządkować alfabetycznie wg nazwy firmy.

SELECT Id_klienta, Nazwa_firmy, Adres

FROM Klienci

WHERE Miasto = `Poznań' ;

ORDER BY Nazwa_firmy ;

1.24 Podać wykaz wszystkich zamówień od klienta o identyfikatorze 1123, złożonych w okresie od 01/12/2003. Dla każdego zamówienia podać numer zamówienia, datę i wartość. Uporządkować wg wartości od największej do najmniejszej.

SELECT Nr_zam, Data_zam, Wartość_zam

FROM Zamówienie

WHERE Id_klienta = 1123 AND Data_zam >= `01/12/2003' ;

ORDER BY Wartość_zam DESC ;

1.25 Podać wykaz wszystkich dostawców. Dla każdego dostawcy podać nazwę firmy, miasto i kraj. Uporządkować alfabetycznie według krajów, a w ramach kraju - wg miast.

SELECT Nazwa_firmy, Miasto, Kraj

FROM Dostawcy

ORDER BY Kraj, Miasto ;

Dołączanie kolumn z wartościami wyliczanymi

1.26 Podać wykaz modeli namiotów produkcji firmy XYZ z ceną aktualną i ceną obniżoną o 20%.

SELECT Nr_towaru, Model, Cena_sprzed, Cena_sprzed * 0.8

FROM Towary

WHERE Rodzaj_towaru = 'Namiot'

AND Nazwa_producenta = 'XYZ' ;

1.27 Podać wykaz dostaw przyjętych w okresie od 15/04/2004 do 30/04/2004, dla których czas oczekiwania przekroczył 20 dni. Dla każdej dostawy podać numer dostawy, identyfikator dostawcy i czas oczekiwania.

SELECT Nr_dostawy, Id_dostawcy, Data_przyjęcia - Data_zam

FROM Dostawy

WHERE Data_przyjęcia BETWEEN '15/04/2004' AND '30/04/2004'

AND Data_przyjęcia - Data_zam > 20 ;

1.28 Podać wykaz wartości zapasów dla poszczególnych modeli namiotów.

SELECT Nr_towaru, Model, Cena_sprzed * Ilość_w_mag

FROM Towary

WHERE Rodzaj_towaru = 'Namiot' ;

Wybranie wierszy, w których wartość we wskazanej kolumnie jest określona/nieokreślona

1.29 Podać wykaz klientów (identyfikator, nazwa firmy), którzy nie mają faksu.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE Nr_faksu IS NULL ;

1.30 Podać wykaz dostaw (numer dostawy, identyfikator dostawcy, czas oczekiwania) zamówionych nie wcześniej niż przed 7 dniami, które już zostały odebrane (data przyjęcia jest określona).

SELECT Nr_dostawy, Id_dostawcy, Data_przyjęcia - Data_zam

FROM Dostawy

WHERE Sysdate - Data_zam < 7 AND Data_przyjęcia IS NOT NULL ;

CZĘŚĆ 2 - DOSTĘP DO DANYCH Z DWÓCH LUB WIĘCEJ TABEL

Złączenie dwóch tabel

W zadaniach 2.1, 2.2 wiersze tabeli wynikowej powstają z par wierszy spełniających warunek złączenia

2.1 Podać wykaz wszystkich zarejestrowanych zamówień. dla każdego zamówienia podać: numer i datę zamówienia oraz nazwę firmy zamawiającej i miasto, w którym ta firma się znajduje.

a) Postać zapytania z wyrażeniem złączeniowym w sekcji FROM:

SELECT Zamówienia.Nr_zam, Zamówienia.Data_zam, Klienci.Nazwa_firmy, Klienci.Miasto

FROM Zamówienia

JOIN Klienci ON Zamówienia.Id_klienta = Klienci.Id_klienta ;

b) Postać zapytania z warunkiem złączenia w sekcji WHERE:

SELECT Zamówienia.Nr_zam, Zamówienia.Data_zam, Klienci.Nazwa_firmy, Klienci.Miasto

FROM Zamówienia, Klienci

WHERE Zamówienia.Id_klienta = Klienci.Id_klienta ;

c) Postać zapytania odwołująca się do operacji złączenia naturalnego - domyślnym warunkiem złączenia jest równość wartości w kolumnach o tej samej nazwie:

SELECT Zamówienia.Nr_zam, Zamówienia.Data_zam, Klienci.Nazwa_firmy, Klienci.Miasto

FROM Zamówienia NATURAL

JOIN Klienci ;

d) Postać zapytania odwołująca się do operacji JOIN USING - domyślnym warunkiem złączenia jest równość wartości w kolumnach o podanej nazwie, tej samej w obu tabelach:

SELECT Zamówienia.Nr_zam, Zamówienia.Data_zam, Klienci.Nazwa_firmy, Klienci.Miasto

FROM Zamówienia

JOIN Klienci USING Id_klienta ;

2.2 Podać wykaz wszystkich zarejestrowanych dostaw. Dla każdej dostawy podać numer katalogowy towaru, zamówioną ilość towaru i nazwę firmy dostawcy.

a)

SELECT Dostawy.Nr_towaru, Dostawy.Ilość_zam, Dostawcy.Nazwa_firmy

FROM Dostawy JOIN Dostawcy ON Dostawy.Id_dostawcy = Dostawcy.Id_dostawcy

b)

SELECT Dostawy.Nr_towaru, Dostawy.Ilość_zam, Dostawca.Nazwa_firmy

FROM Dostawy, Dostawcy

WHERE Dostawy.Id_dostawcy = Dostawcy.Id_dostawcy ;

c)

SELECT Dostawy.Nr_towaru, Dostawy.Ilość_zam, Dostawcy.Nazwa_firmy

FROM Dostawy NATURAL JOIN Dostawcy ;

d)

SELECT Dostawy.Nr_towaru, Dostawy.Ilość_zam, Dostawcy.Nazwa_firmy

FROM Dostawy JOIN Dostawcy USING Id_dostawcy ;

Złączenie dwóch tabel z dodatkową selekcją

W zadaniach 2.3-2.8 wiersze tabeli wynikowej powstają z par wierszy spełniających warunek złączenia i dodatkowe warunki selekcji nałożone na wartości w kolumnach innych niż kolumny uczestniczące w warunku złączenia.

2.3 Podać wykaz zamówień o wartości przekraczającej 30 000 złotych. Dla każdego takiego zamówienia podać identyfikator klienta, nazwę firmy, numer zamówienia i wartość zamówienia.

Dodatkowy warunek selekcji jest nałożony na wiersze z jednej tabeli

a)

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, Zamówienia.Nr_zam, Zamówienia.Wartość_zam

FROM Klienci JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Zamówienia.Wartość_zam > 30000 ;

W postaci z wyrażeniem złączeniowym sekcja WHERE zawiera tylko dodatkowy warunek selekcji

b)

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, Zamówienia.Nr_zam,

Zamówienia.Wartość_zam

FROM Klienci, Zamówienia

WHERE Klienci.Id_klienta =

Zamówienia.Id_klienta AND Zamówienia.Wartość_zam > 30000 ;

W tej postaci zapytania zarówno warunek złączenia, jak i dodatkowy warunek selekcji są zawarte w sekcji WHERE

c)

SELECT Id_klienta, Klienci.Nazwa_firmy, Zamówienia.Nr_zam, Zamówienia.Wartość_zam

FROM Klienci NATURAL JOIN Zamówienia

WHERE Zamówienia.Wartość_zam > 30000 ;

d)

SELECT Id_klienta, Klienci.Nazwa_firmy, Zamówienia.Nr_zam, Zamówienia.Wartość_zam

FROM Klienci JOIN Zamówienia USING Id_klienta

WHERE Zamówienia.Wartość_zam > 30000 ;

W postaciach zapytania odwołujących się do domyślnego warunku złączenia (operacje NATURAL JOIN, JOIN USING) sekcja WHERE oczywiście zawiera tylko dodatkowy warunek selekcji.

W następnych przykładach będzie podawana tylko jedna lub dwie spośród możliwych postaci zapisu złączenia.

2.4 Podać wykaz dostaw przyjętych w okresie od 15/04/2004 do 30/04/2004. Dla każdej dostawy podać identyfikator dostawcy, datę przyjęcia dostawy, numer katalogowy towaru, nazwę producenta, oznaczenie modelu i liczbę sztuk w dostawie.

Podobnie jak w poprzednim zadaniu dodatkowy warunek selekcji jest nałożony na wiersze z jednej tabeli

a)

SELECT Dostawy.Id_dostawcy, Dostawy.Data_przyjęcia, Towary.Nr_towaru,

Towary.Nazwa_producenta, Towary.Model, Dostawy.Ilość_zam

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Dostawy.Data_przyjęcia BETWEEN '15/04/2004' AND '30/04/2004' ;

b)

SELECT Dostawy.Id_dostawcy, Dostawy.Data_przyjęcia, Towary.Nr_towaru,

Towary.Nazwa_producenta, Towary.Model, Dostawy.Ilość_zam

FROM Dostawy, Towary

WHERE Dostawy.Nr_towaru = Towary.Nr_towaru

AND Dostawy.Data_przyjęcia BETWEEN '15/04/2004' AND '30/04/2004';

2.5 Podać wykaz zamówień o wartości przekraczającej 30 000 złotych, złożonych przez klientów z Krakowa. Dla każdego takiego zamówienia podać identyfikator klienta, nazwę firmy, numer zamówienia i wartość zamówienia.

W tym zadaniu są nałożone dodatkowe warunki selekcji na wiersze z obu tabel

a)

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, Zamówienia.Nr_zam,

Zamówienia.Wartość_zam

FROM Klienci, Zamówienia

WHERE Klienci.Id_klienta = Zamówienia.Id_klienta

AND Klienci.Miasto = 'Kraków'

AND Zamówienia.Wartość_zam > 30000 ;

b)

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, Zamówienia.Nr_zam,

Zamówienia.Wartość_zam

FROM Klienci JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Klienci.Miasto = 'Kraków'

AND Zamówienia.Wartość_zam > 30000 ;

2.6 Podać wykaz dostaw namiotów przyjętych po 15/04/2004. Dla każdej dostawy podać identyfikator dostawcy, datę przyjęcia dostawy, numer katalogowy, nazwę producenta i oznaczenie modelu namiotu i liczbę sztuk w dostawie.

Podobnie jak w poprzednim zadaniu, dodatkowe warunki selekcji są nałożone na wiersze z obu tabel

a)

SELECT Dostawy.Id_dostawcy, Dostawy.Data_przyjęcia, Towary.Nr_towaru,

Towary.Nazwa_producenta, Towary.Model, Dostawy.Ilość_zam

FROM Dostawy, Towary

WHERE Dostawy.Nr_towaru = Towary.Nr_towaru

AND Dostawy.Data_przyjęcia > `15/04/2004'

AND Towary.Rodzaj_towaru = 'Namiot' ;

b)

SELECT Dostawy.Id_dostawcy, Dostawy.Data_przyjęcia, Towary.Nr_towaru,

Towary.Nazwa_producenta, Towary.Model, Dostawy.Ilość_zam

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Dostawy.Data_przyjęcia > `15/04/2004'

AND Towary.Rodzaj_towaru = 'Namiot' ;

2.7 Podać wykaz zamówień (numer zamówienia i wartość) złożonych przez klientów z Gdańska w okresie od 15/04/2004 do 30/04/2004.

Tabela wynikowa ma zawierać dane, które pochodzą tylko z tabeli ZAMÓWIENIA. Jest jednak konieczne złączenie z tabelą KLIENCI, aby wybrać te wiersze tabeli ZAMÓWIENIA, dla których odpowiadające im wiersze w tabeli KLIENCI zawierają żądaną wartość w kolumnie MIASTO.

SELECT Zamówienia.Nr_zam, Zamówienia.Wartość_zam

FROM Klienci JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Zamówienia.Data_zam BETWEEN '15/04/2004' AND '30/04/2004'

AND Klienci.Miasto = 'Gdańsk' ;

2.8 Podać wykaz artykułów oferowanych przez dostawcę o numerze identyfikacyjnym 235.

Analogicznie jak w poprzednim zadaniu tabela wynikowa ma zawierać dane, które pochodzą z jednej tylko tabeli. Złączenie jest konieczne, aby wybrać wiersze według warunku odwołującego się do danych z odpowiednich wierszy drugiej tabeli.

SELECT Towary.Rodzaj_towaru, Towary.Nazwa_producenta, Towary.Model

FROM Towary JOIN Oferty_dostawców

ON Towary.Nr_towaru = Oferty_dostawców.Nr_towaru

WHERE Oferty_dostawców.Id_dostawcy = 235 ;

Złączenie trzech lub więcej tabel

2.9 Podać pełny wykaz wszystkich zarejestrowanych ofert dostawców. Dla każdej oferty podać nazwę firmy dostawcy, rodzaj towaru, nazwę producenta, oznaczenie modelu i oferowaną cenę.

a)

SELECT Dostawcy.Nazwa_firmy, Towary.Rodzaj_towaru,

Towary.Nazwa_producenta, Towary.Model, Oferty_dostawców.Cena

FROM Towary, Oferty_dostawców, Dostawcy

WHERE Towary.Nr_towaru = Oferty_dostawców.Nr_towaru

AND Oferty_dostawców.Id_dostawcy = Dostawcy.Id_dostawcy ;

b)

SELECT Dostawcy.Nazwa_firmy, Towary.Rodzaj_towaru, Towary.Nazwa_producenta, Towary.Model, Oferty_dostawców.Cena

FROM (Towary JOIN Oferty_dostawców

ON Towary.Nr_towaru =

Oferty_dostawców.Nr_towaru)

JOIN Dostawcy ON Oferty_dostawców.Id_dostawcy = Dostawcy.Id_dostawcy ;

2.10 Podać wykaz dostaw butów turystycznych zamówionych u dostawców mających siedzibę w Austrii. Dla każdej dostawy podać nazwę firmy dostawcy, nazwę producenta, oznaczenie modelu i zamówioną liczbę par.

SELECT Dostawcy.Nazwa_firmy, Towary.Nazwa_producenta, Towary.Model, Dostawy.Ilość_zam

FROM Towary, Dostawy, Dostawcy

WHERE Towary.Nr_towaru = Dostawy.Nr_towaru

AND Dostawy.Id_dostawcy = Dostawcy.Id_dostawcy

AND Towary.Rodzaj_towaru = 'Buty turystyczne'

AND Dostawcy.Kraj = 'Austria' ;

SELECT Dostawca.Nazwa_firmy, Towary.Nazwa_producenta, Towary.Model, Dostawy.Ilość_zam

FROM (Towary JOIN Dostawy

ON Towary.Nr_towaru = Dostawy.Nr_towaru)

JOIN Dostawcy ON Dostawy.Id_dostawcy = Dostawcy.Id_dostawcy

WHERE Towary.Rodzaj_towaru = 'Plecak'

AND Dostawcy.Kraj = 'Austria' ;

2.11 Podać wykaz modeli namiotów (nazwa producenta, model), dla których są oferty od dostawców z Niemiec.

SELECT Towary.Nazwa_producenta, Towary.Model

FROM Towary, Oferty_dostawców, Dostawcy

WHERE Towary.Nr_towaru = Oferty_dostawców.Nr_towaru

AND Oferty_dostawców.Id_dostawcy = Dostawcy.Id_dostawcy

AND Towary.Rodzaj_towaru = 'Namiot'

AND Dostawcy.Kraj = 'Niemcy' ;

2.12

Podać wykaz pozycji zamówień złożonych po 15/04/2004, w których zamawianym towarem są namioty, a ilość zamawiana wynosi nie mniej niż 100 sztuk. Dla każdej takiej pozycji podać nazwę firmy zamawiającej, numer zamówienia, zamówioną liczbę sztuk i wartość tej pozycji zamówienia.

SELECT Klienci.Nazwa_firmy, Zamówienia.Nr_zam, Pozycje_zamówień.Ilość_zam,

Pozycje_zamówień.Ilość_zam * Towary.Cena_sprzed

AS Wartość_pozycji

FROM Klienci, Zamówienia, Pozycje_zamówień, Towary

WHERE Klienci.Id_klienta = Zamówienia.Id_klienta

AND Zamówienia.Nr_zam = Pozycje_zamówień.Nr_zam

AND Zamówienia.Data_zam > 15/04/2004

AND Pozycje_zamówień.Nr_towaru = Towary.Nr_towaru

AND Towary.Rodzaj_towaru = `Namiot'

AND Pozycje_zamówień.Ilość_zam >= 100 ;

Złączenie w ramach jednej tabeli

2.13 Zapytanie odwołuje się do tabeli o następującej strukturze:
PRACOWNICY

Id_pracownika	Nazwisko	Imię	Stanowisko	Id_przełożonego

Kolumna Id_przełożonego jest kluczem obcym odwołującym się do klucza tej samej tabeli.
Podać listę pracowników - dla każdego pracownika podać jego identyfikator, nazwisko i imię oraz nazwisko i imię przełożonego.

SELECT P1.Id_pracownika, , P1.Nazwisko, P1.Imię,

P2.Nazwisko AS Nazwisko_przełożonego, P2.Imię AS Imię_przełożonego

FROM Pracownicy P1, Pracownicy P2

WHERE P1.Id_przełożonego = P2.Id_pracownika ;

To złączenie można wyobrażać sobie jako złączenie dwóch egzemplarzy jednej tabeli. Aby w odwołaniach rozróżniać te dwa pojęciowe egzemplarze tabeli Pracownicy, trzeba wprowadzić dwa różne synonimy dla nazwy tabeli. Natomiast synonimy nazw kolumn są wprowadzone tylko dla poprawienia czytelności wyniku.

Użycie złączenia do pobrania par wierszy nie powiązanych wartościami klucza obcego

2.14 Zapytanie odwołuje się do tabel o następującej strukturze:
DOSTAWCY

Id_dostawcy	Nazwa_firmy	Adres	Miasto	Kraj

SKLEPY

Nr_sklepu	Adres	Miasto

Podać wykaz sklepów wraz z danymi dostawców z tego samego miasta

SELECT Sklepy.Nr_sklepu, Sklepy.Miasto, Dostawcy.Nazwa_firmy

FROM Sklepy, Dostawcy

WHERE Sklepy.Miasto = Dostawcy. Miasto ;

To złączenie w istocie definiuje (dynamicznie, w czasie wykonywania programu) takie powiązanie między wierszami dwóch tabel, które nie zostało "zaprogramowane" przez wprowadzenie klucza obcego.

Złączenie zewnętrzne

2.15 Podać pełny wykaz klientów z Krakowa wraz z danymi opisującymi złożone przez nich zamówienia (numer i data). Zakładamy, że są zarejestrowani w bazie danych klienci, dla których nie ma zarejestrowanych zamówień.

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, Zamówienie.Nr_zam,

Zamówienie.Data_zam

FROM Klienci LEFT OUTER JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Klienci.Miasto = Kraków ;

CZĘŚĆ 3 - OBLICZENIA ZBIORCZE

Obliczenia zbiorcze w grupach wierszy we wskazanej tabeli

3.1 Dla każdego klienta podać sumę wartości wszystkich zarejestrowanych zamówień

SELECT Id_klienta, SUM (Wartość_zam) AS Suma_wartości

FROM Zamówienia

GROUP BY Id_klienta ;

3.2 Dla każdego klienta podać średnią wartość zamówienia (policzoną po wszystkich zarejestrowanych zamówieniach pochodzących od tego klienta) i najwyższą wartość zamówienia.

SELECT Id_klienta, AVG(Wartość_zam) AS Średnia_wartość, MAX(Wartość_zam) AS Maks_wartość

FROM Zamówienia

GROUP BY Id_klienta ;

W ramach jednego polecenia SELECT można zlecić wykonanie dwóch lub więcej obliczeń zbiorczych W TYCH SAMYCH GRUPACH WIERSZY.

3.3 Podać średnia cenę dla każdego rodzaju towaru.

SELECT Rodzaj_towaru, AVG(Cena_sprzed) AS Średnia_cena

FROM Towary

GROUP BY Rodzaj_towaru ;

Obliczenie zbiorcze w jednej grupie, zawierającej wszystkie wiersze wskazanej tabeli

3.4 Podać średnią wartość zamówienia (policzoną po wszystkich zarejestrowanych zamówieniach).

SELECT AVG(Wartość_zam) AS Średnia_wartość

FROM Zamówienia ;

Obliczenia zbiorcze w tabeli otrzymanej w wyniku operacji selekcji

3.5 Dla każdego klienta podać sumę wartości zamówień złożonych po 02/01/2004.

Obliczenie zbiorcze jest wykonywane w grupach wierszy dla poszczególnych klientów, w tabeli otrzymanej w wyniku selekcji wg daty zamówienia.

SELECT Id_klienta, SUM (Wartość_zam) AS Suma_wartości

FROM Zamówienia

WHERE Data_zam > '02/01/2004'

GROUP BY Id_klienta ;

3.6 Dla każdego towaru podać liczbę sztuk otrzymaną w dostawach przyjętych po 02/01/2004.

Analogicznie jak w poprzednim zadaniu, obliczenie zbiorcze jest wykonywane w grupach wierszy dla poszczególnych towarów, w tabeli otrzymanej w wyniku selekcji wg daty zamówienia dostawy.

SELECT Nr_towaru, SUM(Ilość) AS Suma_dostaw

FROM Dostawy

WHERE Data_przyjęcia > '02/01/2004'

GROUP BY Nr_towaru ;

3.7 Podać sumę wartości zamówień złożonych po 02/01/2004 przez klienta o identyfikatorze 2345.

Obliczenie zbiorcze jest wykonywane w jednej grupie, zawierającej wszystkie wiersze tabeli otrzymanej w wyniku selekcji wg daty zamówienia i identyfikatora klienta.

SELECT SUM(Wartość_zam) AS Suma_wartości

FROM Zamówienia

WHERE Data_zam > '02/01/2004'

AND Id_klienta = 2345 ;

Obliczenia zbiorcze w tabeli otrzymanej w wyniku sekwencji złączenie- selekcja

3.8 Dla każdego rodzaju towaru podać całkowitą liczbę sztuk towarów tego rodzaju przyjętych w dostawach po 02/01/2004.

SELECT Towary.Rodzaj_towaru, SUM(Dostawy.Ilość) AS Suma_dostaw

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Data_przyjęcia > '02/01/2004'

GROUP BY Towary.Rodzaj_towaru ;

Obliczenie zbiorcze jest wykonywane w kolumnie pochodzącej z tabeli DOSTAWY. Złączenie DOSTAWY-TOWARY jest potrzebne, aby można było pogrupować wiersze wg rodzaju towaru. Dodatkowa operacja selekcji wybiera dostawy z zadanego okresu. Obie te operacje są wykonywane PRZED grupowaniem i obliczeniem zbiorczym.

3.9 Podać całkowitą liczbę sztuk wszystkich namiotów przyjętych w dostawach po 02/01/2004.

SELECT SUM(Dostawy.Ilość) AS Suma_dostaw

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Dostawy.Data_przyjęcia > '02/01/2004'

AND Towary.Rodzaj_towaru = `Namiot' ;

Obliczenie zbiorcze jest wykonywane w kolumnie pochodzącej z tabeli DOSTAWY. Dołączenie wierszy z tabeli TOWARY przed obliczeniem zbiorczym jest potrzebne, aby można było wybrać dostawy dla towarów wskazanego rodzaju, spełniające ponadto warunek nałożony na datę zamówienia dostawy. Obliczenie zbiorcze jest wykonywane w jednej grupie, obejmującej całą tabelę otrzymaną w wyniku sekwencji złączenie-selekcja.

3.10 Dla każdego rodzaju towaru i każdego producenta podać całkowitą liczbę sztuk towarów tego rodzaju przyjętych w dostawach po 02/01/2004.

SELECT Towary.Rodzaj_towaru, Towary.Nazwa_producenta, SUM(Dostawy.Ilość) AS Suma_dostaw

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Dostawy.Data_przyjęcia > '02/01/2004'

GROUP BY Towary.Rodzaj_towaru, Towary.Nazwa_producenta ;

Dołączenie wierszy z tabeli TOWARY jest potrzebne, aby pogrupować wiersze dostaw wg kombinacji (Rodzaj_towaru, Nazwa_producenta). Grupowanie wg kombinacji wartości z dwóch kolumn NIE OZNACZA grupowania 2-poziomowego. Obliczenie zbiorcze jest wykonywane TYLKO dla poszczególnych kombinacji wartości (Rodzaj_towaru, Nazwa_producenta), nie ma obliczenia zbiorczego "wyższego poziomu” dla poszczególnych wartości z kolumny Rodzaj_towaru.

3.11 Dla każdego modelu plecaka podać numer katalogowy i całkowitą liczbę sztuk przyjętych w dostawach po 02/01/2004.

SELECT Towary.Nr_towaru, SUM(Dostawy.Ilość) AS Suma_dostaw

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Dostawy.Data_przyjęcia > '02/01/2004'

AND Towary.Rodzaj_towaru = `Plecak'

GROUP BY Dostawy.Nr_towaru ;

Przed obliczeniem zbiorczym jest potrzebna selekcja wierszy wg warunku nałożonego na datę przyjęcia oraz warunku nałożonego na rodzaj towaru-w celu sprawdzenia tego ostatniego warunku tabelę DOSTAWY złączono z tabelą TOWARY

3.12 Polecenie jak w zad. 3.11, ale każdy wiersz tabeli wynikowej powinien zawierać zarówno numer katalogowy, jak i nazwę producenta oraz oznaczenie modelu.

SELECT Towary.Nr_towaru, Towary.Nazwa_producenta, Towary.Model,

SUM(Dostawy.Ilość) AS Suma_dostaw

FROM Dostawy JOIN Towary ON Dostawy.Nr_towaru = Towary.Nr_towaru

WHERE Dostawy.Data_przyjęcia > '02/01/2004'

AND Towary.Rodzaj_towaru = `Plecak'

GROUP BY Towary.Nr_towaru, Towary.Nazwa_producenta, Towary.Model ;

Grupowanie wg kombinacji (Nr_katalog_towaru, Nazwa_producenta, Model) jest równoznaczne z grupowaniem wg wartości w kolumnie Nr_katalog_towaru. Jednak każda kolumna, która ma być włączona do tabeli wynikowej, oczywiście z wyjątkiem kolumny obliczenia zbiorczego, musi być włączona do kombinacji kolumn określającej grupowanie wierszy.

3.13 Polecenie jak w zad. 3.1, ale wiersz tabeli wynikowej powinien zawierać oprócz identyfikatora klienta również nazwę firmy i miasto. Dla każdego klienta podać: identyfikator, nazwę firmy, miasto oraz sumę wartości wszystkich zarejestrowanych zamówień

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, Klienci.Miasto,

SUM (Wartość_zam) AS Suma_wartości

FROM Zamówienia JOIN Klienci ON Zamówienia.Id_klienta = Klienci.Id_klienta

GROUP BY Klienci.Id_klienta, Klienci.Nazwa_firmy, Klienci.Miasto ;

Analogicznie jak w poprzednim zadaniu, do obliczenia zbiorczego wystarczyłoby grupowanie wg Id_klienta - grupowanie wg kombinacji (Id_klienta, Nazwa_firmy, Miasto) wynika z żądania, aby wszystkie te kolumny były włączone do tabeli wynikowej.

3.14 Dla każdego modelu towaru, na który były składane zamówienia w okresie od 15/04/2004 do 30/04/2004 podać średnią i największą liczbę sztuk zamawianych w ramach jednej pozycji zamówienia. W wierszu wyniku podać pełny opis towaru: numer katalogowy, rodzaj, nazwę producenta i oznaczenie modelu.

SELECT Towary.Nr_towaru, Towary.Rodzaj_towaru, Towary.Nazwa_producenta, Towary.Model,

AVG(Pozycje_zamówień .Ilość_zam) AS (Średnia_ilość),

MAX(Pozycje_zamówień.Ilość_zam) AS (Maks_ilość)

FROM Zamówienia, Pozycje_zamówień, Towary

WHERE Zamówienia.Nr_zam = Pozycje_zamówień.Nr_zam

AND Pozycje_zamówień.Nr_towaru = Towary.Nr_towaru

AND Zamówienia.Data_zam BETWEEN '15/04/2004' AND '30/04/2004'

GROUP BY Towary.Nr_towaru, Towary.Rodzaj_towaru, Towary.Nazwa_producenta, Towary.Model ;

Użycie funkcji zbiorczej COUNT

3.15 Dla każdego miasta, w którym są klienci, podać ich liczbę.

SELECT Miasto, COUNT(*) AS Liczba_klientów

FROM Klienci

GROUP BY Miasto ;

3.16 Podać liczbę klientów z Gdańska.

SELECT COUNT(*) AS Liczba_klientów

FROM Klienci

WHERE Miasto = 'Gdańsk' ;

3.17 Podać, ile zamówień złożył każdy z klientów po 02/01/2004. Wiersz tabeli wynikowej powinien zawierać zarówno identyfikator klienta, jak i nazwę firmy.

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy, COUNT (*) AS Liczba_zamówień

FROM Zamówienia JOIN Klienci ON Zamówienia.Id_klienta = Klienci.Id_klienta

WHERE Zamówienia.Data_zam > `02/01/2004'

GROUP BY Klienci.Id_klienta, Klienci.Nazwa_firmy ;

3.18 Podać, ile zamówień złożyła firma o nazwie `ABC' po 02/01/2004.

SELECT COUNT (*) AS Liczba_zamówień

FROM Zamówienia JOIN Klienci ON Zamówienia.Id_klienta = Klienci.Id_klienta

WHERE Zamówienia.Data_zam > `02/01/2004'

AND Klienci.Nazwa_firmy = `ABC' ;

3.19 Podać w ilu miastach są klienci.

SELECT COUNT (DISTINCT Miasto) AS Liczba_miast

FROM Klienci ;

Obliczenia zbiorcze z selekcją wierszy wyniku - użycie sekcji HAVING

3.20 Podać wykaz modeli plecaków, dla których całkowita ilość zamówiona u wszystkich dostawców w okresie od '15/04/2004' do 14/05/2004' przekracza 2000 sztuk. Dla każdego z nich podać numer katalogowy, nazwę producenta, oznaczenie modelu i całkowitą liczbę sztuk zamówionych u wszystkich dostawców.

SELECT Towary.Nr_towaru, Towary.Nazwa_producenta, Towary.Model, SUM(Dostawy.Ilość_zam) AS Suma_zam

FROM Towary, Dostawy

WHERE Towary.Nr_towaru = Dostawy.Nr_towaru

AND Towary.Rodzaj_towaru = 'Plecak'

AND Dostawy.Data_zam BETWEEN '15/04/2004' AND 14/05/2004'

GROUP BY Towary.Nr_towaru, Towary.Nazwa_producenta, Towary.Model

HAVING SUM(Dostawy.Ilość_zam) > 2000 ;

Tabela wynikowa zawiera te wiersze wyniku obliczenia zbiorczego, w których obliczona suma spełnia zadany warunek

CZĘŚĆ 4 - ZAPYTANIA Z ZAGNIEŻDŻONYMI PODZAPYTANIAMI

PODZAPYTANIA W SEKCJI (KLAUZULI) WHERE

Podzapytania użyte z operatorem IN, NOT IN

4.1 Podać wykaz towarów oferowanych przez dostawcę o numerze identyfikacyjnym 235.

SELECT Rodzaj_towaru, Nazwa_producenta, Model

FROM Towary

WHERE Nr_towaru IN

( SELECT Nr_towaru

FROM Oferty_dostawców

WHERE Id_dostawcy = 235 ) ;

Sformułowanie zadania, które odzwierciedla konstrukcję podaną wyżej jest następujące: podać wykaz towarów, takich że numer katalogowy towaru należy do zbioru numerów katalogowych towarów występujących w ofertach od dostawcy o numerze identyfikacyjnym 235. Zapisane wyżej wyrażenie SELECT jest równoważne wyrażeniu jednopoziomowemu z przykł. 2.8 :

SELECT Towary.Rodzaj_towaru, Towary.Nazwa_producenta, Towary.Model

FROM Towary JOIN Oferty_dostawców

ON Towary.Nr_towaru =

Oferty_dostawców.Nr_towaru

WHERE Oferty_dostawców.Id_dostawcy = 235 ;

4.2 Podać wykaz klientów (identyfikator, nazwa firmy), dla których są zamówienia złożone po 15/04/2004.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE Id_klienta IN

( SELECT DISTINCT Id_klienta

FROM Zamówienia

WHERE Zamówienia.Data_zam > 15/04/2004) ;

Konstrukcja podobna jak w poprzednim zadaniu. Równoważne wyrażenie jednopoziomowe:

SELECT DISTINCT Klienci.Id_klienta, Klienci.Nazwa_firmy

FROM Klienci JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Zamówienia.Data_zam > 15/04/2004 ;

4.3 Podać wykaz klientów (identyfikator, nazwa firmy), dla których nie ma zamówień o wartości większej niż 20 000 złotych.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE Id_klienta NOT IN

( SELECT DISTINCT Id_klienta

FROM Zamówienia

WHERE Zamówienia.Wartość_zam > 20000) ;

Tabela wynikowa zawiera wiersze opisujące klientów, dla których WSZYSTKIE zarejestrowane zamówienia spełniają warunek Wartość_zam < = 20000, oraz wiersze opisujące klientów, dla których w ogóle nie ma zarejestrowanych zamówień. Czy można zapisać to zapytanie w postaci prostego wyrażenia jednopoziomowego?

4.4 Podać wykaz zamówień (numer zamówienia i wartość) złożonych przez klientów z Gdańska w okresie od 15/04/2004 do 30/04/2004.

SELECT Nr_zam, Wartość_zam

FROM Zamówienia

WHERE Zamówienia.Data_zam BETWEEN '15/04/2004' AND '30/04/2004'

AND Id_klienta IN

( SELECT Id_klienta

FROM Klienci

WHERE Klienci.Miasto = 'Gdańsk') ;

Warunek odwołujący się do atrybutu klienta jest zrealizowany przez konstrukcję podobną jak w zad. 4.1, 4.2. Równoważne wyrażenie jednopoziomowe zostało przedstawione w zad. 2.7:

SELECT Zamówienia.Nr_zam, Zamówienia.Wartość_zam

FROM Klienci JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Zamówienia.Data_zam BETWEEN '15/04/2004' AND '30/04/2004'

AND Klienci.Miasto = 'Gdańsk' ;

4.5 Podać wykaz modeli namiotów (nazwa producenta, model), dla których są oferty od dostawców z Niemiec.

SELECT Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru = 'Namiot'

AND Nr_towaru IN

( SELECT DISTINCT Nr_towaru

FROM Oferty_dostawców

WHERE Id_dostawcy IN

( SELECT Id_dostawcy

FROM Dostawcy

WHERE Kraj = 'Niemcy' ) ) ;

Sformułowanie zadania odpowiadające strukturze tego 3-poziomowego wyrażenia SELECT:
podać wykaz modeli namiotów, takich że numer katalogowy danego modelu należy do zbioru numerów katalogowych występujących w ofertach, takich że identyfikator dostawcy należy do zbioru identyfikatorów dostawców z Niemiec

Równoważnym wyrażeniem jednopoziomowym jest zapytanie przedstawione w zad. 2.11:

SELECT Towary.Nazwa_producenta, Towary.Model

FROM Towary, Oferty_dostawców, Dostawcy

WHERE Towary.Nr_towaru = Oferty_dostawców.Nr_towaru

AND Oferty_dostawców.Id_dostawcy = Dostawcy.Id_dostawcy

AND Towary.Rodzaj_towaru = 'Namiot'

AND Dostawcy.Kraj = 'Niemcy' ;

Zauważmy, że w przykładach 4.1-4.5 podzapytanie zwraca tabelę jednokolumnową. Jest to warunek poprawności konstrukcji <wyrażenie skalarne> <operator IN/NOT IN> <podzapytanie>

Podzapytania użyte z operatorem porównania

4.6 Podać wykaz zamówień (numer zamówienia, data) złożonych przez firmę o nazwie JKL po 15/04/2004.

SELECT Nr_zam, Data_zam

FROM Zamówienia

WHERE Data_zam > '15/04/2004'

AND Id_klienta =

( SELECT Id_klienta

FROM Klienci

WHERE Nazwa_firmy = 'JKL' ) ;

Równoważne wyrażenie jednopoziomowe:

SELECT Zamówienie.Nr_zam, Zamówienie.Data_zam

FROM Klienci JOIN Zamówienia ON Klienci.Id_klienta = Zamówienia.Id_klienta

WHERE Data_zam > '15/04/2004'

AND Klienci.Nazwa_firmy = 'JKL' ;

Zauważmy, że użyta w zadaniu 4.6, jak i w następnych przykładach, konstrukcja

<wyrażenie skalarne> <operator_porównania> <podzapytanie>

jest poprawna tylko wtedy, gdy podzapytanie zwraca tabelę jednowierszową, jednokolumnową. Nasuwa się jednak uzasadniona wątpliwość co do poprawności porównania wyrażenia skalarnego z tabelą. Należy więc wyjaśnić, że w tym kontekście wyrażenie skalarne jest interpretowane jako tzw. konstruktor wiersza, który zwraca jeden wiersz z jedną kolumną, zawierający oczywiście (w jedynej kolumnie) wartość wyrażenia.

4.7 Podać wykaz dostawców (identyfikator, nazwa firmy), którzy mają siedzibę w tym samym mieście, co dostawca o nazwie ABC.

SELECT Id_dostawcy, Nazwa_firmy

FROM Dostawcy

WHERE Miasto =

( SELECT Miasto

FROM Dostawcy

WHERE Nazwa_firmy = 'ABC' ) ;

Równoważne wyrażenie jednopoziomowe wymaga wykonania złączenia w ramach jednej tabeli. Zauważmy, jak bardzo "sztucznie" wygląda ta konstrukcja w porównaniu z zapytaniem dwupoziomowym:

SELECT Dost1.Id_dostawcy, Dost1.Nazwa_firmy

FROM Dostawcy Dost1, Dostawcy Dost2

WHERE Dost1.Miasto = Dost2.Miasto

AND Dost2.Nazwa_firmy = 'ABC' ;

4.8 Podać wykaz śpiworów (numer katalogowy, producent, oznaczenie modelu, cena) o cenie niższej niż cena śpiwora Suseł 3 firmy XYZ.

SELECT Nr_towaru, Nazwa_producenta, Model, Cena_sprzed

FROM Towary

WHERE Rodzaj_towaru = 'Śpiwór'

AND Cena_sprzed <

( SELECT Cena_sprzed

FROM Towary

WHERE Rodzaj_towaru = 'Śpiwór'

AND Nazwa_producenta = 'XYZ'

AND Model = 'Suseł 3') ;

4.9 Zapytanie odwołuje się do tabeli o następującej strukturze:

PRACOWNICY

Id_pracownika	Nazwisko	Imię	Stanowisko	Wynagrodzenie

Podać listę pracowników pobierających wynagrodzenie wyższe, niż pracownik o numerze identyfikacyjnym 125.

SELECT Id_pracownika, Nazwisko, Imię

FROM Pracownicy

WHERE Wynagrodzenie >

(SELECT Wynagrodzenie

FROM Pracownicy

WHERE Id_pracownika = 125 ) ;

Podzapytania wykonujące obliczenia zbiorcze, użyte z operatorem porównania

4.10 Podać wykaz modeli plecaków produkcji firmy UWZ, których cena jest wyższa od średniej obliczonej dla wszystkich plecaków.

SELECT Nr_towaru, Model

FROM Towary

WHERE Rodzaj_towaru = 'Plecak'

AND Nazwa_producenta = 'UWZ'

AND Cena_sprzed >

( SELECT AVG (Cena_sprzed)

FROM Towary

WHERE Rodzaj_towaru = 'Plecak' ) ;

Jeszcze raz zauważmy, że porównanie wyrażenia skalarnego z wynikiem podzapytania jest poprawne tylko wtedy, gdy podzapytanie zwraca tabelę jednowierszową, jednokolumnową. Dlatego jeśli w takim kontekście używamy podzapytania wykonującego obliczenie zbiorcze, to może to być tylko obliczenie wykonywane w jednej grupie

4.11 Podać wykaz zamówień (numer zamówienia, identyfikator klienta) z datą późniejszą niż 15/05/2004, o wartości przekraczającej średnią wartość zamówień wszystkich klientów w okresie od 15/04/2004 do 15/05/2004.

SELECT Nr_zam, Id_klienta

FROM Zamówienia

WHERE Data_zam > '15/05/2004'

AND Wartość_zam >

( SELECT AVG (Wartość_zam)

FROM Zamówienia

WHERE Data_zam

BETWEEN '15/04/2004'

AND '15/05/2004') ;

4.12 Podać wykaz klientów, dla których suma wartości zamówień złożonych w ciągu ostatnich 90 dni przekracza 50 000 złotych.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE 50000 <

( SELECT SUM (Wartość_zam)

FROM Zamówienia

WHERE Zamówienia.Id_klienta = Klienci.Id_klienta

AND Sysdate - Zamówienia.Data_zam < = 90 ) ;

Zadanie wymaga zastosowania korelacji między podzapytaniem i zapytaniem zewnętrznym. Korelacja wyraża się odwołaniem w podzapytaniu do tabeli z zapytania zewnętrznego. Podzapytanie jest wykonywane dla każdego rozpatrywanego wiersza tabeli w zapytaniu zewnętrznym - i dla każdego wiersza zwraca w ogólności inny wynik. Tutaj dla każdego wiersza tabeli Klienci, przetwarzanej w zapytaniu zewnętrznym, jest wykonywane podzapytanie obliczające sumę wartości zamówień dla danego klienta (w zadanym okresie czasu)

Poniżej równoważne jednopoziomowe wyrażenie SELECT-obliczenie zbiorcze jest wykonywane w grupach wierszy tabeli otrzymanej ze złączenia tabel Zamówienia i Klienci. Wiersze z tabeli Klienci są potrzebne do uzyskania w wyniku nazwy firmy.

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy

FROM Klienci JOIN Zamówienia ON Zamówienia.Id_klienta = Klienci.Id_klienta

WHERE Sysdate - Zamówienia.Data_zam < = 90

GROUP BY Klienci.Id_klienta, Klienci.Nazwa_firmy

HAVING SUM (Zamówienia.Wartość_zam) > 50000 ;

4.13 Podać wykaz towarów (numer katalogowy, oznaczenie modelu), których cena jest wyższa od średniej obliczonej dla towarów danego rodzaju.

Zadanie pozornie podobne do zadania 4.10, ale tutaj interesują nas towary wszystkich rodzajów, takie że cena towaru jest wyższa od średniej policzonej dla towarów tego samego rodzaju, co rozpatrywany towar.

SELECT Nr_towaru, Model

FROM Towary T1

WHERE Cena_sprzed >

( SELECT AVG (Cena_sprzed)

FROM Towary T2

WHERE T2.Rodzaj_towaru = T1.Rodzaj_towaru ) ;

Podobnie jak w poprzednim zadaniu, trzeba użyć podzapytania skorelowanego z zapytaniem zewnętrznym. W tym przykładzie tabela w zapytaniu zewnętrznym jest tą samą tabelą, co tabela przetwarzana w podzapytaniu, więc trzeba wprowadzić synonimy (T1,T2).Dla każdego wiersza tabeli Towary, przetwarzanej w zapytaniu zewnętrznym, jest wykonywane podzapytanie obliczające średnią cenę dla towarów danego rodzaju.

4.14 Podać numery katalogowe tych artykułów, których zapas w magazynie jest mniejszy niż ilość zamówiona przez klientów w ciągu ostatnich 3 dni.

SELECT Nr_towaru

FROM Towary

WHERE Ilość_w_mag <

( SELECT SUM (Pozycje_zamówień.Ilość_zam)

FROM Pozycje_zamówień JOIN Zamówienia

ON Pozycje_zamówień.Nr_zam =

Zamówienia.Nr_zam

WHERE Sysdate - Zamówienia.Data_zam <= 2

AND Pozycje_zamówień.Nr_towaru = Towary.Nr_towaru ) ;

Dla każdego wiersza tabeli Towary jest wyznaczana suma zamówionych ilości danego towaru z ostatnich 3 dni. Suma jest obliczana w tabeli Pozycje_zamówień - złączenie z tabelą Zamówienia jest potrzebne tylko ze względu na warunek nałożony na datę zamówienia.

4.15 Podać numery zamówień złożonych po 15/04/2004, których wartość przekracza średnią wartość zamówień z tego samego dnia.

SELECT Nr_zam

FROM Zamówienia Zam1

WHERE Data_zam > '15/04/2004'

AND Wartość_zam >

( SELECT AVG (Wartość_zam)

FROM Zamówienia Zam2

WHERE Zam2.Data_zam = Zam1.Data_zam ) ;

Analogicznie jak w zad 4.13, korelacja wymaga wprowadzenia synonimów dla tabeli Zamówienia.

4.16 Podać wykaz modeli namiotów, takich że jest więcej niż 10 klientów, z których każdy zamówił w okresie ostatnich 90 dni więcej niż 50 sztuk danego modelu.

SELECT Nr_towaru, Model

FROM Towary

WHERE Rodzaj_towaru = 'Namiot'

AND 10 <

( SELECT COUNT (*)

FROM Klienci

WHERE 50 <

( SELECT SUM (Pozycje_zamówień.Ilość_zam)

FROM Pozycje_zamówień JOIN Zamówienia

ON Pozycje_zamówień.Nr_zam = Zamówienia.Nr_zam

WHERE Sysdate - Data_zam < = 90

AND Pozycje_zamówień.Nr_towaru = Towary.Nr_towaru

AND Zamówienia.Id_klienta = Klienci.Id_klienta ) ) ;

W tym 3-poziomowym zapytaniu występuje korelacja podzapytania poziomu 3 zarówno z zapytaniem na poziomie 2 (odwołanie do tabeli Klienci), jak i z zapytaniem na poziomie 1 (odwołanie do tabeli Towary). Poniżej sformułowanie zadania odpowiadające strukturze tego 3-poziomowego wyrażenia:

podać wykaz modeli namiotów, takich że jest więcej niż dziesięciu klientów, takich że jest więcej niż 50 sztuk danego modelu zamówionych w sumie w ciągu ostatnich 90 dni przez danego klienta

Podzapytanie użyte z operatorem ALL

4.17 Podać wykaz tych modeli plecaków, których cena jest wyższa niż cena najdroższego śpiwora.

SELECT Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru = 'Plecak'

AND Cena_sprzed > ALL

( SELECT Cena_sprzed

FROM Towary

WHERE Rodzaj_towaru = 'Śpiwór' ) ;

Warunek postaci

<wyrażenie skalarne> <operator porównania> <operator ALL> <podzapytanie>

gdzie podzapytanie zwraca tabelę jednokolumnową, jest spełniony wtedy i tylko wtedy, gdy dla każdego wiersza tabeli zwróconej przez podzapytanie porównanie wyrażenia skalarnego z wartością w tym wierszu przyjmuje wartość logiczną prawda.

Poniżej przedstawiono równoważne zapytanie, w którym zamiast operatora ALL użyto funkcji MAX w podzapytaniu:

SELECT Nazwa_producenta, Model

FROM Towary

WHERE Rodzaj_towaru = 'Plecak'

AND Cena_sprzed >

( SELECT MAX (Cena_sprzed)

FROM Towary

WHERE Rodzaj_towaru = 'Śpiwór'

) ;

Podzapytania użyte z operatorem EXISTS, NOT EXISTS

4.18 Podać wykaz klientów, dla których są zamówienia złożone po 15/04/2004.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE EXISTS

( SELECT *

FROM Zamówienia

WHERE Zamówienia.Id_klienta = Klienci.Id_klienta

AND Data_zam > '15/04/2004'

) ;

Symbol * w tym kontekście jest użyty po to, aby zaznaczyć, że wartość logiczna wyrażenia

EXISTS <podzapytanie>

jest niezależna od tego, jakie kolumny to podzapytanie zwraca.

Zauważmy, że podzapytanie użyte z operatorem EXISTS (również NOT EXISTS) jest z reguły podzapytaniem skorelowanym. Użycie w tym kontekście podzapytania nieskorelowanego byłoby dość niezwykłe - w takim przypadku zapytanie zewnętrzne zwracałoby albo całą tabelę, albo tabelę pustą, zależnie od wyniku podzapytania.

Poniżej równoważne zapytanie, w którym użyto konstrukcji <operator IN> <podzapytanie>. Zauważmy, że podzapytanie nie jest skorelowane z zapytaniem zewnętrznym - zamiast korelacji mamy tutaj sprawdzenie, czy Id_klienta należy do zbioru identyfikatorów z wybranych zamówień.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE Id_klienta IN

( SELECT DISTINCT Id_klienta

FROM Zamówienia

WHERE Data_zam > '15/04/2004' ) ;

Równoważne wyrażenie jednopoziomowe:

SELECT Klienci.Id_klienta, Klienci.Nazwa_firmy

FROM Klienci, Zamówienia

WHERE Klienci.Id_klienta = Zamówienia.Id_klienta

AND Zamówienia.Data_zam > '15/04/2004' ) ;

4.19 Podać wykaz klientów, dla których nie ma zamówień o wartości większej niż 20 000 złotych.

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE NOT EXISTS

( SELECT *

FROM Zamówienia

WHERE Zamówienia.Id_klienta = Klienci.Id_klienta

AND Zamówienia.Wartość_zam > 20000 ) ;

Równoważne zapytanie zawierające konstrukcję <operator NOT IN> <podzapytanie>:

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE Id_klienta NOT IN

( SELECT DISTINCT Id_klienta

FROM Zamówienia

WHERE Wartość_zam > 20000 ) ;

4.20 Podać wykaz artykułów oferowanych przez dostawcę o numerze identyfikacyjnym 235.

SELECT Rodzaj_towaru, Nazwa_producenta, Model

FROM Towary

WHERE EXISTS

( SELECT *

FROM Oferty_dostawców

WHERE Oferty_dostawców.Nr_towaru =

Towary.Nr_towaru

AND Oferty_dostawców.Id_dostawcy = 235

);

Równoważne zapytanie z operatorem IN przedstawiono w zadaniu 4.1, a zapytanie jednopoziomowe-w zadaniu 2.8

4.21 Podać wykaz klientów spełniających następujący warunek: wartość każdego zamówienia od danego klienta przekracza 10000 złotych.

Kwantyfikator ogólny ("dla wszystkich x") zaproponowano dopiero w ramach standardu SQL3. W implementacjach języka, w których kwantyfikator ogólny nie występuje, trzeba skorzystać ze znanych praw logiki (zastąpienie kwantyfikatora ogólnego negacją kwantyfikatora szczegółowego i zanegowanie wyrażenia w zasięgu kwantyfikatora). Powyższe zadanie trzeba sformułować w następujący sposób:

podać wykaz klientów,
dla których NIE ISTNIEJE zamówienie o wartości NIE WIĘKSZEJ niż 10 000 złotych

SELECT Id_klienta, Nazwa_firmy

FROM Klienci

WHERE NOT EXISTS

( SELECT *

FROM Zamówienia

WHERE Zamówienia.Id_klienta = Klienci.Id_klienta

AND Wartość_zam < = 10000 ) ;

Zauważmy, że przyjęto założenie, że dla każdego klienta zarejestrowanego w bazie danych istnieje co najmniej jedno zarejestrowane zamówienie. Jak należałoby rozbudować zapytanie, aby uniknąć błędnego wyniku w przypadku, gdyby to założenie nie było prawdziwe?

4.22 Podać wykaz tych dostawców, którzy oferują wszystkie modele namiotów producenta XYZ.

Analogicznie jak w poprzednim zadaniu, trzeba użyć negacji kwantyfikatora szczegółowego i zanegować wyrażenie w zasięgu kwantyfikatora:

podać wykaz dostawców,
dla których NIE ISTNIEJE model namiotu produkowany przez XYZ (występujący w tabeli TOWARY)
dla którego NIE MA oferty od danego dostawcy.

SELECT Id_dostawcy, Nazwa_firmy

FROM Dostawcy

WHERE NOT EXISTS

( SELECT *

FROM Towary

WHERE Rodzaj_towaru = 'Namiot'

AND Nazwa_producenta = 'XYZ'

AND NOT EXISTS

( SELECT *

FROM Oferty_dostawców

WHERE Oferty_dostawców.Id_dostawcy = Dostawcy.Id_dostawcy

AND Oferty_dostawców.Nr_towaru = Towary.Nr_towaru

)

);

Przyjęto założenie, że istnieje co najmniej jeden model namiotu produkowany przez producenta XYZ, zarejestrowany w bazie danych. Jak należałoby uzupełnić zapytanie, aby uniknąć błędnego wyniku w przypadku, gdyby to założenie nie było prawdziwe?

PODZAPYTANIA W SEKCJI (KLAUZULI) FROM

4.23 Podać najwyższą wartość spośród średnich cen policzonych dla poszczególnych rodzajów towarów.

SELECT MAX (Średnie.Średnia_cena)

FROM (SELECT Rodzaj_towaru, AVG(Cena_sprzed) AS Średnia_cena

FROM Towary

GROUP BY Rodzaj_towaru ) Średnie ;

Podzapytanie zwraca tabelę średnich cen dla poszczególnych rodzajów towarów. Obliczenie zbiorcze w zewnętrznym zapytaniu (funkcja MAX) jest wykonywane na tabeli wynikowej podzapytania

4.24 Dla okresu od 15/04/2004 do 30/04/2004 podać średnią sumę wartości zamówień od jednego klienta, policzoną dla poszczególnych miast.

SELECT Klienci.Miasto, AVG (Sumy.Suma_wartości) AS Średnia_suma

FROM Klienci, (SELECT Id_klienta, SUM(Wartość_zam) AS Suma_wartości

FROM Zamówienia

WHERE Data_zam BETWEEN '15/04/2004' AND '30/04/2004'

GROUP BY Id_klienta) Sumy

WHERE Klienci.Id_klienta = Sumy.Id_klienta

GROUP BY Klienci.Miasto ;

Podzapytanie zwraca tabelę zawierającą sumę wartości zamówień dla każdego klienta. W zapytaniu zewnętrznym wiersze są grupowane wg miasta (w tym celu jest wykonywane złączenie z tabelą Klienci), w każdej grupie jest liczona średnia.

4.25 Podać wykaz towarów (numer katalogowy, oznaczenie modelu), których cena jest wyższa od średniej obliczonej dla towarów danego rodzaju.

Najbardziej "naturalnym" rozwiązaniem jest konstrukcja z podzapytaniem w sekcji WHERE, przedstawiona w zad. 4.13. Inna możliwość to podzapytanie zagnieżdżone w sekcji FROM:

SELECT Towary.Nr_towaru, Towary.Model

FROM Towary, (SELECT Rodzaj_towaru, AVG(Cena_sprzed) AS Średnia_cena

FROM Towary

GROUP BY Rodzaj_towaru ) Średnie

WHERE Towary. Rodzaj_towaru = Średnie. Rodzaj_towaru

AND Towary.Cena_sprzed > Średnie.Średnia_cena ;

Podzapytanie zwraca tabelę zawierającą średnią cenę dla każdego rodzaju towaru. Właściwy (ze względu na rodzaj towaru) wiersz tej tabeli jest dołączany do wiersza tabeli Towary w celu sprawdzenia warunku.

Przykłady zapytań w języku SQL Strona 9 z 18

18

---