Programiranje, Baze podataka, Visual Basic, Delphi, Karaoke, Dzinglovi

Primer 1.

Data je sledeća šema baze podataka S = (S, I ),    pri čemu je skup šema relacija:

S = { Odeljenje({    SIF_OD, IME_OD, GRAD} , {    SIF_OD} ),

Radnik({ SIF_RAD, IME, PREZIME,    RADMES, SIF_RUK, DATZAP, PLATA, STIMUL, SIF_OD}    , { SIF_RAD, SIF_OD }    )} ,

a skup međurelacionih ograničenja:

I = { Radnik[    SIF_RUK]  Í    Radnik[ SIF_RAD]    ,

Radnik[ SIF_OD]    Í Odeljenje[    SIF_OD] } .

Obeležja šema relacija imaju sledeća    značenja:

SIF_OD – šifra odeljenja,

GRAD – lokacija odeljenja,

SIF_RAD – šifra (matični broj)    radnika,

RADMES – naziv radnog mesta (dom(RADMES)={    analitičar, trg_putnik, službenik, prodavac, direktor}    )

SIF_RUK – šifra (matični broj)    radnika koji je neposredni rukovodilac datom radniku

DATZAP – datum zaposlenja

PLATA – mesečna    primanja radnika

STIMUL – iznos stimulacije u dinarima    za radnike koji su prodavci ili trgovački putnici

SIF_OD – šifra odeljenja u kome radnik radi.

Neka je rbp = { radnik,    odeljenje }  baza podataka nad šemom S.

Putem naredbi SQL-a realizovati    sledeće upite.

(NAPOMENA: Upiti se realizuju bez zalaženja    u sintaksne formalizme konkretnih softvera za rukovanje bazama podataka kao    što su: PROGRESS, ORACLE, FoxPro. Na primer: oznaka kraja SQL naredbe u ORACLE    softveru je simbol tačke-zareza (;), u PROGRESS-u tačka (.), u ACCESS-u simbol    tačke-zareza (;), u Visual FoxPro softveru nema simbola za oznaku kraja    SQL naredbe itd.)

PROSTI UPITI (PRETRAŽIVANJE BAZE PODATAKA)

1.1. Prikazati nazive i šifre odeljenja.

SELECT IME_OD, SIF_OD ODELJENJE

FROM odeljenje

U ovom upitu se u listi atributa iza SELECT naredbe    nalazi ime ODELJENJE. To je naziv atributa u rezultujućoj tabeli, koji će zameniti    ime atributa IME_OD iz bazne relacije.

1.2. Prikazati sve podatke o radnicima koji rade u odeljenju    sa šifrom 20.

SELECT *

FROM radnik

WHERE SIF_OD = 20

1.3. Prikazati imena i matične    brojeve radnika i šifre odeljenja svih službenika.

SELECT IME, SIF_RAD, SIF_OD

FROM radnik

WHERE RADMES = ² službenik²

Primena operatora: =, != (nije jednako ili < > ), < (manje), < = (manje ili jednako), > (veće), > = (veće ili jednako), između dve vrednosti (BETWEEN …AND…), lista vrednosti (IN lista), poređenje znakova LIKE (za tipove podataka string) i nula – vrednost (NULL), operator negacije NOT.

1.4. Prikazati imena odeljenja i njihove šifre i to za ona odeljenja, čije su šifre veće od 10.

SELECT IME_OD, SIF_OD

FROM odeljenje

WHERE SIF_OD > 10

1.5. Prikazati imena, plate i stimulacije za one radnike kod kojih je stimulacija veća od plate.

SELECT IME, PLATA, STIMUL

FROM radnik

WHERE STIMUL > PLATA

1.6. Prikazati imena, plate i šifre odeljenja za trgovačke putnike iz odeljenja 20, čija je plata veća ili jednaka od 400.

SELECT IME, PLATA, SIF_OD

FROM radnik

WHERE RADMES = ² trg_putnik²

AND SIF_OD = 20

AND PLATA > = 400

Prava prenstva operatora

1.7. Prikazati podatke o svim analitičarima, iz svih odeljenja, i o svim službenicima odeljenja sa šifrom 10.

SELECT *

