Napredni SQL

1. Napredni SQL Prof. dr MILORAD K. BANJANIN Fakultet tehničkih nauka, Univerzitet u Novom Sadu

2. Korišćenje različitih gledišta (views) koje omogućuje personalizaciju prikaza podatakakroz ograničavanje pristupa tabelama sa podacima Kreiranje naprednih pretraga, koje uključuju podpretrage i korelisane pretrage Korišćenje egzistencijalnih i univerzalnih uslova čime se osigurava postojanje određenih elemenata baze podataka Agregiranje podataka i unija rezultata pretraga korišćenjem SUM, MIN, MAX, AVG, COUNT i UNION naredbe i spoljnih spojeva (Outer Joins) Kreiranje i korišćenje indeksa kojima se unapređuju performanse pretraga 1 2 3 4 5 Dabi se mogao savladati napredni SQL neophodno je dobro poznavanje osnovnog SQL-a, što uključuje poznavanje naredbi SELECT, GROUP BY, ORDER BY i ugrađenih funkcija, poželjno je i poznavanje združenih pretraga – spojeva. Savladavanje naprednog SQL-a će, između ostalog, da omogući:

3. GROUP BY Specijalna upotreba naredbe GROUP BY je u povezivanju ugrađenih funkcija (posebno COUNT – određivanje broja redova u svakoj grupi) sa grupama redova. PRIMER Pretpostavlja se da tabela Kupci ima kolonu Cena, kao i da svaki red ima vrednost za tu kolonu. Kada se želi videti cena najskupljeg predmeta koji je kupio svaki kupac neophodno je grupisati sve kupovine svakog kupcai pronaći proizvod sa najvećom cenom: SELECT IDKupca, MAX(Cena) FROM Kupci GROUP BY IDKupca; Kada želimo da vidimo samo kupovinu sa najvećom cenom proizvoda ukoliko je kupovina iznad 1000 dinara, koristi se HAVING klauzula: SELECT IDKupca, MAX(Cena) FROM Kupci GROUP BY IDKupca HAVING Cena>1000;

4. ORDER BY ORDER BY naredba se koristi za alfabetsko ispisivanje rezultata pretrage. PRIMER Pretpostavlja se da postoje tabele Prodavnice i VlasniciProdavnica, gde se u tabeli Prodavnice beleži prodaja proizvoda, dok se tabela VlasniciProdavnica koristi za skladištenje imena i prezimena vlasnika prodavnica u kojima se prodaju proizvodi. Kada se želi videti koji vlasnici su prodali bar jedan proizvod i to u alfabetskom poretku (prvo po prezimenu, a zatim i po imenu) koristi se sledeća sintaksa: Koristi se za eliminisanje duplikata redova, izlistava samo jednom osobu koja je prodala nešto SELECTDISTINCT IDProdavca, PrezimeVlasnika, ImeVlasnika FROM Prodavnice, VlasniciProdavnica WHERE IDProdavca=IDVlasnika ORDER BY PrezimeVlasnika, ImeVlasnika;

5. Ugrađene funkcije INTERSECT - daje redove koji se javljaju u obe pretrage MINUS - daje redove koji se javljaju u prvoj pretrazi, ali ne i u drugoj COMPUTE - postavlja se na kraj pretrage da bi postavila rezultat ugrađene funkcije na kraj liste ABS(X) - konvertuje negativne u pozitivne brojeve CEIL(X) - X je decimalna vrednost koja se zaokružuje na veću celobrojnu vrednost FLOOR(X) - X je decimalna vrednost koja se zaokružuje na manju celobrojnu vrednost GREATEST (X,Y) - vraća veću od unete dve vrednosti LEAST(X,Y) - vraća manju od unete dve vrednosti MOD(X,Y) - vraća ostatak deljenja X/Y ROUND(X,Y) - zaokružuje X na broj decimala Y-a, ako je Y ceo broj onda zaokružuje X na najbliži ceo broj SIGN(X) - vraća minus ukoliko je X<0, drugačije vraća plus SQRT(X) - vraća kvadratni koren od X LEFT(<string>,X) - vraća X karaktera sa leve strane stringa RIGHT(<string>,X) - vraća X karaktera sa desne strane stringa UPPER(<string>) - konvertuje string u sva velika slova LOWER(<string>) - konvertuje string u sva mala slova LENGTH(<string>) - vraća broj karaktera u stringu <string>||<string> - kombinuje dva stringa teksta u jedan, gde je prvi string praćen drugim bez ikakvih razmaka LPAD(<string>,X,’*’) - proširuje string sa leve strane karakterom * do dužine X NVL(<kolona>,<vrednost>) - funkcija null vrednosti menja <vrednost> u NULL u <kolona>, a ako je ta vrednost već NULL NVL nema efekta

