Język SQL – cz.2

konspekt wykładu

1. Złączenia tabel

Złączenie „tradycyjne”:

SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu

FROM Budynki, Lokale
WHERE Budynki.ID = Lokale.ID_budynku AND L_kondygnacji > 4;

Wykorzystanie kwalifikatorów i aliasów:

SELECT B.Nr_bud, B.L_kondygnacji, L.Nr, L.Powierzchnia_lokalu

FROM Budynki B, Lokale L
WHERE B.ID = L.ID_budynku AND L_kondygnacji > 4;

Złączenie w SQL-92:

SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu

FROM Budynki JOIN Lokale
WHERE L_kondygnacji > 4;

SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu

FROM Budynki JOIN Lokale USING ID_bud
WHERE L_kondygnacji > 4;

SELECT Nr_bud, L_kondygnacji, Nr_lokalu, Powierzchnia_lokalu

FROM Budynki JOIN Lokale ON Budynki.ID = Lokale.ID_bud
WHERE L_kondygnacji > 4;

SELECT Nr, Powierzchnia, Nazwisko, Imie

FROM Dzialki JOIN Dzial_Wlasc_int JOIN Wlasciciele
WHERE nazwisko=’Kowalski’;

Złączenie tabeli samej ze sobą.

W operacji złączenia języka SQL ta sama tabela może występować wielokrotnie w klauzuli FROM.
Tabela może więc być łączona sama ze sobą. Konieczne jest użycie aliasów, które pozwalają na
rozróżnienie pomiędzy odwołaniem do tabeli jako źródła informacji o jednostce podrzędnej, a później
nadrzędnej. Najczęściej wykorzystuje się tego typu złączenie w przypadku tabeli w której zapisana
jest zależność hierarchiczna np. w przypadku tabeli z danymi o jednostkach administracyjnych
Przykładowa struktura tabeli Jedn_adm (jednostki administracyjne)

1

Jedn_adm

ID

Rodzaj

Nazwa

ID_j_nadrzednej

1

g

Dąbrowa

41

2

g

Stara Wieś

41

2

g

Nowa Wieś

47

41

p

wąchocki

1

1

w

mazowieckie

0

SELECT A.ID, A.Rodzaj, A.Nazwa, B.Nazwa

FROM jedn_adm A, jedn_adm B
WHERE A.ID = B.Id_j_nadrzednej;

Wynik zapytania:

A.ID

A.Rodzaj

A.Nazwa

A.ID_j_nadrzednej B.ID

B.Rodzaj

B.Nazwa

.ID_j_nadrzednej

1

g

Dąbrowa

41

41

p

wąchocki

1

-

W prostym złączeniu tabel otrzymujemy na wyjściu tylko odpowiadające sobie wiersze z
łączonych tabel.

-

Jeżeli chcemy w odpowiedzi uwzględnić również te wiersze, które nie mają odpowiadających
wierszy w pozostałych tabelach złączenia, należy użyć operatora złączenia zewnętrznego „(+)”

-

Symbol „(+)” stawia się po nazwie kolumny z tej strony operatora relacyjnego, z której może
wystąpić brak informacji

-

Operator „(+)” może stać tylko przy jednej tabeli w ramach jednego zapytania

SELECT dzialki.nr, baseny.powierzchnia FROM dzialki, baseny

WHERE dzialki.nr = baseny.id_dzialki (+);

Składnia SQL-92:

Budynki LEFT OUTER JOIN Lokale
Budynki RIGHT OUTER JOIN Lokale
Budynki FULL OUTER JOIN Lokale

2. Wybrane funkcje systemowe

-

funkcje agregujące: SUM, MAX, MIN, AVG, COUNT, STDDEV, VARIANCE

-

funkcje znakowe: CHR, ASCII, LOWER, UPPER, INITCAP, LPAD, RPAD, LTRIM, RTRIM,
REPLACE, SUBSTR, INSTR, LENGHT

-

funkcje związane z datą i czasem: SYSDATE, ROUND, TRUNC

-

funkcje matematyczne: ABS, EXP, LN, LOG, POWER, ROUND, TRUNC, MOD, SIN, COS,
TAN, SQRT

-

funkcje do konwersji typów danych: TO_CHAR, TO_DATE, TO_NUMBER, NVL

2

3. Funkcje grupowe

Umożliwiają obliczenie liczności oraz parametrów statystycznych (minimum, maksimum, wartość
średnia, odchylenie standardowe, wariancja) grup danych złożonych z wielu wierszy

Do określenia liczby wierszy służy funkcja grupowa COUNT.
Jeżeli w zapytaniu nie ma klauzuli GROUP BY zliczanie dotyczy całej tabeli jako jednej grupy

