Bazy Danych cz II

Zapytania z grupowaniem

I warunkiem NOT EXISTS

Potrzeba grupowania

• Zapytania z funkcjami agreguj

ą

cymi s

ą

bardzo potrzebne ale chc

ą

c policzy

ć

ś

redni

ą

pensj

ę

w ka

ż

dej z firm trzeba:

– Napisa

ć

program który w p

ę

tli, dla ka

ż

dej

firmy wyliczy w niej

ś

redni

ą

– Wynik b

ę

dzie zapisywał do tabeli

tymczasowej

– Na ko

ń

cu wy

ś

wietli zawarto

ść

tabeli

tymczasowej

Zapytanie z grupowaniem

• Chc

ą

c otrzyma

ć

wspomniany uprzednio

raport w jednym zapytaniu, nale

ż

y

skorzysta

ć

z zapytania z grupowaniem

SELECT lista_kolumn, Funkcje_Agregujace

FROM tabele

WHERE warunki_na_wiersze

GROUP BY lista_kolumn

HAVING warunki_na_funkcje_agreguj

ą

ce

Zasady grupowania

• Lista kolumn w klauzuli SELECT i GROUP

BY musi by

ć

taka sama

• Klauzula HAVING jest opcjonalna

• Ide

ą

zapytania jest:

– Wylicz warto

ś

ci Funkcji Agreguj

ą

cych dla

danej grupy (czyli powtarzaj

ą

cych si

ę

warto

ś

ci

w kolumnie)

Grupowanie – Cwiczenie 1

SELECT id_osoby, id_firmy, stanowisko, pensja FROM Etaty order by id_firmy, id_osoby

id_osoby id_firmy stanowisko pensja do

----------- -------- ---------- ----------- ----------

1 FŁP Kierownik 6200.0000 2004-10-22

4 FŁP Prezes 65200.0000 NULL

1 HP Konsultant 20000.0000 NULL

5 HP Dyrektor 50000.0000 2002-10-21

1 PW Magazynier 4200.0000 2004-10-22

1 PW Doktorant 600.0000 1998-01-01

1 PW Asystent 1600.0000 2000-01-01

1 PW Adjunkt 3200.0000 NULL

1 PW Sprz

ą

tacz 2200.0000 NULL

2 PW Adjunkt 3200.0000 NULL

Liczymy

ś

redni

ą

w całej bazie

• Zwykłe zapytanie z funkcjami agreguj

ą

cymi:

SELECT

AVG(e.pensja) AS

ś

r_w_bazie

,

COUNT(*)

AS ile_etatów

FROM Etaty e

ś

r_w_bazie ile_etatów

--------------------- -----------

15640.0000 10

Ś

rednia pensja w firmach

SELECT e.id_firmy

,

avg(e.pensja)

as

ś

r_w_firmie

,

count(*)

as ile_etatów_w_firmie

FROM etaty e

GROUP BY e.id_firmy

id_firmy

ś

r_w_firmie ile_etatów_w_firmie

-------- --------------------- -------------------

FŁP 35700.0000 2

HP 35000.0000 2

PW 2500.0000 6

Dodanie kolumn, nie zmieniaj

ą

cych

wyniku zapytania i jego logiki

S

ą

to kolumny opisujace dodatkowo wybran

ą

ju

ż

grup

ę

. W naszym przykładzie grupujemy „po

firmach”. Dodanie dodatkowych kolumn opisuj

ą

cych

Firmy nie zmieni logiki zapytania

SELECT e.id_firmy, f.nazwa, f.ulica

,

avg(e.pensja)

as

ś

r_w_firmie

,

count(*)

as ile_etatów_w_firmie

FROM etaty e join firmy f

on (f.nazwa_skr = e.id_firmy)

GROUP BY e.id_firmy

, f.nazwa, f.ulica

id_firmy pelna_nazwa ulica

ś

r_w_firmie ile_etatów_w_firmie

-------- -------------------- -------------------- ------------- -------------------

FŁP Fabryka Łodzi Podwod Na dnie 4 35700.0000 2

HP Hewlett Packard Szturmowa 2a 35000.0000 2

