Vježba 5 PDF

Select naredba na samo jednoj tablici nam daje mogućnost filtriranja i dohvaćanja podataka samo iz te jedne tablice. Vrlo rijetko će se u praksi pokazati potreba da dohvaćate podatke iz samo jedne tablice. Zašto? Zato što su tablice mođusobno povezane vanjskim ključevima i podatkovno su ovisne jedne...

Select naredba na samo jednoj tablici nam daje mogućnost filtriranja i dohvaćanja podataka samo iz te jedne tablice. Vrlo rijetko će se u praksi pokazati potreba da dohvaćate podatke iz samo jedne tablice. Zašto? Zato što su tablice mođusobno povezane vanjskim ključevima i podatkovno su ovisne jedne od drugima. Najčešći slučaj dohvaćanja podataka je primjer uvjeta u jednoj tablici a ispisa rezultata iz druge tablice. U našem katalogu primjer bi bio da želim znati koje je sve knjige napisao August Šenoa? Idemo prvo vidjeti naš „stari način“ gdje radimo select na jednoj tablici. Prvo moramo izvesti naredbu: select sifra from autor where ime='August' and prezime ='Šenoa'; i dobijemo jedinstveni rezultat broj 2. Što sada? Sada idemo u tablicu katalog i izvodimo sljedeći select: select naslov from katalog where autor=2; jeeeee, dobijemo dva rezultata, Čuvaj se senjske ruke i Zlatarevo zlato. To je točno. Mi u bazi imamo samo dvije knjige od Augusta. Ovo nije način dohvaćanja podataka kroz više tablica! Ako imamo potrebu dohvatiti podatke kroz više tablica pratimo sljedeću kuharicu. 1. Ispred sebe imati ERA dijagram 2. Napisati select i select listu ostaviti praznu 3. Napisati from dio i pospajati tablice prema uvjetima vanjskih ključeva 4. Napisati uvjete za filtriranje (where dio) 5. Napisati koje kolone želimo pokazati u select dio Ključni dio kod spajanja tablica je pojam lanac vanjskih ključeva! Ako pogledate dijagram ispod vidjet ćete lanac. Primjer (slika plava boja*). Tablice autor, katalog i mjesto tvore lanac od tri tablice zato što su međusobno povezane vanjskim ključevima. 1. Ispred sebe imati ERA dijagram Prije svega moramo identificirati koje sve tablice sudjeluju u našem selectu. Za primjer svih naslova Augusta Šenoe u selectu sudjeluju tablice autor i katalog, odnosno veza između njih (crvena boja*). U tablici autor se nalazi uvjet za filtriranje a u tablici katalog se nalazi podataka koji želimo dobiti. * linije su neravne jer Hrvatske željeznice nemaju dobar sustav amortiziranja vagona J 2. Napisati select i select listu ostaviti praznu select 3. Napisati from dio i pospajati tablice prema uvjetima vanjskih ključeva select from autor a inner join katalog b on a.sifra = b.autor 4. Napisati uvjete za filtriranje (where dio) select from autor a inner join katalog b on a.sifra = b.autor where a.ime='August' and a.prezime='Šenoa'; 5. Napisati koje kolone želimo pokazati u select dio select b.naslov from autor a inner join katalog b on a.sifra = b.autor where a.ime='August' and a.prezime='Šenoa'; Zašto preskačemo dijelove SQL naredbe? Zato što u trenutku pisanja select dijela naredbe ne znamo iz koje tablice ćemo htjeti podatke. Tablice u from dijelu dobivaju zamjenska imena (a,b,c,d,e,...). Ta imena mogu biti biti bilo koji niz znakova. Zamjenska imena dajemo zato da si olakšamo posao pisanja sql naredbe gdje umjesto da svaki put napišemo npr. katalog.sifra mi napišemo a.sifra jer smo tablici katalog dali zamjensko ime a. Drugi razlog davanja zamjenskih imena je potreba jedinstvenog identificiranja kolone kada su u igri više tablica. U tablici katalog i u tablici autor imamo kolonu sifra. Ako bi napisali u select dio samo sifra (bez zamjenskog imena ili stvarnog imena tablice) dobili bi grešku. Primjer select naredbe kroz sve 4 tablice koje se nalaze u bazi. Izlistajte mi sve naslove knjiga i nazive mjesta u kojima je izdana knjiga čiji autor je rođen 1976 i izdavač je još uvijek aktivan. 1. Staviti dijagram ispred sebe (preporučam tiskani oblik) 2. Napisati select i select listu ostaviti praznu select 3. Napisati from dio i pospajati tablice prema uvjetima vanjskih ključeva select from autor a inner join katalog b on a.sifra = b.autor inner join izdavac c on b.izdavac = c.sifra inner join mjesto d on b.mjesto = d.sifra 4. Napisati uvjete za filtriranje (where dio) select from autor a inner join katalog b on a.sifra = b.autor inner join izdavac c on b.izdavac = c.sifra inner join mjesto d on b.mjesto = d.sifra where a.datumrodenja between '1976-01-01' and '1976-12-31' and c.aktivan=1; 5. Napisati koje kolone želimo pokazati u select dio select b.naslov, d.naziv from autor a inner join katalog b on a.sifra = b.autor inner join izdavac c on b.izdavac = c.sifra inner join mjesto d on b.mjesto = d.sifra where a.datumrodenja between '1973-01-01' and '1974-01-01' and c.aktivan=1; Do sada nam je ostala nepoznanica INNER JOIN. To je način spajanja tablica. Konkretno INNER JOIN definira spajanje samo onih podataka na kojima postoji podudaranje vanjskih ključeva. Nešto kasnije ćemo definirati i ostale načine spajanja. Prebacimo se sada na jednu malo veću bazicu, reda veličine pola milijuna slogova u tablici (ne svim tablicama). Dijagram pokazuje izgled baze. Vidimo kako postoji „puno“ tablica i kako su sve međusobno povezane. Inače, baze većih sustava mogu imati i po nekoliko stotina tablica ako ne i preko 1000. Baza je velika 300 MB i nećemo je unijeti preko SQL skripte kao što smo kreirali katalog, već ćemo je „priljepiti“ (attach) na server. Datoteke svastara.mdf i svastara_log.ldf kopirajte u direktorij C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data U SQL manageru desni klik na Databases i odaberite Attach… Otvori se prozor a priljepljivanje baze. Kliknemo na Add… Odaberite bazu svastara.mdf Promjeni (1) svojstvo Attach as u svastara i (kliknite OK) Vježba za zagrijavanje: 1. izlistajte sve županije 2. izlistajte sve općine 3. izlistajte sve kupce kojima adresa sadrži broj 86 i nepoznato im je mjesto stanovanja i ime im počinje s slovom s 4. izlistajte nazive i cijene artikla čija je cijena između 10 i 11 kuna i jedinica mjere je kg Idemo sada izvući smislene podatke 1. Izlistajte nazive općina koje se nalaze u osječko-baranjskoj županiji 2. Postoji li u zadarskoj županiji mjesta koja počinje s slovom a? 3. Koji su sve kupci iz Osijeka? Kao što smo već spomenuli, svi dosadašnji načini spajanja su išli s INNER JOIN načinom. Postoje i ostali načini spajanja i sljedeće slike zorno prikazuju njihove funkcionalnosti. Uvijek se gledaju dvije tablice kao dva skupa podataka (lijeva i desna) INNER JOIN LEFT JOIN RIGHT JOIN  FULL JOIN CROSS JOIN Zadaci za vježbu: 1. izlistajte sva mjesta u općini Popovac u Osječko baranjskoj županiji 2. izlistajte sve različite nazive artikala koji se nalaze na primkama koje su napravljene prošli mjesec 3. izlistajte sve kupce koji u jmbg nemaju broj 7 i žive u zadarskoj županiji 4. izlistajte sve nazive artikala koji su preko dokumenta primka dobavljeni od dobavljača koji se nalazi u Osječko baranjskoj županiji Još jedan grafički primjer spajanja tablica Dopuna SQL select naredbe Promotrimo sljedeću SQL naredbu select c.rednibroj, a.duginaziv, b.cijena from artikli a inner join artiklinaprimci b on a.sifra=b.artikl inner join primke c on b.primka=c.sifra where c.rednibroj='1769/2009' and b.cijena>1000; Ova naredba nam daje redni broj primke, nazive artikala i njihovu cijenu tamo gdje je redni broj primke jednak 1769/2009 i cijena primke je veća od 1000 kn. Rezultati iz naše svaštare su sljedeći. Ono što mi sada želimo je saznati koliki je ukupni iznos na toj primci (1796/2009) Jedan način je uzeti kalkulator u ruku i pozbrajati ove iznose u koloni cijena a drugi (lakši) je napisati SQL naredbu. Kada želimo nešto agregirati (da bi sumirali, izvukli prosjek, pronašli najveći broj, najmanji) tada prvo skup rezultat grupiramo. U našem slučaju grupirati ćemo po rednom broju primke a zbrajati ćemo cijene. Primjetite kako u prethodnoj rečenici ne spominjem naziv artikla. Razlog tome je što kog grupiranja na nivo primke pojedini artikl (njegov naziv) postaje nebitan. Nadograđena SQL naredba koja će nam dati tražene rezultate izgleda ovako select c.rednibroj, sum(b.cijena) from artikli a inner join artiklinaprimci b on a.sifra=b.artikl inner join primke c on b.primka=c.sifra where c.rednibroj='1769/2009' and b.cijena>1000 group by c.rednibroj; Rezultat izvođenja je iznos 10051,9067 što je i zbroj svih redova u koloni cijena iz prethodne SQL naredbe. Sljedeća naredba sumu cijena svih primki iz 2009 godine select c.rednibroj, sum(b.cijena) from artikli a inner join artiklinaprimci b on a.sifra=b.artikl inner join primke c on b.primka=c.sifra and c.rednibroj like '%2009%' group by c.rednibroj; Sada dobijemo 684 rezultata. PREVIŠE za neku kvalitetnu analizu. Ako želimo suziti izbor na sve primke čija suma cijena prelazi neki iznos (recimo 100 000 kuna) tada uvodimo HAVING klauzulu u select naredbu. select c.rednibroj, sum(b.cijena) from artikli a inner join artiklinaprimci b on a.sifra=b.artikl inner join primke c on b.primka=c.sifra and c.rednibroj like '%2009%' group by c.rednibroj having sum(b.cijena)>100000; Ostaje nam još jedan dio select naredbe. To je ORDER BY klauzula. Ona kaže na koji način prikazujemo rezultate pretraživanja (po abecedi uzlazno/silazno, datumu od najranijeg prema novijima i obrnuto i od najmanjeg prema najvećem i obrnuto. select c.rednibroj, sum(b.cijena) from artikli a inner join artiklinaprimci b on a.sifra=b.artikl inner join primke c on b.primka=c.sifra and c.rednibroj like '%2009%' group by c.rednibroj having sum(b.cijena)>100000 order by sum(b.cijena) desc; Podupiti Realna situacija od nas traži različite informacije koje moramo izvuči na osnovu podataka koje imamo. Ponekad traženu informaciju dobijemo vrlo jednostavnom select naredbom a ponekad moramo raditi razne gimnastike nebi li došli do tražene informacije. Jedan dobar primjer je Izlistajte mi sve artikle koji se ne nalazi na nijednoj primci. Da malo razjasnim. Postoji tablica artikli gdje se nalazi 52601 artikl. Velika većina tih artikala se nalazi na jednoj ili više primki. Međutim, postoje artikli koji se ne nalaze na bilo kojoj primci. Takvi artikli su mi nepotrebni i mogu razmisliti da ih obrišem. No prije brisanja moram saznati koji su to artikli. Kako krenuti. S jedne strane vrlo lako mogu saznati šifre svih artikala. select sifra from artikli; S druge strane mogu saznati koji se sve artikli nalaze na nekoj od primki select distinct artikl from artiklinaprimci; Ali niti to mi ne treba samo po sebi J Treba mi kombinacija. Treba mi naziv artikala gdje se šifra ne nalazi u drugom selectu (da vizualno si predočimo problem) select duginaziv from artikli where sifra not in (select distinct artikl from artiklinaprimci); Kao rezultat izvođenja ove naredbe dobijemo dva artikla (od 52601). Funkcije http://msdn.microsoft.com/en-us/library/ms174318(SQL.90).aspx Funkcije nam služe kao dodatna mogućnost upravljanja podacima. Funkcija ima jako puno. Ovdje ću prikazati samo neke najosnovnije. Agregirane funkcije* Funkcija Koristi se za AVG(kolona) dohvaćanje prosječne vrijednosti svih vrijednost u koloni COUNT(kolona) dohvaćanje broja redova u koloni COUNT(*) dohvaćanje broja redova u tablici MIN(kolona) dohvaćanje minimalne vrijednosti svih vrijednost u koloni MAX(kolona) dohvaćanje maksimalne vrijednosti svih vrijednost u koloni SUM(kolona) dohvaćanje sume svih vrijednost u koloni *koriste se u kombinaciji s group by klauzulom Datumske funkcije Funkcija Koristi se za DATEADD Na zadani datum dodaje dio vremenskih jedinica* dateadd(dioVremenskihJedinica,broj,datum) DATEDIFF Za dva zadana datuma vraća broj zadanih vremenskih jedinica* datediff(dioVremenskihJedinica,datum1,datum2) DATEPART Za zadani datum vraća vrijednost zadane vremenske jedinice datepart(dioVremenskihJedinica,datum) GETDATE vraća trenutni datum i vrijeme getdate() Znakovne funkcije Funkcija Koristi se za LOWER(kolona) ispisivanje malim slovima UPPER(kolona) ISPISIVANJE VELIKIM SLOVIMA SUBSTRING ispisivanje određenog dijela znakovnog niza substring(kolona,početak,duljina) RTRIM, LTRIM čišćenje znakovnog niza od nepotrebnih praznih mjesta prije početka i nakon kraja niza Matematičke funkcije Funkcija Koristi se za ABS(kolona) ispisivanje absolutne vrijednosti POWER potenciranje vrijednosti power(kolona,potencija) SQRT(kolona) ispivanje drugog korijena *vremenske jedinice Vremenska jedinica Kraći način pisanja year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww Hour hh minute mi, n second ss, s millisecond ms Okidači https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql- server-ver16 Okidač je objekt u bazi podataka koji omogućuje izvođenje SQL naredbi nakon određene akcije. Primjer bi bio: Prilikom svakog unosa novog mjesta u bazu povećaj sadržaj neke kolone u drugoj tablici za 1. Pohranjene procedure https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored- procedures-database-engine?view=sql-server-ver16 Pohranjena procedura je grupa SQL naredbi objedinjena u sklopu jedne logičke cjeline. Primjer bi bio: Želim iz jedne tablice izvući traženi podatak i onda s tim podatkom radim update u drugoj tablici te na kraju radim select iz te druge tablice.