6. NULL VREDNOSTI Kada kod---ne postoji vrednost nekog atributa moguće je postaviti NULL vrednost, a to može da znači sledeće: • Da ne postoji vrednost • Da vrednost postoji, ali nije poznata • Da vrednost nije primenjljiva • Itd. Shema specifikuje za svaki atribut da li on može dobiti NULL vrednost (da li je nulabilan atribut) ili ne. Provera NULL vrednosti se realizuje na sledeći način: a IS NULL a IS NOT NULL korišćenje provere NULL vrednostiomogućuje uključivanje svih osoba, tj. i onih kojima nisu definisane godine PRIMER SELECT * FROM Osobe WHERE godine<25 ORgodine>=25 OR godine IS NULL

7. INDEKSI Dozvoljavaju DBMS-u da pristupa podacima brže, jer sistem kreira internu strukturu podataka (indeks) koja omogućava brži izbor redova kada se selekcija zasniva na indeksovanim kolonama. Indeks govori DBMS-u gde se nalazi određeni red u tabeli na osnovu vrednosti indeksa kolone, analogno indeksu u knjizi koji govori na kojoj stranici knjige se nalazi određeni pojam. Indeksi su veoma važni za smanjivanje vremena potrebnog za procesiranje pretraga Kreiranje indeksa - Opšta sintaksa CREATE INDEX ime_indeksa ON ime_tabele (kolone_na_koje_se_odnosi); Indeksi mogu da se odnose na više od jednog atributa iz date tabele Brisanje indeksa - Opšta sintaksa DROP INDEX ime_indeksa;

8. Sekvencijalno skeniranje nekog fajla može da traje dugo, a indeksi skraćuju vreme pretrage. PRIMER Sekvencijalno skeniranje za Osobolje(ime, zvanje,plata) SELECT * FROM Osoblje WHERE ime=“Petar” Kreiranje indeksa za ime, kojim se ubrzava vreme prethodne pretrage B+ drva mogu imati najviše 4 nivoa Marko Nikola Ivan ... Petar ... CREATE INDEX ImeIndeks ON Osoblje(ime) Kreiranje indeksa za više atributa CREATE INDEX DupliIndeks ON Osoblje(ime,plata)

9. PODPRETRAGE Podpretragaproizvodi jednu vrednost ili relaciju, a ukoliko vrati više od jedne vrednosti to predstavlja run-time grešku. PRIMER SELECT Prodavnice.proizvod FROM Prodavnice WHERE kupac= (SELECT ime FROM Osobe WHERE ssn=‘123456789’); PODPRETRAGA To isto se može napisati i bez podpretrage, ali samo u slučaju kada je ssn ključ, a ‘123456789’ postoji u bazi podataka, a tada je sekvenca sledeća: SELECT Prodavnice.proizvod FROM Prodavnice, Osobe WHERE kupac=ime AND ssn=‘123456789’);

10. Podpretraga može da vrati kao rezultat i relaciju. PRIMER Pronaći kompanije čije je proizvode kupio Petar Petrović SELECTDISTINCT Kompanije.ime FROM Kompanije, Proizvodi WHERE Kompanije.ime=Proizvodi.proizvodjac AND Proizvodi.ime IN (SELECT Kupovine.proizvod FROM Kupovine WHERE Kupovine.kupac=‘Petar Petrovic’); Koristi se za eliminisanje duplikata u rezultatima podpretrage U ovom slučaju podpretraga vraća skup vrednosti, ali bez run-time greške Prethodni izraz je ekvivalentan sledećem izrazu bez podpretrage: SELECTDISTINCT Kompanije.ime FROM Kompanije, Proizvodi, Kupovine WHERE Kompanije.ime=Proizvodi.proizvodjac AND Proizvodi.im=Kupovine.proizvod AND Kupovine.kupac=‘Petar Petrovic’);