PW Politechnika Warszaw Pl. Politechniki 1 2500.0000 6

Kolumny zmieniaj

ą

ce logik

ę

i wynik

zapytania

Dodanie kolumny ID_OSOBY zmienia sens zapytania. Chcemy

obejrze

ć

ś

redni

ą

pensj

ę

kazdej z osób w róznych firmach (jak 1

osoba ma kilka etatów w firmie to policzy jej

ś

r. Jak ma jeden to

go poka

ż

e)

SELECT e.id_firmy,

e.id_osoby

,

avg(e.pensja)

as

ś

r_p_osoby_w_firmie

,

count(*)

as ile_etatów_osoby_firmie

FROM etaty e

GROUP BY e.id_firmy,

e.id_osoby

Kolumny zmieniaj

ą

ce logik

ę

i wynik

zapytania - WYNIK

id_firmy id_osoby

ś

r_p_osoby_ ile_etatów_osoby_firmie

-------- ----------- ------------- -----------------------

FŁP 1 6200.0000 1

HP 1 20000.0000 1

PW 1 2360.0000 5

PW 2 3200.0000 1

FŁP 4 65200.0000 1

HP 5 50000.0000 1

• Logika została zmieniona – grupujemy dodatkowo po

innej logicznej cało

ś

ci

• Osoba o ID = 1 ma

ś

redni

ą

pensj

ę

w firmie ‘PW’

wyliczon

ą

z jego 5 etatów na których pracuje

(pracowała)

Warunki na grup

ę

• Pokaza

ć

maksymaln

ą

pensje w ka

ż

dej z firm

pod warunkiem, ze w firmie pracuje wi

ę

cej ni

ż

jedna osoba (to jest co innego ni

ż

ilo

ść

etatów):

SELECT e.id_firmy, MAX(e.pensja) AS maks_p

FROM Etaty e

WHERE (e.do IS NULL)

GROUP BY e.id_firmy

HAVING

COUNT(DISTINCT ID_OSOBY) > 2

/* HAVING

COUNT(*) > 2

*/

NOT EXISTS – zapytania

wewn

ę

trzne (zagnie

ż

d

ż

one)

SELECT lista_kolumn_zapytania_głównego

FROM lista_tabel_zapytania_gł
WHERE [warunki_na_wiersze AND]
NOT EXISTS

(SELECT cokolwiek

FROM lista_tabel_wewn

ę

trznych

WHERE

WARUNEK_Ł

Ą

CZ

Ą

CY_GŁ_Z_WEW

[ AND dodatkowe_war_na_wiersze_tab_wew]

)

NOT EXITS - Przykład

• Pokaza

ć

osoby, które nie maj

ą

aktualnie

ż

adnego etatu

• Inaczej mówi

ą

c przeszukujemy tabel

ę

osób i po kolei -> dla ka

ż

dej z nich

sprawdzamy:

– Istnieje dla tej osoby aktualny etat (jeden lub

dowolna ich ilo

ść

, genralnie

jakiekolwiek

)?

– Jak NIE to pokazujemy t

ę

osob

ę

NOT EXISTS – Przykład cd.

SELECT o.*

FROM osoby

o

WHERE NOT EXISTS

( SELECT 1

FROM etaty ew

WHERE/* war ł

ą

cz

ą

cy*/

(ew.id_osoby =

o

.id_osoby)

AND

(ew.do IS NULL) /*aktualne etaty*/

)

NOT EXISTS – Przykład 2

• Pokaza

ć

firmy w których nie pracuje aktualnie

nikt mieszkaj

ą

cy w mie

ś

cie o nazwie Warszawa

• Przegladamy baz

ę

firm i dla ka

ż

dej z nich

sprawdzamy czy nie istniej

ą

w tej firmie

pracownicy maj

ą

cy aktualny etat i mieszkaj

ą

cy w

Warszawie

– Mo

ż

e by

ć

taki jeden, kilku lub wszyscy

– Wystarczy jak jest jeden -> to ju

ż

firmy nie

pokazujemy

NOT EXISTS – Przykład 2 cd.