Zliczenie wszystkich wierszy w tabeli:
SELECT Count (*) FROM Drogi;

Obliczenie liczby budynków posiadających nazwę własną (np. „Pałac Kultury i Nauki”, „Spodek”)
SELECT Count(nazwa) FROM Budynki
-

wyszukiwanie wszystkich wierszy, w których w kolumnie "nazwa" występuje wartość różna od
NULL

Aby ignorować duplikaty wierszy podczas zliczania należy dodać klauzulę DISTINCT
SELECT Count(Distinct Nazwa) FROM Ulice;

Zliczenie liczby odcinków dróg o tym samym rodzaju nawierzchni
SELECT Nawierzchnia, Count(*) FROM Odcinki_jezdni GROUP BY Nawierzchnia;

Mb, 234
Kk, 23
Bt, 45
Gr, 567

Obliczenie parametrów statystycznych odcinków jezdni dla poszczególnych rodzajów nawierzchni:

SELECT nawierzchnia, MIN (długosc) minimum,

MAX (dlugosc) maksimum,
AVG (dlugosc) średnia

FROM Odcinki_jezdni GROUP BY nawierzchnia;

Nawierzchnia minimum maksimum średnia
Mb 3 5 3.8

Nawierzchnia MIN (długosc) MAX (dlugosc) AVG (dlugosc)
Mb 3 5 3.8

4. Podzapytania

W języku SQL można oprócz prostych zapytań formułować zapytania zagnieżdżone. Podzapytania nie
przekazują wyniku na ekran, ale wynik przekazywany jest do zapytania wyższego poziomu.

Tabele: STUDENT, STUD_AKAD, AKADEMIK
Pytanie: W jakim akademiku mieszka Kowalski

SELECT Nazwa FROM Akademiki WHERE ID_akademika = ANY (SELECT ID_akad FROM
Stud_akad WHERE Id_studenta = ANY (SELECT Id FROM Student WHERE nazwisko =
‘Kowalski’));

Zapytania zagnieżdżone w warunkach zawierających operatory porównania są jednowierszowe.

3

Jeżeli podzapytanie może wyszukać więcej niż jeden wiersz, należy użyć innych operatorów:
= ALL (dla każdego wiersza)
= ANY (dla któregokolwiek wiersza) – to samo co IN
.....WHERE id_teryt IN (SELECT nr FROM gminy WHERE nazwa IN (‘Koluszki’, ‘Wąchock’))

Wyszukanie gmin leżących w powiatach o powierzchni większej od 1000 km2
Select * from gminy where id_powiatu=ANY (SELECT id FROM powiaty WHERE powierzchnia >
1000);

WADY:
-

Przy wielu poziomach zagnieżdżenia mała czytelność

-

Możliwość uzyskania w odpowiedzi tylko kolumn z tabeli występującej w zapytaniu najbardziej
zewnętrznym

5. Stosowanie operatorów teoriomnogościowych

W SQL oprócz operatorów projekcji, selekcji i złączenia mogą być używane klasyczne operatory
teoriomnogościowe – suma (UNION) , przecięcia (INTERSECT) i różnica zbiorów (MINUS)
-

wyniki zapytań mogą być traktowane jako zbiory i mogą być argumentami operacji
teoriomnogościowych

-

aby 2 zapytania mogły być połączone muszą mieć identyczną strukturę (istotna jest liczba,
kolejność i typy kolumn, nieistotne są nazwy)

-

klauzula ORDER BY może wystąpić tylko raz, po ostatnim zapytaniu; kryteria sortowania mogą
być wyłącznie numerami kolumn (zgodnie z pozycją kolumny)

Przykład:
Wypisanie danych o gminach i powiatach na jednej liście posortowanej po powierzchni:
SELECT Nazwa, Powierzchnia FROM Gminy

UNION

SELECT Nazwa_pow, Area FROM Powiaty

ORDER BY 2;

Wypisanie nazw miast o statusie gminy:
SELECT Nazwa, Powierzchnia FROM Gminy

INTERSECT

SELECT Nazwa, Powierzchnia FROM Miasta;

6. Wstawianie danych

INSERT Into Odcinki_jezdni (ID, Szerokosc, Stan, Kategoria_ruchu, Data_wprowadzenia_info)

VALUES (1, 5.5 , null, ’1’ , sysdate);

7. Aktualizacja danych

UPDATE Szlaki_drogowe

SET KolejnoscOdsniezania = 1

WHERE Lenght(Nr)<3;

4

UPDATE Budynki

SET Uwagi = 'Brak danych o wysokości"

WHERE Wysokosc IS NULL;

UPDATE Odcinki_rzek

