Uvod u relacione baze podataka¶
prof. dr Saša Malkov
Univerzitet u Beogradu - Matematički fakultet
2023/24
Čas 2 - Uvod u SQL¶
Na početku moramo da podesimo okruženje i povežemo se sa bazom podataka:
%run db2-jupyter-master/db2.ipynb
%sql connect to stud2020 user smalkov using ?
%sql set schema da
Db2 Extensions Loaded.
Connection successful. Command completed.
Ograničavanje broja izdvojenih redova¶
SQL standard:
SELECT ...
[OFFSET <m> ROWS] FETCH {FIRST|NEXT} [<n>] {ROW|ROWS} ONLY
Alternativno:
LIMIT <n> [OFFSET <m>]
LIMIT <m>,<n>
%%sql
select *
from dosije
offset 5 rows fetch next 10 rows only
--offset 5 rows
--fetch next 10 rows only
--limit 10 offset 5
--limit 5, 10
INDEKS | IDPROGRAMA | IME | PREZIME | POL | MESTORODJENJA | IDSTATUSA | DATUPISA | DATDIPLOMIRANJA | |
---|---|---|---|---|---|---|---|---|---|
0 | 20150071 | 101 | Andjela | Beric | z | Beograd (Savski venac) | -2 | 2015-07-06 | 2020-06-30 |
1 | 20150275 | 101 | Lola | Zivic | z | Beograd (Zvezdara) | -1 | 2015-07-06 | <NA> |
2 | 20150177 | 101 | Blagoje | Kostic | m | Beograd (Savski venac) | 2 | 2015-07-06 | <NA> |
3 | 20150135 | 101 | Vukasin | Zivkovic | m | Beograd (Zvezdara) | 2 | 2015-07-06 | <NA> |
4 | 20150357 | 101 | Milos | Simic | m | Beograd (Savski venac) | -2 | 2015-07-06 | 2020-06-30 |
5 | 20150236 | 101 | Sara | Ruzic | z | Uzice | -2 | 2015-07-06 | 2019-09-30 |
6 | 20150390 | 101 | Bojana | Pesic | z | Uzice | -2 | 2015-07-06 | 2020-09-08 |
7 | 20150174 | 103 | Luka | Blagic | m | Beograd (Savski venac) | 2 | 2015-07-06 | <NA> |
8 | 20150336 | 101 | Petar | Malbasic | m | Zrenjanin | -2 | 2015-07-06 | 2019-09-30 |
9 | 20150425 | 101 | Aleksandar | Cvorovic - Hajdinjak | m | Fier | 2 | 2015-07-06 | <NA> |
Optimizacija za određen broj redova¶
U nekim slučajevima rezultat upita može da bude potencijalno veoma obiman, a da se pri upotrebi tog rezultata koriste i čitaju samo nekoliko prvih redova. Tada možemo da sugerišemo SUBP-u da ćemo verovatno da se uzdržimo od čitanja preostalih redova, što može da promeni način optimizovanja upita. Klauzulom:
OPTIMIZE FOR <n> {ROW|ROWS}
se sugeriše SUBP-u da će se čitati oko n
redova i da je potrebno da se obračun opterećenja obavi u skladu sa tom pretpostavkom. Za razliku od klauzule LIMIT
, i dalje može da se čita i manje ili više redova - ova klauzula ne utiče na sadržaj rezultata izračunavanja, već se radi samo o optimizaciji.
Klauzula OPTIMIZE
ne može da stoji u podupitu već samo na kraju glavnog upita.
%%sql
select *
from dosije
optimize for 5 rows
INDEKS | IDPROGRAMA | IME | PREZIME | POL | MESTORODJENJA | IDSTATUSA | DATUPISA | DATDIPLOMIRANJA | |
---|---|---|---|---|---|---|---|---|---|
0 | 20150069 | 101 | Nemanja | Nicic | m | Sabac | -2 | 2015-07-06 | 2019-09-12 |
1 | 20150090 | 103 | Andrijana | Beara | z | Sabac | -2 | 2015-07-06 | 2019-09-06 |
2 | 20150320 | 103 | Milos | Milenkovic | m | Krusevac | 2 | 2015-07-06 | <NA> |
3 | 20150185 | 101 | Valentina | Stojanov | z | Beograd (Savski venac) | 2 | 2015-07-06 | <NA> |
4 | 20150230 | 103 | Aida | Babic | z | Cacak | 2 | 2015-07-06 | <NA> |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
3491 | 20180100 | 103 | Tara | Bozinovic | z | Beograd (Savski venac) | 1 | 2018-07-02 | <NA> |
3492 | 20190011 | 101 | Ana | Ilic | z | Vrsac | 1 | 2019-07-01 | <NA> |
3493 | 20190254 | 101 | Tamara | Zejak | z | Beograd (Savski venac) | 1 | 2019-07-02 | <NA> |
3494 | 20200213 | 103 | Uros | Stanojkov | m | Uzice | 1 | 2020-07-15 | <NA> |
3495 | 20151029 | 201 | Milos | Djokic | m | Valjevo | -5 | 2015-10-23 | <NA> |
3496 rows × 9 columns
Tipovi¶
Tipovi podataka u SQL'u su prilično bogati, kako zbog raznovrsnoti tipova koje je potrebno podržati za različite vrste aplikacija, tako i zbog prisutnosti različitih varijanti tipova.
Pregled osnovnih tipova:¶
Celobrojni tipovi
SMALLINT
- 16-bitniINTEGER
,INT
- 32-bitniBIGINT
- 64-bitni
Razlomljeni tipovi
sa pokretnom zapetom
REAL
- 32-bitniDOUBLE
- 64-bitniDECFLOAT(n)
- IEEE 754r, preciznost n=16 ili 34 cifara, opseg do 10^6144 (za n=34)
sa fiksnom zapetom
DECIMAL(n,m)
- n=ukupan broj cifara, m=broj cifara desno od zapete, 0<=m<=n<=31
Niske
jednobajtne
CHAR(n)
- niske fiksne dužine, n u opsegu 1 do 255VARCHAR(n)
- niske promenljive dužine, n do 32672CLOB(n)
- velike niske, do 2GiB - 1
dvobajtne
GRAPHIC(n)
- niske fiksne dužine, n u opsegu 1 do 127VARGRAPHIC(n)
- niske promenljive dužine, n do 16336DBCLOB(n)
- velike niske, do 1GiB - 1
binarne
BINARY(n)
- niske bajtova fiksne dužine, n u opsegu 1 do 255VARBINARY(n)
- niske bajtova promenljive dužine, n do 32672BLOB(n)
- velike niske bajtova, do 2GiB - 1
Vremenski tipovi
TIME
- vremeDATE
- datumTIMESTAMP
- datum i vreme
Logički tip
BOOLEAN
- logički tip, ima vrednostiTRUE
iFALSE
Kompletan pregled tipova koje podržava DB2 može da se pogleda u dokumentaciji.
Konverzije tipova¶
SQL je relativno strogo tipiziran jezik. Vrlo često će se odvijati automatski konverzije tipova, gde je to moguće, ali će u mnogim situacijama biti potrebno da se eksplicitno navede konverzija. Postoji opšta funkcija za konverziju, kao i veći broj različitih funkcija za specifične konverzije.
Opšta konverzija - funkcija CAST
¶
Za konvertovanje tipova se koristi funkcija/izraz CAST( <exp> AS <tip> )
:
%%sql
values cast( 1 as double )
1 | |
---|---|
0 | 1.0 |
%%sql
values cast( 42.987 as int )
1 | |
---|---|
0 | 42 |
Specifične konverzije¶
Za veliki broj tipova postoje funkcije, koje se zovu ili isto kao i tip ili vrlo slično, a koje konvertuju dati argument u odgovarajući tip. Na primer, imamo funkcije: CHAR
, DECIMAL
, DATE
, DOUBLE
, FLOAT
, INT
, TIME
i još mnoge druge:
%%sql
values (
int('42'),
int('42.987'),
double('20'),
dec('42')
)
1 | 2 | 3 | 4 | |
---|---|---|---|---|
0 | 42 | 42 | 20.0 | 42.0 |
%%sql
values (
date('15.3.2020'),
date('3/15/2020'),
date('2020-03-15'),
date(737711),
time('12:23'),
-- Jupyter ne ume da prikaže TIMESTAMP
char(timestamp('2023-09-17 12:25:10'))
)
1 | 2 | 3 | 4 | 5 | 6 | |
---|---|---|---|---|---|---|
0 | 2020-03-15 | 2020-03-15 | 2020-03-15 | 2020-10-13 | 12:23:00 | 2023-09-17-12.25.10.000000 |
Izrazi¶
U SQL-u možemo da koristimo izraze na sličan način kao u programskim jezicima, ali postoje neke specifičnosti. Ovde nećemo navesti sve elemente izraza u SQL-u, zato što ih ima mnogo i delimično zavise od implementacije, ali ćemo predstaviti neke specifičnosti.
Najpre primetimo da u izraze spadaju:
- konstante;
- imena kolona;
- posebni_registri;
- zagrađeni izrazi;
- podupiti;
- izraz CASE;
- izraz CAST;
- OLAP izrazi;
- i još mnogo toga.
Od nastajanja SQL-a dodavani su mnogi elementi, poput OO elemenata, XML-a i drugih stvari, pa je svako dodavanje dalje usložnjavalo kako ceo jezik tako i koncept izraza.
Konstante¶
Već smo videli kako se zapisuju osnovne konstantne vrednosti. Dodaćemo par informacija.
Realni brojevi¶
Realni brojevi mogu da se zapisuju u obliku sa eksponentom:
<znak><ceo broj> [.[<decimalni deo>]] [E<exponent>]
Postoje posebne realne vrednosti:
INFINITY
iliINF
- beskonačna vrednost, može da bude označena;NAN
- Not A Number, zapis koji nije broj, ali ne izaziva greške;SNAN
- Signaling NAN, zapis koji nije broj, i izaziva greške;
Niske¶
Niske se zapisuju između apostrofa. U okviru niske apostrof se navodi tako što se zapiše dva puta. Posebno, niske se mogu zapisati i u obliku:
- X'...' - sadržaj mora da bude paran broj heksadekadno kodiranih bajtova koji predstavljaju sadržaj niske; praznine u zapisu su dopuštene i ignorišu se;
- U&'...' [UESCAPE '...'] - UNICODE niska; može da sadrži UTF-8 karaktere ili kodirane UNICODE karaktere u jednom od dva zapisa
\<4 heksa-cifre>
ili\+<6 heksa-cifara>
;
Poseban slučaj niski su:
- dvobajtno kodirane niske, tipa
GRAPHIC
se zapisuju kao:G'...'
iliN'...'
, gde telo zapisa čini 4K heksadekadnih cifara, tj. K kodova ‚dvobajtnih karaktera; - binarne niske, tipa
BINARY
se zapisuju kao:BX'...'
, gde telo zapisa čini 2K heksadekadnih cifara, tj. K kodova bajtova.
%%sql
values
'Obicna niska',
'Обична ниска може да садржи УТФ-8',
X'404142616263',
U&'Знак € може да се запише и овако: \20AC \000aovo je bio novi red, \000aa ovo su neke note \266B\266A'
1 | |
---|---|
0 | Obicna niska |
1 | Обична ниска може да садржи УТФ-8 |
2 | @ABabc |
3 | Знак € може да се запише и овако: € ovo je bi... |
Možemo da iskoristimo uobičajene tehnika rada u Pythonu i Jupyteru da ispišemo prethodni rezultat koristeći promenljivu _
i uobičajen rad sa tabelama podataka (klasa pandas.core.frame.DataFrame
):
print(_["1"][3])
Знак € може да се запише и овако: € ovo je bio novi red, a ovo su neke note ♫♪
Datum i vreme¶
Datum se zapisuje u obliku: DATE 'YYYY-MM-DD'
. Pored toga, niske kojima ne prethodi ključna reč DATE
će se, već preka kontekstu, automatski konvertovati u datume ako su zapisane u jednom od tri naredna oblika:
'YYYY-MM-DD'
'DD.MM.YYYY'
'MM/DD/YYYY'
Vreme se zapisuje kao TIME 'HH:MM[:SS]'
. Kao i u slučaju datuma, niska sa odgovarajućim zapisom može da se automatski konvertuje u vreme.
Konstante tipa TIMESTAMP
(datum i vreme) se zapisuju kao TIMESTAMP 'YYYY-MM-DD HH:MM:SS.nnnnn'
ili TIMESTAMP 'YYYY-MM-DD-HH.MM.SS.nnnnn'
.
%%sql
values
-- bar jedan red mora da ima eksplicitno oznacen datum
date '2020-10-15',
'2020-10-15',
'15.10.2020',
'10/15/2020'
1 | |
---|---|
0 | 2020-10-15 |
1 | 2020-10-15 |
2 | 2020-10-15 |
3 | 2020-10-15 |
Posebni registri¶
Posebni registri predstavljaju predefinisane globalne podatke (nisu promenljive zato što korisnik ne može da ih menja), koji mogu da se upotrebljavaju u upitima i drugim naredbama SQL-a. Ima više takvih registara a ovde ćemo navesti samo neke od najvažnijih:
CURRENT_DATE
- trenutni datum;CURRENT_TIME
- trenutno vreme;CURRENT_TIMESTAMP
,NOW
- sinonimi, trenutno vreme i datum;CURRENT_USER
,USER
- sinonimi, trenutno autorizovan korisnik.
Važno: koliko god puta da se koriste registri za čitanje vremena u jednoj istoj naredbi, svaki put će vratiti istu vrednost, bez obzira na trajanje izvršavanja te naredbe.
%sql values( current_date, current_time, now, user )
Operatori¶
SQL obuhvata sledeće skalarne operatore:
+
,-
,*
,/
- osnovne aritmetičke operacije;%
- ostatak celobrojnog deljenja;**
- stepenovanje;||
,CONCAT
- nadovezivanje niski, sinonimi;&
,|
,^
- binarne logičke operacije nad celim brojevima kao nizovima bitova.
%%sql
values (
'a' concat 'b',
2 ** 3,
19 % 4,
19 | 6,
5 > 3 and 2 < 7
)
1 | 2 | 3 | 4 | 5 | |
---|---|---|---|---|---|
0 | ab | 8 | 3 | 23 | True |
Operacije i funkcije sa datumom i vremenom¶
(neke od stvari koje navodimo za datume i vreme su specifične za DB2)
Operacije sa datumom i vremenom podrazumevaju labeliranje vrednosti sa kojima se radi, tj. eksplicitno označavanje da li brojevi koje navodimo predstavljaju dane, mesece, godine, sate, minute, sekunde ili mikrosekunde.
%%sql
-- koji datum će biti za 1234 dana
values current_date + 1234 days
1 | |
---|---|
0 | 2027-02-25 |
%%sql
-- koji datum i vreme su bili pre 1234567 minuta
values now - 123456 minutes
Oduzimanje dva datumska podatka ima za rezultat osmocifren ceo broj koji ima vrednost:
10000 * <broj celih godina> + 100 * <broj celih meseci> + <broj celih dana>
tako da:
D1 - D2 = RESULT
mora da bude saglasno sa:
D2 + (RESULT/10000) years + (RESULT/100)%100 months + RESULT%100 days = D1
Na sličan način rade i vreme i datum/vreme, s tim da se kod vremena rezultat računa kao:
10000 * <broj celih sati> + 100 * <broj celih minuta> + <broj celih sekundi>
+ <broj celih mikrosekundi>/1000000
a za datum/vreme kao:
100000000 * <broj celih godina> + 1000000 * <broj celih meseci> + 10000 * <broj celih dana>
+ 10000 * <broj celih sati> + 100 * <broj celih minuta> + <broj celih sekundi>
+ <broj celih mikrosekundi>/1000000
%sql values current date - date '2017-02-01'
1 | |
---|---|
0 | 60809.0 |
%sql values now - date '2017-02-01'
1 | |
---|---|
0 | 6.080920e+10 |
%sql values now - date '2017-02-01' - 60802000000
1 | |
---|---|
0 | 7200235.825 |
%sql values current date - 4 years - 8 months - 12 days
1 | |
---|---|
0 | 2019-01-29 |
Postoji veći broj funkcija za rad sa datumom i vremenom:
YEAR(date)
,QUARTER(date)
,MONTH(date)
,WEEK(date)
,DAY(date)
- izračunavaju komponentu datuma, rade za argumente tipaDATE
iTIMESTAMP
;AGE(date)
- slično kaoCURRENT_DATE - date
;DAYNAME(date)
,DAYOFMONTH(date)
,DAYOFWEEK(date)
,DAYOFYEAR(date)
- ...;MONTHNAME(date)
- ...;DAYS(date)
- za 1 više od broja dana od 01.01.0001.;DAYS_BETWEEN(d1,d2)
- broj punih dana od d2 do d1;FIRST_DAY(date)
,LAST_DAY(date)
- prvi i poslednji datum u datom mesecu;NEXT_WEEK(date)
- prvi datum u narednoj nedelji;NEXT_MONTH(date)
- prvi datum u narednom mesecu;NEXT_QUARTER(date)
- prvi datum u narednom tormesečju;NEXT_YEAR(date)
- prvi datum u narednoj godini;HOUR(time)
,MINUTE(time)
,SECOND(time)
,MICROSECOND(time)
- izračunavaju komponentu vremena, rade za argumente tipaTIME
iTIMESTAMP
;HOURS_BETWEEN(t1,t2)
- broj punih sati od t2 do t1;MIDNIGHT_SECONDS(time)
- broj punih sekundi od prethodne ponoći do datig vremena;- i mnoge druge.
%sql values( dayname(now), dayofmonth(now), dayofweek(now), dayofyear(now) )
1 | 2 | 3 | 4 | |
---|---|---|---|---|
0 | Tuesday | 10 | 3 | 283 |
Ako želimo da izračunamo broj dana između dva datuma, onda možemo da koristimo funkciju DAYS_BETWEEN
ili funkciju DAYS
:
%%sql
-- duzina studiranja diplomaca u danima
select
days_between(datdiplomiranja, datupisa),
days(datdiplomiranja) - days(datupisa)
from dosije
where datdiplomiranja is not null
order by 1
1 | 2 | |
---|---|---|
0 | 217 | 217 |
1 | 225 | 225 |
2 | 239 | 239 |
3 | 245 | 245 |
4 | 245 | 245 |
... | ... | ... |
400 | 1911 | 1911 |
401 | 1913 | 1913 |
402 | 1913 | 1913 |
403 | 1913 | 1913 |
404 | 1913 | 1913 |
405 rows × 2 columns
Funkcije za rad sa niskama¶
Ima mnogo funkcija za rad sa niskama, pri čemu je potrebno obratiti pažnju na specifične funkcije za rad sa UTF i UNICODE niskama:
LENGTH(s)
- dužina niske;LENGTHB(s)
- dužina niske u bajtovima;LENGTH2(s)
- dužina niske u UTF formatu;LENGTH4(s)
- dužina niske u 32/bitnom formatu;SUBSTR(s,p,n)
- vraća podnisku niske s od pozicije p (1+) dužine n (može bez dužine, onda se ide do kraja)SUBSTRB(s,p,n)
- isto u bajtovimaSUBSTR2(s,p,n)
- isto u UTF formatuSTRLEFT
,STRRIGHT
,LEFT
,RIGHT
,...CONCAT(s1,s1)
- spajanje niski, može da se koristi i kao funkcija i kao operator;SPACE(n)
- vraća nisku od n praznih mesta;POSSTR(s1,s2)
- traži gde počinje prva niska u drugoj;REPEAT(s,n)
- ponavlja nisku s n puta;REPLACE(s,x,y)
- zamenjuje sva pojavljivanja niske x u s sa y;LTRIM(s)
,RTRIM(s)
,TRIM(s)
- odsecanje praznina sa početka i/ili kraja;UCASE(s)
,LCASE(s)
- konvertovanje niske u velika/mala slova;- i mnogo drugih.
Za neke operacije sa niskama, koje kao argumente ili rezultat imaju indekse ili dužine niski, postoje verzije koje rade sa 8-bitnim, 16-bitnim, 32-bitnim karakterima. Ako je baza podataka napravljena sa opcijom UTF-8 ili Unicode, onda je potrebno da se koriste verzije za 16-bitne ili 32-bitne karaktere:
%%sql
values
-- konkretna baza je napravljena kao UTF-8
-- zato dužina nije konstantna po karakteru
length('abcdАБВГ'),
lengthb('abcdАБВГ'),
length2('abcdАБВГ'),
length4('abcdАБВГ')
1 | |
---|---|
0 | 12 |
1 | 12 |
2 | 8 |
3 | 8 |
%%sql
values
-- ovo radi ispravno zato sto su svi obuhvaćeni znaci ASCII
substr('abcdАБВГ',2,3),
-- ovo ne radi, zato što se uzima "pola" ćiriličnog slova А
substr('abcdАБВГ',2,4),
-- ovo ne radi, zato što se uzimaju dva bajta jednog karaktera
substr('abcdАБВГ',2,5),
-- svi naredni izrazi rade ispravno
substr2('abcdАБВГ',2,3),
substr2('abcdАБВГ',2,4),
substr2('abcdАБВГ',2,5)
1 | |
---|---|
0 | bcd |
1 | bcd |
2 | bcdА |
3 | bcd |
4 | bcdА |
5 | bcdАБ |
Za neke druge operacije ne postoje odgovarajuće verzije, već postoji opcioni dodatni argument, čija vrednost može da bude jedna od specijalnih konstanti:
codeunits16
codeunits32
octets
a ako se ne navede, onda se podrazumeva rad sa 8-bitnim karakterima:
%%sql
values
-- prva tri izraza ne rade ispravno
left('abcdАБВГ',5),
left('abcdАБВГ',6),
left('abcdАБВГ',7),
-- poslednja tri izraza rade ispravno
left('abcdАБВГ',5,codeunits16),
left('abcdАБВГ',6,codeunits16),
left('abcdАБВГ',7,codeunits16)
1 | |
---|---|
0 | abcd |
1 | abcdА |
2 | abcdА |
3 | abcdА |
4 | abcdАБ |
5 | abcdАБВ |
Operacije u kojima se ne koriste indeksi i dužine niski rade ispravno bez obzira na sadržaj:
%sql values replace( 'abcdАБВГ', 'dА', 'ШЂ' )
1 | |
---|---|
0 | abcШЂБВГ |
%sql values ucase('abcABCшђжШЂЖšđžŠĐŽ'), lcase('abcABCшђжШЂЖšđžŠĐŽ')
1 | |
---|---|
0 | ABCABCШЂЖШЂЖŠĐŽŠĐŽ |
1 | abcabcшђжшђжšđžšđž |
Predikati IN
, BETWEEN
, LIKE
¶
U SQL-u predikatima nazivamo izraze koji izračunavaju logičku vrednost. Pored uobičajenih operatora poređenja i već predstavljenih predikata IS NULL
i IS NOT NULL
, imamo na raspolaganju još neke dodatne predikate:
<exp> [NOT] IN ( <list> )
- proverava da li vrednost datog izraza (ne) pripada datoj listi (skupu) elemenata;<exp> [NOT] BETWEEN <exp> AND <exp>
- proverava da li vrednost datog izraza (ne) pripada datom opsegu, koji obuhvata i granične vrednosti; izraz<exp1> BETWEEN <exp2> AND <exp3>
je ekvivalentan sa<exp2> <= <exp> AND <exp> <= <exp3>
;<exp> [NOT] LIKE <mask> [ESCAPE <esc>]
- proverava da li vrednost izraza odgovara šablonu koji je opisan maskom; maska može da sadrži obične karaktere i specijalne simbole%
(bilo koja podniska, uključujući i praznu) i_
(bilo koji znak).
%%sql
select * from dosije
where ime not in ('Milan','Ana')
INDEKS | IDPROGRAMA | IME | PREZIME | POL | MESTORODJENJA | IDSTATUSA | DATUPISA | DATDIPLOMIRANJA | |
---|---|---|---|---|---|---|---|---|---|
0 | 20150069 | 101 | Nemanja | Nicic | m | Sabac | -2 | 2015-07-06 | 2019-09-12 |
1 | 20150090 | 103 | Andrijana | Beara | z | Sabac | -2 | 2015-07-06 | 2019-09-06 |
2 | 20150320 | 103 | Milos | Milenkovic | m | Krusevac | 2 | 2015-07-06 | <NA> |
3 | 20150185 | 101 | Valentina | Stojanov | z | Beograd (Savski venac) | 2 | 2015-07-06 | <NA> |
4 | 20150230 | 103 | Aida | Babic | z | Cacak | 2 | 2015-07-06 | <NA> |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
3370 | 20170115 | 103 | Andrea | Novakovic | z | Beograd (Savski venac) | 2 | 2017-07-04 | <NA> |
3371 | 20180100 | 103 | Tara | Bozinovic | z | Beograd (Savski venac) | 1 | 2018-07-02 | <NA> |
3372 | 20190254 | 101 | Tamara | Zejak | z | Beograd (Savski venac) | 1 | 2019-07-02 | <NA> |
3373 | 20200213 | 103 | Uros | Stanojkov | m | Uzice | 1 | 2020-07-15 | <NA> |
3374 | 20151029 | 201 | Milos | Djokic | m | Valjevo | -5 | 2015-10-23 | <NA> |
3375 rows × 9 columns
%%sql
select * from dosije
where indeks between 20170100 and 20170105
INDEKS | IDPROGRAMA | IME | PREZIME | POL | MESTORODJENJA | IDSTATUSA | DATUPISA | DATDIPLOMIRANJA | |
---|---|---|---|---|---|---|---|---|---|
0 | 20170100 | 101 | Aleksandar | Laketic | m | Uzice | -5 | 2017-07-05 | <NA> |
1 | 20170101 | 101 | Gavrilo | Klima | m | Beograd (Savski venac) | 2 | 2017-07-03 | <NA> |
2 | 20170102 | 103 | Dusan | Ilic | m | Cacak | -5 | 2017-07-04 | <NA> |
3 | 20170103 | 101 | David | Raznatovic | m | Valjevo | -1 | 2017-10-24 | <NA> |
4 | 20170104 | 103 | Tamara | Rasic | z | Beograd (Savski venac) | 2 | 2017-07-04 | <NA> |
5 | 20170105 | 101 | Jovan | Vrecica | m | Beograd (Savski venac) | 2 | 2017-07-03 | <NA> |
%%sql
select ime from dosije
where ime like 'Gor%'
IME | |
---|---|
0 | Goran |
1 | Gordana |
2 | Goran |
3 | Goran |
4 | Gorana |
5 | Goran |
6 | Goran |
7 | Gorica |
8 | Goran |
%%sql
select distinct ime from dosije
where ime like '_ora%'
IME | |
---|---|
0 | Goran |
1 | Gorana |
2 | Zora |
3 | Zoran |
4 | Zorana |
Izraz CASE
¶
Predstavlja uslovni izraz sa više grana. U jednom obliku podseća na C-ovsku naredbu switch
ali sa izrazima:
CASE <exp0>
WHEN <exp1> THEN <exp1x>
[ WHEN <exp2> THEN <exp2x> ]*
[ ELSE <expE> ]
END
Najpre se izračunava prvi izraz <exp0>
. Zatim se poredi redom sa izrazima iza klauzula WHEN
(najpre sa <exp1>
pa sa <exp2>
i tako redom). Ako se pronađe jednak izraz, onda se kao rezultat vraća vrednost izraza iza THEN
koji odgovara tom izrazu. U suprotnom se vraća vrednost izraza iza ELSE
, a ako ne postoji grana ELSE
onda se vraća NULL
.
U drugom obliku liči na više puta ponovljeni uslovni izraz:
CASE
WHEN <cond1> THEN <exp1>
[ WHEN <cond2> THEN <exp2> ]*
[ ELSE <expE> ]
END
Najpre se izračunavaju uslovni izrazi navedeni iza klauzula WHEN
(najpre <cond1>
pa <cond2>
i tako redom). Ako neki od njih ima tačnu vrednost, onda se iza THEN
koji odgovara prvom tačnom uslovu. U suprotnom se vraća vrednost izraza iza ELSE
, a ako ne postoji grana ELSE
onda se vraća NULL
.
Obično se koristi za implementaciju jednostavnih diskretnih preslikavanja, ali može da ima i složenije namene, na primer primenu različitih algoritama izračunavanja u različitim slučajevima.
%%sql
-- Izlistati st.programe sa oznakom da li je program osnovni ili ne
select
id,
case idnivoa
when 1 then 'Osnovne'
else 'Poslediplomske'
end,
naziv
from studijskiprogram
ID | 2 | NAZIV | |
---|---|---|---|
0 | 101 | Osnovne | Matematika |
1 | 103 | Osnovne | Informatika |
2 | 104 | Osnovne | Astronomija i astrofizika |
3 | 201 | Poslediplomske | Matematika |
4 | 202 | Poslediplomske | Informatika |
5 | 203 | Poslediplomske | Informatika |
6 | 204 | Poslediplomske | Astronomija i astrofizika |
7 | 301 | Poslediplomske | Matematika |
8 | 302 | Poslediplomske | Informatika |
9 | 303 | Poslediplomske | Astronomija i astrofizika |
U narednom primeru računamo za sve studente dužinu studiranja. Ako je student diplomirao, onda je dužina studiranja broj dana od datuma upisa do datuma diplomiranja. Ako nije diplomirao, onda je dužina studiranja razlika od datuma upisa do današnjeg datuma:
%%sql
select
indeks,
days_between(
case when datdiplomiranja is null then current_date
else datdiplomiranja
end, datupisa )
from dosije
INDEKS | 2 | |
---|---|---|
0 | 20150069 | 1529 |
1 | 20150090 | 1523 |
2 | 20150320 | 3018 |
3 | 20150185 | 3018 |
4 | 20150230 | 3018 |
... | ... | ... |
3491 | 20180100 | 1926 |
3492 | 20190011 | 1562 |
3493 | 20190254 | 1561 |
3494 | 20200213 | 1182 |
3495 | 20151029 | 2909 |
3496 rows × 2 columns
To isto može da se izračuna primenom funkcije COALESCE
, koju ćemo uskoro detaljnije da upoznamo:
%%sql
select
indeks,
days_between( coalesce(datdiplomiranja,current_date), datupisa )
from dosije
INDEKS | 2 | |
---|---|---|
0 | 20150069 | 1529 |
1 | 20150090 | 1523 |
2 | 20150320 | 3018 |
3 | 20150185 | 3018 |
4 | 20150230 | 3018 |
... | ... | ... |
3491 | 20180100 | 1926 |
3492 | 20190011 | 1562 |
3493 | 20190254 | 1561 |
3494 | 20200213 | 1182 |
3495 | 20151029 | 2909 |
3496 rows × 2 columns
Brojač redova¶
U nekim slučajevima je potrebno da izdvojimo podatak o tome koji je neki podatak po redu u odnosu na neko uređenje. Naravno, jedan način je da uredimo rezultat upita i onda brojimo redove (ili izdvojimo segment sa LIMIT
i OFFSET
), ali nekada to nije dovoljno. Zbog toga postoji funkcija ROWNUMBER()
, koja omogućava da izračunamo redni broj reda u odnosu na izabrani kriterijum. Puna sintaksa ove funkcije je:
{ ROWNUMBER | ROW_NUMBER }() OVER (
[PARTITION BY <lista kolona>]
[ORDER BY <lista kolona ili izraza>]
)
Klauzula ORDER BY
određuje uređenje po kome se broji i ima isti oblik kao kada je koristimo za uređivanje rezultata upita.
Na primer, u narednom upitu ćemo da za svaki semestar evidentiran u bazi podataka izračunamo koji je on po redu:
%%sql -a
select
semestar.*,
rownumber() over (order by semestar, skgodina) as rbr
from semestar
-- order by skgodina, semestar
-- offset 5 rows
SKGODINA | SEMESTAR | RBR | |
---|---|---|---|
0 | 2015 | 1 | 1 |
1 | 2016 | 1 | 2 |
2 | 2017 | 1 | 3 |
3 | 2018 | 1 | 4 |
4 | 2019 | 1 | 5 |
5 | 2020 | 1 | 6 |
6 | 2015 | 2 | 7 |
7 | 2016 | 2 | 8 |
8 | 2017 | 2 | 9 |
9 | 2018 | 2 | 10 |
10 | 2019 | 2 | 11 |
11 | 2020 | 2 | 12 |
Opciona klauzula PARTITION BY
određuje da se brojanje ne odvija na celom skupu redova, nego posebno za svaki skup redova koji ima identične vrednosti kolona koje su navedene u toj klauzuli.
Na primer, u narednom upitu u koloni RBR1
izračunavamo za svakog studenta koji je on po redu diplomirao na MATF (u odnosu na ceo skup studenata, slično kao u prethodnom primeru), dok u koloni RBR2
izračunavamo koji je on po redu diplomirao na svom studijskom programu. Znači, kada se računa kolona RBR2
, prvo se svi studenti podele u grupe prema studijskom programu, pa se onda za svaku grupu posebno studenti urede i izračuna im se redni broj:
%%sql
select
indeks, ime, prezime, idprograma,
rownumber() over( order by datdiplomiranja ) as rbr1,
rownumber() over( partition by idprograma order by datdiplomiranja ) as rbr2
from dosije
where datdiplomiranja is not null
order by indeks
--order by datdiplomiranja
--order by idprograma, datdiplomiranja
INDEKS | IME | PREZIME | IDPROGRAMA | RBR1 | RBR2 | |
---|---|---|---|---|---|---|
0 | 20150006 | Nikola | Milojkovic | 101 | 184 | 13 |
1 | 20150011 | Ana | Pantovic | 103 | 177 | 10 |
2 | 20150015 | Milica | Stefanovic | 101 | 328 | 47 |
3 | 20150016 | Andjela | Nakaradic | 101 | 327 | 46 |
4 | 20150018 | Miodrag | Kuburovic | 103 | 236 | 25 |
... | ... | ... | ... | ... | ... | ... |
400 | 20191028 | Jovan | Gligorijevic | 201 | 393 | 216 |
401 | 20191035 | Cvija | Sadikovic | 201 | 348 | 202 |
402 | 20191060 | Milica | Djuric | 201 | 290 | 189 |
403 | 20191081 | Nenad | Cvetkovic | 201 | 394 | 217 |
404 | 20191094 | Olivera | Lukac | 201 | 405 | 221 |
405 rows × 6 columns
Naredni upit za svaki semestar izračunava u kojoj je školskoj godini po redu bio, prema redosledu održavanja:
%%sql
select
semestar.*,
rownumber() over (partition by semestar order by skgodina) as rbr
from semestar
order by skgodina, semestar
limit 5,5
SKGODINA | SEMESTAR | RBR | |
---|---|---|---|
0 | 2017 | 2 | 3 |
1 | 2018 | 1 | 4 |
2 | 2018 | 2 | 4 |
3 | 2019 | 1 | 5 |
4 | 2019 | 2 | 5 |
Funkcije COALESCE
, NULLIF
¶
U programskim jezicima i alatima često nisu podržane nedefinisane vrednosti. U tim slučajevima je obično potrebno da se pri čitanju podataka iz baze podataka nedefinisana vrednost preslika u neku specijalnu vrednost, a pri pisanju podataka u bazu podataka da se specijalna vrednost preslika u nedefinisanu vrednost. Za to služe funkcije COALESCE
i NULLIF
.
Funkcija COALESCE( x1, ..., xn )
izračunava redom argumente i vraća prvi koji je definisan, ili NULL
ako su svi nedefinisani. Upotrebljava se da se nedefinisane vrednosti prevedu u neke specifične definisane vrednosti. (Retko se koristi sa više od dva argumenta.)
Funkcija NULLIF( x, y )
poredi x
i y
i vraća x
ako su vrednosti različite, a NULL
ako su iste. Upotrebljava se da se neke specifične vrednosti prevedu u nedefinisane vrednosti.
%%sql
-- Izračunati dužinu studiranja aktivnih studenata, od upisa do datuma diplomiranja ili današnjeg datuma.
select
indeks,
days( coalesce( datdiplomiranja, current_date )) - days(datupisa)
from dosije
where idstatusa > 0
order by 2 desc
INDEKS | 2 | |
---|---|---|
0 | 20150320 | 3018 |
1 | 20150185 | 3018 |
2 | 20150230 | 3018 |
3 | 20150177 | 3018 |
4 | 20150135 | 3018 |
... | ... | ... |
2219 | 20200112 | 1107 |
2220 | 20200015 | 1107 |
2221 | 20200022 | 1105 |
2222 | 20200011 | 1104 |
2223 | 20200239 | 1103 |
2224 rows × 2 columns
Na primer, ako je potrebno da se pročita neki broj iz baze podataka i upiše u celobrojnu promenljivu, onda to može da bude problem ako iz baze podataka može da se pročita nedefinisana vrednost, a ako naš programski jezik ne poznaje nedefinisane vrednosti. Onda, na primer, možemo da u programu nedefinisanu vrednosti zamenimo "specijalnom" vrednošću -1.
- Pri čitanju vrednosti iz baze podataka bismo koristili izraz:
coalesce( ..., -1 )
; - Pri upisivanju vrednosti u bazu podataka bismo koristili izraz:
nullif( ..., -1 )
.
Aliasi tabela¶
Kao što u klauzuli SELECT može da se uvede novo ime kolone, tako u klauzuli FROM može da se uvede novo ime tabele:
<tabela> [as] <ime>
. To je veoma korisno u slučajevima kada se koristi više tabela pa je neophodno da se one eksplicitno referišu u upitu.
%%sql
select d.ime, d.prezime
from dosije as d
limit 5
IME | PREZIME | |
---|---|---|
0 | Nemanja | Nicic |
1 | Andrijana | Beara |
2 | Milos | Milenkovic |
3 | Valentina | Stojanov |
4 | Aida | Babic |
Spajanje tabela¶
Do sada opisan oblik upita omogućava izdvajanje podataka iz jedne tabele. Ako želimo da jednim upitom izdvojimo podatke iz više tabela, onda je potrebno da te tabele spojimo u jednu, pa tek onda da izdvojimo podatke. Formalno, možemo da kažemo da u klauzuli FROM
uvek stoji jedna tabela, ali da ona može biti ili fizički prisutna u bazi podataka ili je izračunavamo spajanjem drugih tabela. Spajanje tabela može semantički da se posmatra kao prvi korak koji se izvodi pri izračunavanju rezultata upita (iako stvaran redosled operacija ne mora da prati taj koncept). Nije pogrešno ni reći da se u klauzuli FROM
navodi više tabela.
Postoji više vrsta spajanja:
- unutrašnje spajanje;
- spoljašnje spajanje i
- unakrsno spajanje.
Kod svakog spajanja važi sledeće:
- spajanje kao deo upita predstavlja izraz nad tabelama čiji je rezultat nova tabela;
- rezultat spajanja sadrži ("redom", mada to retko ima značaja) sve kolone prve tabele i sve kolone druge tabele;
- jedan red rezultata upita se gradi od (najviše) jednog reda prve tabele i (najviše) jednog reda druge tabele;
- ako jedna tabela ima N a druga M redova, onda rezultat spajanja može da ima najviše MxN redova
- osim u slučaju spoljašnjeg spajanja kada je jedna tabela prazna.
Spajanje više tabela se izvodi kao više spajanja po dve tabele. Spajanje je levo asocijativno, a mogu da se koriste i zagrade.
Unutrašnje spajanje¶
Unutrašnje spajanje predstavlja spajanje redova jedne tabele sa odgovarajućim redovima druge tabele prema datom uslovu spajanja. Unutrašnje spajanje izdvaja samo one redove svake od tabela za koje u drugoj tabeli postoji bar jedan odgovarajući red. Ako za neki red jedne tabele u drugoj ne postoji nijedan odgovarajući red, onda se podaci iz tog reda neće pojaviti u rezultatu. Naziv unutrašnje odgovara konceptu preseka skupova.
Sintaksa ima dva oblika:
<tab1> [INNER] JOIN <tab2> ON <uslov>
<tab1> [INNER] JOIN <tab2> USING ( <lista kolona> )
U prvom obliku se spajaju parovi redova koji zadovoljavaju uslov naveden iza ON
.
U drugom obliku se spajaju parovi redova koji imaju jednake vrednosti svih kolona navedenih u listi. Važi:
... USING (kol1,... koln)
==
... ON tab1.kol1 = tab2.kol1 AND ... AND tab1.koln = tab2.koln
Ako unutrašnje spajanje podrazumeva i neku vrstu odabira redova jedne od tabele, onda taj uslov izbora može da se navede bilo u okviru uslova spajanja bilo u klauzuli WHERE
. Uslov je dobro navoditi tamo gde će se lakše razumeti u kontekstu održavanja upita.
Naredni upit izdvaja spisak imena studenata sa nazivom studijskog programa na koji su upisani. Imena studenata se nalaze u tabeli Dosije
, a nazivi studijskih programa u tabeli StudijskiProgram
. Zbog toga je neophodno da koristimo obe tabele. Podatke o jednom studentu spajamo sa podacima o upravo onom studijskom programu na koji je upisan:
%%sql
select ime, prezime, sp.naziv
from dosije d
inner join studijskiprogram sp
on d.idprograma = sp.id
IME | PREZIME | NAZIV | |
---|---|---|---|
0 | Svetozar | Micic | Informatika |
1 | Nemanja | Nicic | Matematika |
2 | Andrijana | Beara | Informatika |
3 | Milos | Milenkovic | Informatika |
4 | Valentina | Stojanov | Matematika |
... | ... | ... | ... |
3491 | Andrea | Novakovic | Informatika |
3492 | Tara | Bozinovic | Informatika |
3493 | Ana | Ilic | Matematika |
3494 | Tamara | Zejak | Matematika |
3495 | Milos | Djokic | Matematika |
3496 rows × 3 columns
U narednom primeru ćemo da spojimo bez restrikcije i bez projekcije sve podatke iz tabela StudijskiProgram
i NivoKvalifikacije
. Povezaćemo svaki studijski program sa odgovarajućim nivoom kvalifikacije.
%%sql
select * from studijskiprogram
ID | OZNAKA | NAZIV | IDNIVOA | OBIMESPB | ZVANJE | OPIS | |
---|---|---|---|---|---|---|---|
0 | 101 | M | Matematika | 1 | 240 | Diplomirani matematicar | Studijski program Matematika, osnovne akademsk... |
1 | 103 | I | Informatika | 1 | 240 | Diplomirani informaticar | Studije na studijskom programu osnovnih akadem... |
2 | 104 | A | Astronomija i astrofizika | 1 | 240 | Diplomirani astronom | Studijski program Astronomija i astrofizika, o... |
3 | 201 | M2 | Matematika | 2 | 60 | Master matematicar | Studijski program Matematika, master akademske... |
4 | 202 | I2.2 | Informatika | 2 | 120 | Master informaticar | Studije na studijskom programu master akademsk... |
5 | 203 | I2.1 | Informatika | 2 | 60 | Master informaticar | Studije na studijskom programu master akademsk... |
6 | 204 | A2 | Astronomija i astrofizika | 2 | 60 | Master astronom | Studijski program Astronomija i astrofizika, m... |
7 | 301 | M3 | Matematika | 3 | 180 | Doktor nauka - matematicke nauke | Studijski program Matematika, doktorske akadem... |
8 | 302 | I3 | Informatika | 3 | 180 | Doktor nauka - racunarske nauke | Studije se sastoje iz 2 obavezna predmeta koji... |
9 | 303 | A3 | Astronomija i astrofizika | 3 | 180 | Doktor nauka - astronomske nauke | Studijski program Astronomija i astrofizika, d... |
%%sql
select * from nivokvalifikacije
ID | NAZIV | |
---|---|---|
0 | 1 | Osnovne akademske studije |
1 | 3 | Doktorske akademske studije |
2 | 2 | Master akademske studije |
%%sql
-- unutrašnje spajanje programa i nivoa ...
select *
from studijskiprogram sp
join nivokvalifikacije nk
on sp.idnivoa = nk.id
ID | OZNAKA | NAZIV | IDNIVOA | OBIMESPB | ZVANJE | OPIS | ID | NAZIV | |
---|---|---|---|---|---|---|---|---|---|
0 | 101 | M | Matematika | 1 | 240 | Diplomirani matematicar | Studijski program Matematika, osnovne akademsk... | 1 | Osnovne akademske studije |
1 | 103 | I | Informatika | 1 | 240 | Diplomirani informaticar | Studije na studijskom programu osnovnih akadem... | 1 | Osnovne akademske studije |
2 | 104 | A | Astronomija i astrofizika | 1 | 240 | Diplomirani astronom | Studijski program Astronomija i astrofizika, o... | 1 | Osnovne akademske studije |
3 | 201 | M2 | Matematika | 2 | 60 | Master matematicar | Studijski program Matematika, master akademske... | 2 | Master akademske studije |
4 | 202 | I2.2 | Informatika | 2 | 120 | Master informaticar | Studije na studijskom programu master akademsk... | 2 | Master akademske studije |
5 | 203 | I2.1 | Informatika | 2 | 60 | Master informaticar | Studije na studijskom programu master akademsk... | 2 | Master akademske studije |
6 | 204 | A2 | Astronomija i astrofizika | 2 | 60 | Master astronom | Studijski program Astronomija i astrofizika, m... | 2 | Master akademske studije |
7 | 301 | M3 | Matematika | 3 | 180 | Doktor nauka - matematicke nauke | Studijski program Matematika, doktorske akadem... | 3 | Doktorske akademske studije |
8 | 302 | I3 | Informatika | 3 | 180 | Doktor nauka - racunarske nauke | Studije se sastoje iz 2 obavezna predmeta koji... | 3 | Doktorske akademske studije |
9 | 303 | A3 | Astronomija i astrofizika | 3 | 180 | Doktor nauka - astronomske nauke | Studijski program Astronomija i astrofizika, d... | 3 | Doktorske akademske studije |
Sada sledi jedan malo ozbiljniji primer. Potrebno je da za konkretnog studenta sa brojem indeksa 20180054 izdvojimo indeks, ime i prezime, ali i nazive svih položenih obaveznih predmeta, broj osvojenih poena i ocenu. Podaci o polaganjima su u tabeli Ispit
, ime i prezime studenta o studentu su u tabeli Dosije
, podaci o obaveznim predmetima su u tabeli PredmetPrograma
, a naziv predmeta je u tabeli Predmet
. Potrebno je spojiti na odgovarajući način sve četiri tabele. To se radi postepeno i dodajemo jednu po jednu tabelu.
Vodite računa da sva spajanja budu potpuno tačno definisana. Ako neki kriterijum spajanja nije dovoljno precizan, dobiće se neispravan rezultat. U nekim slučajevima takav neispravan rezultat može da bude ogroman.
%%sql
-- za studenta 20180054 izdvojiti podatke o položenim obaveznim predmetima
select
d.indeks, d.ime, d.prezime,
p.naziv,
i.poeni, i.ocena
from dosije d
join predmetprograma pp
using (idprograma)
join predmet p
on p.id = pp.idpredmeta
join ispit i
on d.indeks = i.indeks
and p.id = i.idpredmeta
and i.status = 'o'
and i.ocena > 5
where d.indeks = 20180054
and pp.vrsta = 'obavezan'
INDEKS | IME | PREZIME | NAZIV | POENI | OCENA | |
---|---|---|---|---|---|---|
0 | 20180054 | Milos | Djurisic | Programiranje 1 | 99 | 10 |
1 | 20180054 | Milos | Djurisic | Diskretne strukture 1 | 100 | 10 |
2 | 20180054 | Milos | Djurisic | Programiranje 2 | 98 | 10 |
3 | 20180054 | Milos | Djurisic | Diskretne strukture 2 | 93 | 10 |
4 | 20180054 | Milos | Djurisic | Geometrija | 88 | 9 |
... | ... | ... | ... | ... | ... | ... |
12 | 20180054 | Milos | Djurisic | Analiza 1 | 100 | 10 |
13 | 20180054 | Milos | Djurisic | Algoritmi i strukture podataka | 99 | 10 |
14 | 20180054 | Milos | Djurisic | Uvod u veb i internet tehnologije | 100 | 10 |
15 | 20180054 | Milos | Djurisic | Algebra 1 | 100 | 10 |
16 | 20180054 | Milos | Djurisic | Objektno-orijentisano programiranje | 100 | 10 |
17 rows × 6 columns
Spoljašnje spajanje¶
Spoljašnje spajanje predstavlja proširenje unutrašnjeg spajanja koje obezbeđuje da se svaki red tabele pojavi bar jednom u rezultatu upita. Ako za neki red jedne tabele ne postoji nijedan odgovarajući red druge tabele, onda se pravi red rezultujuće tabele koji sadrži prepisan taj red i prazan red druge tabele, tj. sve vrednosti u redu rezultatu koje odgovaraju drugoj tabeli će biti nedefinisane. Dok je unutrašnje spajanje odgovaralo konceptu preseka skupova, možemo da kažemo da spoljašnje spajanje odgovara konceptu unije skupova.
Imamo tri vrste spoljašnjeg spajanja:
- levo spoljašnje spajanje - samo redovi prve (leve) tabele mogu da se spajaju sa praznim redovima;
- desno spoljašnje spajanje - samo redovi druge (desne) tabele mogu da se spajaju sa praznim redovima i
- puno spoljašnje spajanje - redovi obeju tabela mogu da se spajaju sa praznim redovima.
Sintaksa spoljašnjeg spajanja je:
<tab1> {LEFT | RIGHT | FULL} [OUTER] JOIN <tab2> ON <uslov>
<tab1> {LEFT | RIGHT | FULL} [OUTER] JOIN <tab2> USING ( <lista kolona> )
Napisaćemo sličan upit kao malopre, ali sada želimo da izdvojimo i podatke o nepoloženim obaveznim predmetima, uključujući i one koji nisu ni polagani pa čak ni prijavljeni. Koristimo iste tabele kao u prethodnom primeru, ali ih drugačije spajamo. Najpre pravimo unutrašnje spajanje tabela Dosije
, Predmet
i PredmetPrograma
, tako da dobijamo spisak svih obaveznih predmeta za našeg studenta. Zatim pravimo levo spoljašnje spajanje takvog međurezultata sa tabelom Ispit
, da bismo svaki obavezan predmet povezali sa njegovim polaganjem, ako je položen, ili sa praznim null redom, ako nije položen:
%%sql
-- za studenta 20180054 izdvojiti podatke o položenim i nepoloženim obaveznim predmetima
select
d.indeks, d.ime, d.prezime,
p.naziv,
i.poeni, i.ocena
from dosije d
join predmetprograma pp
using (idprograma)
join predmet p
on p.id = pp.idpredmeta
left join ispit i
on d.indeks = i.indeks
and p.id = i.idpredmeta
and i.status = 'o'
and i.ocena > 5
where d.indeks = 20180054
and pp.vrsta = 'obavezan'
INDEKS | IME | PREZIME | NAZIV | POENI | OCENA | |
---|---|---|---|---|---|---|
0 | 20180054 | Milos | Djurisic | Programiranje 1 | 99 | 10 |
1 | 20180054 | Milos | Djurisic | Diskretne strukture 1 | 100 | 10 |
2 | 20180054 | Milos | Djurisic | Programiranje 2 | 98 | 10 |
3 | 20180054 | Milos | Djurisic | Diskretne strukture 2 | 93 | 10 |
4 | 20180054 | Milos | Djurisic | Geometrija | 88 | 9 |
... | ... | ... | ... | ... | ... | ... |
24 | 20180054 | Milos | Djurisic | Istrazivanje podataka 1 | <NA> | <NA> |
25 | 20180054 | Milos | Djurisic | Statistika | <NA> | <NA> |
26 | 20180054 | Milos | Djurisic | Racunarske mreze | <NA> | <NA> |
27 | 20180054 | Milos | Djurisic | Projektovanje baza podataka | <NA> | <NA> |
28 | 20180054 | Milos | Djurisic | Objektno-orijentisano programiranje | 100 | 10 |
29 rows × 6 columns
"Spajanje" razlike¶
Rekli smo da unutrašnje spajanje konceptualno odgovara preseku, a spoljašnje uniji skupova. Ako bismo hteli da izdvojimo samo razliku skupova, onda se to ponekad naziva "spajanje" razlike, iako se zapravo ne radi o spajanju, već o izdvajanju onih redova jedne tabele za koje ne postoje odgovarajući redovi druge tabele. To možemo da ostvarimo tako što napravimo spoljašnje spajanje, a kao uslov restrikcije stavimo da su ključni atributi desne tabele nedefinisani.
Drugi način za opisivanje "spajanja razlike" je upotreba podupita sa predikatom NOT EXISTS
, što ćemo obraditi uskoro.
%%sql
-- za studenta 20180054 izdvojiti podatke o nepoloženim obaveznim predmetima
select
d.indeks, d.ime, d.prezime,
p.naziv,
i.poeni, i.ocena
from dosije d
join predmetprograma pp
using (idprograma)
join predmet p
on p.id = pp.idpredmeta
left join ispit i
on d.indeks = i.indeks
and p.id = i.idpredmeta
and i.status = 'o'
and i.ocena > 5
where d.indeks = 20180054
and pp.vrsta = 'obavezan'
and i.indeks is null
INDEKS | IME | PREZIME | NAZIV | POENI | OCENA | |
---|---|---|---|---|---|---|
0 | 20180054 | Milos | Djurisic | Relacione baze podataka | <NA> | <NA> |
1 | 20180054 | Milos | Djurisic | Prevodjenje programskih jezika | <NA> | <NA> |
2 | 20180054 | Milos | Djurisic | Vestacka inteligencija | <NA> | <NA> |
3 | 20180054 | Milos | Djurisic | Uvod u numericku matematiku | <NA> | <NA> |
4 | 20180054 | Milos | Djurisic | Programske paradigme | <NA> | <NA> |
... | ... | ... | ... | ... | ... | ... |
7 | 20180054 | Milos | Djurisic | Verovatnoca | <NA> | <NA> |
8 | 20180054 | Milos | Djurisic | Istrazivanje podataka 1 | <NA> | <NA> |
9 | 20180054 | Milos | Djurisic | Statistika | <NA> | <NA> |
10 | 20180054 | Milos | Djurisic | Racunarske mreze | <NA> | <NA> |
11 | 20180054 | Milos | Djurisic | Projektovanje baza podataka | <NA> | <NA> |
12 rows × 6 columns
Unakrsno spajanje¶
Unakrsno spajanje odgovara Dekartovom proizvodu tabela - svaki red jedne tabele se spaja sa svakim redom druge tabele. Odgovara unutrašnjem spajanju za koje je uslov spajanja uvek tačan. Ako jedna tabela ima N a druga M redova, onda rezultat unakrsnog spajanja ima tačno MxN redova.
Sintaksa unakrsnog spajanja je: <tab1> CROSS JOIN <tab2>
%%sql
select *
from (values 1, 2, 3)
cross join (values 'a', 'b')
1 | 2 | |
---|---|---|
0 | 1 | a |
1 | 2 | a |
2 | 3 | a |
3 | 1 | b |
4 | 2 | b |
5 | 3 | b |
Implicitno spajanje - SELECT... FROM... WHERE...
¶
Stara sintaksa SQL-a nije imala operatore spajanja već se spajanje izvodilo implicitno, korišćenjem klauzula FROM
i WHERE
. Klauzula FROM
može da sadrži više tabela razdvojenih zapetama. Semantika takvog navođenja tabela je ekvivalentna unakrsnom spajanju svih navedenih tabela. Ako se restrikcija navedena u klauzuli WHERE
(ili deo uslova restrikcije) posmatra kao uslov spajanja, onda se kombinovanjem tog uslova i unakrsnog spajanja dobija unutrašnje spajanje sa odgovarajućim uslovom spajanja.
Spoljašnje spajanje nije moguće opisati implicitno na ovaj način, mada može da se opiše kao unija unutrašnjeg spajanja i odgovarajućih delova dobijenih bez spajanja.
VAŽNO: Ne preporučuje se upotreba implicitne sintakse spajanja! Prvi problem se odnosi na nejasno zapisivanje uslova restrikcije, u kome u nekim složenim okolnostima ne može lako da se ustanovi šta je uslov kog spajanja a šta restrikcija, pa se otežava i pisanje i održavanje upita. Drugi problem je posledica prvog, a to je da se daleko lakše dešava da uslov spajanja potpuno izostane ili da ne bude potpun, što dovodi do neispravnosti upita koja se često manifestuje eksplozijom broja redova i nepotrebnim opterećivanjem sistema.
%%sql
-- unutrašnje spajanje programa i nivoa ...
select *
from studijskiprogram sp,
nivokvalifikacije nk
where idnivoa = nk.id
ID | OZNAKA | NAZIV | IDNIVOA | OBIMESPB | ZVANJE | OPIS | ID | NAZIV | |
---|---|---|---|---|---|---|---|---|---|
0 | 101 | M | Matematika | 1 | 240 | Diplomirani matematicar | Studijski program Matematika, osnovne akademsk... | 1 | Osnovne akademske studije |
1 | 103 | I | Informatika | 1 | 240 | Diplomirani informaticar | Studije na studijskom programu osnovnih akadem... | 1 | Osnovne akademske studije |
2 | 104 | A | Astronomija i astrofizika | 1 | 240 | Diplomirani astronom | Studijski program Astronomija i astrofizika, o... | 1 | Osnovne akademske studije |
3 | 201 | M2 | Matematika | 2 | 60 | Master matematicar | Studijski program Matematika, master akademske... | 2 | Master akademske studije |
4 | 202 | I2.2 | Informatika | 2 | 120 | Master informaticar | Studije na studijskom programu master akademsk... | 2 | Master akademske studije |
5 | 203 | I2.1 | Informatika | 2 | 60 | Master informaticar | Studije na studijskom programu master akademsk... | 2 | Master akademske studije |
6 | 204 | A2 | Astronomija i astrofizika | 2 | 60 | Master astronom | Studijski program Astronomija i astrofizika, m... | 2 | Master akademske studije |
7 | 301 | M3 | Matematika | 3 | 180 | Doktor nauka - matematicke nauke | Studijski program Matematika, doktorske akadem... | 3 | Doktorske akademske studije |
8 | 302 | I3 | Informatika | 3 | 180 | Doktor nauka - racunarske nauke | Studije se sastoje iz 2 obavezna predmeta koji... | 3 | Doktorske akademske studije |
9 | 303 | A3 | Astronomija i astrofizika | 3 | 180 | Doktor nauka - astronomske nauke | Studijski program Astronomija i astrofizika, d... | 3 | Doktorske akademske studije |
Podsetimo se, odgovarajući upit sa primenom sintakse unutrašnjeg spajanja izgleda ovako:
%%sql
-- unutrašnje spajanje programa i nivoa ...
select *
from studijskiprogram sp
join nivokvalifikacije nk
on idnivoa = nk.id
ID | OZNAKA | NAZIV | IDNIVOA | OBIMESPB | ZVANJE | OPIS | ID | NAZIV | |
---|---|---|---|---|---|---|---|---|---|
0 | 101 | M | Matematika | 1 | 240 | Diplomirani matematicar | Studijski program Matematika, osnovne akademsk... | 1 | Osnovne akademske studije |
1 | 103 | I | Informatika | 1 | 240 | Diplomirani informaticar | Studije na studijskom programu osnovnih akadem... | 1 | Osnovne akademske studije |
2 | 104 | A | Astronomija i astrofizika | 1 | 240 | Diplomirani astronom | Studijski program Astronomija i astrofizika, o... | 1 | Osnovne akademske studije |
3 | 201 | M2 | Matematika | 2 | 60 | Master matematicar | Studijski program Matematika, master akademske... | 2 | Master akademske studije |
4 | 202 | I2.2 | Informatika | 2 | 120 | Master informaticar | Studije na studijskom programu master akademsk... | 2 | Master akademske studije |
5 | 203 | I2.1 | Informatika | 2 | 60 | Master informaticar | Studije na studijskom programu master akademsk... | 2 | Master akademske studije |
6 | 204 | A2 | Astronomija i astrofizika | 2 | 60 | Master astronom | Studijski program Astronomija i astrofizika, m... | 2 | Master akademske studije |
7 | 301 | M3 | Matematika | 3 | 180 | Doktor nauka - matematicke nauke | Studijski program Matematika, doktorske akadem... | 3 | Doktorske akademske studije |
8 | 302 | I3 | Informatika | 3 | 180 | Doktor nauka - racunarske nauke | Studije se sastoje iz 2 obavezna predmeta koji... | 3 | Doktorske akademske studije |
9 | 303 | A3 | Astronomija i astrofizika | 3 | 180 | Doktor nauka - astronomske nauke | Studijski program Astronomija i astrofizika, d... | 3 | Doktorske akademske studije |
Podupiti¶
Podupit je svaki izraz koji predstavlja upit, a koristi se kao manji deo kompletnog upita. U SQL-u postoje tri vrste podupita prema složenosti rezultata koji izračunavaju:
- tabelarni podupit je najopštiji, to je svaki podupit koji izračunava neku tabelu;
- vektorski podupit je spec. slučaj tabelarnog, koji izračunava tačno jednu kolonu i
- skalarni podupit je spec. slučaj vektorskog, izračunava tačno jednu vrednost (t.j. tabelu sa jednom kolonom i jednim redom).
Skalarni podupiti¶
Skalarni podupiti mogu da se koriste na svim mestima gde se koriste skalari. Navode se između zagrada.
U narednom primeru izdvajamo spisak studenata koji se zovu isto kao student sa indeksom 20170115. Podupitom ćemo da izračunamo ime studenta sa indeksom 20170115, da bismo zatim u glavnom delu upita izdvojili sve studente koji se zovu isto kao taj:
%%sql
select *
from dosije
where ime = (select ime from dosije where indeks=20170121)
INDEKS | IDPROGRAMA | IME | PREZIME | POL | MESTORODJENJA | IDSTATUSA | DATUPISA | DATDIPLOMIRANJA | |
---|---|---|---|---|---|---|---|---|---|
0 | 20150320 | 103 | Milos | Milenkovic | m | Krusevac | 2 | 2015-07-06 | <NA> |
1 | 20150357 | 101 | Milos | Simic | m | Beograd (Savski venac) | -2 | 2015-07-06 | 2020-06-30 |
2 | 20150204 | 101 | Milos | Radenovic | m | Loznica | 1 | 2015-07-06 | <NA> |
3 | 20150034 | 101 | Milos | Mijalkovic | m | Beograd (Savski venac) | -5 | 2015-07-07 | <NA> |
4 | 20151101 | 201 | Milos | Mladenovic | m | Uzice | -2 | 2015-10-22 | 2016-10-31 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
70 | 20200298 | 103 | Milos | Radovic | m | Beograd (Savski venac) | 2 | 2020-07-16 | <NA> |
71 | 20150398 | 103 | Milos | Jovanovic | m | Pirot | 2 | 2015-07-06 | <NA> |
72 | 20150103 | 103 | Milos | Radojevic | m | Beograd (Savski venac) | -5 | 2015-10-20 | <NA> |
73 | 20160056 | 101 | Milos | Knezevic | m | Cacak | -5 | 2016-07-06 | <NA> |
74 | 20151029 | 201 | Milos | Djokic | m | Valjevo | -5 | 2015-10-23 | <NA> |
75 rows × 9 columns
Vektorski podupiti¶
Vektorski podupiti se uobičajeno koriste sa operatorima IN
, ANY
, SOME
i ALL
.
Operator
IN
proverava da li data vrednost postoji u datom skupu:<exp> IN (<exp>)
. Negacija ovog izraza može da se zapiše i kao<exp> NOT IN (<exp>)
.Operator
ANY
(i njegov sinonimSOME
) proverava da li se data vrednost ispravno poredi datim operatorom poređenja sa bar jednom vrednošću u skupu:<exp> <oper> ANY (<exp>)
. Ako je operator poređenja=
, onda je to isto kao primena operatoraIN
, a ako je<>
onda je isto kaoNOT IN
.Operator
ALL
proverava da li se data vrednost ispravno poredi datim operatorom poređenja sa svim vrednostima u skupu:<exp> <oper> ALL (<exp>)
.
Uobičajeno je da se u svim ovim slučajevima skupovi vrednosti izračunavaju vektorskim podupitima:
U narednom primeru ćemo da izdvojimo osnovne podatke (indeks, ime i prezime) studenata koji su položili ispit iz predmeta 2348. Podupitom izračunavamo skup brojeva indeksa studenata koji su položili ispit iz tog predmeta. Zatim izdvajamo podatke o svim studentima čiji broj indeksa je u tom skupu:
%%sql
select indeks, ime, prezime
from dosije
where indeks in (
-- podupitom računamo spisak brojeva indeksa studenata koji su položili ispit
select indeks
from ispit
where idpredmeta = 2348
and status = 'o'
and ocena > 5
)
INDEKS | IME | PREZIME | |
---|---|---|---|
0 | 20150112 | Slobodan | Mijatovic |
1 | 20150128 | David | Jovovic |
2 | 20180038 | Bogdan | Lipovac |
3 | 20150075 | Mirko | Radojkovic |
4 | 20170205 | Nikola | Erakovic |
U narednom primeru ćemo da izdvojimo podatke o položenim ispitima studenta 20190087, takvih da iz njih nema najmanju ocenu. Za svaki konkretan ispit koji je taj student polagao, proveravamo koje su različite ocene dobijene, da bismo zatim proverili da li je ocena koju je dobio taj student veća od bar jedne od tih ocena:
%%sql
select *
from ispit i
where indeks = 20190087
and ocena > any (
select ocena
from ispit i2
where idpredmeta = i.idpredmeta
and ocena > 5
)
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2019 | jan1 | 20190087 | 2174 | o | 2020-01-14 | 88 | 9 |
1 | 2019 | jan2 | 20190087 | 1578 | o | 2020-01-31 | 72 | 8 |
2 | 2019 | jun1 | 20190087 | 2176 | o | 2020-06-24 | 89 | 9 |
Sada ćemo tražiti obrnuto, one ispite iz kojih student 20190087 ima najmanju ocenu. Ponovo ćemo za svaki konkretan ispit koji je taj student polagao podupitom izdvojiti sve različite ocene, ali ćemo zatim proveravati da li je ocena koju je dobio taj student manja ili jednaka od svih tih ocena:
%%sql
select *
from ispit i
where indeks = 20190087
and ocena >5
and ocena <= all (
select ocena
from ispit i2
where idpredmeta = i.idpredmeta
and ocena >5
)
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2019 | jan1 | 20190087 | 2170 | o | 2020-01-12 | 55 | 6 |
1 | 2019 | jun2 | 20190087 | 1590 | o | 2020-07-20 | 52 | 6 |
2 | 2019 | sep1 | 20190087 | 1580 | o | 2020-09-09 | 51 | 6 |
3 | 2019 | sep3 | 20190087 | 2171 | o | 2020-09-29 | 55 | 6 |
4 | 2019 | sep3 | 20190087 | 2172 | o | 2020-09-27 | 55 | 6 |
Korelisani i nekorelisani podupiti¶
Podupit je korelisan ako ne može da stoji kao samostalan upit, t.j. ako se u telu podupita na neki način referišu elementi šireg upita. Nasuprot tome, nekorelisan je ako može da stoji kao samostalan upit.
Na primer, pogledajmo sledeći nekorelisani podupit:
%%sql
-- imena studenata koji su polozili ispit iz predmeta 2348
select ime, prezime
from dosije
where indeks in (
select indeks
from ispit
where idpredmeta = 2348
and status = 'o'
and ocena > 5
)
IME | PREZIME | |
---|---|---|
0 | Slobodan | Mijatovic |
1 | David | Jovovic |
2 | Bogdan | Lipovac |
3 | Mirko | Radojkovic |
4 | Nikola | Erakovic |
Semantiku prethodnog upita možemo da opišemo kao da se prvo izračunava nekorelisani podupit, koji izdvaja sve brojeve indeksa studenata koji su položili ispit, a da zatim izdvajamo imena onih studenata čiji je indeks u tom skupu.
Isti rezultat se može dobiti ako se stvari postave na molo drugačiji način - umesto da prvo potražimo skup indeksa studenata koji su položili traženi predmet, možemo da redom za svakog studenta proveravamo da li je traženi predmet u skupu onih predmeta koje je položio:
%%sql
-- imena studenata koji su polozili ispit iz predmeta 2348
select ime, prezime
from dosije
where 2348 in (
-- skup predmeta koje je položio student dosije.indeks
select idpredmeta
from ispit
where indeks = dosije.indeks
and status = 'o'
and ocena > 5
)
IME | PREZIME | |
---|---|---|
0 | Slobodan | Mijatovic |
1 | David | Jovovic |
2 | Bogdan | Lipovac |
3 | Mirko | Radojkovic |
4 | Nikola | Erakovic |
Konceptualno posmatrano:
- nekorelisani upit se izračunava jedanput i zatim se njegov rezultat upotrebljava više puta;
- korelisani upit se za svakog studenta izračunava ponovo i njegov rezultat se upotrebljava po jedanput.
Tako posmatrano, nekorelisani upiti su potencijalno efikasniji od korelisanih. U praksi će optimizator upita često ovakve upite uspešno svoditi na isti oblik, pa neće dolaziti do razlike u efikasnosti, ali u nekim slučajevima to neće uspeti pa bi razlike u efikasnosti mogle da budu značajne. Zbog toga je dobro da koristimo nekorelisane upite kada god je to moguće.
Podupiti...¶
Nastavak o podupitima sledi na narednom času.