Osnove SQL

1. Osnove SQL Prof. dr MILORAD K. BANJANIN Univerzitet u Istočnom Sarajevu Tehnološki fakultet Zvornik

2. IME ENTITETA(PREZIME, PLATA VREDNOST ENTITETA(BANJANIN, 1000) ATRIBUT IDENTIČAN SA SVOJIM DOMENOM SKUP SVIH MOGUĆIH VREDNOSTI (INSTANCI) ODREĐENOG ATRIBUTA VREDNOST U DOMENU SE JEDNOM JAVLJA SVE VREDNOSTI ODREĐENOG SVOJSTVA(npr., MESTO) ČINE GRUPU POD JEDNIM IMENOM(npr., PANCEVO, BEOGRAD) PREDSTAVLJANJE REALNOG SVETA DISKRETNIM VREDNOSTIMA OPISIVANJE REALNOG SVETA ENTITET SVOJSTVA ENTITETA OBJEKAT POSMATRANJA KLJUČ ENTITETA ATRIBUTI DOMEN PODACI PREDSTAVLJAJU VREDNOSTI POJEDINIH ATRIBUTA KOJI SE UNOSE U MEMORIJU

3. BAZA PODATAKA zbirkauzajamnopovezanihpodataka, kojisumemorisanisakontrolisanomredundansom, da bi optimalnoslužilirazličitimaplikacijama podacisumemorisaninezavisnoodprogramakojiihkoriste za dodavanjenovihpodatakaimodifikovanjeilipretraživanjepostojećihpodatakakoriste se zajednickiikontrolisanipristupi Data Definition Language SISTEM ZA UPRAVLJANJE BAZAMA PODATAKA(SUBP) Data Manipulation Language DML DDL manipulisanjepodacima memorisanjeiodrzavanjepodataka STRUKTURA Logička (logičkipogledna bazu podataka - model podataka i model procesa) Fizička (internipoglednapodatke)

4. Svakipodatak u relacionojbazipodatakadostupan je prekokombinacijeimenatabele(relacije), vrednostiprimarnogključa iimenakolne(atributa) Strukturabazepodataka se nalogičkomnivoupredstavljasamotabelama 1 2 RELACIONA BAZA PODATAKA odnosiizmeđu podatakasu u oblikudvodimenzionihtabela K O L O N Esadrže samovrednostijednogdomena R E D O V I se razlikujuizmeđu sebe K LJ U Č RELACIJE predstavljaatributkojijednoznačno identifikuje red KRITERIJUMI ZA OCENU RELACIONIH SUBP-a

5. Specifičan indikator (razlicitod "praznog" nizakaraktera, nuleilibilokogbroja) koristi se zapredstavljanjenulavrednosti, bezobzirana tip podatka Sistemposedujeefikasanalgoritampomoću koga može daodredi, zasvakidefinisanipogled, u trenutkunjegovogdefinisanja, dali se ikojeoperacijeodržavanjamoguprimenitinatajpogled –čiji rezultat se smešta u katalogbazepodataka Bezobzirakolikojezikainačinakorišćenjasistempodržava, moraposedovatibaremjedanjezikčije se naredbemoguizrazitikaonizkarakterasadobrodefinisanomsintaksomikojipodržava: Sistemposedujekatalog (rečnik) podatakakoji se logičkipredstavljanaistinačin kaoisamabazapodataka -korisnik može dakoristiistijezikda bi pristupiotim meta podacima 3 4 5 6 (1) definicijupodataka (2) definicijupogleda (3) manipulacijupodatka, interaktivnoikrozprograme (4) definicijupravilaintegriteta (5) autorizaciju (sigurnost) (6) granicetransakcija (BEGIN, COMMIT, ROLLBACK)

6. Akorelacionisistemposedujeili može daradisanekimjezikomtreće generacije u kome se obrađujejedna n-torka u jednomtrenutkuvremena, kroztajjezik se ne moguzaobićipravilaintegritetazadana prekosamogrelacionogjezika Nad baznimrelacijamainadpogledimamogu se izvršavatine samooperacijepretraživanja, većioperacijeodržavanjabaze podataka Aplikacioni program iinteraktivnakomunikacijaostajuneizmenjenikada se promenifizička organizacijabazeilifizičkimetodpristupa Pravilaintegriteta se definišu uokvirudefinicijebazepodatakaičuvajuse u katalogu - ne implementiraju se krozaplikacioneprograme Aplikacioni program iinteraktivnakomunikacijaostajunepromenjenekada se bilokojepromene, koje ne menjajuodgovarajućisadržajtabele, unesu u baznutabelu Svenavedenekarakteristikesunezavisneoddistribucijebazepodataka 7 8 10 11 12 9

7. SQL Structured Query Language - standardnirelacioniupitnijezik jezik za direktan intereaktivni pristup bazi podataka Sastoji se iz: DDL (Data Definition Language) iDML (Data Manipulation Language) naredbi DDL naredbe Mogu se kreirati/promeniti/obrisati CREATE tabele ALTER indeksi DROP pogledi Postoje i drugi objekti, ali su specifični za svakog proizvođača (sekvence, tablespace-ovi, itd.)

8. TIPOVI PODATAKA INTEGER, NUMBER(dužina,dec), NUMERIC, DECIMAL brojevi (celobrojni, razlomci) nisu floating point! FLOAT, DOUBLE floating point brojevi CHAR(dužina), VARCHAR(dužina) stringovi fiksne i varijabilne dužine dužina nije impozantna! DATE, TIME, TIMESTAMP datum, vreme i datum sa vremenom BOOLEAN, BIT boolean tip podatka BLOB, CLOB, TEXT Binary Large OBject, za smeštanje binarnih objekata (slika, filmova, itd.) TEXT se koristi za smeštanje velike količine teksta

9. KREIRANJE/IZMENA/BRISANJE TABELA Kreiranje tabele - Opšta sintaksa • CREATE TABLE ime_tabele • (obl1 tip_podatka [NULL | NOT NULL] • [, obl2 tip_podatka [NULL | NOT NULL]...]) PRIMER CREATE TABLE PROFESORI (PROFESOR_ID INTEGER not null, IME VARCHAR(25) not null, PREZIME VARCHAR(35) not null, ZVANJE VARCHAR(15) not null) Izmena tabele (definicije) - Opšta sintaksa ALTER TABLE ime_tabele ADD|MODIFY|DROP parametri

10. Dodavanje novih kolona (ADD) - Opšta sintaksa ALTER TABLE ime tabele ADD (obl1 tip_podatka [NULL | NOT NULL] [, obl2 tip_podatka [NULL | NOT NULL)]...]) PRIMER ALTER TABLE PROFESORI ADD (Plata NUMBER(5,2)) Menja tip, dužinu, NULL/NOT NULL Izmena tabele (MODIFY) - Opšta sintaksa Potencijalno ogromni problemi zbog konverzije tipova - nekad i nemoguće (zbog primarnih ključeva, spoljnih ključeva, itd.) ALTER TABLE ime_tablele MODIFY (obl1 tip_podatka [NULL | NOT NULL] [, obl2 tip_podatka [NULL | NOT NULL)]...]) PRIMER ALTER TABLE PROFESORI MODIFY (Plata NUMBER(7,2))