11. Za podpretrage koje vraćaju skup vrednosti ili relaciju se može koristiti i semantika: s> ALL R s> ANY R EXISTS R PRIMER Pronaći proizvod koji je skuplji od svih proizvoda koje proizvodi “Matijevic” SELECT ime FROM Proizvodi WHERE cena > ALL (SELECT cena FROM Kupovine WHERE proizvodjac=‘Matijevic’);

12. KORELISANE PRETRAGE Da bi se ubrzalo i pojednostavilo pretraživanje baze podataka po nekom kriterijumu koristi se korelacija koja se ugrađuje u pretragu. PRIMER Pronaći film čiji se naziv pojavljije više od jednog puta SELECTDISTINCT naziv FROM Filmovi AS x WHERE godina <>ANY (SELECT godina FROM Filmovi WHERE naziv=x.naziv); KORELACIJA PRIMER Pronaći proizvod (i njegovog proizvođača) koji se skuplji od svih proizvoda proizvedenih od strane tog proizvođača pre 1982. godine SELECTDISTINCT imeproizvoda, proizvodjac FROM ProizvodiAS x WHERE cena >ALL (SELECT cena FROM Proizvodi AS y WHEREx.proizvodjac=y.proizvodjac AND y.godina<1982); Primer kompleksne korelisane pretrage sa dve korelacije

13. EGZISTENCIJALNI/UNIVERZALNI USLOVI Egzistencijalni uslovi se koriste za utvrđivanje postojanja određenog elementa, a lakši su za realizaciju od univerzalnih uslova koji zahtevaju ispunjenost uslova u celoj bazi podataka. PRIMER Pronaći kompanije kod kojih neki proizvodi imaju cenuveću ili jednaku 100 SELECTDISTINCT Kompanije.imekompanije FROM Kompanije WHERE Kompanije.imekompanije IN (SELECT Proizvodi.kompanija FROM Proizvodi WHERE Proizvodi.cena>=100) Egzistencijalni uslov PRIMER Pronaći sve kompanije kod kojih svi proizvodi imaju cenumanju od 100 SELECTDISTINCT Kompanije.imekompanije FROM Kompanije WHERE Kompanije.imekompanije NOTIN (SELECT Proizvodi.kompanija FROM Proizvodi WHERE Proizvodi.cena>=100) Univerzalni uslov

14. AGREGIRANJE SQL podržava korišćenje nekoliko operacija za agregiranje (SUM, MIN, MAX, AVG i COUNT), koje se osim u slučaju COUNT operacije primenjuju na jedan atribut. Operacija COUNT se primenjuje na duplikate, osim ukoliko se to drugačije ne definiše: SELECT Count(kategorija) FROM Proizvodi WHERE godina > 1999 Drugi, čak i bolji, način za korišćenje operacije COUNT je sledeći: SELECT Count(DISTINCT kategorija) FROM Proizvodi WHERE godina > 1999 PRIMER Primer jednostavnog agregiranja je korišćenje operacije SUM: SELECT Sum(cena * kvantitet) FROM Kupovine WHERE proizvod =‘hleb’

15. Opšta forma za operacije grupisanja i agregiranja je sledeća: može da sadrži atribute a1,...,ak i/ili bilo koje agregate ali bez dodatnih atributa SELECT S FROM R1,...,Rn WHERE C1 GROUP BY a1,...,ak HAVING C2 predstavlja bilo koji uslov vezan za atribute R1,...,Rn predstavlja bilo koji uslov vezan za izraz agregiranja Koraci sprovođenja opšte forme za operacije grupisanja i agregiranja su sledeći: 1. Sprovođenje FROM-WHERE dela, koji se odnosi na tabelu sa svim atributima u R1,...,Rn 2. Grupisanje po atributima a1,...,ak 3. Izračunavanje agregatau C2 i zadržavanje samo onih grupa koje zadovoljavaju C2 4. Izračunavanje agregata u S i vraćanje rezultata

