Transcript SQL

Lekcija 9
Baze podataka
Uvod u SQL
Jezik relacione BP
28.11.2006.
Predavanja
1
SQL
Structured Query Language
•
•
•
•
•
Strukturni upitni jezik za RBP, modifikovana rel. algebra
Razvio ga je IBM u okviru projekta System R
Danas je SQL ugrađen u sve vodeće SUBP
ANSI standard American National Standards Institute
Standardizacija:
– Zaštita programa od zastarevanja
– Vertikalna kompatibilnost
– Prenosivost programa na druge računare
• Standardizacija za relacione BP
– Prvi standard 1986, dopunjen 1989
– Značajnije dopune 1992. (ANSI SQL), 1995., 1999.
– Razvoj SQL-a traje i danas
28.11.2006.
Predavanja
2
SQL
• Svaki programski jezik obuhvata podatke i manipulacije nad
tim podacima
• SQL-jezik:
– Objekti manipulacije su relacije
– Rezultati manipulacija su relacije
• Terminologija:
– relacija  tabela
– n-torka  red (vrsta)
– Vrednosti u n-torkama za jedan atribut  kolona
• SQL se zasniva na relacionom računu n-torki
28.11.2006.
Predavanja
3
SQL
• SQL sadrži naredbe za:
– definisanje relacija (shema)
– ažuriranje podataka (unos, izmena, brisanje)
– postavljanje upita
– sortiranje i formatiranje ispisa
– aritmetičke operacije nad podacima
– definisanje pogleda (view)
– kontrolu sigurnosti
– itd....
• SQL podržava 3 osnovne funkcije BP: definicije,
manipulacije i kontrolu
28.11.2006.
Predavanja
4
SQL
- definicije • Definicija BP: struktura, koje tabele, koji atributi, koji tipovi,
koja ograničenja, pomoćni indeksi za direktan pristup
(DDL – Data Definition Language)
• CREATE TABLE – kreiranje tabele u BP
DROP TABLE – uklanjanje tabele iz BP
ALTER TABLE – izmena definicije tabele
CREATE INDEX – kreiranje indeksa
DROP INDEX – uklanjanje indeksa
CREATE VIEW – kreiranje pogleda
DROP VIEW – uklanjanje pogleda
28.11.2006.
Predavanja
5
SQL
- manipulacije • Manipulacija BP:
– upit nad bazom podataka (izveštavanje),
– ažuriranje (unos, izmena, brisanje)
• (DML – Data manipulation Language)
• SELECT – prikaz željenog sadržaja RBP
UPDATE – izmena vrednosti kolona tabele
DELETE – izbacivanje redova tabele
INSERT – dodavanje redova u tabelu
28.11.2006.
Predavanja
6
SQL
- kontrola • Kontrola pristupa podacima:
– koji korisnici imaju pristup,
– kojim podacima,
– šta mogu da rade sa tim podacima
• (DCL – Data Control Language)
• GRANT (dodeljivanje ovlašćenja nad objektima baze
drugim korisnicima od strane vlasnika)
• REVOKE (ukidanje ovalašćenja dodeljenih pomoću
GRANT)
28.11.2006.
Predavanja
7
SQL
• SQL – neodgovarajući naziv - nije samo upitni jezik
• SQL podržava oba režima rada sa BP:
– Interaktivni: korisnik zadaje jednu po jednu SQL
naredbu, preko tastature, ishod se prikazuje na monitoru,
pristup BP je ograničen jedino pravima korisnika
– Programski: korisnik pokreće program u kome su
ugrađene SQL naredbe, pristup BP je ograničen pravima
korisnika i sadržajem programa
• SQL blokovi ugrađeni u klasični viši programski jezik
28.11.2006.
Predavanja
8
SQL
• Logičke operacije: AND, OR i NOT
• Operacije poređenja:
– =, <, >, , , 
– IN, ANY, ALL, BETWEEN, IS NULL, LIKE, …
• Skupovne operacije:
– UNION, INTERSECT, EXCEPT
• Funkcije nad skupovima podataka:
– COUNT, SUM, MIN, MAX, AVG
• Izrazi se mogu grupisati pomoću zagrada
28.11.2006.
Predavanja
9
SQL
tipovi podataka
• INTEGER: (INT), ceo broj sa ili bez predznaka čiji broj cifara
zavisi od konkretne implement.
• SMALLINT: ceo broj sa brojem cifara manjim u odnosu na
INTEGER
• REAL: realni broj sa ili bez predznaka, preciznost zavisi od
konkreten implement.
• DOUBLE PRECISION: (DOUBLE), proširena preciznost u
odnosu na REAL
• DECIMAL(m,n): (DEC(m,n)), decimalni broj sa ili bez
predznaka, m-cifara, a n-decimalnih
28.11.2006.
Predavanja
10
SQL
tipovi podataka
• CHARACTER(n): (CHAR(n)), niz znakova fiksne dužine n
– Konstanta tipa CHAR se piše između jednostrukih
navodnika
• VARCHAR(n) - niz znakova dužine najviše n
• TEXT – tekst proizvoljne dužine
• Praktično sve implementacije SQL-a podržavaju dodatne
tipove podataka kao što su:
– BOOLEAN – TRUE/FALSE (tačno/netačno)
– DATE – datum (‘2004-12-01’)
– TIME – vreme (’16:50:07’)
28.11.2006.
Predavanja
11
SQL
- sintaksa • SQL ne pravi razliku između velikih i malih slova (case
insensitive). Sledeće dve naredbe su jednake:
– select prezime from osoba where
ime = ‘Marko’;
– SELECT prezime FROM osoba WHERE ime = ‘Marko’;
• Komentari:
– -- ovo je komentar
– /* ovo je komentar
koji se proteže
u više redova */
• Za nazive (imena) se ne smeju koristiti rezervisane reči
28.11.2006.
Predavanja
12
SQL
- sintaksa • Separator naredbi:
naredba1;
naredba2;
• FORMAT naredbi: Sledeće naredbe su ispravno napisane
– SELECT *
FROM student
WHERE BrInd ≤ 100;
– SELECT * FROM student WHERE BrInd ≤ 100;
– SELECT *
FROM
student
WHERE
BrInd
≤ 100;
28.11.2006.
Predavanja
13
Kreiranje tabela
• Prilikom kreiranja tabele (definicija njene strukture i osobina)
neophodno je navesti:
– Ime tabele, mora biti unikatno u BP
– Ime svake kolone, mora biti unikatno unutar tabele
– Tip svake kolone
– Jedno ili više ograničenja za kolone, koje ih imaju
– Jedno ili više ograničenja za svaku tabelu, ako postoje
28.11.2006.
Predavanja
14
Kreiranje tabela
• Sintaksa naredbe kreiranja tabele:
CREATE TABLE ImeTabele (
ImeKolone TipKolone OgraničenjeKolone ...
{, ImeKolone TipKolone OgraničenjeKolone ...}
[OgraničenjeTabele {, OgraničenjeTabele}]);
• ImeTabele i ImeKolone – pravila koja važe za većinu
varijabli: prvi znak je slovo, ostali znaci su slova, cifre,
posebni znaci itd.)
• TipKolone – SQL tip podataka
• Uz svaku kolonu mogu se navesti jedno ili više
ograničenja za tu kolonu
28.11.2006.
Predavanja
15
Kreiranje tabela
• Osnovne klauzule OgraničenjeKolone:
– NOT NULL – u koloni nije dozvoljena NULL
– UNIQUE – u koloni nije dozvoljeno ponavljanje iste
vrednosti
– PRIMARY KEY – kolona je primarni ključ, nije
dozvoljena NULL vrednost niti ponavljanje vrednosti
– CHECK (Predikat) – svaka vrednost u koloni mora da
zadovolji uslov zadat logičkim izrazom Predikat. U izrazu
se ne mogu navoditi druge kolone
28.11.2006.
Predavanja
16
Kreiranje tabela
– DEFAULT=Konstanta – ako se prilikom unošenja jednog
reda podataka u tabelu za kolonu ne zada vrednost,
podrazumeva se Konstanta
– REFERENCES ImeTabele - specifikacija referencijalnog
integriteta za jednu kolonu
• kolona je strani ključ u odnosu na tabelu ImeTabele,
• mora imati ili vrednost primarnog ključa u toj tabeli ili
NULL (ako nema NOT NULL ograničenja)
28.11.2006.
Predavanja
17
Kreiranje tabela
• Za celu tabelu se mogu zadati:
– ni jedno,
– jedno ili
– više ograničenja.
• Ograničenja mogu da važe za jednu ili više kolona
• UNIQUE(ListaKolona) – nije dozvoljeno ponavljanje istih
vrednosti kombinacija
• PRIMARY KEY(ListaKolona) – navedene kolone su primarni
ključ,...
• Konstrukcija ListaKolona označava:
ImeKolone {, ImeKolone}
28.11.2006.
Predavanja
18
Kreiranje tabela
• Klauzule za dinamičku specifikaciju referencijalnog
integriteta,
– Šta se dešava u slučaju pokušaja brisanja reda u ciljnoj
tabeli ImeTabele,
– Šta se dešava u slučaju pokušaja izmene vrednosti
primarnog ključa u ciljnoj tabeli ImeTabele
• DELETE OF ImeTabele
{RESTRICTED CASCADES NULLS}
• UPDATE OF ImeTabele
{RESTRICTED CASCADES NULLS}
28.11.2006.
Predavanja
19
Kreiranje tabela
• Sva ograničenja navedena u CREATE TABLE su aktivna u
svakom trenutku postojanja tabele
• SUBP će odbiti svaki pokušaj sa tabelom, koji je u
suprotnosti sa ograničenjima
• Olakšica za projektante i programere BP
• Provere se ne moraju ugrađivati u aplikativne programe
• Deklarativna moć naredbe CREATE TABLE je od velikog
značaja, naročito kod dinamičke specifikacije referencijalnog
integriteta
28.11.2006.
Predavanja
20
Kreiranje tabela
• Primer: Kompletna definicija BP Biblioteka
• CREATE TABLE Oblast (
SifO
CHAR(2)
PRIMARY KEY,
Naziv
CHAR(20) NOT NULL UNIQUE );
• CREATE TABLE Naslov (
SifN
CHAR(4)
PRIMARY KEY,
Naziv
CHAR(20) NOT NULL
SifO
CHAR(2)
NOT NULL REFERENCES Oblast
UPDATE OF Oblast CASCADES,
DELETE OF Oblast RESTRICTED );
28.11.2006.
Predavanja
21
Kreiranje tabela
• CREATE TABLE Autor (
SifA
CHAR(3)
PRIMARY KEY,
Ime
CHAR(15) NOT NULL );
• CREATE TABLE Clan (
SifC
CHAR(3)
PRIMARY KEY,
Ime
CHAR(15) NOT NULL );
• CREATE TABLE Knjiga (
SifK
CHAR(3)
PRIMARY KEY,
SifN
CHAR(4)
NOT NULL REFERENCES Naslov
UPDATE OF Naslov CASCADES,
DELETE OF Naslov RESTRICTED );
28.11.2006.
Predavanja
22
Kreiranje tabela
• CREATE TABLE Je_Autor (
SifA
CHAR(3)
REFERENCES Autor,
SifN
CHAR(4)
REFERENCES Naslov,
Koji
INT
NOT NULL CHECK(Koji>0)
PRIMARY KEY (SifA,SifN),
UPDATE OF Autor CASCADES,
DELETE OF Autor RESTRICTED,
UPDATE OF Naslov CASCADES,
DELETE OF Naslov RESTRICTED );
28.11.2006.
Predavanja
23
Kreiranje tabela
• CREATE TABLE Drzi (
SifK
CHAR(3)
PRIMARY KEY
REFERENCES Knjiga,
SifC
CHAR(3)
NOT NULL
REFERENCES Clan,
Datum
DATE
NOT NULL
UPDATE OF Knjiga CASCADES,
DELETE OF Knjiga RESTRICTED,
UPDATE OF Clan CASCADES,
DELETE OF Clan RESTRICTED );
28.11.2006.
Predavanja
24
Uklanjanje tabela
• Izbor dinamičke specifikacije referencijalnih integriteta, kod
uklanjanja predstavlja delikatnu operaciju
• Ako se nepromišljeno koristi RESTRICTED, nameće se krut
režim, npr. ne mogu se ukloniti pogrešno uneti podaci iz
tabele
• Naredba uklanjanja tabele iz BP
DROP TABLE ImeTabele ;
• Tabela koja se uklanja mora biti prazna. U suprotnom SUBP
neće izvršiti tu naredbu
28.11.2006.
Predavanja
25
Kreiranje indeksa
• Indeks – pomoćna datoteka za ubrzanje pristupa podacima
u osnovnoj datoteci
• U osnovnoj datoteci zapisi se nalaze u nekom fizičkom
redosledu (redosled unošenja)
• Indeks – može se preuređivati u rastućoj ili opadajućoj
vrednosti indeksnog niza
• Sintaksa naredbe kreiranja indeksa:
CREATE [UNIQUE] INDEX ImeIndeksa
ON ImeTabele (ListaKolona)
28.11.2006.
Predavanja
26
Kreiranje indeksa
• UNIQUE – opcija da indeks mora biti unikatan. U tabeli na
koju se indeks odnosi ne sme se više puta ponoviti
vrednost ListaKolona
– ImeIndeksa – unikatni naziv indeksa u BP
– Ime tabele – tabela na koju se indeks odnosi
– ListaKolona – navođenje jedne ili više kolona, po kojima
se formira INDEKS
• Nad istom tabelom se može definisati više indeksa (za
različite pristupe podacima)
• Indeks se može kreirati odmah (dok je tabela prazna) ili
naknadno
28.11.2006.
Predavanja
27
Kreiranje i
uklanjanje indeksa
• Klauzula UNIQUE u definiciji indeksa ima efekat na klauzulu
UNIQUE kod tabele:
– SUBP odbija svaku izmenu podataka u tabeli koja
narušava unikatnost indeksa
– SUBP odbija kreiranje unikatnog indeksa za tabelu čiji
zatečeni sadržaj narušava tu unikatnost
• Indeks se može bilo kada i bez obzira na sadržaj svoje
tabele ukloniti naredbom:
DROP INDEX ImeIndeksa
28.11.2006.
Predavanja
28
Indeksi
• Primer: Ako se želi brz pristup podacima u tabeli Je_Autor
po dva osnova, po šifri autora i po šifri naslova, kreiraju se
dva indeksa:
• CREATE INDEX Je_Autor1 ON Je_Autor(SifA);
• CREATE INDEX Je_Autor2 ON Je_Autor(SifN);
je_autor1 (SIFA)
AP0
AP1
DM0
DM0
IT0
JN0
ZP0
ZP0
28.11.2006.
je_autor (SIFA
AP0
JN0
DM0
ZP0
DM0
AP1
IT0
ZP0
SIFN KOJI)
RBP0 1
RBP0 2
RK00 1
PP00 1
PP00 2
PJC0 1
PP00 3
PJC0 2
Predavanja
je_autor2 (SIFN)
PJC0
PJC0
PP00
PP00
PP00
RBP0
RBP0
RK00
29
Indeksi
• Napomene:
• Svaka indeksna datoteka ima dva dela:
– ListaKolona, po kojima se vrši pretraživanje i po kojima
se vrši uređivanje indeksa
– Indeks, koji služi za vezu sa osnovnom datot.
• Primer: Kreiranje indeksa nad jednim atributom koji nije
primarni. Zadata je tabela
gradjanin (matbr#,prezime,ime,datrodj,adresa)
– Redni broj zapisa (record number) vodi se u većini
programskih paketa
– Neka je INDGRAD indeksna datoteka
28.11.2006.
Predavanja
30
Indeksi
gradjanin
indgrad
Redbr
matbr#
prez
ime
1
13248
Antić
2
43286
3
ind
prez
Zoran
1
Antić
Jović
Milan
4
Babić
56732
Marić
Goran
2
Jović
4
56879
Babić
Dragan
6
Lazić
5
42116
Rodić
Petar
3
Marić
6
89764
Lazić
Ana
7
Perić
7
13589
Perić
Vera
5
Rodić
28.11.2006.
datrodj
Predavanja
adresa
31
Indeksi
• Nisu svi atributi dobri kandidati za indeks. Npr. bit-map, text
ili slika
• Po pravilu su strani ključevi kandidati za indeks
• Indeksiranje ima i svojih nedostataka: Prilikom ažuriranja
osnovne tabele (brisanje, unošenje), mora se vršiti
reindeksiranje, pa se gubi na vremenu.
• Tabele sa malim brojem podataka u zapisu se ne
indeksiraju, jer se pretraga može efikasno izvršiti brzim
računarima.
28.11.2006.
Predavanja
32
Izmena postojeće
tabele
• Naredba ALTER
• Sintaksa:
ALTER TABLE ImeTabele
ADD (ImeKolone Tip, [ImeKolone Tip]...);
• Primer: U tabelu Odeljenje dodati kolone Sef_Od i Br_Zap
ALTER TABLE Odeljenje
ADD (Šef_Od INTEGER, Br_Zap NUMBER(2));
• Primer: U istoj tabeli povećati dužinu za Br_Zap
ALTER TABLE Odeljenje
MODIFY (Br_Zap NUMBER(6));
28.11.2006.
Predavanja
33
Kreiranje i uklanjanje
pogleda
•
•
•
•
•
•
•
•
Osnovne tabele – fizički postoje na disku
Pogled – virtuelna (izvedena) tabela
Nastaje kao rezultat upita
Sintaksa naredbe kreiranja pogleda:
CREATE VIEW ImePogleda
[ListaKolona] AS Upit;
ImePogleda - unikatno ime u BP
Upit – naredba upita SELECT
Pogled nasleđuje tipove kolona iz osnovnih tabela
Uklanjanje – DROP VIEW ImePogleda;
28.11.2006.
Predavanja
34
Prednosti
pogleda
– Jednostavnost korišćenja, uprošćavaju se upiti
– Tajnost, mehanizam za kontrolu pristupa podacima
(korisnik vidi samo neke podatke)
– Performanse, definicija pogleda se čuva u
kompajliranom, prevedenom obliku
– Nezavisnost podataka, menjaju se definicije pogleda, a
ne aplikacije koji koriste podatke iz BP preko pogleda
– Vrsta potprograma u SQL-u
– Jednom kreiran može da se koristi u podupitima, u
WHERE i HAVING klauzulama
– Zamenjuje komplikovane upite
28.11.2006.
Predavanja
35
UPIT
- SELECT -
• Najznačajnija i najčešće korišćena SQL naredba za
manipulaciju podacima
• Kod svakog upita zadajemo (u principu):
– Koje podatke tražimo kao rezultat,
– Iz kojih tabela to tražimo,
– Koji uslov treba da zadovolje podaci, da bi bili uključeni u
rezultat
– U kom redosledu želimo prikaz podataka
28.11.2006.
Predavanja
36
Prost upit nad
jednom tabelom
• Podrazumeva se naredba upita SELECT, nad jednom
tabelom
• Kao rezultat daje niz redova (ili jedan ili nijedan) koji
zadovoljavaju eventualno zadati uslov
• SELECT lista – podrazumeva se specifikacija podataka u
rezultatu upita
• Specifikacija – zadata jednim ili sa više izraza odvojenih
zarezima (R-lista)
• Rezultat upita ne mora biti relacija (unikatnost)
28.11.2006.
Predavanja
37
Prost upit nad
jednom tabelom
• Sintaksa
• SELECT * {[ALL  DISTINCT] R-Lista}
FROM ImeTabele
[WHERE R-Predikat]
[ORDER BY ImeKolone [DESC]
{, ImeKolone [DESC]} …];
• * - Specijalni slučaj R-liste, kada u rezultat želimo da
uključimo sve kolone tabele
• ALL – iz rezultata neuklanja istovetne redove,
DISTINCT – suprotno. Podrazumeva se ALL.
• R-Lista se zadaje kao jedan ili više R-Izraza, pored naziva
kolone javljaju se i konstante
28.11.2006.
Predavanja
38
Prost upit nad
jednom tabelom
• Klauzula FROM (“odakle”), specificira se ImeTabele. To je
ime osnovne tabele ili pogleda nad kojim se vrši upit. Ovo je
obavezna klauzula.
• R_Predikat , uslov prikazivanja rezultata,
– to je logički izraz izračunljiv nad svakim pojedinim redom
tabele.
– Rezultat upita se dobija samo za one vrednosti
R_Predikata koje daju istinitosnu vrednost.
– Najčešće je to relacioni izraz (>,<,=,…) sa kolonama, a
sa desne strane može se javiti i konstanta
• ORDER BY – daje željeni redosled prikaza rezultata.
Podrazumeva se rastući redosled (ASC). U suprotnom se
navodi DESC uz odgovarajuću kolonu. Uvek je poslednja
klauzula u SELECT bloku.
28.11.2006.
Predavanja
39
Prost upit nad
jednom tabelom
• Najjednostavniji mogući SQL upit je u formi:
SELECT * FROM ImeTabele;
– Ova naredba prikazuje sve redove tabele čije je ime
navedeno iza FROM klauzule
– U svakom redu prikazuju se vrednosti svih kolona, onim
redom kako je to zapisano u datoteci (tj. kreirano sa
CREATE TABLE)
• Kod upita se obično traži prikaz samo određenih kolona, ili
prikaz svih kolona u redosledu koji je drugačije određen.
• Ovo odgovara operaciji projekcije, ali se ne elimišu
višestruka ponavljanja istih vrednosti
28.11.2006.
Predavanja
40
Prost upit nad
jednom tabelom
• Primeri: Upiti za RBP BIBLIOTEKA sa dobijenim rezultatima
• Upit za prikaz cele tabele:
SELECT *
BP
Baze podataka
FROM Oblast ;
RM Računarske mreže
PJ
Programski jezici
• Upit za prikaz cele tabele (željeni redosled)
SELECT *
BP
Baze podataka
FROM Oblast
PJ
Programski jezici
ORDER BY SifO;
RM Računarske mreže
28.11.2006.
Predavanja
41
Prost upit nad
jednom tabelom
• Upit za prikaz samo jedne kolone iz tabele i bez
eliminacije duplikata
SELECT SifN
RBP0
FROM Knjiga ;
RBP0
RK00
PJC0
PJC0
PJC0
PP00
PP00
PP00
28.11.2006.
Predavanja
42
Prost upit nad
jednom tabelom
• Upit za prikaz samo jedne kolone iz tabele i sa eliminacijom
duplikata :
SELECT DISTINCT SifN
RBP0
FROM Knjiga
RK00
ORDER BY SifN ;
PJC0
PP00
• Upit za prikaz dve kolone sa zadavanjem uslova:
SELECT SifN, Naziv
PP00 PASCAL programiranje
FROM Naslov
PJC0 Programski jezik C
WHERE SifO=‘PJ’;
• Operacije poređenja kod klauzule WHERE mogu biti u formi
relacionog izraza:
Izraz1 {<<= = <> >= >} Izraz2
28.11.2006.
Predavanja
43
Prost upit nad
jednom tabelom sa
izvedenim rezultatom
• Podrazumeva se naredba upita SELECT nad jednom
tabelom koja kao rezultat daje jedan red podataka koji su
izvedeni iz svih redova tabele, koji zadovoljavaju zadati
uslov
• SELECT lista takvog upita se sastoji iz jednog ili više izraza
(G-Lista i G-Izrazi)
• Zadavanje redosleda redova u rezultatu nema smisla
(dobija se samo jedan red)
28.11.2006.
Predavanja
44
Prost upit nad
jednom tabelom sa
izvedenim rezultatom
• Sintaksa za SELECT (prost upit nad jednom T sa
izvedenim rezultatom)
SELECT G-Lista
FROM ImeTabele
[WHERE R-Predikat];
• G-Izrazi: najčešće ih čine posebne SQL funkcije (svodne ili
agregatne funkcije)
• Svodne funkcije:
– SUM (ImeKolone)
Nalazi sumu svih ne-NULL
vrednosti zadate kolone
– AVG (ImeKolone)
Nalazi prosečnu vrednost svih neNULL vrednosti zadate kolone
28.11.2006.
Predavanja
45
Prost upit nad
jednom tabelom sa
izvedenim rezultatom
– MIN (ImeKolone)
Nalazi minimalnu vrednost svih neNULL vrednosti zadate kolone
– MAX (ImeKolone)
Nalazi maksimalnu vrednost svih
ne-NULL vrednosti zadate kolone
– COUNT(*)
Nalazi ukupan broj redova u tabeli
– COUNT([ALLDISTINCT] ListaKolona)
Bez DISTINCT nalazi ukupan broj ne-NULL vrednosti
zadate kombinacije kolona
Sa DISTINCT nalazi ukupan broj različitih ne-NULL
vrednosti zadate kombinacije kolona
28.11.2006.
Predavanja
46
Prost upit nad
jednom tabelom sa
izvedenim rezultatom
• Primeri:
• Upit za prikaz ukupnog broja članova (odgovara broju
redova u tabeli Clan)
SELECT COUNT(*)
4
FROM Clan ;
• Upit za prikaz broja članova koji su vršili pozajmice
(odgovara broju različitih vrednosti kolone SifC u
Pozajmica)
SELECT COUNT(DISTINCT SifC)
3
FROM Pozajmica ;
28.11.2006.
Predavanja
47
Prost upit nad
jednom tabelom sa
izvedenim rezultatom
• Upit za prikaz broja naslova koje je napisao autor šifre ‘DM0’:
SELECT COUNT(*)
2
FROM Je_Autor
WHERE SifC=‘DM0’ ;
• Upit za prikaz sume trajanja svih pozajmica:
SELECT SUM(Dana)
27
FROM Pozajmica ;
• Upit za prikaz minimalnog i maksimalnog trajanja pozajmica:
SELECT MIN(Dana), MAX(Dana)
2
7
FROM Pozajmica ;
28.11.2006.
Predavanja
48
Prost upit nad
jednom tabelom sa
izvedenim rezultatom
• Upit za prikaz sume i proseka trajanja pozajmice za člana
šifre ‘JJ1’:
SELECT SUM(Dana), AVG(Dana)
9
5
FROM Pozajmica
WHERE SifC= ‘JJ1’;
– Primedba: rezultat AVG funkcije preuzima tip podataka
od argumenta (tip kolone)
• Upit koji nema smisla:
SELECT SifC, SUM(Dana)
FROM Pozajmica;
Ovde je SifC podatak na nivou jednog reda, a
SUM(Dana) podatak sveden iz više redova
28.11.2006.
Predavanja
49
Svodni upit nad
jednom tabelom
• Prethodni primer: Želeo se prikaz sume trajanja pozajmica
po šiframa članova.
• Dopuna: Prikazati samo podatke za članove za koje je npr.
trajanje pozajmice >10
• Postupak je sledeći:
– Od tabele Pozajmica formira se međurezultat sa
kolonama (SifC i Dana) grupisane po SifC
– Formira se nova tabela sa jednom vrednošću za SifC i
izračunate SUM (Dana) po SifC
– Prikazati redove koji zadovoljavaju traženi uslov
28.11.2006.
Predavanja
50
Svodni upit nad
jednom tabelom
SifP
1
2
3
4
5
6
SifC SifK Dana
JJ0 004 5
PP0 007 2
JJ1 005 6
JJ0 008 7
PP0 002 4
JJ1 009 3
SifC
JJ0
JJ0
PP0
PP0
JJ1
JJ1
Dana
5
7
2
4
6
3
SifC Dana
JJ0 12
PP0 6
JJ1
9
Rezultat
JJ0 12
Uslov:
SUM(Dana)>10
• Potrebno je dodatno precizirati:
– Po kojim kolonama se vrši grupisanje, i koje svodne
funkcije se traže unutar grupe
– Koji uslov se zadaje za uključenje svodnih redova u
rezultat
28.11.2006.
Predavanja
51
Svodni upit nad
jednom tabelom
• Sintaksa za SELECT za svodni upit nad jednom tabelom
SELECT ListaKolona [ListaFunkcija]
FROM ImeTabele
[WHERE R-Predikat]
GROUP BY ListaKolona
HAVING G-Predikat
[ORDER BY Element [DESC]
{, Element [DESC]} …];
28.11.2006.
Predavanja
52
Svodni upit nad
jednom tabelom
– WHERE, zadaje se uslov koji svaki red u tabeli
ImeTabele mora da zadovolji
– GROUP BY, navodi se jedna ili više kolona po kojima se
vrši grupisanje
Kolone koje se navode ne moraju biti uz SELECT
Kolene koje su uz SELECT moraju se naći uz GRUP BY
– HAVING, formira se uslov koji svaki red formiran
svođenjem mora da zadovolji da bi bio uključen u rezultat
Mogu da se jave kolone i funkcije koje nisu uz SELECT
– ORDER BY, mogu se kao Element navoditi samo kolone
ili funkcije koje su navedene uz klauzulu SELECT
28.11.2006.
Predavanja
53
Svodni upit nad
jednom tabelom
SifP
1
2
3
4
5
6
SifC SifK Dana
JJ0 004 5
PP0 007 2
JJ1 005 6
JJ0 008 7
PP0 002 4
JJ1 009 3
SifC
JJ0
JJ0
PP0
PP0
JJ1
JJ1
Dana
5
7
2
4
6
3
SifC Dana
JJ0 12
PP0 6
JJ1
9
Rezultat
JJ0 12
HEAVING
SUM(Dana)>10
GROUP BY
SifC, Dana
28.11.2006.
Predavanja
54
Svodni upit nad
jednom tabelom
• Primeri:
• Upit za prikaz šifara autora i broja naslova koje su napisali
SELECT SifA, COUNT(*)
FROM Je_Autor
GROUP BY SifA ;
28.11.2006.
Predavanja
AP0
JN0
DM0
ZP0
AP1
IT0
1
1
2
2
1
1
55
Svodni upit nad
jednom tabelom
• Upit za prikaz šifara članova čija je suma trajanja pozajmice
veća od 10
SELECT SifC
JJ0
FROM Pozajmica
GROUP BY SifC
HAVING SUM(Dana)>10;
• Upit za prikaz šifara članova i njihovog ukupnog broja i
trajanja pozajmica, ali samo za pozajmice duže od 2 dana
SELECT SifC,COUNT(*), SUM(Dana)
FROM Pozajmica
JJ0 2 12
WHERE Dana>2
PP0 1 4
GROUP BY SifC;
JJ1 2 9
28.11.2006.
Predavanja
56
Upiti nad više
tabela
• Podrazumevaju spajanje tabela po nekom uslovu
• Iza FROM klauzule SELECT naredbe navodi se više tabela
odvojenih zarezima
• Sintaksa:
SELECT * {[ALL  DISTINCT] R-Lista}
FROM ImeTabele [NadimakTabele] {,…}
[WHERE R-Predikat]
[ORDER BY ImeKolone [DESC]
{, ImeKolone [DESC]} …];
28.11.2006.
Predavanja
57
Upiti nad više
tabela
• Za kolone koje se nalaze u više tabele obavezno je
navođenje
– ImeTabele.ImeKolone
– NadimakTabele.ImeKolone
• R-Predikat- navodi se uslov spajanja u formi uslova
jednakosti vrednosti odgovarajućih kolona u tabelama
• Upit nad više tabela bez uslova spajanja daje kao rezultat
Dekartov proizvod tih tabela
28.11.2006.
Predavanja
58
Upiti nad više
tabela
• Primeri: Upit koji daje nazive naslova i nazive njihovih
oblasti (spajaju se tabele Naslov i Oblast po uslovu
jednakosti kolona SifO)
SELECT N.Naziv, O.Naziv
FROM Naslov N, Oblast O
WHERE N.SifO=O.SifO
ORDER BY N.Naziv ;
28.11.2006.
Predavanja
59
Upiti nad više
tabela
• Upit koji daje šifre i nazive naslova knjiga koje članovi drže
kod sebe (spajaju se tabele Drzi, Knjiga i Naslov po dva
uslova jednakosti kolona koja se kombinuju sa AND)
SELECT DISTINCT N.SifN, Naziv
FROM Drzi D, Knjiga K, Naslov N
WHERE D.SifK=K.SifK AND K.SifN=N.SifN;
28.11.2006.
Predavanja
60
Upiti nad više
tabela
• Upit koji daje imena članova koji su pozajmljivali knjige
(spajaju se tabele Pozajmica i Clan po uslovu jednakosti
kolona SifC, svako ime treba da se javi samo jednom u
rezultatu)
SELECT DISTINCT C.SifC, Ime
FROM Clan C, Pozajmica P
WHERE C.SifC=P.SifC;
28.11.2006.
Predavanja
61
Prost upit sa
svodnim rezultatom
nad više tabela
• Sintaksa:
SELECT G-Lista
FROM ImeTabele [NadimakTabele] {,…}
[WHERE R-Predikat]
• Primer: Upit koji daje ukupno trajanje pozajmica svih knjiga
sa šifrom naslova ‘PP00’
SELECT SUM(Dana)
FROM Pozajmica P, Knjiga K
WHERE P.SifK=K.SifK AND SifN=‘PP00’
28.11.2006.
Predavanja
62
Klauzule WHERE i
HAVING
• Predikati – to su relacioni izrazi, koji se mogu kombinovati
• Predikati: prosti i složeni
• Prost predikat: elementarni logički izraz izračunljiv nad
svakim redom neke tabele
• Složen predikat: formira se od prostih, primenom logičkih
operatora AND, OR i NOT
• Forme prostih predikata:
Izraz1 {<|<=|=|<>|>=|>} Izraz2
ispituje da li su vrednosti navedenih izraza u zadatom
odnosu
28.11.2006.
Predavanja
63
Klauzule WHERE i
HAVING
• Izraz [NOT] BETWEEN Izraz1 AND Izraz2
ispituje da li je (ili nije) vrednost izraza u zadatim granicama.
Ekvivalentno bi bilo:
[NOT] Izraz>=Izraz1 AND Izraz<=Izraz2
• Kolona IS [NOT] NULL
ispituje da li je (ili nije) vrednost kolone NULL
• ZnakovniIzraz [NOT] LIKE ZnakovnaMaska
Znakovna vrednost – tipa CHARACTER
Dva specijalna znaka (džokeri):
‘_’ bilo koji znak (može ih biti više),
‘%’ bilo koji broj znakova
28.11.2006.
Predavanja
64
Klauzule WHERE i
HAVING
• Izraz [NOT] IN (Konstanta {, Konstanta})
ispituje da li je (ili nije) vrednost izraza jednaka nekoj od
navedenih konstanti (isti tip)
• Izraz {<|<=|=|<>|>=|>} ANY (Konstanta {, Konstanta})
ispituje da li je (ili nije) vrednost izraza u navedenom odnosu
sa bar jednom konstantom
• Izraz {<|<=|=|<>|>=|>} ALL (Konstanta {, Konstanta})
ispituje da li je (ili nije) vrednost izraza u navedenom odnosu
sa svim navedenim konstantama (zahteva se isti tip)
28.11.2006.
Predavanja
65
Klauzule WHERE i
HAVING
• Primeri upotrebe: Upiti za RBP BIBLIOTEKA sa dobijenim
rezultatima
• Upit koji daje šifre članova i ukupna trajanja pozajmica od 5
do 10 dana:
SELECT SifC,SUM(Dana)
FROM Pozajmica
GROUP BY SifC
HAVING SUM(Dana) BETWEEN 5 AND 10;
• Upit koji daje nazive svih naslova u kojima se nalazi reč
“jezik”
SELECT Naziv
FROM Naslov
WHERE Naziv LIKE %jezik% ;
28.11.2006.
Predavanja
66
Klauzule WHERE i
HAVING
• Upit koji daje šifre knjiga koje odgovaraju naslovima šifara
“RBP0” i “RK00”
SELECT SifK
FROM Knjiga
WHERE SifN IN (‘RBP0’,’RK00’);
• Prethodni upit, uz upotrebu ANY forme
SELECT SifK
FROM Knjiga
WHERE SifN ANY (‘RBP0’,’RK00’);
• Upit koji daje šifre naslova za sve knjige osim za one sa
šiframa ‘001’, ‘002’, ‘003’
SELECT DISTINCT SifN
FROM Knjiga
WHERE SifK <> ALL (‘001’,’002’, ‘003’);
28.11.2006.
Predavanja
67
Upiti sa podupitima
• Definicija podupita:
– SELECT naredba koja se nalazi u sklopu WHERE i
HAVING klauzula
– Izvršavanje podupita prethodi vrednovanju predikata u
datim klauzulama
28.11.2006.
Predavanja
68
Upiti sa podupitima
• Klasifikacija podupita po načinu izvršavanja:
– Nekorelisani podupit – njegovo izvršavanje ne zavisi od
izvršavanja spoljnog upita; Izvršava se samo jednom – na
početku.
– Korelisani podupit – njegovo izvršavanje zavisi od
spoljnog upita; Izvršava se za svaki red tabele koju
obrađuje spoljni upit
• Posebna forma prostog predikata:
[NOT] EXISTS (R-Upit)
Utvrđuje se ishod podupita. Ako R-Upit kao rezultat daje bar
jedan red, EXISTS daje vrednost “istina”. U suprotnom je
“nije istina”.
28.11.2006.
Predavanja
69
Upiti sa podupitima
• Primer 1:
Sastaviti upit koji daje podatke o pozajmicama
natprosečnog trajanja.
1) Određuje se prosek trajanja svih pozajmica
2) Trajanje svake pozajmice poredimo sa dobijenom
srednjom vrednosti
SELECT *
FROM Pozajmica
WHERE Dana > (SELECT AVG (Dana)
FROM Pozajmica);
• Radi se o nekorelisanom podupitu
28.11.2006.
Predavanja
70
Upiti sa podupitima
• Primer 2:
Sastaviti upit koji daje imena članova čije je ukupno
trajanje pozajmica veće od 10 dana. Za svakog člana iz
tabele Clan treba prema njegovoj šifri utvrditi da li je
njegovo ukupno trajanje pozajmica iznad 10
SELECT Ime
FROM Clan C
WHERE 10 < (SELECT SUM (Dana)
FROM Pozajmica
WHERE SifC=C.SifC);
• Podupit je korelisan i izvršava se za svakog člana
• Spoljna SELECT određuje kolonu koja se koristi u
podupitu
28.11.2006.
Predavanja
71
Upiti sa podupitima
• Primer 3:
Sastaviti upit koji daje imena članova koji drže knjige.
Ovo je primer za korišćenje IN forme predikata. Šifre
članova koji drže knjige daje nekorelisani podupit
SELECT Ime
FROM Clan
WHERE SifC IN (SELECT SifC
FROM Drzi);
28.11.2006.
Predavanja
72
Upiti sa podupitima
• Primer 4:
Sastaviti upit koji daje podatke o pozajmicama koje su
trajale duže od svih pozajmica člana šifre ‘PP0’.
Rešenje primenom ALL konstrukcije
SELECT *
FROM Pozajmica
WHERE Dana > ALL (SELECT Dana
FROM Pozajmica
WHERE SifC=‘PP0’);
28.11.2006.
Predavanja
73
Unija, razlika i
presek upita
• Primena skupovnih operatora na skupove redova koje daju
pojedini upiti
• Upiti koji se kombinuju moraju zadovoljavati uslov unijske
kompatibilnosti
• Definicija klauzula:
– UNION [ALL] – unija dva upita sa eliminacijom identičnih,
ako se ne naglasi ALL
– INTERSECT – presek dva upita, ostaju samo oni redovi
koji se nalaze u rezultatima oba upita
– EXCEPT – razlika dva upita, od redova upita ispred
except klauzule ostaju samo oni koji se ne nalaze u
rezultatu upita iza te klauzule (MINUS)
28.11.2006.
Predavanja
74
Unija, razlika i
presek upita
• Primer 1:
Sastaviti upit koji daje šifre knjiga koje su bile u prometu
Članovi ih drže kod sebe ili su ranije pozajmljivane.
SELECT SifK
FROM Drzi
UNION
SELECT DISTINCT SifK
FROM Pozajmica;
28.11.2006.
Predavanja
75
Unija, razlika i
presek upita
• Primer 2:
Sastaviti upit koji daje naslove knjiga koje su kod članova, a
ranije nisu pozajmljivane
SELECT DISTINCT Naziv
FROM Naslov N, Knjiga K
WHERE N.SifN=K.SifN
AND SifK IN (SELECT SifK
FROM Drzi
EXCEPT
SELECT DISTINCT SifK
FROM Pozajmica);
28.11.2006.
Predavanja
76