Systemy baz
danych

Agnieszka Lubacka

[e-mail]

1

Zagadnienia



Powtórzenie i rozszerzenie



DML – polecenia JOIN



Modelowanie – Encja dominująca



DDL – ograniczenia polecenia CREATE



Sekwencje, perspektywy, indeksy



Uprawnienia z poziomu SQL (DCL)



Procedury wyzwalane



Procedury składowane



Funkcje



Transakcje



Pakiety

Zapytania wybierające



SELECT [DISTINCT] nazwa_kolumny1,

nazwa_kolumny2, ...,

nazwa_kolumnyN [*] FROM

nazwa_tabeli



SELECT imie FROM Pracownik;



SELECT DISTINCT imie FROM Pracownik;



SELECT [Distinct] nazwa_kolumny
[ [AS] alias] , ..., nazwa_kolumnyN
FROM nazwa_tabeli



SELECT imie as imię FROM Pracownik;



SELECT imie imię FROM Pracownik;



SELECT imie imię_kierownika

FROM Pracownik;



SELECT imie „imię kierownika”

FROM Pracownik;

Zapytania wybierające

Operacje arytmetyczne
( + , – , * , / , || ) na kolumnach



SELECT nazwa_kolumny1 operator_arytmetyczny

nazwa_kolumny2 FROM nazwa_tabeli



SELECT 2010-rok_ur FROM Osoba;



SELECT extract(year from sysdate)-rok_ur wiek

FROM Osoba;



SELECT to_char(sysdate,'yyyy')-rok_ur FROM Osoba;



SELECT stawka + premia + potracenia as kwota

FROM Wyplata



SELECT imie + ‘ ‘ + nazwisko FROM Osoba;



SELECT imie || ‘ ‘ || nazwisko FROM Osoba;

Uporządkowanie wyników
zapytania



SELECT * FROM nazwa_tabeli

ORDER BY nazwa_kolumny [ASC/DESC]



SELECT imie, nazwisko FROM Pracownik

ORDER BY nazwisko;



SELECT imie, nazwisko FROM Pracownik

ORDER BY 2;



SELECT imie, nazwisko FROM Pracownik

ORDER BY 2, 1;



SELECT imie, nazwisko FROM Pracownik

ORDER BY nazwisko DESC, imie DESC;

Funkcja ROWNUM



SELECT * FROM nazwa_tabeli

ORDER BY ROWNUM [ASC/DESC]



SELECT imie, nazwisko FROM Pracownik

ORDER BY ROWNUM



SELECT ROWNUM, imie, nazwisko FROM Pracownik

ORDER BY ROWNUM



SELECT ROWNUM Lp, imie, nazwisko FROM Pracownik

ORDER BY Lp



SELECT ROWNUM Lp, imie, nazwisko FROM Pracownik

ORDER BY 1 DESC



SELECT * FROM nazwa_tabeli

WHERE warunek

Operatory porównania:

=, <, >,< >, !=, <=, >=

Zakresy:

[NOT] BETWEEN wartość AND wartość

Listy:

[NOT] IN (wartość1, wartość2, ..., wartośćN)

Dopasowanie wzorców znakowych:

[NOT] LIKE wzorzec



% – zastępuje dowolny ciąg znaków

lub o znaków np. ‘A%’



_ – zastępuje jeden znak np. ’A_’



ESCAPE – definiuje znak, po którym znaki

specjalne są traktowane dosłownie

Filtrowanie wyniku zapytania

Funkcje UPPER, LOWER,
INITCAP



SELECT UPPER(imie), INITCAP(nazwisko),

LOWER(miasto) FROM Pracownik;

imie

nazwisko

miasto

ADAM

Kowal

katowice

MARTA

Las

zabrze

Wybór wierszy według
wzorca



SELECT * FROM Pracownik

WHERE imie LIKE ‘A%’;



SELECT * FROM Pracownik

WHERE imie LIKE ‘%a%’;



SELECT * FROM Pracownik

WHERE UPPER(imie) LIKE ‘%A%’;



SELECT * FROM Dzial

WHERE nazwa LIKE ‘/_%’ ESCAPE ‘/’;



SELECT * FROM Dzial

WHERE nazwa LIKE ‘%?_%’ ESCAPE ‘?’;

Funkcje agregujące



MAX (nazwa_kolumny) – zwraca największą wartość

dla podanej kolumny



MIN (nazwa_kolumny) – zwraca najmniejszą wartość

dla podanej kolumny



SUM (nazwa_kolumny) – oblicza sumę wszystkich

wartości dla podanej kolumny



AVG (nazwa_kolumny) – oblicza średnią arytmetyczną

z wszystkich wartości dla podanej kolumny



