ĆWICZENIE 2/2012
1. Operator połączenia zewnętrznego
Polecenie SELECT może służyć do połączenia więcej niż dwóch tabel wymienionych w klauzuli FROM. Jednak w celu połączenia n tabel 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 tabeli, w której brakuje wierszy umożliwiających wyświetlenie wszystkich wierszy drugiej tabeli po ich połączeniu. Operator (+) powoduje utworzenie dodatkowych pustych wierszy.
np. SELECT NAZWISKO, z.ID_ZESP, NAZWA FROM PRAC p, ZESP z
WHERE p.ID_ZESP(+) = z.ID_ZESP;
2. 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 pojedynczy wiersz, to w klauzuli WHERE zapytania zewnętrznego stosuje się najczęściej operatory porównania: =, >, >=, ...
Jeśli podzapytanie wyznacza więcej niż jeden wiersz, w klauzuli WHERE zapytania zewnętrznego stosuje się operatory: IN, ANY lub ALL.
np. 1) SELECT * FROM P C 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 tabel, 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:
2. 1. Wyświetlić posortowane nazwiska pracowników zarabiających od 2000 do 3000 i nazwiska ich szefów.
SELECT NAZWISKO, p.id_szefa FROM PRAC p WHERE placa_pod > 2000 AND placa_pod < 3000 ORDER BY NAZWISKO asc;
2.2. Wyświetlić nazwiska pracowników, nazwy i adresy zespołów pracowników, których miesięczna pensja przekracza 2000 PLN.
SELECT nazwisko, nazwa, adres from prac, zesp where placa_pod>2000 and prac.id_ZESP=ZESP.ID_ZESP;
2.3. Wyświetlić pracowników z ulicy Sosnkowskiego w rosnącym porządku alfabetycznym ich nazwisk.
SELECT P.NAZWISKO FROM PRAC P, ZESP Z WHERE Z.ADRES LIKE 'SOSNKOWSKIEGO%' AND P.ID_ZESP = Z.ID_ZESP ORDER BY P.NAZWISKO;
2.4 Wyświetlić zespoły, które nie zatrudniają pracowników.
SELECT Z.NAZWA FROM ZESP Z WHERE Z.ID_ZESP NOT IN(SELECT DISTINCT P.ID_ZESP FROM PRAC P);
2.5. Znaleźć pracowników, którzy mają zarobki wyższe od najwyższych zarobków w zespole INFORMATYKA.
SELECT NAZWISKO, PLACA_POD FROM PRAC WHERE PLACA_POD > ALL (SELECT PLACA_POD FROM PRAC WHERE ID_ZESP = 20);
3. 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 rekordach
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 nie są brane do obliczeń te wiersze, 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,0) ZAROBKI FROM PRAC;
Zadania do samodzielnego wykonania:
2.6. Wyświetlić pracowników, którzy nie mają szefa, wypisując dodatkowo tekst 'brak szefa'.
SELECT NAZWISKO ||' Brak szefa' FROM PRAC WHERE id_szefa is NULL;
2.7. Wyświetlić w poniższej formie informacje o wszystkich pracownikach:
Pracownik i jego etat
--------------------------------------------------------
BABACKI********&&&&&&&&ADIUNKT
select nazwisko || '*********&&&&&&&&&'|| etat from prac;
2.8. 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:
NAZWISKO KOD
-----------------------------
ABACKI AB1010
SELECT NAZWISKO, RPAD(SUBSTR(NAZWISKO, 0, 2), 6, SUBSTR(ID_PRAC, 0, 2)) AS "KOD" FROM PRAC;
4. MODYFIKACJA ZAWARTOŚCI TABEL
a) Wstawianie wierszy (krotek, rekordów)
W celu wstawienia wiersza do tablicy stosujemy polecenie insert w następującym formacie ogólnym:
insert into nazwa_tabeli (atrybut1, atrybut2, ..) values (wartość1, wartość2);
W przypadku wstawiania wielu wierszy wygodnie jest użyć zmiennych,
np.: insert into zesp (id_zesp, nazwa, adres) values (&N_ZESP, '&NAZWA_ZESP','&LOKAL');
i wielokrotne jego wykonanie.
Wstawiane rekordy mogą być wynikiem zapytania select, mogą więc pochodzić z innych tabel. W tym celu stosujemy polecenie insert o formacie ogólnym:
insert into nazwa_tabeli (atrybut1, atrybut2, ..) select lista from tabela1, tabela2;
b) Modyfikowanie wierszy
Do modyfikowania wierszy służy polecenie update o formacie ogólnym:
update nazwa_tabeli [alias] set atrybut [,atrybut]={wyrażenie|podzapytanie}
where warunki;
np.:
update prac
set placa_pod=(select placa_pod from dodatki d
where d.id_prac=prac.id_prac)
where id_prac in (select id_prac from dodatki);
c) Usuwanie wierszy z tabeli
W celu usunięcia wiersza z tabeli stosujemy polecenie delete o formacie:
Delete from nazwa_tabeli [where warunki];
np.: delete from prac where etat='ASYSTENT';
Zadania do samodzielnego wykonania
2.9. Utworzyć tabelę PRAC_PROF jako kopię PRAC, usunąć wszystkie dane i wpisać tych pracowników, z tabeli PRAC, którzy pracują na stanowisku profesorskim i zostali zatrudnieni przed rokiem 1978.
CREATE TABLE PRAC_PROF AS SELECT * FROM PRAC;
DELETE FROM prac_prof;
INSERT INTO PRAC_PROF (NAZWISKO, ZATRUDNIONY)
SELECT NAZWISKO, ZATRUDNIONY FROM PRAC
WHERE ETAT = 'PROFESOR' AND ZATRUDNIONY<'78/01/01';
2.10. Podnieść pensję pracownikom najniżej zarabiającym do średniej pensji w zakładzie pracy.
UPDATE PRAC
SET PLACA_POD=(SELECT AVG (PLACA_POD) FROM PRAC)
WHERE PLACA_POD<(SELECT AVG (PLACA_POD) FROM PRAC);
2.11. Uaktualnić pensję dodatkową pracownikom zespołu 20. Nowa pensja ma być równa średniej pensji podstawowej pracowników, których przełożonym jest '*****'.
UPDATE PRAC
SET PLACA_DOD=(SELECT AVG (PLACA_POD) FROM PRAC)
WHERE ID_ZESP='20';
2.12. Zwiększyć płacę podstawową do 120% średniej płacy podstawowej w zespole pracownika oraz zwiększyć płacę dodatkową do wartości równej maksymalnej płacy dodatkowej w zespole pracownika. Operacji dokonać tylko dla pracowników zatrudnionych po 1990 roku.
Wskazówka: użyć funkcji nvl(wyrażenie, wartość) służącej do obsługi tzw. wartości pustych.
UPDATE PRAC
SET (PLACA_POD, PLACA_DOD)= (SELECT AVG(PLACA_POD)*1.2,
MAX(NVL(PLACA_DOD,0))FROM PRAC)
WHERE ZATRUDNIONY > '1990/1/1';
BAZY DANYCH I - laboratorium
INFORMATYKA III rok studia stacjonarne I - go stopnia
rok akademicki 2012/2013
semestr zimowy
2