11. Izbacuje kolonu - Ponekad nemoguće (zbog primarnih ključeva, stranih ključeva, itd.) Izmena tabele (DROP) - Opšta sintaksa ALTER TABLE ime_tabele DROP [COLUMN] ime_kolone PRIMER ALTER TABLE PROFESORI DROP Plata Uklanjanje tabela - Opšta sintaksa DROP TABLE ime_tabele PRIMER DROP TABLE PROFESORI

12. KREIRANJE/PROMENA/BRISANJE INDEKSA Kreira indeks nad kolonom/kolonama Kreiranje indeksa - Opšta sintaksa CREATE [UNIQUE] INDEX ime_indeksa ON ime_tabele (obl1 [ASC|DESC] [, obl2 [ASC|DESC]...]) PRIMER CREATE UNIQUE INDEX PROFESORI_KEY ON PROFESORI (PROFESOR_ID) Uklanjanje indeksa - Opšta sintaksa DROP INDEX ime_indeksa PRIMER DROP INDEX PROFESORI_KEY

13. DML naredbe INSERT SELECT UPDATE DELETE Ubacuju se ili kompletne kolone ili samo one koje moraju da budu unete (NOT NULL) Ubacivanje novih podataka u tabelu (INSERT) Opšta sintaksa 1 INSERT INTO ime_tabele (obl1 [, obl2, ...]) VALUES (vred1 [, vred2 ...]) Opšta sintaksa 2 INSERT INTO ime_tabele VALUES (vred1 [, vred2 ...]) PRIMER INSERT INTO PROFESORI VALUES (1, ‘Petar’, ‘Petrović’, ‘Docent’)

