Ćwiczenie 3
I. Operator połączenia zewnętrznego
Polecenie SELECT może służyć do połączenia więcej niż dwóch relacji wymienionych w klauzuli FROM. Jednak w celu połączenia n relacji konieczne jest określenie co najmniej n-1 warunków połączenia. Wiersze nie spełniające warunku połączenia mogą być wyświetlone w wyniku zapytania wtedy, gdy zastosuje się operator połączenia zewnętrznego (+). Umieszcza się go po prawej stronie tej relacji, w której brakuje krotek umożliwiających wyświetlenie wszystkich krotek drugiej relacji po ich połączeniu. Operator (+) powoduje utworzenie dodatkowych pustych krotek .
np. SELECT NAZWISKO, Z.ID_ZESP, NAZWA FROM PRAC p, ZESP z
WHERE p.ID_ZESP(+) = z.ID_ZESP;
II. Zagnieżdżanie zapytań.
Zagnieżdżanie zapytań stosuje się wtedy, gdy trzeba rozbudować klauzulę WHERE zapytania zewnętrznego (lub klauzulę HAVING) tak by uwzględniała różne wyniki uzyskane po wykonaniu zapytania wewnętrznego (podzapytania).
W trybie nieskorelowanym podzapytanie jest wykonywane jest jeden raz jako pierwsze, a jego wyniki są przesyłane do zapytania zewnętrznego.
Jeśli podzapytanie wyznacza pojedynczą krotkę, to w klauzuli WHERE zapytania zewnętrznego stosuje się najczęściej operatory porównania: =, >, >=, ...
Jeśli podzapytanie wyznacza więcej niż jedną krotkę, w klauzuli WHERE zapytania zewnętrznego stosuje się operatory: IN, ANY lub ALL.
np. 1) SELECT * FROM PRAC WHERE PLACA_POD =
(SELECT min(PLACA_POD) FROM PRAC);
2) SELECT * FROM PRAC WHERE (PLACA_POD, ID_ZESP) IN
(SELECT min(PLACA_POD), ID_ZESP FROM PRAC
GROUP BY ID_ZESP);
3) SELECT NAZWISKO, PLACA_POD FROM PRAC WHERE PLACA_POD>
ALL (SELECT PLACA_POD FROM PRAC WHERE ID_ZESP = 10);
W trybie skorelowanym najpierw wykonywane jest zapytanie zewnętrzne, a po nim zapytanie wewnętrzne na każdej z wartości przekazanych z zapytania zewnętrznego. Konieczne jest wtedy zastosowanie aliasów relacji, na których operuje zapytanie zewnętrzne i odwołanie się do nich w podzapytaniu.
np. SELECT NAZWISKO,PLACA_POD, ETAT FROM PRAC p
WHERE PLACA_POD > (SELECT avg(PLACA_POD) FROM PRAC
WHERE ETAT =p.ETAT);
Sprawdzenie czy podzapytanie wybrało jakąś wartość realizuje się przy użyciu operatorów EXISTS lub NOT EXISTS.
np. SELECT ID_PRAC, NAZWISKO, ETAT FROM PRAC p WHERE NOT EXISTS
(SELECT ID_PRAC FROM PRAC
WHERE ETAT = p.ETAT AND ID_PRAC != p.ID_PRAC);
Zadania do samodzielnego wykonania:
Zad. 1. Znaleźć pracowników, którzy mają zarobki wyższe niż najwyższe zarobki w zespole INFORMATYKA.
SELECT * FROM PRAC WHERE PLACA_POD>(SELECT max(PLACA_POD) FROM PRAC WHERE ID_ZESP=(SELECT ID_ZESP FROM ZESP WHERE NAZWA='INFORMATYKA'));
Zad. 2. Wyświetlić zespoły, które nie zatrudniają pracowników stosując podzapytanie.
SELECT * FROM ZESP WHERE ID_ZESP NOT IN (SELECT ID_ZESP FROM PRAC);
Zad.3. Wyświetlić numery zespołów zatrudniających więcej niż 3 pracowników.
SELECT ID_ZESP FROM PRAC GROUP BY ID_ZESP HAVING count(*) >3;
Zad.4. Wykorzystując grupowanie krotek sprawdzić, czy wszystkie numery pracowników są unikalne.
SELECT ID_PRAC FROM PRAC GROUP BY ID_PRAC;
Zad.5. Wyświetlić najniższą płacę podstawową pracowników w grupie każdego przełożonego. Pominąć grupy, w których minimalne płace podstawowe są niższe od 1000. Wyniki uporządkować wg rosnących zarobków.
SELECT MIN(PLACA_POD) AS MIN, SZEF FROM PRAC WHERE PLACA_POD > 1000 GROUP BY SZEF ORDER BY MIN;
Zad.6. Bez korzystania z podzapytania skorelowanego wyświetlić najkrócej zatrudnionych pracowników każdego zespołu. Wyniki uszeregować w kolejności zatrudnienia.
SELECT MAX(ZATRUDNIONY) AS NOWICJUSZ, ID_ZESP FROM PRAC GROUP BY ID_ZESP ORDER BY NOWICJUSZ;
Zad. 7. Wyświetlić pracowników zarabiających najmniej w ramach swoich grup etatowych. Wyniki uszeregować w malejącym porządku zarobków. Wykorzystać podzapytanie skorelowane.
SELECT NAZWISKO,PLACA_POD, ETAT FROM PRAC p WHERE PLACA_POD = (SELECT MIN(PLACA_POD) FROM PRAC WHERE ETAT =p.ETAT);
II. Funkcje
a) operacje na datach
Daty można porównywać oraz wykonywać na nich następujące operacje matematyczne:
operacja |
znaczenie |
data+liczba |
powiększenie daty o określoną liczbę dni |
data-liczba |
pomniejszenie daty o określoną liczbę dni |
data-data |
wyznaczenie liczby dni różniące dwie daty |
data+liczba/24 |
powiększenie daty o określoną liczbę godzin |
Odwołanie do daty systemowej realizuje zmienna sysdate.
np. SELECT sysdate, ZATRUDNIONY, ZATRUDNIONY +7 FROM PRAC
WHERE ZATRUDNIONY LIKE '%MAY%';
b) funkcje operujące na pojedynczych krotkach
abs(wyrażenie) |
wartość bezwzględna |
add_months(data,n) |
zwiększenie daty o n miesięcy |
ceil(wyrażenie) |
najmniejsza całkowita >= wartości argumentu |
decode(w,test1,wyn1,[... ] wyn2) |
jeśli w=test1 wykonuje wyn1, jeśli w!=test1 wykonuje wyn2 |
floor(wyrażenie) |
największa całkowita <= wartości argumentu |
greatest(w1,w2, w3,...) |
zwraca największą z wartości w1, w2, w3,... |
initcap(wyrażenie) |
zamienia pierwszy znak każdego słowa z małej na dużą |
instr(wyrażenie,'tekst') |
wyznacza pozycję pierwszego wystąpienia 'tekst' w wyrażeniu |
instr(wyrażenie,'tekst',poz,n) |
wyznacza pozycję n-tego wystąpienia 'tekst' w wyrażeniu, rozpoczynając poszukiwanie od 'poz' |
last_day(data) |
wyznacza datę ostatniego dnia miesiąca, w którym zawiera się data będąca argumentem |
least(w1,w2, w3, ...) |
wyznacza wartość najmniejszego argumentu |
lenght(wyrażenie) |
wyznacza ilość znaków argumentu |
lower(wyrażenie) |
zamienia duże litery argumentu na małe |
lpad(wyrażenie, n, 'zn') |
uzupełnia argument z lewej strony o n znaków 'zn' |
ltrim(wyrażenie,'znaki') |
usuwa z wyrażenia tekst 'znaki' |
mod(wyrażenie) (wyrażenie) |
reszta z dzielenia |
months_between(data1, data2) |
liczba miesięcy między dwiema datami |
next_day(data,dzień) |
wyznacza datę najbliższego po data dnia tygodnia o nazwie lub numerze dzień |
nvl(wyrażenie,wartość) |
przypisuje tymczasowo wartość NULL pustemu argumentowi wyrażenie |
power(wyrażenie,n) |
n-ta potęga argumentu |
round(wyrażenie,n) |
zaokrąglenie do n znaków po przecinku |
rpad(wyrażenie,n,'zn') |
uzupełnia wyrażenie z prawej strony o n znaków 'zn' |
rtrim(wyrażenie,'znaki') |
usuwa z wyrażenie ostatnie wystąpienie tekstu 'znaki' |
sign(wyrażenie) |
zwraca 1, 0 lub -1 w zależności od znaku wyrażenie |
soundex(wyrażenie) |
wyznacza łańcuch znaków reprezentujący fonetyczne brzmienie wyrażenie |
sqrt(wyrażenie) |
pierwiastek kwadratowy |
substr(wyrażenie,poz,n) |
wybranie łańcucha n znaków począwszy od poz |
to_char(liczba|data,'[fmt']) |
zamienia liczbę lub datę na tekst o formacie fmt |
to_number(l_zn,'[fmt']) |
Zmiana liczby zapisanej znakowo na wartość numeryczną |
to_date(data_zn,'[fmt']) |
Zmiana zapisu znakowego daty na wartość typu date |
translate(wyrażenie,z,na) |
Zmiana znaku przekazanego argumentem |
trunc(wyrażenie,n) |
Obcina część ułamkową |
upper(wyrażenie) |
Zamienia małe litery na duże |
vsize(wyrażenie) |
Rozmiar argumentu w pamięci w bajtach |
UWAGA: przy stosowaniu funkcji kolumnowych (patrz ćwiczenie 2) nie są brane do obliczeń te krotki, które zawierają puste atrybuty. Aby temu zapobiec należy zastosować dla atrybutu funkcję nvl przypisującą pustemu atrybutowi wartość zerową.
np. SELECT NAZWISKO, PLACA_POD + nvl(PLACA_DOD) ZAROBKI FROM PRAC;
Zadania do samodzielnego wykonania:
Zad. 8. Wyświetl pracowników, którzy nie mają szefa, wypisując dodatkowo tekst 'brak szefa'.
SELECT nazwisko, 'brak szefa' AS "NAZWISKO SZEFA" FROM prac WHERE szef IS NULL
Zad. 9. Wyświetlić w poniższej formie informacje o wszystkich pracownikach
SELECT NAZWISKO||'********&&&&&&&&'||ETAT AS "Pracownik i jego etat " FROM PRAC;
Pracownik i jego etat
--------------------------------------------------------
BABACKI********&&&&&&&&ADIUNKT
Zad. 10 .Wyświetlić nazwiska pracowników oraz ich kod złożony z dwóch pierwszych liter nazwiska i numeru pracownika zawartego w atrybucie id_prac w następujący sposób:
SELECT NAZWISKO, substr(NAZWISKO,0,2)||substr(ID_PRAC,0,2)||substr(ID_PRAC,0,2) AS KOD FROM PRAC;
// czy jest możliwość duplikowania fragmentów, bo ładnie by to wyglądało
NAZWISKO KOD
-----------------------------
ABACKI AB1010
LABORATORIUM z BAZ DANYCH
ORACLE
4