FROM radnik

WHERE RADMES = ² analitičar²

OR (RADMES = ² službenik² AND SIF_OD = 10)

Zagrade u ovom upitu su nepotrebne, jer operator AND ima prvenstvno u odnosu na OR, ali je ovaj način pregledniji.

1.8. Prikazati ime, naziv radnog mesta i šifru odeljenja za upravnike koji ne rade u odeljenju 10.

SELECT IME, RADMES, SIF_OD

FROM radnik

WHERE RADMES = ² upravnik²

AND SIF_OD != 10

1.9. Prikazati sve podatke o radnicima koji nisu upravnici ili službenici a rade u odeljenju 20.

SELECT *

FROM radnik

WHERE NOT (RADMES = ² upravnik² OR RADMES = ² službenik² )

AND SIF_OD = 20

ili, upotrebom operatora IN:

SELECT *

FROM radnik

WHERE RADMES NOT IN (² upravnik² , ² službenik² )

AND SIF_OD = 20

1.10. Prikazati imena radnika koji imaju drugo slovo imena a.

SELECT IME

FROM radnik

WHERE IME LIKE ² _ _a²

1.11. Prikazati imena, šifre odeljenja i plate radnika odeljenja 20 uređene u rastućem redosledu atriubta PLATA.

SELECT IME, SIF_OD, PLATA

FROM radnik

WHERE SIF_OD = 20

ORDER BY PLATA

SPOJ RELACIJA

1.12. Kako se zovu gradovi u kojima rade radnici sa imenom Mirko.

SELECT GRAD

FROM radnik, odeljenje

WHERE IME = ² Mirko²

AND radnik.SIF_OD = odeljenje.SIF_OD

ili:

SELECT GRAD

FROM radnik r, odeljenje o

WHERE IME = ² Mirko²

AND r.SIF_OD = o.SIF_OD

U ovim upitima u klauzuli WHERE u slučaju spoja relacija, koristimo ili puno ime relacije (radnik, odeljenje) ili sinonime (r, o), koji su navedeni u listi iza klauzule FROM.

Inače, ovaj tip spoja se zove i SPOJ NA JEDNAKOST ili EQUI JOIN, zato što je operator poređenja n-torki ovih dvaju relacija operator jednakosti (=).

SELF JOIN (SPAJANJE TABELE SAME SA SOBOM)

1.13. Prikazati imena i plate svih radnika, koji imaju platu veću od plate radnika Markovića.

SELECT r1.IME, r1.PLATA, r2.IME, r2.PLATA

FROM radnik r1, radnik r2

WHERE r1.PLATA > r2.PLATA

AND r2.PREZIME = ² Marković²

U ovom upitu, relacija radnik se spaja sama sa sobom na osnovu operatora > (veće): r1.PLATA > r2.PLATA. Svakoj n-torci relacije r1 je pridružena n-torka sa vrednošću obeležja PREZIME= ² Marković² relacije r2, ako je zadovoljen uslov da je vrednost obeležja PLATA n-torke relacije r1 veća od vrednosti istog obeležja n-torke relacije r2.

Operator spoljnog spoja (+) OUTER JOIN

Da bi se u rezultat upita uključile i one n-torke (oni redovi) relacije koji ne zadovoljavaju uslov spajanja, koristi se operator + .

1.14. Prikazati podatke o odeljenjima, imena radnika koji u njima rade, ali u rezultat upita uključiti i podatke o odeljenjima, koji trenutno, možda, nemaju radnika.

SELECT o.SIF_OD, o.IME_OD, r.IME

FROM odeljenje o, radnik r

WHERE odeljenje.SIF_OD = radnik.SIF_OD (+)

ORDER BY SIF_OD

Simbol (+) u WHERE izrazu ima za posledicu da se relacija radnik posmatra kao da sadrži još jedan red (n-torku) više, koji sadrži nula-vrednosti za sve atribute. SQL spaja te redove tabele relacije radnik sa bilo kojim redom tabele odeljenje, koji se ne može spojiti sa stvarnim redom relacije radnik.