SELECT f.nazwa_skr, f.nazwa, m.nazwa AS miasto_firmy

FROM firmy

f

join miasta m ON (m.id_miasta=f.id_miasta)

WHERE NOT EXISTS

(SELECT 1

FROM etaty eW

join osoby oW ON (oW.id_osoby=eW.id_osoby)

join miasta mW ON (mW.id_miasta=oW.id_miasta)

WHERE

(eW.id_firmy =

f

.nazwa_skr)

AND

(eW.do IS NULL) /* aktualne */

AND

(mW.nazwa = 'Warszawa')

)

nazwa_skr nazwa miasto_firmy

--------- ------------------------------ ------------

FŁP Fabryka Łodzi Podwodnych WARSZAWA

HP Hewlett Packard WARSZAWA

NOT EXISTS z IF

•

/* usu

ń

procedur

ę

o nazwie Test – jak istnieje i utwórz na nowo */

IF EXISTS

( SELECT 1

FROM sysobjects o

/*tabela zawieraj

ą

ca obiekty w bazie*/

WHERE (o.[name] = 'Test')

/* nazwa obiektu */

AND

(OBJECTPROPERTY(o.[ID],'IsProcedure')=1)

)

BEGIN

DROP PROCEDURE Test

END

GO

CREATE PROCEDURE Test

AS

SELECT 'Hello'

GO

EXEC Test

Procedura do usuwania procedur

/* Jak ju

ż

istnieje takowa to usuwamy j

ą

*/

IF EXISTS

(SELECT 1

FROM sysobjects o

WHERE (o.[name] = 'UsunProc') AND
(OBJECTPROPERTY(o.[ID],'IsProcedure')=1)

)

BEGIN

DROP PROCEDURE UsunProc

END

GO

Procedura do usuwania procedur -

definicja

CREATE PROCEDURE UsunProc (@proc nvarchar(100))

AS

DECLARE @stmt nvarchar(1000)

IF EXISTS

(SELECT 1

FROM sysobjects o

WHERE

(o.[name] = @proc)

AND

(OBJECTPROPERTY(o.[ID], 'IsProcedure')=1)

)

BEGIN

SET @stmt = 'DROP PROCEDURE ' + @proc

EXEC sp_executeSql @stmt=@stmt

END

GO

Procedura do usuwania procedur -

testowanie

• Wykonujemy trzy osobne działania.

Poniewa

ż

wcze

ś

niej stworzyli

ś

my

procedur

ę

Test

to sprawdzamy, czy

istnieje – uruchamiaj

ą

c j

ą

:

EXEC Test /*powinno dzialac*/

• Usuwamy stosuj

ą

c nasz

ą

procedur

ę

:

EXEC UsunProc @proc = ‘Test’

• Sprawdzamy czy nadal Test istnieje

EXEC Test /*bł

ą

d – procedura nie isnieje*/

Podsumowanie wiedzy -

Ć

wiczenie

•

Napisa

ć

procedur

ę

, która zwróci

ś

redni

ą

pensj

ę

w kazdej z firm, maksymaln

ą

i

dane osoby która ma t

ę

maksymaln

ą

•

Nale

ż

y pamieta

ć

,

ż

e tak jak w przypadku

zapytania z funkcj

ą

agreguj

ą

c

ą

, nie da

rady uzyska

ć

wyniku w jednym zapytaniu

•

Wynik uzyskujemy w dwu krokach (z
zapamietaniem po

ś

rednich wyników w

tabeli tymczasowej)

Ć

wiczenie posumowuj

ą

ce

Krok 1

Wybieramy zapytaniem z grupowaniem firmy i

srednie i maksymalne pensje do tabeli
roboczej #w

SELECT e.id_firmy

, AVG(e.pensja) AS sr_p

, MAX(e.pensja) AS max_p

, MIN(e.pensja) AS min_p

INTO #w

FROM etaty e

WHERE

(e.do IS NULL)

GROUP BY e.id_firmy

Tabel

ę

tymczasow

ą

łaczymy z

etatami

SELECT LEFT(f.nazwa,30) AS Firma

,

