Transcript SQL2 - WordPress.com

Klauzule GROUP BY i HAVING
Klauzula GROUP BY ima zadatak da omogući
grupisanje vrsta u rezultujućoj tabeli na osnovu
zajedničkih vrednosti. Time se povećava vrednost
funkcija agregacije jer se u kombinaciji sa GROUP BY
klauzulom mogu primenjivati na grupe vrsta, a ne
samo na čitavu rezultujuću tabelu.
Potrebno je voditi računa, da ukoliko ne
postoji GROUP BY klauzula, u SELECT klauzuli nije
moguće kombinovati funkcije agregacije sa imenima
kolona. U nastavku je dat SQL upit koji NE MOŽE DA
SE IZVRŠI i koji će dovesti do POJAVE GREŠKE.
SELECT Radnik, SUM(Sati) AS UkupanBrojRadnihSati
FROM RADI_NA;
Ovaj upit je moguć samo uz upotrebu GROUP BY klauzule.
U nastavku je dat pravilan SQL upit.
SELECT Radnik, SUM(Sati) AS UkupanBrojRadnihSati
FROM RADI_NA
GROUP BY Radnik;
Klauzula GROUP BY zahteva od DBMS-a da izvrši
sortiranje rezultujuće tabele prema specificiranim
kolonama i izvrši grupisanje vrsta koje imaju iste
vrednosti za specificirane kolone. Ukoliko su prisutne
funkcije agregacije one će se primeniti na tako dobijene
grupe. Tek uz prisustvo GROUP BY klauzule moguće je u
SELECT klauzuli kombinovati imena kolona i funkcije
agregacije.
Bitno je da napomenuti da se klauzula GROUP BY
izvršava nakon klauzule WHERE odnosno da se
grupisanje vrši tek nakon što su određene vrste koje
treba da uđu u sastav rezultujuće tabele.
U nastavku je dat primer SQL upita koji za
svaki sektor računa broj radnika koji rade u njemu.
Za grupisanje radnika po broju sektora u kome radi
iskorišćena je GROUP BY klauzula.
SELECT Sektor, COUNT(*) AS BrojRadnika
FROM RADNIK
GROUP BY Sektor;
Klauzulu GROUP BY je moguće primeniti
istovremeno na veći broj kolona. Pri tome su kriterijumi
za
formiranje
grupa
zajedničke
vrednosti
u
specificiranim kolonama. Prilikom formiranja grupa vodi
se računa i o redosledu po kome su kolone za
grupisanje navedene (kao da se formiraju grupe sa
podgrupama u okviru njih).
SELECT Sektor, Pol, AVG(Plata) AS ProsekPlata
FROM RADNIK
GROUP BY Sektor, Pol
ORDER BY AVG(Plata);
VAŽNA NAPOMENA
Prilikom korišćenja klauzule GROUP BY, sve
kolone koje su navedene u klauzuli SELECT, a na
koje nije primenjena neka funkcija agregacije,
MORAJU BITI NAVEDENE U GROUP BY KLAUZULI. U
suprotnom SQL upit neće moći da se izvrši.
Klauzula HAVING omogućava filtriranje rezultata
dobijenih korišćenjem GROUP BY klauzule.
HAVING je sličan WHERE klauzuli!
Modifikujmo sada prethodni upit tako da
prikažemo podatke samo o sektorima koji imaju više od
jednog radnika.
SELECT Sektor, COUNT(*) AS BrojRadnika
FROM RADNIK
GROUP BY Sektor
HAVING COUNT(*) > 1;
Klauzule GROUP BY i HAVING je moguće
kombinovati sa WHERE klauzulom. Pri tome treba
voditi računa o redosledu izvršavanja!
Redosled je sledeći:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Sledeći upit prikazuje sektore u kojima je broj
radnika ženskog pola veći od 1.
SELECT Sektor, COUNT(*) AS BrojRadnica
FROM RADNIK
WHERE Pol = 'Ž'
GROUP BY Sektor
HAVING COUNT(*) > 1;
Svi SQL upiti koje smo do sada razmatrali su
koristili podatke iz samo jedne tabele! Često se javlja
situacija da se tražena informacija nalazi u većem
broju tabela. U takvim situacijama potrebno je
izvršiti spajanje vrsta iz različitih tabela i generisanje
rezultujuće tabele. Za pribavljanje podataka iz većeg
broja tabela dovoljno je u klauzuli FROM navesti
imena tabela iz kojih želimo da pribavimo podatke.
Da bi spajanje tabela bilo uspešno, osim u
nekim specijalnim slučajevima, potrebno je da
navedemo uslov spoja, odnosno da navedemo
kolone na osnovu čijih vrednosti se vrši spajanje
vrsta iz različitih tabela.
Spajanje tabela se vrši tako što se najčešće
uparuje strani ključ iz jedne tabele sa primarnim
ključem koji referencira u drugoj tabeli.
Uslov spajanja može da se zada u okviru
WHERE klauzule ili korišćenjem ključne reči JOIN u
okviru FROM klauzule.
Spoj na jednakost (equi-join) obezbeđuje
spajanje podataka iz dve ili više tabela na osnovu
jednakosti odgovarajućih atributa, obično na
osnovu primarnih i spoljnih ključeva.
Najjednostavniji slučaj navođenja spoja je
kada se u WHERE klauzuli specificira uslov spoja
po jednakosti.
Dekartov proizvod je slučaj kada u WHERE
klauzuli ne postoji uslov spoja, a u FROM klauzuli
je navedeno više tabela.
U tom slučaju nema spajanja vrsta po
vrednosti nekog
atributa, već se pravi
kombinacija svake vrste iz jedne tabele sa
svakom vrstom iz druge tabele (u slučaju
Dekartovog proizvoda dve tabele).
Spoljni spoj (outer-join) omogućava spajanje
dve tabele po vrednosti nekog atributa, ali i
uključivanje onih torki (vrsta) iz jedne ili druge
tabele (ili iz obe), koje ne zadovoljavaju uslov
jednakosti.
Da bi bolje razumeli različite tipove spoja
koristićemo tabele RADNIK i SEKTOR, ali u nešto
jednostavnijem
obliku.
Pojednostavljene
verzije
tabela, RADNIK1 i SEKTOR1, su date u nastavku. Za
svaki tip spoja daćemo oba oblika navođenja uslova
spoja: i korišćenjem WHERE klauzule i korišćenjem
ključne reči JOIN u klauzuli FROM.
Modifikovane tabele RADNIK i SEKTOR
Dekartov proizvod dve tabele (A CROSS JOIN B)
se dobija tako što se svaka vrsta iz jedne tabele
kombinuje sa svakom vrstom iz druge tabele.
Prilikom korišćenja Dekartovog proizvoda
tabela treba voditi računa da sintaksa CROSS JOIN
nije podržana od strane MS Access-a.
SELECT *
FROM SEKTOR1, RADNIK1;
ili
SELECT *
FROM SEKTOR1 CROSS JOIN RADNIK1;
Inner join predstavlja najčešće korišćeni tip
spoja. Ovaj tip spoja, u osnovi, definiše presek vrsta
iz tabela koje učestvuju u spoju.
Prilikom spajanja dve tabele (A INNER JOIN B)
uzimaju se sve vrste iz tabele A i pronalazi im se
odgovarajuća vrsta u tabeli B. Ukoliko vrsta iz
tabele A nema odgovarajuću vrstu u tabeli B ne
uključuje se u rezultat. Ukoliko vrsti iz tabele A
odgovara više vrsta tabele B ona se u rezultatu
ponavlja više puta (po jednom za svaku
odgovarajuću vrstu u tabeli B).
Prilikom spajanja tabela treba voditi računa o
tome da kolone u različitim tabelama mogu imati ista
imena. Potrebno je obezbediti mehanizam koji će
DBMS-u precizno specificirati kolonu koju želite da
referencirate.
U takvim situacijama se koristi sintaksa
IME_TABELE.IME_KOLONE. U SQL upitima koji su dati u
nastavku taj pristup je iskorišćen za kolone
SEKTOR1.Broj i RADNIK1.Sektor mada nije bilo
neophodno jer kolone imaju različita imena.
Isti pristup treba primeniti i za klauzulu SELECT
ukoliko se javi sličan problem.
SELECT *
FROM SEKTOR1, RADNIK1
WHERE SEKTOR1.Broj = RADNIK1.Sektor;
ili
SELECT *
FROM SEKTOR1 INNER JOIN RADNIK1
ON SEKTOR1.Broj = RADNIK1.Sektor;
Left-outer join u osnovi predstavlja prošireni
inner-join. Ovaj tip spoja, pored vrsta koje uključuje
unutrašnji spoj, uključuje i vrste iz tabele A (leve tabele)
koje nemaju odgovarajuću vrstu u tabeli B (desnoj
tabeli). Kolone iz tabele B u ovom slučaju imaju vrednost
NULL.
SELECT *
FROM SEKTOR1 LEFT OUTER JOIN RADNIK1
ON SEKTOR1.Broj = RADNIK1.Sektor;
Right outer join funkcioniše kao i left outer join
samo je sada uloga tabela promenjena. Desni spoljašnji
spoj u rezultat uključuje vrste iz tabele B (desne tabele)
koje nemaju odgovarajuću vrstu u tabeli A (levoj tabeli).
SELECT *
FROM SEKTOR1 RIGHT OUTER JOIN RADNIK1
ON SEKTOR1.Broj = RADNIK1.Sektor;
Full outer join predstavlja kombinaciju rezultata
koje vraćaju left outer i right outer join. Poptpuni
spoljašnji spoj sadrži vrste iz obe tabele (i iz A i iz B)
koje nemaju odgovarajuće slogove u drugoj tabeli.
MS Access ne podržava FULL OUTER JOIN
sintaksu koja je korišćena u narednom SQL upitu.
SELECT *
FROM SEKTOR1 FULL OUTER JOIN RADNIK1
ON SEKTOR1.Broj = RADNIK1.Sektor;
Primer1. U nastavku je dat SQL upit koji prikazuje imena
sektora i broj radnika koji rade u njima. Obratite pažnju
da je kod ovog upita iskorišćena mogućnost da se
tabelama dodele pseudonimi.
SELECT S.Broj, S.Naziv, COUNT(*) AS BrojR
FROM SEKTOR1 AS S, RADNIK1 AS R
WHERE S.Broj=R.Sektor
GROUP BY S.Broj, S.Naziv;
Primer 2. Sledeći SQL upit za sve žene radnike određuje
imena projekata na kojima su angažovane.
Obratite pažnju da WHERE klauzula pored uslova
spoja može da sadrži i uslove kojima se selektuju vrste
koje će ući u rezultujuću tabelu.
Takođe, obratite pažnju da sada podatke izvlačimo
iz tri tabele: RADNIK, RADI_NA i PROJEKAT. Broj tabela iz
kojih izvlačimo podatke nije ničim ograničen. Potrebno je
voditi računa da spojevi između tabela budu definisani na
odgovarajući način kako bi dobili željene podatke
(RADI_NA.Radnik i RADNIK.MatBr, RADI_NA.Projekat i
PROJEKAT.Broj).
SELECT R.MatBr, R.Ime, R.Prezime, P.Naziv
FROM RADNIK AS R, RADI_NA AS RN, PROJEKAT AS P
WHERE R.MatBr = RN.Radnik AND RN.Projekat = P.Broj
AND R.Pol = "Ž";
Primer 3. Naredni SQL upit za svaki sektor daje ukupan
broj radnih sati koje radnici provode na projektima za
koje je taj sektor zadužen.
SELECT S.Broj, S.Naziv, Sum(RN.Sati) AS SatiUkupno
FROM SEKTOR AS S, PROJEKAT AS P, RADI_NA AS RN
WHERE S.Broj = P.Sektor AND P.Broj = RN.Projekat
GROUP BY S.Broj, S.Naziv;
ZADACI ZA VEŽBU
1. Napisati SQL upit koji za svaki broj sektora određuje broj projekata za koji je on
zadužen.
2. Napisati SQL upit koji za svaki broj sektora računa prosečnu i maksimalnu platu
radnika koji rade u tom sektoru.
3. Napisati SQL upit koji za svaki tip srodstva određuje broj članova porodice koji
imaju taj tip srodstva.
4. Napisati SQL upit koji određuje brojeve sektora koji su zaduženi za dva i više
projekta.
5. Napisati SQL upit koji određuje koliko radnika radi na projektu broj 2, a koliko na
projektu broj 20.
6. Napisati SQL upit koji određuje koliko ukupno radnika radi na projektu broj 2 i 20.
7. Napisati SQL upit koji za svakog radnika prikazuje ime, prezime, pol i naziv
sektora u kome radi.
8. Napisati SQL upit koji za svaki projekat prikazuje njegov broj, naziv i broj
angažovanih radnika.
9. Modifikovati upit iz zadatka 8 tako da se prikazuju samo podaci o projektima
koji imaju više od jednog angažovanog radnika.
10. Napisati SQL upit koji prikazuje imena i prezimena radnika koji imaju više od
jednog člana porodice.
11. Napisati SQL upit koji određuje imena i prezimena radnika koji rade u
sektoru broj 4 i nemaju nijednog člana porodice.
12. Napisati SQL upit koji za svakog radnika određuje ime i prezime njegovog
rukovodioca.
13. Napisati SQL upit koji određuje imena i prezimena radnika koji se nalaze na
poziciji šef sektora.
14. Napisati SQL upit koji za svaki projekat određuje ime i prezime rukovodioca
sektora koji je zadužen za taj projekat.