SET Klasa_czystosci = Klasa_czystosci+1

WHERE id=245;

UPDATE Sprzet

SET GwarancjaStart = TRUNC(SYSDATE,’DD.MM.YYYY’),
GwarancjaStop=TRUNC (SYSDATE,’DD.MM.YYYY’)+364;

8. Usuwanie danych

DELETE FROM Odc_drogi

WHERE Dlugosc IS NULL;

9. Modyfikacja struktury danych

Struktury danych mogą być łatwo powiększane. Zmniejszanie (np. usunięcie kolumny) jest zwykle
niedopuszczalne lub ograniczone pewnymi warunkami

Przykład z systemu Oracle:

SQL> ALTER TABLE powiaty MODIFY nr_stat number(4);
SQL> ALTER TABLE powiaty MODIFY nr_stat number(2);
ERROR at line 1
ORA-01440: column to be modified must be empty to decrease precision or scale

Zmiana typu kolumny możliwa jest tylko jeżeli w tabeli nie ma danych:
SQL> ALTER TABLE Powiaty MODIFY nr_stat VARCHAR2(4);

Dodanie nowej kolumny:
SQL> ALTER TABLE Powiaty ADD powierzchnia number(10);

10. Definiowanie perspektyw

SQL> Create View DzialkiBudyn (Nr_działki, Rodzaj_budynku, Liczba_pięter) As

SELECT dzialki.nr, budynki.rodzaj, budynki.liczba_kondygnacji
FROM dzialki, dzialki_bud_int, budynki
WHERE dzialki.ID = dzialki_bud_int.ID_dzialki AND

dzialki_bud_int.ID_bud= budynki.ID;

Select * From DzialkiBudyn;

-

Typy kolumn w perspektywie dziedziczone są po elementach listy SELECT

-

Należy unikać używania w tworzeniu perspektywy po klauzuli SELECT znaku (*)

-

Dodanie klauzuli WITH CHECK OPTION powoduje nałożenie ograniczenia na operacje
modyfikacji danych; nie pozwoli na wprowadzenie danych które nie będą widoczne w
perspektywie

5

SQL> Create View WysokieBudynki As

SELECT ID, Nr_ew, Funkcja, Liczba_kondyg, Wysokosc FROM Budynki
WHERE Liczba_kondyg>4

11. Definiowanie indeksów

SQL> Create Unique Index On Wlasciciele (nazwisko,imie, data_ur,imie_ojca);

12. Definiowanie synonimów

SQL> Create Synonym DK for DKowalski.DaneOsobiste;
SQL> SELECT * FROM DK;

13. Definiowanie sekwencji

Sekwencje umożliwiają pobieranie unikalnych liczb całkowitych, przez różnych użytkowników w
sposób współbieżny

SQL> Create Sequence AutoNr1;

SQL> Create Sequence AutoNr2

increment By 10
minvalue 100
maxvalue 10000
cycle;

Wprowadzanie danych z użyciem sekwencji:
SQL> INSERT Into Pracownicy (Identyfikator, Imie, Nazwisko)

Values (AutoNr1.nextval, ‘Piotr’, ‘Krawczyk’);

SQL> Alter Sequence AutoNr2 Increment By 1

Operatory dotyczące sekwencji (nextval, currval) nie mogą być użyte w następujących miejscach:
-

we frazie WHERE

-

w zapytaniach z ORDER BY, CONNECT BY, GROUP BY, DISTINCT

-

w perspektywach

-

definiowanie, modyfikacja więzów integralności i ograniczeń

14. Ustanawianie więzów integralności

Integralność kolumny i wiersza

SQL> ALTER TABLE Szlaki_drogow MODIFY Nr NOT NULL;
SQL> ALTER TABLE Jezdnie MODIFY Kategoria_ruchu DEFAULT ‘A’;
SQL> ALTER TABLE Jezdnie ADD CONSTRAINT jezdnie_chk CHECK (klasa_techniczna
BETWEEN 1 AND 6);
SQL> ALTER TABLE Jezdnie ADD CONSTRAINT jezdnie_chk CHECK (klasa_techniczna>=1
AND klasa_techniczna<=6);

6

Klucz główny:
SQL>ALTER TABLE Gminy ADD CONSTRAINT gminy_pk PRIMARY KEY (Nr_TERYT);

Klucz unikalny:
SQL> ALTER TABLE Wlasciele ADD CONSTRAINT wlas_uk UNIQUE (imie, nazwisko);

Klucz obcy:
SQL> ALTER TABLE Gminy ADD CONSTRAINT gminy_pow_fk FOREIGN KEY
(Nr_TERYT_pow) REFERENCES Powiaty (Nr_TERYT) ON DELETE CASCADE;

7