Upotreba sinonima relacija u SQL upitima omogućava spajanje relacija samih sa sobom, na način kao da je reč o dve posebne relacije. To se koristi naročito u slučajevima kada se želi spojiti (udružiti) jedan red tabele sa drugim iz iste tabele, odnosno, kada je u pitanju SELF JOIN.

1.15. Prikazati podatke o radnicima čija je plata veća od plate njihovih rukovodilaca.

SELECT osoba.IME, osoba.PLATA, upravnik.IME, upravnik.PLATA

FROM radnik osoba, radnik upravnik

WHERE osoba.SIF_RUK = upravnik.SIF_RAD

AND osoba.PLATA > upravnik.PLATA

Rad sa numeričkim podacima

1.16. Prikazati ukupna primranja radnika, čije je radno mesto trg_putnik.

SELECT IME, PLATA, STIMUL, PLATA+STIMUL

FROM radnik

WHERE RADMES = ² trg_putnik²

1.17. Prikazati podatke o radnicima čija je stimulacija veća od četvrtine njihovih plata.

SELECT IME, PLATA, STIMUL

FROM radnik

WHERE STIMUL > 0.25 * PLATA

Ovaj upit ilustruje situaciju u kojoj se u WHERE izrazu može koristiti aritmetički izraz ili, u ORDER BY klauzuli, što ilustruje sledeći upit.

1.18. Prikazati podatke o trgovačkim putnicima po opadajućem redosledu odnosa stimulacije i plata.

SELECT IME, STIMUL/PLATA, STIMUL, PLATA

FROM radnik

WHERE RADMES = ² trg_putnik²

ORDER BY STIMUL/PLATA DESC

1.19. Prikazati ukupna godišnja primanja prodavaca.

SELECT IME, PLATA, STIMUL, 12 * (PLATA + STIMUL)

FROM radnik

WHERE RADMES = ² prodavac²

1.20. Prikazati dnevnu platu radnika iz odeljenja 20 (uzeti da mesec ima 22 radna dana) i zaokružiti rezultat na dva decimalna mesta.

SELECT IME, PLATA, ROUND(PLATA/22,2)

FROM radnik

WHERE SIF_OD = 20

Ovaj upit ilustruje upotrebu funkcije ROUND, koja zaokružuje broj na naznačen broj decimalnih mesta.

Grupne funkcije (AVG(), COUNT(), MAX(), MIN(), SUM() )

1.21. Prikazati prosečnu platu službenika.

SELECT AVG(PLATA)

FROM radnik

WHERE RADMES = ² službenik²

1.22. Prikazati prosečna godišnja primanja trgovačkih putnika.

SELECT 12 * AVG( PLATA+STIMUL)

FROM radnik

WHERE RADMES = ² trg_putnik²

Funkcija COUNT() računa broj numeričkih vrednosti ili n-torki selektovanih pretraživanjem, koji imaju vrednost različitu od nule (od nula-vrednosti).

1.23. Koliko radnika prima stimulaciju?

SELECT COUNT(STIMUL)

FROM radnik

Da bi se eliminisale višetruke vrednosti atributa u n-torkama upotrebljava se izraz DISTINCT, odnosno upotrebom ove klauzule funkcija COUNT() će odrediti broj različitih numeričkih vrednosti.

1.24. Odrediti broj različitih radnih mesta u odeljenju 20.

SELECT COUNT(DISTINCT RADMEST)

FROM radnik

WHERE SIF_OD = 20

Oblik funkcije COUNT(*) određuje broj n-torki koji zadovoljavaju logički izraz u WHERE izrazu.

1.25. Koliko radnika radi u odeljenju 10?

SELECT COUNT(*)

FROM radnik

WHERE SIF_OD = 10

NAPOMENA: Ako se koristi grupna funkcija u SELECT naredbi, onda se ne može izdvojiti pojedinačni rezultat. To znači, na primer, da je pogrešna naredba:

SELECT IME, AVG(PLATA)

Atribut IME ima vrednost za svaku n-torku , dok AVG(PLATA) daje kao rezultat jednu vrednost za ceo upit. Kao ilustracija ovog slučaja, poslužiće sledeći upit.

1.26. Koji radnik ima najveću platu?

SELECT IME, PLATA

FROM radnik