14. Pretraživanje baze podataka (SELECT) Opšti oblik (obaveznielementi) Ako se ne navede, podrazumeva se ALL, tj. svi redovi, bez obzira na duplikate SELECT {* | obl [, obl2 ...]} FROM ime_tabele Opšti (prošireni) oblik SELECT [ALL|DISTINCT] {* | obl [alias] [, obl2 [alias2]...]} FROM tabela [alias] [, tabela2 [alias2]...] [WHERE uslov ] [ORDER BY obl [ASC|DESC] [, obl2 [ASC|DESC]...]] [GROUP BY obl [, obl2...] [HAVING uslov]] PRIMER Prikazati sva zvanja u tabeli PROFESORI (bezduplikata) SELECT DISTINCT ZVANJE FROM PROFESORI

15. Selekcija željenih slogova – WHERE klauzula Zadaje se uslov iza WHERE klauzule Unutar uslova se mogu koristiti sledeći operatori = jednako != nije jednako > >= <= veće, veče ili jednako,... NOT IN nije unutar liste IN unutar liste [NOT] BETWEEN x AND y [nije] između x i y EXISTS ako postoji barem jedan slog [NOT] LIKE [nije] poput zadatog stringa IS [NOT] NULL [nije] NULL NOT negacija AND logičko I OR logičko ILI PRIMER Svi profesori čija plata je između 2000 i 3000 KM SELECT * FROM PROFESORI WHERE PLATA BETWEEN 2000 AND 3000

16. Funkcije Postoji veliki broj funkcija koje operišu nad podacima unutar izraza AVG(obl) srednja vrednost SUM(obl) suma svih obeležja MIN(obl) minimalna vrednost MAX(obl) maksimalna vrednost COUNT({*|[DISTINCT]obl}) broj pojavljivanja ABS(broj) apsolutna vrednost MOD(br1, br2) br1 po modulu br2 POWER(br, e) br na e-ti stepen LENGTH(str) dužina stringa UPPER(str) u velika slova SUBSTRing(str, pos [, d]) podstring od str, sa poz. pos CONCAT(str1, str2, str3, ...) konkatenacija stringova YEAR(obl) vraća godinu iz datuma PRIMER Naći sumu svih plata za docente SELECT SUM(PLATA) FROM PROFESORI WHERE ZVANJE=‘Docent’

17. Izmenapodataka (UPDATE) Omogućuje izmenu podataka u tabeli Opšta sintaksa UPDATE ime_tabele SET obl1=vred1 [,obl2=vred2...] [WHERE uslov] Bez WHERE klauzule menjaju se podaci u celoj tabeli, a upotrebom WHERE klauzule se precizira koji slog se želi izmeniti PRIMER UPDATE PROFESORI SET ZVANJE=‘Docent’ WHERE PROFESOR_ID=1

18. Brisanje slogova (DELETE) Briše slogove u tabeli Opšta sintaksa DELETE FROM ime_tabele [WHERE uslov] Bez WHERE klauzule se brišu podaci u celoj tabeli, a upotrebom WHERE klauzule se precizira koji slog se želi brisati PRIMER DELETE FROM PROFESORI WHERE PROFESOR_ID=1

19. Primarniključ (PRIMARY KEY) Definiše se prilikom definisanja tabele Opšta sintaksa CONSTRAINT ime_ograničenja PRIMARY KEY (obl1 [, obl2, ...]) PRIMER CREATE TABLE RADNA_MESTA ( RADNO_MESTO_ID INTEGER NOT NULL, NAZIV VARCHAR(20), CONSTRAINT PK_RADNO_MESTO PRIMARYKEY(RADNO_MESTO_ID) ) Veze Referencijalni integritet garantuje da će podaci u tabelama koje su povezane imati smisla PRIMER ako se ne definiše referencijalni integritet postoji problem kod: dodavanja, brisanja i izmene Radnik  Radno mesto

20. Strani ključ (FOREIGN KEY) Definiše se prilikom kreiranja tabele Opšta sintaksa CONSTRAINT ime_ograničenja FOREIGN KEY (obl1) REFERENCES tabela_na_koju_se_referencira (obl_iz_strane_tabele) [ON UPDATE akcija] [ON DELETE akcija] PRIMER CREATE TABLE RADNICI ( RADNIK_ID INTEGER NOT NULL, IME VARCHAR(20), RADNO_MESTO_ID INTEGER, CONSTRAINT PK_RADNIK PRIMARY KEY(RADNIK_ID), CONSTRAINT FK_RADNIK_RADNO_MESTO FOREIGN KEY(RADNO_MESTO_ID) REFERENCES RADNA_MESTA (RADNO_MESTO_ID) )

21. Upiti nad više tabela Tabele se mogu “kombinovati” spajanjem, koje se vrši navođenjem u FROM klauzuli Ako se ne precizira, radi se Dekartov proizvod tabela (“svaki sa svakim”) PRIMER SELECT * FROM RADNICI, RADNA_MESTA Spajanje se vrši po paru (spoljni ključ prve tabele, primarni ključ druge tabele) PRIMER izlistati imena svih profesora i nazive predmeta koje predaju SELECT A.IME, A.PREZIME, B.NAZIVFROM PROFESORI A, PREDMETI B, PREDAJE C WHERE A.PROFESOR_ID=C.PROFESOR_ID ANDB.PREDMET_ID=C.PREDMET_ID