COUNT (nazwa_kolumny [*]) – zwraca liczbę wierszy

COUNT(nazwa_kolumny [*])

Id

Imie

Nr_telefonu

1

Adam

1234567

2

Adam

NULL

3

Anna

3456771

4

Julia

NULL

5

Anna

3456672

SELECT COUNT(*) FROM Osoba

5

SELECT COUNT(imie) FROM Osoba

5

SELECT COUNT(nr_telefonu) FROM Osoba

3

Grupowanie



SELECT

[funkcja agregująca],

nazwa_kolumny1,

nazwa_kolumny2, … , nazwa_kolumnyN

FROM nazwa_tabeli

GROUP BY

nazwa_kolumny1, nazwa_kolumny2, … ,

nazwa_kolumnyN

,

[nazwa_kolumnyM]

[HAVING warunek]

SELECT count(*),

imie

FROM Osoba GROUP BY

imie

;

Adam

2

Anna

2

Julia

1

Id

Imie

Nazwisko

Nr_telefonu

1

Adam

Kowal

1234567

2

Adam

Nowak

Null

3

Anna

Nowak

3456771

4

Julia

Kowal

Null

5

Anna

Nowak

3456672

SELECT count(*),

imie

FROM Osoba GROUP BY

imie

;

Adam

2

Anna

2

Julia

1

SELECT count(*),

nazwisko

FROM Osoba GROUP BY

nazwisko

;

Kowal

2

Nowak

3

Id

Imie

Nazwisko

Nr_telefonu

1

Adam

Kowal

1234567

2

Adam

Nowak

Null

3

Anna

Nowak

3456771

4

Julia

Kowal

Null

5

Anna

Nowak

3456672

SELECT count(*),

imie

FROM Osoba GROUP BY

imie

;

Adam

2

Anna

2

Julia 1

SELECT count(*),

nazwisko

FROM Osoba GROUP BY

nazwisko

;

Kowal

2

Nowak

3

SELECT count(*),

imie, nazwisko

FROM Osoba GROUP BY

imie,

nazwisko

;

Adam

Kowal 1

Adam

Nowak 1

Anna

Nowak 2

Julia Kowal 1

Id

Imie

Nazwisko

Nr_telefonu

1

Adam

Kowal

1234567

2

Adam

Nowak

Null

3

Anna

Nowak

3456771

4

Julia

Kowal

Null

5

Anna

Nowak

3456672

SELECT count(*),

imie

FROM Osoba GROUP BY

imie

;

Adam

2

Anna

2

Julia

1

SELECT count(*),

imie

FROM Osoba GROUP BY

imie

HAVING count(*) > 1;

Adam

2

Anna

2

Id

Imie

Nazwisko

Nr_telefonu

1

Adam

Kowal

1234567

2

Adam

Nowak

Null

3

Anna

Nowak

3456771

4

Julia

Kowal

Null

5

Anna

Nowak

3456672

SELECT count(*),

nazwisko

FROM Osoba GROUP BY

nazwisko

;

Kowal

2

Nowak

3

SELECT count(*),

nazwisko

FROM Osoba GROUP BY

nazwisko

HAVING count(*) < 3;

Kowal

2

Id

Imie

Nazwisko

Nr_telefonu

1

Adam

Kowal

1234567

2

Adam

Nowak

Null

3

Anna

Nowak

3456771

4

Julia

Kowal

Null

5

Anna

Nowak

3456672

SELECT count(*),

imie, nazwisko

FROM Osoba

GROUP BY

imie, nazwisko

;

Adam

Kowal 1

Adam

Nowak 1

Anna

Nowak 2

Julia Kowal 1

SELECT count(*),

imie, nazwisko

FROM Osoba

GROUP BY

imie, nazwisko

HAVING COUNT(*) = 1;

Adam

Kowal 1

Adam

Nowak 1

Julia Kowal 1

Id

Imie

Nazwisko

Nr_telefonu

1

Adam

Kowal

1234567

2

Adam

Nowak

Null

3

Anna

Nowak

3456771

4

Julia

Kowal

Null

5

Anna

Nowak

3456672

Grupowanie. Przykład

SELECT

Dzial_id

, count(Dzial_id) ile_osób

FROM Pracownik
GROUP BY

Dzial_id

HAVING count(Dzial_id)>6

Grupowanie. Przykład

SELECT

Nazwa

, count(Dzial_id) ile_osób

FROM Dzial dz, Pracownik p
WHERE p.Dzial_id = dz.id
GROUP BY

Nazwa

, Dzial_id

HAVING count(Dzial_id) >6

Join – łączenie tabel



CROSS JOIN – złączenie krzyżowe

(iloczyn kartezjański)



NATURAL JOIN – złączenie naturalne

(po nazwach pól)