WHERE PLATA = (SELECT MAX(PLATA)

FROM radnik)

1.27. Kolika su ukupna primanja trgovačkih putnika?

SELECT IME, PLATA, STIMUL, PLATA+STIMUL

FROM radnik

WHERE RADMES = ² trg_putnik²

GROUP BY klauzula

1.28. Izračunati prosečnu platu za svako odeljenje.

SELECT SIF_OD, AVG(PLATA)

FROM radnik

GROUP BY SIF_OD

1.29. Izračunati prosečnu godišnju platu za svako odeljenje, izuzimajući upravnike i direktora.

SELECT SIF_OD, 12 * AVG(PLATA)

FROM radnik

WHERE RADMES NOT IN (² upravnik² , ² direktor² )

GROUP BY SIF_OD

N-torke tabele moguće je grupisati i po više atributa. To ilustruje sledeći primer.

1.30. Izračunati prosečnu platu za svako radno mesto grupisano po odeljenju.

SELECT SIF_OD, RADMES, COUNT(*), 12 * AVG(PLATA)

FROM radnik

GROUP BY SIF_OD, RADMES

HAVING klauzula

Putem ove klauzule moguće je selektovati grupe n-torki, pošto su te grupe već formirane sa GROUP BY klauzulom.

1.31. Prikazati prosečnu platu za sve grupe radnih mesta, na kojima radi više od 2 radnika.

SELECT RADMES, COUNT(*), AVG(PLATA)

FROM radnik

GROUP BY RADMES

HAVING COUNT(*) > 2

1.32. Prikazati nazive radnih mesta, za koje je prosečna plata veća od upravnikove plate.

SELECT RADMES, AVG(PLATA)

FROM radnik

GROUP BY RADMES

HAVING AVG(PLATA) >

(SELECT AVG(PLATA)

FROM radnik

WHERE RADMES = ² upravnik² )

Rad sa nula-vrednostima (funkcija NVL() )

Numerička vrednost atributa nula u relaciji je semantički drugačija od one vrednosti koja sadrži broj nula (0). Vrednost nula ili null-vrednost se pikazuje kao prazan prostor, a broj nula kao broj (0). Na primer, samo radnici koji su raspoređeni na radno mesto trgovačkih putnika ili prodavaca imaju stimulaciju, pa je za te n-torke vrednost atributa STIMUL relacije radnik definisana (eventualno za one trgovačke putnike ili prodavce, koji nisu dobili stimulaciju, jer nisu ispunili neke od uslova za to). Za sve ostale n-torke relacije radnik vrednost atributa STIMUL je neodređena (odn. neprimenjivo svojstvo), odnosno, kažemo da je to nula-vrednost (NULL).

1.33. Prikazati podatke o radnicima koji nemaju stimulaciju.

SELECT IME, PLATA, STIMUL, RADMES

FROM radnik

WHERE STIMUL IS NULL

1.34. Koliko ima radnika u odeljenju 10 koji primaju platu i stimulaciju.

SELECT COUNT(PLATA), COUNT(STIMUL)

FROM radnik

WHERE SIF_OD = 10

U slučaju da je potrebno sabrati vrednosti PLATA + STIMUL i to za sve n-torke, potrebno je nula-vrednost zameniti nekom određenom numeričkom vrednošću. Za to se koristi funkcija NVL().

1.35. Prikazati ukupna primanja za svakog radnika posebno.

SELECT IME, PLATA, STIMUL, PLATA + NVL(STIMUL,0)

FROM radnik

U ovom upitu, sve nula-vrednosti atributa STIMUL u relaciji radnik biće zamenjene numeričkom vrednošću 0.

1.36. Izračunati prosečnu stimulaciju, prosečna primanja, kao i ukupna primanja za celo preduzeće.

SELECT AVG(STIMUL), AVG(PLATA + NVL(STIMUL,0)),

SUM(PLATA + NVL(STIMUL,0))

FROM radnik

PODUPITI

1.37. Prikazati imena i nazive radnih mesta radnika koji imaju isto radno mesto kao i radnik Marković.

SELECT IME, RADMES

FROM radnik

WHERE RADMES =

(SELECT RADMES

FROM radnik

WHERE PREZIME = ² Marković² )