16. UNIJA I SPOLJNI SPOJEVI Postoje situacije kada je potrebno zajedno prikazati rezultate više pretraga, kombinovanjem njihovih autputa, a za to se koristi operacija UNION. PRIMER Potrebno je spojiti autput iz dve pretrage nad dve različite tabele, tako da se prikažu identifikacioni brojevi svih kupaca i svi oni koji su postavili narudžbinu: SELECT IDKupca FROM Prodavnice UNION SELECT IDVlasnika FROM Narudzbine Treba naglasiti da SQL zahteva da SELECT lista (kolona) mora da se podudara, kolonu po kolonu, po pitanju tipa podataka U ovom slučaju IDKupca i IDVlasnika su sigurno istog tipa (integer). Pored toga, važno je naglasiti i da SQL automatski eliminiše duplikatekada se koristi operacija UNOIN, pa nije potrebno koristiti operaciju DISTINCT kao u jednoj pretrazi.

17. Spoljni spoj (outer join) se koristi kada je pretraga spoja (join) “ujedinjena” sa redovima koji nisu uključeni u taj spoj. PRIMER prva pretraga koristi spoj da izdvoji sve vlasnike koji se nalaze u obe tabele SELECT IDVlasnika FROM Narudzbine, Prodavnice WHERE IDVlasnika=IDKupca UNION SELECT IDKupca FROM Prodavnice WHERE IDKupca NOT IN (SELECT IDVlasnika FROM Narudzbine); drugi autput se generiše tako što se prvo izdvoje oni identifikacioni brojevi koji nisu u tabeli Narudzbine, a to su ID-ovi koji se isključuju iz pretrage spoja zatim se pretražuje svaki red u tabeli Prodavnice, a ako IDKupca nije u listi isključenih ID-ova on postaje deo autput liste Međutim, takav koncept je koristan u situacijama kada je primarni ključ povezan sa stranim ključem, jer postoje i situacije kada je vrednost stranog ključa za neke primarne ključeve NULL vrednost

18. GLEDIŠTA (VIEWS) Gledišta predstavljaju relacije, osim ukoliko nisu fizički uskladištena, a koriste se za predstavljanje različitih informacija različitim korisnicima. Tipovi gledišta su: • Virtuelna gledišta - koriste se u bazama podataka, sprovode se samo po zahtevu, njihovo pokretanje je sporo i uvek su ažurna; • Materijalizovana gledišta - koriste se u skladištima podataka, sprovode se unapred (offline), njihovo pokretanje je brzo i mogu sadržati zastarele podatke; PRIMER Postoji tabela Zaposleni(licnibr, ime, departman, projekat, plata), definisati gledište za razvojni tim na tajnačin se svima ograničava pristup na tabelu Razvojnitim, dok departman za finansije ima pristup tabeli Zaposleni, kao i uvid u plate CREATE VIEW Razvojnitim AS SELECT ime, projekat FROM Zaposleni WHERE departman=‘razvoj’

19. PRIMER Postoje tabele Osobe(ime, grad) Kupovine(kupac, prodavac, proizvod, prodavnica) Proizvodi(ime, proizvodjac, kategorija) Potrebno je formirati gledište koje će da izdvoji kupce iz Novog Sada CREATE VIEW NoviSad-kupci AS SELECT kupac, prodavac, proizvod, prodavnica FROM Osobe, Kupovine WHERE Osobe.grad=‘Novi Sad’ AND Osove.ime=Kupovine.kupac Na taj način se kreira nova virtuelna tabela: NoviSad-kupci(kupac, prodavac, proizvod, prodavnica) Kasnije se to gledište može koristiti na sledeći način: SELECT ime, prodavnica FROM NoviSad-kupci, Proizvodi WHERE NoviSad-kupci.proizvod=Proizvodi.ime AND Proizvodi.kategorija=‘obuća’

