plik

Język SQL – cz.2

konspekt wykładu

1. Złączenia tabel

Złączenie „klasyczne”:

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)

Jedn_adm

Rodzaj

Nazwa

ID_j_nadrzednej

Dąbrowa

Stara Wieś

Nowa Wieś

Wąchocki

112

Mazowieckie

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

Dąbrowa

wąchocki

112

Zapytania zewnętrzne:

- 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 (z systemu Oracle)

-  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

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 MIN (długosc)  MAX (dlugosc)   AVG (dlugosc)
Mb        3       5     3.8
Kk     0.5       2    1.2

SELECT nawierzchnia, MIN (długosc),

MAX (dlugosc),

AVG (dlugosc)

FROM Odcinki_jezdni GROUP BY nawierzchnia;

Nawierzchnia minimum maksimum średnia
Mb 3 5 3.8
Kk

0.5

1.2

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: DZIALKI, DZIAL_WLAS, WLASICIELE
Pytanie: Znajdź numery działek należących do Kowalskiego

SELECT nr FROM Dzialki WHERE Id = ANY (SELECT ID_dzialki FROM Dzial_Wlas WHERE
Id_wlasciciela = ANY (SELECT Id FROM Wlasciciele WHERE nazwisko = ‘Kowalski’));

Zapytania zagnieżdżone w warunkach zawierających operatory porównania są jednowierszowe.
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;

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 = Sysdate()

GwarancjaStop=Sysdate()+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;

12/1, m, 2
134, m, 1
12/2, g, 1
12/2, m, 2
231, p, 3

-  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

SQL> Create View WysokieBudynki As

SELECT ID, Nr_ew, Funkcja, Liczba_kondyg, Wysokosc FROM Budynki

WHERE Liczba_kondyg>4 WITH CHECK OPTION;

11.  Definiowanie indeksów

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

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

12.  Definiowanie synonimów

SQL> Create Synonym DK for DKowalski.DaneOsobiste;
SQL> SELECT * FROM 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);

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

Klucz unikalny:
SQL> ALTER TABLE Własciciele 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;