22. najuobičajenijispojevizaizraduselektovanihupita -prikazuju u jednojtabelisvezapisekojiimajuodgovarajućezapise u drugojtabeli vezaizmeđuzapisa je određenaistimvrednostima(u SQL-u where polje1=polje2) u poljimakojaspajajutabele spoljašnispojevise koriste ako je potrebno uključiti i one slogove koji ne zadovoljavaju uslove postoje levi, desni i pun spoljašnji spoj samokompariranispojevi, povezujupodatkeunutarjednetabele spojevioperatorskekomparacije -povezujupodatkekorišćenjemkomparativnenejednakosti(<>), korišćene u upitimaprojektovanimdavratezapisekojinemajunekurelaciju Equi jointsili inner-joints Outher joints Spojevi (Joins) u SQL Self-joints Theta-joints

23. Spojevi PRIMER Postojedvetabele u bazipodataka: Proizvodi (Naziv, Cena, Kategorija, Proizvodjac) Kompanija (Ime, Akcija, Zemlja) Potrebno je pronaći sve proizvode jeftinije od 200$ proizvedene u Japanu i selektovati njihova imena i cene SELECTNaziv, CenaFROMProizvod, KompanijaWHEREProizvodjac=Ime AND Zemlja=‘Japan’AND Cena <= 200 Veza izmeđutabela Proizvod i Kompanija Proizvodi Kompanija

24. Značenje (Semantika) SQL upita SELECTa1, a2, …, ak FROM R1 AS x1, R2 AS x2, …, Rn AS xn WHEREUslov 1. Ugneždene petlje Odgovor = {} for x1 in R1 do for x2 in R2 do ….. for xn in Rn do ifUslov then Answer = Answer  {(x1,…,xk)} return Answer 2. Paraleleni zadaci Answer = {} for all assignments x1 in R1, …, xn in Rn do if Conditions then Answer = Answer  {(x1,…,xk)} return Answer

25. Ugnježdeni upiti Upiti kod kojih je jedna SELECT naredba ugrađena u WHERE klauzulu druge SELECT naredbe Predikatski izrazi: IN, ANY, ALL, EXISTS IN Operator IN podrazumeva da je levi operand unutar liste navedene kao desni operand ovog operatora PRIMER Prikazati najbolje plaćene radnike po radnim mestima SELECT IME, PLATA FROMRADNICI WHERE (RADNO_MESTO_ID, PLATA) IN (SELECT RADNO_MESTO_ID, MAX(PLATA) FROM RADNICI GROUP BY RADNO_MESTO_ID)

26. ANY Operator ANY podrazumeva bilo koju vrednost iz ugnježdenog upita da ispunjavaju uslov Izraz =ANY je ekvivalentan IN izrazu PRIMER Prikazati radnike koji zarađuju više od proseka SELECT IME, PLATA FROM RADNICI WHERE PLATA > ANY (SELECT AVG(PLATA) FROM RADNICI) ALL Operator ALL podrazumeva sve vrednosti iz ugnježdenog upita da ispunjavaju uslov Izraz != ALL ekvivalentan NOT IN izrazu PRIMER Prikazati radnike koji imaju platu veću od svih radnika SELECT IME FROM RADNICI WHERE PLATA >= ALL (SELECT PLATA FROM RADNICI)

27. EXISTS Proverava da li rezultujuća tabela ima barem jedan red PRIMER Prikazati ona radna mesta na kojima nema ni jednog radnika SELECT * FROM RADNA_MESTA WHERE NOT EXISTS ( SELECT * FROM RADNICI WHERE RADNICI.RADNO_MESTO_ID=RADNA_MESTA.RADNO_MESTO_ID) SELECTA.RADNO_MESTO_ID, A.NAZIV FROM RADNA_MESTA A, RADNICI B WHERE A.RADNO_MESTO_ID = B.RADNO_MESTO_ID (+) AND B.RADNIK_ID ISNULL SELECTRADNA_MESTA.RADNO_MESTO_ID, RADNA_MESTA.NAZIV FROM RADNA_MESTA LEFT JOIN RADNICI ON RADNA_MESTA.RADNO_MESTO_ID=RADNICI.RADNO_MESTO_ID WHERE RADNICI.RADNIK_ID IS NULL

28. HVALA NA PAŽNJI Prof. dr Milorad Banjanin