20. Pošto tabele koje predstavljaju gledišta fizički ne postoje kada se želi ažurirati gledište koristi se sledeća semantika: INSERT INTO Razvojnitim VALUES (“Petar”, ”Istrazivac”) Međutim, tu semantiku SQL prevodi da bi izmenio postojeću (stvarnu) tabelu i koristi na sledeći način: INSERT INTO Zaposleni(licnibr, ime, departman, projekat, plata) VALUES (NULL, “Petar”, NULL, ”Istrazivac”, NULL) Važno je napomenuti da određena gledišta nisu podložna ažuriranju. PRIMER Postoje tabele Osobe(ime, grad) Kupovine(kupac, prodavac, proizvod, prodavnica) definisati gledište za grad-prodavnice ne može se dodati par (“Novi Sad”, “Zara”) ukoliko se ne poznaje ime osobe koja je izvršila kupovinu CREATE VIEW Grad-Prodavnica AS SELECT Osobe.grad, Kupovine.prodavnica FROM Osobe, Kupovine WHERE Osobe.ime=Kupovine.kupac

21. PRIMER UGRAĐENOG SQL KODA Ovaj primer se prikazuje zbog argumentacije, a koristi se ugrađeni SQL kod u C okruženju /*********************************************/ /* Ovaj program se ne može kompajlirati ili izvršiti */ /* Koristi se samo u svrhu davanja primera */ /*********************************************/ #include <stdio.h> /* Sledideklarisanje host varijabli, to su varijable koje koristi sam program, ali koje i SQL koristi za dodeljivanje ili preuzimanje vrednosti . */ EXEC SQL BEGIN DECLARE SECTION; int IDKupca; char Ime[100], Prezime[100], Predmet[100]; EXEC SQL END DECLARE SECTION; /* Sledeća linija koda uključuje i SQLCA varijable, pa može da se sprovede i provera grešaka. */ EXEC SQL INCLUDE SQLCA;

22. main() { /* Sledimogućinačin za pristup bazi podataka. */ EXEC SQL CONNECT UserID/Password; /* Sledeće linije kodaproveravaju da li je povezivanje sa bazom podataka uspelo ili je došlo do greške. */ If (sqlca.sqlcode) { printf (Printer, “Postojigreska u povezivanjusabazompodataka.\n”); exit(); } printf (“Povezivanjesabazompodatakauspesno.\n”); /* Sledeće linije deklarišu kursor, koji je neophodan ukoliko pretrage vraćaju rezultat u više redova, a potrebno je izvršiti operacije nad svakim od tih redova. Npr. može se koristiti operacija FETCH da se uzme jedan red. */ EXEC SQL DECLARE KursorPredmeta CURSOR FOR SELECT Predmet, IDKupca FROM Prodavnice ORDER BY Predmet EXEC SQL OPEN KursorPredmeta;

23. /* Na ovom mestu se može postaviti sličan blok za proveru grešaka. */ /* Operacija FETCH postavlja vrednosti “sledećeg” reda pretrage u host varijable, respektivno. Kada je kursor bez podataka biće generisan sqlcode koji dozvoljava napuštanje petlje. */ EXEC SQL FETCH KursorPredmeta INTO :Predmet, :IDKupca; while(!sqlca.sqlcode) { /* Sa svakimredom se radi više stvari. Prvo, povećava se cena za 50 dinara (marža prodavca) i uzima se ime prodavca da se stavi u izveštaj. Da bi se to postiglo koriste se UPDATE i SELECT pre štampanja linije na ekranu.Međutim, tu se pretpostavlja da je svaki kupac kupio samo jedan od svih ponuđenih predmeta, jer će inače cena biti povećana previše puta. Ukoliko to nije slučaj potrebno je koristiti “RowID” logiku, koja ovde neće biti obuhvaćena. Treba obratiti pažnju i na kolonu pre imena host varijabli kada se koristi unutar SQL izjave. */ EXEC SQL UPDATE Prodavnice SET Cena=Cena+50 WHERE PREDMET= :Predmet AND IDKUPCA= :IDKupca EXEC SQL SELECT IMEVLASNIKA, PREZIMEVLASNIKA INTO :Ime, :Prezime FROM VLASNICIPRODAVNICA WHERE IDKUPCA = :IDKpuca; printf(“%25s %25s %25s”, Ime, Prezime, Predmet);

24. /* Da bi se dobio neki oblik izveštaja potrebno je dodati još i sledeću liniju koda. */ EXEC SQL FETCH KursorPredmeta INTO :Predmet, :IDKupca; } /* Na kraju se zatvara kursor, sprovode promene i izlazi se iz programa. */ EXEC SQL CLOSE KursorPredmeta; EXEC SQL COMMIT RELEASE; exit(); }