#w.*

,

oMin.imi

ę

_i_nazwisko AS osoba_z_min_p

,

oMax.imi

ę

_i_nazwisko AS osoba_z_max_p

FROM firmy f

join #w ON (f.nazwa_skr = #w.id_firmy)

join etaty eMin ON

(

(eMin.pensja = #w.min_p)

AND

(eMin.id_firmy=#w.id_firmy)

)

join etaty eMax ON

(

(eMax.pensja=#w.max_p)

AND

(eMax.id_firmy=#w.id_firmy)

)

join osoby oMin ON (oMin.id_osoby=eMin.id_osoby)

join osoby oMax ON (oMax.id_osoby=eMax.id_osoby)

WHERE

(eMin.do IS NULL) AND (eMax.do IS NULL)

ORDER BY f.nazwa

Ć

wiczenie - cało

ść

EXEC UsunProc @proc = 'StatystykiFirm'
GO
CREATE PROCEDURE StatystykiFirm
AS

SELECT e.id_firmy
,

AVG(e.pensja) AS sr_p

,

MAX(e.pensja) AS max_p

,

MIN(e.pensja) AS min_p INTO #w
FROM etaty e
WHERE

(e.do IS NULL)

GROUP BY e.id_firmy

SELECT LEFT(f.nazwa,30) AS Firma
,

#w.*

,

oMin.imi

ę

_i_nazwisko AS osoba_z_min_p

,

oMax.imi

ę

_i_nazwisko AS osoba_z_max_p

FROM firmy f
join #w ON (f.nazwa_skr = #w.id_firmy)
join etaty eMin ON

(

(eMin.pensja = #w.min_p)

AND

(eMin.id_firmy=#w.id_firmy)

)

join etaty eMax ON

(

(eMax.pensja=#w.min_p)

AND

(eMax.id_firmy=#w.id_firmy)

)

join osoby oMin ON (oMin.id_osoby=eMin.id_osoby)
join osoby oMax ON (oMax.id_osoby=eMax.id_osoby)
WHERE

(eMin.do IS NULL) AND (eMax.do IS NULL)

ORDER BY f.nazwa

GO

Ć

wiczenie – testowanie procedury

• Uruchomienie procedury

exec StatystykiFirm

• Wynik:

Firma id_firmy sr_p max_p min_p osoba_z_min_p osoba_z_max_p

----------------- -------- ---------- ---------- ---------- ------------- -------------

Fabryka Łodzi Pod FŁP 65200.00 65200.00 65200.00

K. Neptun K. Neptun

Hewlett Packard HP 20000.00 20000.00 20000.00 M. Stodolski M. Stodolski

Politechnika Wars PW 2866.67 3200.00 2200.00 M. Stodolski M. Stodolski

Politechnika Wars PW 2866.67 3200.00 2200.00 M. Stodolski J. Korytkows

Wstawianie rekordu do tabeli

• Dodanie nowej danej:

INSERT INTO etaty

( id_firmy

, id_osoby

, stanowisko

, od

, pensja

) VALUES

( 'FŁP'

, 1

, 'polerowacz wyrz.torped'

, CONVERT(datetime, '20061101', 112)

, 2000

)

Test po wstawieniu nowego

wiersza

Firma id_firmy sr_p max_p min_p osoba_z_min_p osoba_z_max_p

----------------- -------- ---------- ---------- ---------- ------------- -------------

Fabryka Łodzi Pod FŁP 33600.00 65200.00 2000.00 M. Stodolski K. Neptun

Hewlett Packard HP 20000.00 20000.00 20000.00 M. Stodolski M. Stodolski

Politechnika Wars PW 2866.67 3200.00 2200.00 M. Stodolski M. Stodolski

Politechnika Wars PW 2866.67 3200.00 2200.00 M. Stodolski J. Korytkows

• Politechnika wyst

ę

puje dwukrotnie, gdy

ż

zł

ą

czenie #w i eMax daje dwa wiersze -> dwie

osoby maj

ą

takie same, maksymalne pensje.

Dalsze zł

ą

czenie tych dwu osób z eMin daje

ka

ż

dy z kazdym, czyli dwa wiersze

Zmiana procedury

•

Rezygnujemy z pokazywania naraz osoby z min i max pensj

ą

ALTER PROCEDURE StatystykiFirm

AS

SELECT e.id_firmy

,

AVG(e.pensja) AS sr_p

,

MAX(e.pensja) AS max_p

,

MIN(e.pensja) AS min_p INTO #w

FROM etaty e

WHERE

(e.do IS NULL)

GROUP BY e.id_firmy

SELECT

LEFT(f.nazwa,20) AS Firma

,

#w.id_firmy

,

STR(#w.sr_p,10,2) AS sr_p

,

STR(#w.max_p,10,2) AS max_p

,

STR(#w.min_p,10,2) AS min_p

,

LEFT(oMax.imi

ę

_i_nazwisko,12) AS osoba_z_max_p

FROM firmy f

join #w ON (f.nazwa_skr = #w.id_firmy)

join etaty eMax ON ((eMax.pensja=#w.max_p) AND (eMax.id_firmy=#w.id_firmy))

join osoby oMax ON (oMax.id_osoby=eMax.id_osoby)

WHERE

(eMax.do IS NULL)

ORDER BY f.nazwa

Wynik poprawionej procedury

EXEC StatystykiFirm

Firma id_firmy sr_p max_p min_p osoba_z_max_p

---------------- -------- ---------- ---------- ---------- -------------

Fabryka Łodzi Po FŁP 33600.00 65200.00 2000.00 K. Neptun

Hewlett Packard HP 20000.00 20000.00 20000.00 M. Stodolski

Politechnika War PW 2866.67 3200.00 2200.00 M. Stodolski

Politechnika War PW 2866.67 3200.00 2200.00 J. Korytkows

Słownik cech

select c.*, wc.opis_wartosci AS opis

from cechy c

join wartosci_cech wc

ON (wc.id_cechy = c.id_cechy)

order by c.id_cechy

id_cechy opis_cechy jednowybieralna opis

----------- ------------------------------ --------------- ---------

1 Bran

ż

a 0 Komputery

1 Bran

ż

a 0 Drukarki

1 Bran

ż

a 0 Szkolenia

2 Odpowiedz na Akcj

ę

Marketingow 1 NIE

2 Odpowiedz na Akcj

ę

Marketingow 1 TAK

Zasady projektowania efektywnych

baz

• Wszystkie tabele projektowa

ć

zgodnie ze sztuk

ą

– Ka

ż

da z kluczem głownym

– Klucze obce

– Unika

ć

automatycznych mechanizmów kaskadowych

• Rzeczy, cechy których nie przewidzimy – da

ć

mozliwo

ś

c definiowania ich przez u

ż

ytkownika

– Budujemy słownik cech

– Słownik mo

ż

liwych warto

ś

c

– Tabel

ę

wybranych cech

Rozbudowa Bazy testowej

C E C H Y

id _C E C H Y

O p is_c e c h y

j e d n o w y b ie r a ln a

e ta ty

id _o so b y

id _fir m y

sta n o w isk o

p e n sj a

o d

d o

id _e ta tu

f i r m y *

n a zw a _sk r

id _m ia sta

n a zw a

k o d _p o c zto w y

u lic a

F I R M Y _ C E C H Y *

id _w a r to sc i

id _fir m y

m i a s ta

id _m ia sta

k o d _w o j

n a zw a

o s o b y

id _o so b y

id _m ia sta

im ię

n a zw isk o

im ię _i_n a zw isk o

d a ta _u r

w ie k

W A R T O S C I _ C E C H

id _w a r to sc i

id _C E C H Y

O p is_w a r to sc i

w o j

k o d _w o j

n a zw a

Przeciez ka

ż

d

ą

kolumn

ę

da si

ę

zdefiniowa

ć

jako cech

ę

w słowniku

• TAK baza staje si

ę

wtedy elestyczna ALE:

– Aplikacja bardzo trudna w realizacji
– Bardzo trudne zachowanie integralno

ś

ci bazy

– Znacznie mniej efektywne zapytania

• Dlatego nale

ż

y zdefiniowa

ć

tabele ze wszystkimi

waznymi (i mniej waznymi) kolumnami, które
mog

ą

by

ć

wykorzystane na ekranach, raportach,

wydrukach

• Słownik cech traktowa

ć

jako narz

ę

dzie do np.

wyszukiwania danych, statystyk

Przykładowe dane:

SELECT fc.id_firmy, LEFT(c.opis_cechy,30) AS Cecha

,

LEFT(wc.opis_wartosci,10) AS wartosc, c.id_cechy

, wc.id_wartosci AS idw

FROM firmy_cechy fc

join wartosci_cech wc on (wc.id_wartosci=fc.id_wartosci)

join cechy c on (c.id_cechy = wc.id_cechy)

id_firmy Cecha wartosc id_cechy idw

-------- ------------------------------ ---------- -------- ----

HP Bran

ż

a Komputery 1 1

HP Bran

ż

a Drukarki 1 2

HP Bran

ż

a Szkolenia 1 3

PW Bran

ż

a Szkolenia 1 3

FŁP Odpowiedz na Akcj

ę

Marketingow TAK 2 5

HP Odpowiedz na Akcj

ę

Marketingow TAK 2 5

Jak znale

źć

dane spełniaj

ą

ce kilka

cech ?

Np. Bran

ż

a -> Szkolenia, Na akcj

ę

-> TAK:

SELECT fc.id_firmy, LEFT(c.opis_cechy,30) AS Cecha

, LEFT(wc.opis_wartosci,10) AS wartosc,

c.id_cechy

, wc.id_wartosci AS idw

FROM firmy_cechy fc

join wartosci_cech wc on
(wc.id_wartosci=fc.id_wartosci)

join cechy c on (c.id_cechy = wc.id_cechy)

WHERE

(c.id_cechy = 1 AND wc.id_wartosci=3)

AND

(c.id_cechy = 2 AND wc.id_wartosci=5)

•

Wynik zapytania PUSTY. Nie jest mo

ż

liwe

ż

eby w jednym

wierszu jedo pole miało zarówno warto

ść

1 jak i 2

JEDNOCZE

Ś

NIE

Poprawne zapytanie

SELECT fc.id_firmy, LEFT(c.opis_cechy,30) AS Cecha

, LEFT(wc.opis_wartosci,10) AS wartosc,c.id_cechy

, wc.id_wartosci AS idw

FROM firmy_cechy fc join wartosci_cech wc

on (wc.id_wartosci=fc.id_wartosci)

join cechy c on (c.id_cechy = wc.id_cechy)

WHERE

(c.id_cechy = 1 AND wc.id_wartosci=3)

OR

(c.id_cechy = 2 AND wc.id_wartosci=5)

id_firmy Cecha wartosc id_cechy idw

-------- ----------------------------- ---------- ----------- ---

FŁP Odpowiedz na Akcj

ę

Marketigow TAK 2 5

HP Odpowiedz na Akcj

ę

Marketngow TAK 2 5

HP Bran

ż

a Szkolenia 1 3

PW Bran

ż

a Szkolenia 1 3

Szukanie danych spełniaj

ą

cych

cechy

• Szukamy N róznych warto

ś

ci cech

• Je

ż

eli firma X ma je wszystkie to w wyniku

zapytania wyst

ą

pi tyle razy ilu cech

szukamy

• Wystarczy skorzysta

ć

z umiej

ę

tno

ś

ci

grupowania danych z dodatkowym
warunkiem HAVING.

• Liczymy z ilu rekordów składa si

ę

grupa.

Procedura szukania danych

posiadaj

ą

cych wybrane cechy

EXEC UsunProc 'FirmyMajaceCechy'

GO

CREATE PROCEDURE FirmyMajaceCechy

AS

DECLARE @ile_cech int

SELECT @ile_cech = COUNT(*) FROM #wc

SELECT

f.nazwa_skr, f.nazwa

FROM firmy f join firmy_cechy fc

ON (f.nazwa_skr = fc.id_firmy)

join wartosci_cech wc

on (wc.id_wartosci=fc.id_wartosci)

join #wc ON (#wc.id_wartosci=wc.id_wartosci)

GROUP BY

f.nazwa_skr, f.nazwa

HAVING COUNT(*) = @ile_cech

GO

Testowanie Procedury

• Tworzymy tymczasow

ą

tabel

ę

i wstawiamy

do niej szukane warto

ś

ci cech

CREATE TABLE #wc (id_wartosci int not null)

INSERT INTO #wc (id_wartosci) VALUES (5)

INSERT INTO #wc (id_wartosci) VALUES (3)

EXEC FirmyMajaceCechy

• Uzyskany wynik:

nazwa_skr nazwa

--------- ------------------------------

HP Hewlett Packard

„Klejenie” wyników zapyta

ń

• Naszym zadaniam jest pokazanie osób, które

nigdzie aktualnie nie pracuj

ą

i pracuj

ą

cych

aktualnie wraz z ich pensjami na jednym
raporcie

• Skorzystami z mozliwo

ś

ci „klejenia” zapyta

ń

UNION

• Zapytania musz

ą

mie

ć

tak

ą

sam

ą

ilo

ść

kolumn,

takich samych typów

• Kolumny wynikowe przyjm

ą

nazwy kolumn z

pierwszego zapytania

Ć

wiczenie na UNION

• Pokaza

ć

nazwy województw i miast w bazie oraz

województwa bez wpisanych miast

SELECT w.kod_woj, w.nazwa AS Woj, m.nazwa AS Miasto

FROM woj w join miasta m ON (w.kod_woj=m.kod_woj)

UNION ALL /*bez ALL SQL wybierze wszystkie wiersze z 1

pytania i te z drugiego, których nie było w pierwszym*/

SELECT w.kod_woj, w.nazwa, '<Brak miast>'

FROM woj w

WHERE NOT EXISTS

( SELECT 1

FROM Miasta mw

WHERE

(mw.kod_woj = w.kod_woj)

)

ORDER BY

2 /*mo

ż

na tylko podawa

ć

numery kolumn*/

Wynik

ć

wiczenia z UNION

kod_woj Woj Miasto

------- ------------------------------ ------------

??? <Nieznane> <Brak miast>

Maz Mazowieckie WESOŁA

Maz Mazowieckie WARSZAWA

Pom Pomorskie GDA

Ń

SK

Pom Pomorskie SOPOT

• WNIOSKI

:

– Połaczenie dwóc tabel w zapytaniu SELECT

ze sob

ą

daje w wyniku tylko wiersze, które

maj

ą

odpowiedniki w obydwu tabelach

Zł

ą

czenia „otwarte”

• Je

ż

eli chcemy w wniku zapytania uzyska

ć

wszystkie wiersze z jednej tabeli i ew.
odpowiadaj

ą

ce im wiersze z drugiej tabeli

to nale

ż

y u

ż

y

ć

zł

ą

czenia lewostronnie

otwartego :O

SELECT w.*, m.nazwa AS miasto

FROM woj w

LEFT OUTER join

miasta m

ON (w.kod_woj = m.kod_woj)

Wynik zł

ą

czenia „otwartego”

• Jak nie ma odpowiedników w drugiej tabeli to

SQL pokazuje NULL

kod_woj nazwa miasto

------- ------------ ---------

??? <Nieznane>

NULL

Maz Mazowieckie WESOŁA

Maz Mazowieckie WARSZAWA

Pom Pomorskie GDA

Ń

SK

Pom Pomorskie SOPOT

Zapytaie, które zwróci ten sam

wynik co UNION

SELECT w.kod_woj, w.nazwa

,

ISNULL(m.nazwa,'<Brak miast>')

AS miasto

FROM woj w

LEFT OUTER join

miasta m

ON (w.kod_woj = m.kod_woj)

kod_woj nazwa miasto

------- ------------ ------------

??? <Nieznane> <Brak miast>

Maz Mazowieckie WESOŁA

Maz Mazowieckie WARSZAWA

Pom Pomorskie GDA

Ń

SK

Pom Pomorskie SOPOT