Naredbe koje podržavaju WHERE izraz su: SELECT, UPDATE, DELETE, INSERT i CREATE.

1.38. Koji radnici zarađuju više od bilo kog radnika odeljenja 10.

SELECT IME, PLATA, SIF_OD

FROM radnik

WHERE PLATA > ANY

(SELECT PLATA

FROM radnik

WHERE SIF_OD = 10)

Ako u prethodnom upitu umesto operatora ANY upotrebimo operator ALL, pretraživanje će selektovati n-torke čija vrednost atributa PLATA je veća od svih vrednosti dobijenih podupitom.

1.39. Prikazati podatke o radnicima iz odeljenja 10 sa istim radnim mestom kao bilo ko iz odeljenja 20.

SELECT IME. RADMES

FROM radnik

WHERE SIF_OD = 10

AND RADMES IN

(SELECT RADMES

FROM RADNIK

WHERE SIF_OD = 20)

U slučaju ORACLE softvera moguće je upoređivati više atributa u spoljašnjem i unutrašnjem upitu. Na primer, kao u sledećem upitu.

1.40. Prikazati podatke o radnicima koji imaju isto radno mesto i platu kao analitičar Jović.

SELECT IME, RADMES, PLATA

FROM radnik

WHERE (RADMES, PLATA) =

(SELECT RADMES, PLATA

FROM radnik

WHERE PREZIME = ² Jović² )

Mogući su i takvi oblici upita koji sadrže više uslova sa podupitima, koji su spojeni operatorima AND i OR.

1.41. Prikazati podatke o radnicima koji imaju isto radno mesto kao i Marković, ili istu ili veću platu kao Jović, i to uređene prema nazivima radnih mesta, a zatim prema rastućem redosledu plata.

SELECT IME, RADMES, SIF_OD, PLATA

FROM radnik

WHERE RADMES =

(SELECT RADMES

FROM radnik

WHERE PREZIME = ² Marković² )

OR PLATA > =

(SELECT PLATA

FROM radnik

WHERE PREZIME = ² Jović² )

ORDER BY RADMES, PLATA

Takođe, podupit može sadržati podupite (odn. ugnježdene podupite).

1.42. Prikazati podatke o radnicima odeljenja 20 sa istim radnim mestom kao bilo ko iz odeljenja ANALIZA.

SELECT IME, RADMES

FROM radnik

WHERE SIF_OD = 20

AND RADMES IN

(SELECT RADMES

FROM radnik

WHERE SIF_OD =

(SELECT SIF_OD

FROM odeljenje

WHERE IME_OD = ² ANALIZA² ))

Primena operatora unije (UNION), preseka (INTERSECT) i razlike (MINUS)

Upit (spoljašnji ili unutrašnji) može biti sastavljen od dva ili više SELECT blokova, koji su povezani operatorima (UNION, INTERSECT, MINUS). To ilustruje sledeća slika (Marjanović Zoran, ORACLE relacioni SUBP, Beograd, 1990, str. 31).

.

1.43. Prikazati podatke o radnicima čija je plata jednaka plati koju ima radnik Marković ili Jović.

SELECT IME, PLATA

FROM radnik

WHERE PLATA IN

(SELECT PLATA

FROM radnik

WHERE PREZIME = ² Marković²

UNION

SELECT PLATA

FROM radnik

WHERE PREZIME = ² Vasić² )

Podupit može davati za rezultat informacije iz više relacija. To ilustruje sledeći primer.

1.44. Koji radnici obavljaju isti posao kao radnici koji rade u odeljenjima čija je lokacija Novi Sad.

SELECT IME, PREZIME, RADMES

FROM radnik

WHERE RADMES IN

(SELECT RADMES

FROM radnik, odeljenje

WHERE odeljenje.GRAD = ² Novi Sad²

AND radnik.SIF_OD = odeljenje.SIF_OD)

S obzirom da se informacije o lokaciji odeljenja u kojima su zaposleni radnici nalaze u relaciji odeljenje, u podupitu je potrebno spojiti relacije radnik i odeljenje na osnovu istih vrednosti zajedničkih obeležja ovih relacija a to je obeležje SIF_OD.