25. PROVERA ZNANJA Na osnovu prikazanih primera potrebno je napisati SQL linije koda za: 1. Prikazivanje svih narudžbina obuce u tabeli Prodavnice, kao i prezimena i imena osoba koje su ih postavile. 2. Prikazivanje svih kolona u tabeli statistike zaposlenih prema poziciji, a zatim prema identifikacionom broju zaposlenih. SELECT VlasniciObuce.PrezimeVlasnika, VlasniciObuce.ImeVlasnika, Narudzbine.ZeljeniPredmet FROM VlasniciObuce, Narudzbine WHERE VlasniciObuce=Narudzbine.IDVlasnika; ili SELECT VlasniciObuce.PrezimeVlasnika, VlasniciObuce.ImeVlasnika, Narudzbine.ZeljeniPredmet FROM VlasniciObuce RIGHT JOIN Narudzbine ON VlasniciObuce.IDVlasnika=Narudzbine.IDVlasnika; 3. Prikazivanje godišnjeg budžeta za beneficije iz tabele statistike zaposlenih. SELECT * FROM TabelaStatistikeZaposlenih ORDER BY Pozicija, IDZaposlenog; 4. Korišćenjem IN operatora prikazati imena vlasnika papuča. SELECT Sum(Beneficije) FROM TabelaStatistikeZaposlenih; 5. Prikazati imena svih vlasnika obuće koji nemaju postavljenu narudžbinu. SELECT PrezimeVlasnika, ImeVlasnika FROM VlasniciObuce, Prodavnice; WHERE Predmet In (‘Papuce’) AND VlasniciObuce.IDVlasnika=Prodavnice.IDKupca; 6. Prikazati imena onih koji su postavli narudžbinu obuće, bez duplikata. SELECT PrezimeVlasnika, ImeVlasnika FROM VlasniciObuce WHERE IDVlasnika NOT IN (SELECT IDVlasnika FROM Narudzbine); SELECT DISTINCT PrezimeVlasnika, ImeVlasnika FROM Narudzbine, VlasniciObuce WHERE VlasniciObuce.IDVlasnika=Narudzbine.IDVlasnika; ili ako se koristi notacija spoja: SELECT DISTINCT VlasniciObuce.PrezimeVlasnika, VlasniciObuce.ImeVlasnika FROM VlasniciObuce RIGHT JOIN Narudzbine ON VlasniciObuce.IDVlasnika=Narudzbine.IDVlasnika;

26. 7. Kreirati tabelu Zaposleni sa kolonama IDZaposlenog, Ime, Prezime. 8. Prikazati godišnji budžet za plate po svakoj poziciji iz tabele statistike zaposlenih. CREATE TABLE Zaposleni (IDZaposlenog INTEGER NOT NULL, Ime CHAR(40) NOT NULL, Prezime CHAR(40) NOT NULL); SELECT Pozicija, Sum(Plata) FROM TabelaStatistikeZaposlenih GROPU BY Pozicija; 9. Ukoliko u tabeli ne postoji primarni ključ, dodavanje kog tipa kolone se preferira da bi tabela dobila primarni ključ? Dodaje se integer identifikacioni broj. 10. Koja funkcija dozvoljava zamenu neke vrednosti sa NULL vrednošću iz SELECT izjave? 11. Kada se koristi ugrađeni SQL šta je potrebno kreirati da bi se omogućila iteracija kroz rezultate pretrage sa više redova, jedan po jedan red? NVL. Kursor. 12. Koji termin se koristi za opisivanje situacije u kojoj sistem baze podataka automatski ažurira vrednosti stranih ključeva u drugim tabelama kada se ažurira vrednost primarnog ključa? Kaskadno ažuriranje. 13. Koji objekat baze podataka obezbeđuje brz pristup podacima u redovima tabele? ALTER TABLE. 14. Šta znači kada je u izjavi CREATE TABLE kolona označena sa NOT NULL? Potrebna je vrednost u toj koloni za svaki red u tabeli. 15. Ukoliko se želi napisati pretraga koja se više bazira na drugim pretragama, nego na tabelama, kako treba da budu kreirane te druge pretrage ? Kao gledišta (views).

27. HVALA NA PAŽNJI Prof. drMiloradBanjanin