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:

In [2]:
%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>

In [3]:
%%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
Out[3]:
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.

In [4]:
%%sql
select *
from dosije
optimize for 5 rows
Out[4]:
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-bitni
    • INTEGER, INT - 32-bitni
    • BIGINT - 64-bitni

  • Razlomljeni tipovi

    • sa pokretnom zapetom

      • REAL - 32-bitni
      • DOUBLE - 64-bitni
      • DECFLOAT(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 255
      • VARCHAR(n) - niske promenljive dužine, n do 32672
      • CLOB(n) - velike niske, do 2GiB - 1

    • dvobajtne

      • GRAPHIC(n) - niske fiksne dužine, n u opsegu 1 do 127
      • VARGRAPHIC(n) - niske promenljive dužine, n do 16336
      • DBCLOB(n) - velike niske, do 1GiB - 1

    • binarne

      • BINARY(n) - niske bajtova fiksne dužine, n u opsegu 1 do 255
      • VARBINARY(n) - niske bajtova promenljive dužine, n do 32672
      • BLOB(n) - velike niske bajtova, do 2GiB - 1

  • Vremenski tipovi

    • TIME - vreme
    • DATE - datum
    • TIMESTAMP - datum i vreme

  • Logički tip

    • BOOLEAN - logički tip, ima vrednosti TRUE i FALSE

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> ):

In [5]:
%%sql
values cast( 1 as double )
Out[5]:
1
0 1.0
In [6]:
%%sql
values cast( 42.987 as int )
Out[6]:
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:

In [7]:
%%sql
values (
    int('42'),
    int('42.987'),
    double('20'),
    dec('42')
)
Out[7]:
1 2 3 4
0 42 42 20.0 42.0
In [8]:
%%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')) 
)
Out[8]:
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 ili INF - 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'...' ili N'...', 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.
In [9]:
%%sql 
values
    'Obicna niska',
    'Обична ниска може да садржи УТФ-8',
    X'404142616263',
    U&'Знак € може да се запише и овако: \20AC \000aovo je bio novi red, \000aa ovo su neke note \266B\266A'
Out[9]:
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):

In [10]:
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'.

In [11]:
%%sql
values
    -- bar jedan red mora da ima eksplicitno oznacen datum
    date '2020-10-15',
    '2020-10-15',
    '15.10.2020',
    '10/15/2020'
Out[11]:
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.

In [12]:
%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.
In [13]:
%%sql
values (
    'a' concat 'b',
    2 ** 3,
    19 % 4,
    19 | 6,
    5 > 3 and 2 < 7
)
Out[13]:
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.

In [14]:
%%sql
-- koji datum će biti za 1234 dana
values current_date + 1234 days
Out[14]:
1
0 2027-02-25
In [15]:
%%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
In [16]:
%sql values current date - date '2017-02-01'
Out[16]:
1
0 60809.0
In [17]:
%sql values now - date '2017-02-01'
Out[17]:
1
0 6.080920e+10
In [18]:
%sql values now - date '2017-02-01' - 60802000000
Out[18]:
1
0 7200235.825
In [19]:
%sql values current date - 4 years - 8 months - 12 days
Out[19]:
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 tipa DATE i TIMESTAMP;
  • AGE(date) - slično kao CURRENT_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 tipa TIME i TIMESTAMP;
  • 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.
In [20]:
%sql values( dayname(now), dayofmonth(now), dayofweek(now), dayofyear(now) )
Out[20]:
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:

In [21]:
%%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
Out[21]:
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 bajtovima
  • SUBSTR2(s,p,n) - isto u UTF formatu
  • STRLEFT, 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:

In [22]:
%%sql 
values 
    -- konkretna baza je napravljena kao UTF-8
    -- zato dužina nije konstantna po karakteru
    length('abcdАБВГ'),
    lengthb('abcdАБВГ'),
    length2('abcdАБВГ'),
    length4('abcdАБВГ')
Out[22]:
1
0 12
1 12
2 8
3 8
In [23]:
%%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)
Out[23]:
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:

In [24]:
%%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)
Out[24]:
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:

In [25]:
%sql values replace( 'abcdАБВГ', 'dА', 'ШЂ' )
Out[25]:
1
0 abcШЂБВГ
In [26]:
%sql values ucase('abcABCшђжШЂЖšđžŠĐŽ'), lcase('abcABCшђжШЂЖšđžŠĐŽ')
Out[26]:
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).

In [27]:
%%sql
select * from dosije
where ime not in ('Milan','Ana')
Out[27]:
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

In [28]:
%%sql
select * from dosije
where indeks between 20170100 and 20170105
Out[28]:
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>
In [29]:
%%sql
select ime from dosije
where ime like 'Gor%'
Out[29]:
IME
0 Goran
1 Gordana
2 Goran
3 Goran
4 Gorana
5 Goran
6 Goran
7 Gorica
8 Goran
In [30]:
%%sql
select distinct ime from dosije
where ime like '_ora%'
Out[30]:
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.

In [31]:
%%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
Out[31]:
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:

In [32]:
%%sql
select 
    indeks,
    days_between(
        case when datdiplomiranja is null then current_date
            else datdiplomiranja
        end, datupisa )
from dosije
Out[32]:
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:

In [33]:
%%sql
select 
    indeks,
    days_between( coalesce(datdiplomiranja,current_date), datupisa )
from dosije
Out[33]:
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:

In [34]:
%%sql -a
select
    semestar.*,
    rownumber() over (order by semestar, skgodina) as rbr
from semestar
-- order by skgodina, semestar
-- offset 5 rows
Out[34]:
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:

In [35]:
%%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
Out[35]:
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:

In [36]:
%%sql
select
    semestar.*,
    rownumber() over (partition by semestar order by skgodina) as rbr
from semestar
order by skgodina, semestar
limit 5,5
Out[36]:
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.

In [37]:
%%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
Out[37]:
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.

In [38]:
%%sql
select d.ime, d.prezime
from dosije as d
limit 5
Out[38]:
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:

In [39]:
%%sql
select ime, prezime, sp.naziv
from dosije d
    inner join studijskiprogram sp
        on d.idprograma = sp.id
Out[39]:
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.

In [40]:
%%sql 
select * from studijskiprogram
Out[40]:
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...
In [41]:
%%sql 
select * from nivokvalifikacije
Out[41]:
ID NAZIV
0 1 Osnovne akademske studije
1 3 Doktorske akademske studije
2 2 Master akademske studije
In [42]:
%%sql
-- unutrašnje spajanje programa i nivoa ...
select *
from studijskiprogram sp
    join nivokvalifikacije nk
        on sp.idnivoa = nk.id
Out[42]:
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.

In [43]:
%%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'
Out[43]:
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:

In [44]:
%%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'
Out[44]:
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.

In [45]:
%%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
Out[45]:
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>

In [46]:
%%sql
select *
from (values 1, 2, 3)
        cross join (values 'a', 'b')
Out[46]:
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.

In [47]:
%%sql
-- unutrašnje spajanje programa i nivoa ...
select *
from studijskiprogram sp,
     nivokvalifikacije nk
where idnivoa = nk.id
Out[47]:
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:

In [54]:
%%sql
-- unutrašnje spajanje programa i nivoa ...
select *
from studijskiprogram sp
        join nivokvalifikacije nk
            on idnivoa = nk.id
Out[54]:
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:

In [55]:
%%sql
select * 
from dosije
where ime = (select ime from dosije where indeks=20170121)
Out[55]:
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 sinonim SOME) 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 operatora IN, a ako je <> onda je isto kao NOT 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:

In [56]:
%%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
)
Out[56]:
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:

In [57]:
%%sql
select *
from ispit i
where indeks = 20190087
  and ocena > any (
    select ocena
    from ispit i2
    where idpredmeta = i.idpredmeta
      and ocena > 5
)
Out[57]:
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:

In [58]:
%%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
)
Out[58]:
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:

In [59]:
%%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
)
Out[59]:
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:

In [60]:
%%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
)
Out[60]:
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.