Do sada su bili navedeni primeri upita, kod kojih se podupit izvršavao samo jednom, a rezultat podupita se koristi u WHERE izrazu glavnog upita. Međutim, postoje i takvi upiti, kod kojih se podupiti izvršavaju više puta, jednom za svaku n-torku koja se koristi u glavnom upitu. To su korelisani upiti ili zavisni ugdnježdeni upiti (Mogin P, Luković I, Principi baza podataka, Novi Sad, 1996, str. 209)

1.45. Koji radnici zarađuju više od prosečne plate za odeljenje u kome rade?

U ovom upitu glavni upiti će izgledati:

SELECT SIF_OD, IME, PLATA

FROM radnik

WHERE PLATA > prosečne plate za odeljenje u kome radnik radi

Podupit će izgledati:

(SELECT AVG(PLATA)

FROM radnik

WHERE SIF_OD = šifra odeljenja za datog radnika)

Sada je jasno zašto se ovakvi upiti zovu zavisni ugnježdeni upitii. Izvršavanje podupita zavisi od rezultata spoljašnjeg (glavnog) upita. U ovom slučaju, šifra odeljenja u podupitu zavisi od šifre odeljenja, koja je selektovana u spoljašnjem upitu.

Upit ima konačni izgled:

SELECT SIF_OD, IME, PLATA

FROM radnik rX

WHERE PLATA >

(SELECT AVG(PLATA)

FROM radnik rY

WHERE rX.SIF_OD = rY.SIF_OD)

Upotreba operatora EXISTS

U slučaju upotrebe operatora EXISTS glavni upit će se izvršiti ako unutrašnji upit kao rezultat pretraživanja ima bar jednu n-torku. (Opširnije o ovom operatoru u primeru br. 3).

1.46. Prikazati podatke o radnicima koji imaju najmanje jednog radnika koji im je podređen.

SELECT IME, PREZIME, RADMES, SIF_OD

FROM radnik rX

WHERE EXISTS

(SELECT *

FROM radnik rY

WHERE rX.SIF_RAD = rY.SIF_RUK)

Ažuriranje baze podataka (naredbe INSERT, UPDATE i DELETE)

1.47. Uneti podatke o novom radniku.

INSERT INTO radnik

VALUES(7978, ² Zdravko² , ² Čolić² , ² analitičar² , 7790, ² 01.05.99² , 1000,NULL,20)

S obzirom da se radi o radniku koji obavlja posao analitičara, pa nema pravo na stimulaciju, vrednost atributa STIMUL je nula-vrednost, odnosno, NULL (po sintaksi ORACLE softvera).

U naredbi INSERT može se koristiti i podupit, pa se na taj način mogu n-torke iz jedne relacije uneti u drugu. Podupit zamenjuje izraz VALUES.

1.48. Svim analitičarima i upravnicima dati premiju u iznosu od 10% njihovih plata. Te informacije uneti u relaciju premija zajedno sa datumom zaposlenja.

INSERT INTO PREMIJA(SIF_RAD,PREMIJA, RADMES, DATZAP)

SELECT SIF_RAD, 0.10 * PLATA, RADMES, DATZAP

FROM radnik

WHERE RADMES IN (² analitičar² , ² upravnik² )

1.49. Svim trgovačkim putnicima i savetnicima iz odeljenja 10 povećati platu za 8%.

UPDATE radnik

SET PLATA = PLATA * 1.08

WHERE (RADMES = ² trg_putnik² OR RADMES = ² savetnik² )

AND SIF_OD = 10

Naredba UPDATE može da koriti i podupite u WHERE klauzuli.

1.50. Sve radnike koji su zaposleni u Novom Sadu rasporediti na radno mesto službenika.

UPDATE radnik

SET RADMES = ² službenik²

WHERE SIF_OD IN

(SELECT SIF_OD

FROM odeljenje

WHERE GRAD = ² Novi Sad² )

1.51. Izbrisati podatke o svim radnicima koji rade u odeljenju PRIPREMA.

DELETE radnik

WHERE SIF_OD IN (SELECT SIF_OD

FROM odeljenje

WHERE IME_OD = ² PRIPREMA² )