INNER JOIN – złączenie wewnętrzne

(po kluczach)



OUTER JOIN – złączenie zewnętrzne

(po kluczach, uwzględniając

pola z wartością NULL)



LEFT OUTER JOIN – lewostronne



RIGHT OUTER JOIN – prawostronne



FULL OUTER JOIN – obustronne

CROSS JOIN

SELECT imie, nazwisko, nazwa FROM Pracownik CROSS JOIN Dzial

imie

nazwisko

nazwa

Jan

Nowak

Kadry

Jan

Nowak

Płace

Jan

Nowak

Biuro

Anna

Kot

Kadry

Anna

Kot

Płace

Anna

Kot

Biuro

Adam

Loska

Kadry

Adam

Loska

Płace

Adam

Loska

Biuro

CROSS JOIN

SELECT a.imie, a.nazwisko, b.imie, b.nazwisko

FROM Pracownik a CROSS JOIN Pracownik b

a.imie

a.nazwisko

b.imie

b.nazwisko

Jan

Nowak

Jan

Nowak

Jan

Nowak

Anna

Kot

Jan

Nowak

Adam

Loska

Anna

Kot

Jan

Nowak

Anna

Kot

Anna

Kot

Anna

Kot

Adam

Loska

Adam

Loska

Jan

Nowak

Adam

Loska

Anna

Kot

Adam

Loska

Adam

Loska

CROSS JOIN

SELECT a.imie, a.nazwisko, b.imie, b.nazwisko

FROM Pracownik a CROSS JOIN Pracownik b WHERE a.id <> b.id

a.Imie

a.nazwisko

b.imie

b.nazwisko

Jan

Nowak

Anna

Kot

Jan

Nowak

Adam

Loska

Anna

Kot

Jan

Nowak

Anna

Kot

Adam

Loska

Adam

Loska

Jan

Nowak

Adam

Loska

Anna

Kot

NATURAL JOIN

SELECT * FROM Pracownik

NATURAL

JOIN Dzial

NATURAL JOIN

SELECT * FROM Pracownik

NATURAL

JOIN Dzial

SELECT * FROM Pracownik NATURAL

JOIN Dzial

NATURAL JOIN

Klient

Pracownik

Id klienta

Nazwisko

Pesel

Id pracownika

Nazwisko

Pesel

55555

Kowalski

123456

243423

Kawasa

789012

11111

Kowalska

234567

443444

Kowalski

123456

33333

Sosna

345678

636666

Sosna

890123

22222

Kawel

456789

776777

Kawel

456789

66666

Kos

567890

SELECT * FROM Klient NATURAL JOIN Pracownik;

Id klienta

Nazwisko Pesel

Id pracownika

55555

Kowalski

123456

443444

22222

Kawel

456789

776777

NATURAL JOIN

Klient

Pracownik

Id klienta

Nazwisko

Pesel

Id pracownika

Nazwisko

Pesel

55555

Kowalski

123456

243423

Kawasa

789012

11111

Kowalska

234567

443444

Kowalski

123456

33333

Sosna

345678

636666

Sosna

890123

22222

Kawel

456789

776777

Kawel

456789

66666

Kos

567890

SELECT * FROM Klient JOIN Pracownik USING(Pesel,
Nazwisko);

Id klienta

Nazwisko Pesel

Id pracownika

55555

Kowalski

123456

443444

22222

Kawel

456789

776777

NATURAL JOIN

Klient

Pracownik

Id klienta

Nazwisko

Pesel

Id pracownika

Nazwisko

Pesel

55555

Kowalski

123456

243423

Kawasa

789012

11111

Kowalska

234567

443444

Kowalski

123456

33333

Sosna

345678

636666

Sosna

890123

22222

Kawel

456789

776777

Kawel

456789

66666

Kos

567890

SELECT * FROM Klient JOIN Pracownik USING(Pesel);

Id klienta

Nazwisko1 Nazwisko2 Pesel

Id pracownika

55555

Kowalski

Kowalski

123456

443444

22222

Kawel

Kawel

456789

776777

NATURAL JOIN

Klient

Pracownik

Id klienta

Nazwisko

Pesel

Id pracownika

Nazwisko

Pesel

55555

Kowalski

123456

243423

Kawasa

789012

11111

Kowalska

234567

443444

Kowalski

123456

33333

Sosna

345678

636666

Sosna

890123

22222

Kawel

456789

776777

Kawel

456789

66666

Kos

567890

SELECT * FROM Klient JOIN Pracownik USING(Nazwisko);

Id klienta

Nazwisko Pesel1

Pesel2

Id pracownika

55555

Kowalski

123456

123456

443444

22222

Kawel

456789

456789

776777

33333

Sosna

