background image

 

 

Systemy baz 
danych

Agnieszka Lubacka

alubacka@interia.pl

1

background image

 

 

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

background image

 

 

Zapytania wybierające

SELECT [DISTINCT] nazwa_kolumny1, 

nazwa_kolumny2, ..., 

nazwa_kolumnyN [*] FROM 

nazwa_tabeli

SELECT imie FROM Pracownik; 

SELECT DISTINCT imie FROM Pracownik;

background image

 

 

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

background image

 

 

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; 

background image

 

 

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;

background image

 

 

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

background image

 

 

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

background image

 

 

Funkcje UPPER, LOWER, 
INITCAP

SELECT UPPER(imie), INITCAP(nazwisko), 

LOWER(miasto) FROM Pracownik;

imie

nazwisko

miasto

ADAM

Kowal

katowice

MARTA

Las

zabrze

background image

 

 

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 ‘?’;

background image

 

 

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

background image

 

 

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

background image

 

 

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]

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

Grupowanie. Przykład

SELECT 

Dzial_id

, count(Dzial_id) ile_osób 

FROM Pracownik
GROUP BY 

Dzial_id

HAVING count(Dzial_id)>6

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

NATURAL JOIN

SELECT * FROM Pracownik 

NATURAL 

JOIN Dzial

background image

 

 

NATURAL JOIN

SELECT * FROM Pracownik 

NATURAL 

JOIN Dzial

SELECT * FROM Pracownik NATURAL

 

JOIN Dzial

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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

background image

 

 

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;

background image

 

 

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;

background image

 

 

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;

background image

 

 

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;

background image

 

 

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;

background image

 

 

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;

background image

 

 

Łączenie wielu tabel

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

background image

 

 

Łą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

background image

 

 

Łą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

background image

 

 

Łą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

background image

 

 

Łą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

background image

 

 

Łą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 

background image

 

 

Łą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 

background image

 

 

Łą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 

background image

 

 

JOIN

background image

 

 

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;

background image

 

 

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;

background image

 

 

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;

Document Outline