345678

890123

636666

INNER JOIN

SELECT imie, nazwisko, nazwa

FROM Pracownik p, Dzial dz

WHERE p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p, Dzial dz

WHERE dz.id = p.Dzial_id;

INNER JOIN

SELECT imie, nazwisko, nazwa

FROM Pracownik p

INNER

JOIN Dzial dz

ON p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p

INNER

JOIN Dzial dz

ON dz.id = p.Dzial_id;

INNER JOIN

SELECT imie, nazwisko, nazwa

FROM Pracownik p JOIN Dzial dz

ON p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p JOIN Dzial dz

ON dz.id = p.Dzial_id;

OUTER JOIN

SELECT imie, nazwisko, nazwa

FROM Pracownik p LEFT

OUTER

JOIN Dzial dz

ON p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p RIGHT

OUTER

JOIN Dzial dz

ON p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p FULL

OUTER

JOIN Dzial dz

ON p.id = dz.Pracownik_id;

OUTER JOIN

SELECT imie, nazwisko, nazwa

FROM Pracownik p LEFT JOIN Dzial dz

ON p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p RIGHT JOIN Dzial dz

ON p.id = dz.Pracownik_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p FULL JOIN Dzial dz

ON p.id = dz.Pracownik_id;

OUTER JOIN

SELECT imie, nazwisko, nazwa

FROM Pracownik p RIGHT JOIN Dzial dz
ON dz.id = p.Dzial_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p LEFT JOIN Dzial dz
ON dz.id = p.Dzial_id;

SELECT imie, nazwisko, nazwa

FROM Pracownik p FULL JOIN Dzial dz
ON dz.id = p.Dzial_id;

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM pracownik, stanowisko, dzial

WHERE pracownik.id_stanowiska = stanowisko.id
AND pracownik.id_dzialu = dzial.id

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM stanowisko, dzial, pracownik

WHERE pracownik.id_stanowiska = stanowisko.id
AND pracownik.id_dzialu = dzial.id

SELECT * FROM stanowisko JOIN dzial JOIN pracownik

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM stanowisko, dzial, pracownik

WHERE pracownik.id_stanowiska = stanowisko.id
AND pracownik.id_dzialu = dzial.id

SELECT * FROM

stanowisko JOIN dzial

JOIN pracownik

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM stanowisko, dzial, pracownik

WHERE pracownik.id_stanowiska = stanowisko.id
AND pracownik.id_dzialu = dzial.id

SELECT * FROM pracownik JOIN stanowisko JOIN dzial

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM stanowisko, dzial, pracownik

WHERE pracownik.id_stanowiska = stanowisko.id
AND pracownik.id_dzialu = dzial.id

SELECT * FROM pracownik JOIN stanowisko JOIN dzial

SELECT * FROM pracownik JOIN stanowisko

ON pracownik.id_stanowiska = stanowisko.id

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM stanowisko, dzial, pracownik

WHERE pracownik.id_stanowiska = stanowisko.id

AND pracownik.id_dzialu = dzial.id

SELECT * FROM pracownik JOIN stanowisko JOIN dzial

SELECT * FROM pracownik JOIN stanowisko

ON pracownik.id_stanowiska = stanowisko.id

JOIN dzial ON pracownik.id_dzialu = dzial.id

Łączenie wielu tabel

Pracownik(id, imie, nazwisko, id_stanowiska, id_dzialu)
Stanowisko(id, nazwa)
Dzial(id,nazwa)

SELECT * FROM stanowisko, dzial, pracownik

WHERE pracownik.id_stanowiska = stanowisko.id

AND pracownik.id_dzialu = dzial.id

SELECT * FROM pracownik JOIN stanowisko JOIN dzial

SELECT * FROM pracownik

INNER

JOIN stanowisko

ON pracownik.id_stanowiska = stanowisko.id

INNER

JOIN dzial ON pracownik.id_dzialu = dzial.id

JOIN

JOIN

SELECT p.imie, p.nazwisko, nazwa, k.imie, k.nazwisko

FROM Pracownik p JOIN Dzial dz ON dz.id = p.Dzial_id

JOIN Pracownik k ON k.id = dz.Pracownik_id;

JOIN

SELECT p.imie, p.nazwisko, nazwa, k.imie, k.nazwisko

FROM Pracownik p RIGHT JOIN Dzial dz ON dz.id =
p.Dzial_id

JOIN Pracownik k ON k.id = dz.Pracownik_id;

JOIN

SELECT p.imie, p.nazwisko, nazwa, k.imie, k.nazwisko

FROM Pracownik p RIGHT JOIN Dzial dz ON dz.id =
p.Dzial_id

LEFT JOIN Pracownik k ON k.id = dz.Pracownik_id;