Uvod u relacione baze podataka¶
prof. dr Saša Malkov
Univerzitet u Beogradu - Matematički fakultet
2023/24
Čas 1 - 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.
Tabele¶
Osnovni pojam u relacionim bazama podataka su relacije ili tabele. O terminologiji i formalnom definisanju ovih pojmova će biti više reči kasnije. Na nivou implementacije i upotrebe baze podataka obično se upotrebljava termin tabela.
Tabela ima redove i kolone. Svaka kolona ima svoje ime i tip.
Svaka trajna tabela u bazi podataka ima svoje ime. Radi lakše logičke organizacije tabele se grupišu u tzv. sheme i referišu se u obliku <ime sheme>.<ime tabele>
.
Ako tabelu referišemo bez navođenja imena sheme, podrazumeva se ime sheme koje je identično korisničkom imenu trenutno priavljenog korisnika. Ako želimo da podrazumevano ime sheme bude neko drugo, onda koristimo naredbu sistema DB2 SET SCHEMA
:
%sql set schema da
Command completed.
Komentari¶
Komentari u SQL-u se zapisuju kao linijski komentari. Protežu se od simbola --
do kraja reda. Ista sintaksa važi i u okviru naredbi sistema DB2:
%sql set schema da --ovo je komentar
Command completed.
Upiti¶
Naredbe za čitanje (često kažemo izdvajanje) podataka iz postojećeg sadržaja baze podataka nazivamo upitnim naredbama. One čitaju podatke iz postojećih tabela baze podataka i daju rezultat u obliku privremene tabele. Može se reći da upitne naredbe izračunavaju nove tabele na osnovu postojećih.
Potrebno je da pravimo razliku između upita i upitne naredbe.
Upitna naredba je naredba SUBP.
Upit je izraz koji čini glavni deo upitne naredbe. Podupit je nešto manje uopšten upitni izraz, koji se može koristiti kao deo većeg upita ili čak većeg podupita. Danas je razlika između upita i podupita relativno mala:
- u podupitima često ne mogu da se koriste skupovne operacije;
- neke stvari, poput uređivanja, nema smisla navoditi u podupitima;
- većina upita, ako se navedu u okviru zagrada, mogu da se iskoriste kao podupiti.
Upiti se prema složenosti rezultata dele na tri vrste:
- Tabelarni upit izračunava tabelu - to je najopštija vrsta upita;
- Vektorski upit izračunava jednu kolonu i
- Skalarni upit izračunava jednu vrednost.
Osnovna upitna naredba je naredba SELECT
. Pre naredbe SELECT
upoznaćemo naredbu VALUES
koja služi za zapisivanje konstantnih tabela ili literala. Kasnije ćemo upoznati i naredbu WITH
za zapisivanje složenijih upita, kao i druge složenije oblike upita.
VALUES¶
Naredba VALUES
služi za navođenje konstantnih tabela:
VALUES <red_1>, <red_2>,... <red_n>
Svaki red se navodi kao lista vrednosti (kolona) unutar zagrada:
( <kol_1>, <kol_2>,... <kol_m> )
Odgovarajuće kolone u svim redovima moraju biti saglasnog tipa.
Ako red ima samo jednu kolonu, onda može da se navodi bez zagrada.
%%sql
values
(1, 'a', 15.7),
(2, 'b', 20)
1 | 2 | 3 | |
---|---|---|---|
0 | 1 | a | 15.7 |
1 | 2 | b | 20.0 |
Odgovarajuće kolone u svim redovima moraju biti saglasnog tipa ili će doći do greške:
%%sql
values
(1, 'a', 15.7),
(2, 'b', 'abc')
SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018 SQLCODE=-420
Ako red ima samo jednu kolonu, onda može da se navodi bez zagrada:
%%sql
values 1, 2, 3, 4, 5
1 | |
---|---|
0 | 1 |
1 | 2 |
2 | 3 |
3 | 4 |
4 | 5 |
SELECT¶
Puna sintaksa naredbe SELECT
je relativno složena, pa ćemo je zato upoznavati postepeno. Pun oblik ćemo rezimirati kada upoznamo sve elemente naredbe. Obično kažemo da osnovni oblik naredbe SELECT
čine tri komponente:
SELECT <lista imena kolona>
FROM <tabela>
[WHERE <uslov>]
Naredba SELECT
čita tabelu navedenu u klauzuli FROM
, odabire samo redove koji zadovoljavaju dati uslov i iz njih izdvaja samo izabrane kolone.
Biranje kolona nazivamo projekcijom:
%%sql
select SkGodina, Datpocetka
from SkolskaGodina
SKGODINA | DATPOCETKA | |
---|---|---|
0 | 2015 | 2015-10-01 |
1 | 2016 | 2016-10-01 |
2 | 2017 | 2017-10-01 |
3 | 2018 | 2018-10-01 |
4 | 2019 | 2019-10-01 |
5 | 2020 | 2020-10-01 |
Ako se umesto liste imena kolona navede samo *
onda se izdvajaju sve kolone date tabele:
%%sql
select *
from SkolskaGodina
SKGODINA | DATPOCETKA | DATKRAJA | |
---|---|---|---|
0 | 2015 | 2015-10-01 | 2016-09-30 |
1 | 2016 | 2016-10-01 | 2017-09-30 |
2 | 2017 | 2017-10-01 | 2018-09-30 |
3 | 2018 | 2018-10-01 | 2019-09-30 |
4 | 2019 | 2019-10-01 | 2020-09-30 |
5 | 2020 | 2020-10-01 | 2021-09-30 |
Kasnije ćemo videti da tabela iz koje se čita može da se izračunava iz više drugih tabela. Ako ne želimo da izdvojimo sve kolone svih obuhvaćenih tabela, ali želimo sve kolone neke od njih, onda umesto *
navodimo <ime tabele>.*
. U narednom primeru to ne pravi razliku, ali ipak navodimo radi ilustracije:
%%sql
select SkolskaGodina.*
from SkolskaGodina
SKGODINA | DATPOCETKA | DATKRAJA | |
---|---|---|---|
0 | 2015 | 2015-10-01 | 2016-09-30 |
1 | 2016 | 2016-10-01 | 2017-09-30 |
2 | 2017 | 2017-10-01 | 2018-09-30 |
3 | 2018 | 2018-10-01 | 2019-09-30 |
4 | 2019 | 2019-10-01 | 2020-09-30 |
5 | 2020 | 2020-10-01 | 2021-09-30 |
Klauzula WHERE
je opciona. Ako je ne navedemo, onda će se izdvojiti svi redovi tabele. Ako je navedemo, onda će se izdvojiti samo redovi koji ispunjavaju dati uslov. To se naziva restrikcija:
%%sql
select *
from SkolskaGodina
where SkGodina = 2018
SKGODINA | DATPOCETKA | DATKRAJA | |
---|---|---|---|
0 | 2018 | 2018-10-01 | 2019-09-30 |
%%sql
select *
from SkolskaGodina
where SkGodina > 2018
SKGODINA | DATPOCETKA | DATKRAJA | |
---|---|---|---|
0 | 2019 | 2019-10-01 | 2020-09-30 |
1 | 2020 | 2020-10-01 | 2021-09-30 |
%%sql
select SkGodina, DatPocetka
from SkolskaGodina
where SkGodina = 2018
SKGODINA | DATPOCETKA | |
---|---|---|
0 | 2018 | 2018-10-01 |
VAŽNO: Izbegavajte postavljanje upita bez restrikcije. Na taj način se izdvajaju svi redovi tabele, što može da ima za posledicu nepotrebno zauzeće resursa.
%%sql
-- Izdvajanje imena i prezimena svih studenata rođenih u Prijepolju
select ime, prezime
from dosije
where mestorodjenja = 'Prijepolje'
IME | PREZIME | |
---|---|---|
0 | Natalija | Velickovic |
1 | Anes | Mandic |
2 | Mateja | Gluscevic |
3 | Mirna | Baranin |
4 | Milica | Pavela |
... | ... | ... |
37 | Stefan | Todorovic |
38 | Ivan | Kasapu |
39 | Milos | Vasiljevic |
40 | Milan | Panic |
41 | Kristijan | Prodan |
42 rows × 2 columns
Zadatak¶
Izdvojiti sve različite nazive predmeta koji počinju sa "Prog":
%%sql -a
-- Izdvajanje svih naziva predmeta koji počinju sa "Prog"
select naziv
from predmet
where naziv >= 'Prog'
and naziv < 'Proh'
NAZIV | |
---|---|
0 | Programiranje 1 |
1 | Programiranje 2 |
2 | Programiranje 1 |
3 | Programiranje 2 |
4 | Programski paketi u matematici |
5 | Programske paradigme |
6 | Programiranje baza podataka |
7 | Programske paradigme |
8 | Programiranje baza podataka |
9 | Programiranje ogranicenja |
10 | Programiranje za veb |
11 | Programiranje za veb |
12 | Programiranje Veb aplikacija koriscenjem Sprin... |
13 | Programski jezik Javaskript |
14 | Programski jezik JavaScript |
15 | Programiranje mreznog saobracaja |
Baza podataka Stud2020¶
Na predavanjima i na vežbama ćemo za sve primere upotrebljavati bazu podataka Stud2020
. To je pojednostavljen model baze podataka informacionog sistema fakulteta.
Podaci o bazi podataka¶
Sistem DB2 ima više naredbi koje mogu da nam pomognu da bolje upoznamo bazu podataka sa kojom radimo. Jedna od njih je naredba LIST TABLES
, koja izdvaja spisak tabela baze podataka:
%sql list tables
TABNAME | TABSCHEMA | DESCRIPTION | |
---|---|---|---|
0 | ADVISE_INDEX | SMALKOV | Table |
1 | ADVISE_INDEX | SYSTOOLS | Table |
2 | ADVISE_INSTANCE | SMALKOV | Table |
3 | ADVISE_INSTANCE | SYSTOOLS | Table |
4 | ADVISE_MQT | SMALKOV | Table |
5 | ADVISE_MQT | SYSTOOLS | Table |
6 | ADVISE_PARTITION | SMALKOV | Table |
7 | ADVISE_PARTITION | SYSTOOLS | Table |
8 | ADVISE_TABLE | SMALKOV | Table |
9 | ADVISE_TABLE | SYSTOOLS | Table |
10 | ADVISE_WORKLOAD | SMALKOV | Table |
11 | ADVISE_WORKLOAD | SYSTOOLS | Table |
12 | DOSIJE | DA | Table |
13 | DOSIJE | DB | Table |
14 | DOSIJEEXT | DA | Table |
15 | DOSIJEEXT | DB | Table |
16 | EXPLAIN_ACTUALS | SMALKOV | Table |
17 | EXPLAIN_ACTUALS | SYSTOOLS | Table |
18 | EXPLAIN_ARGUMENT | SMALKOV | Table |
19 | EXPLAIN_ARGUMENT | SYSTOOLS | Table |
20 | EXPLAIN_DIAGNOSTIC | SMALKOV | Table |
21 | EXPLAIN_DIAGNOSTIC | SYSTOOLS | Table |
22 | EXPLAIN_DIAGNOSTIC_DATA | SMALKOV | Table |
23 | EXPLAIN_DIAGNOSTIC_DATA | SYSTOOLS | Table |
24 | EXPLAIN_INSTANCE | SMALKOV | Table |
25 | EXPLAIN_INSTANCE | SYSTOOLS | Table |
26 | EXPLAIN_OBJECT | SMALKOV | Table |
27 | EXPLAIN_OBJECT | SYSTOOLS | Table |
28 | EXPLAIN_OPERATOR | SMALKOV | Table |
29 | EXPLAIN_OPERATOR | SYSTOOLS | Table |
30 | EXPLAIN_PREDICATE | SMALKOV | Table |
31 | EXPLAIN_PREDICATE | SYSTOOLS | Table |
32 | EXPLAIN_STATEMENT | SMALKOV | Table |
33 | EXPLAIN_STATEMENT | SYSTOOLS | Table |
34 | EXPLAIN_STREAM | SMALKOV | Table |
35 | EXPLAIN_STREAM | SYSTOOLS | Table |
36 | ISPIT | DA | Table |
37 | ISPIT | DB | Table |
38 | ISPITNIROK | DA | Table |
39 | ISPITNIROK | DB | Table |
40 | KURS | DA | Table |
41 | KURS | DB | Table |
42 | NIVOKVALIFIKACIJE | DA | Table |
43 | NIVOKVALIFIKACIJE | DB | Table |
44 | OBJECT_METRICS | SMALKOV | Table |
45 | OBJECT_METRICS | SYSTOOLS | Table |
46 | PREDMET | DA | Table |
47 | PREDMET | DB | Table |
48 | PREDMETPROGRAMA | DA | Table |
49 | PREDMETPROGRAMA | DB | Table |
50 | PRIZNATISPIT | DA | Table |
51 | PRIZNATISPIT | DB | Table |
52 | SEMESTAR | DA | Table |
53 | SEMESTAR | DB | Table |
54 | SKOLSKAGODINA | DA | Table |
55 | SKOLSKAGODINA | DB | Table |
56 | STUDENTSKISTATUS | DA | Table |
57 | STUDENTSKISTATUS | DB | Table |
58 | STUDIJSKIPROGRAM | DA | Table |
59 | STUDIJSKIPROGRAM | DB | Table |
60 | UPISANKURS | DA | Table |
61 | UPISANKURS | DB | Table |
62 | UPISGODINE | DA | Table |
63 | UPISGODINE | DB | Table |
64 | USLOVNIPREDMET | DA | Table |
65 | USLOVNIPREDMET | DB | Table |
Još jedna korisna naredba je naredba DESCRIBE
, koja opisuje kolone izabrane tabele ili čak kolone upita koji smo postavili. Opis obuhvata nazive kolona i njihove tipove:
%sql describe table dosije
COLNAME | TYPESCHEMA | TYPENAME | LENGTH | SCALE | NULLABLE | |
---|---|---|---|---|---|---|
0 | INDEKS | SYSIBM | INTEGER | 4 | 0 | N |
1 | IDPROGRAMA | SYSIBM | INTEGER | 4 | 0 | N |
2 | IME | SYSIBM | VARCHAR | 50 | 0 | N |
3 | PREZIME | SYSIBM | VARCHAR | 50 | 0 | N |
4 | POL | SYSIBM | CHARACTER | 1 | 0 | Y |
5 | MESTORODJENJA | SYSIBM | VARCHAR | 50 | 0 | Y |
6 | IDSTATUSA | SYSIBM | SMALLINT | 2 | 0 | N |
7 | DATUPISA | SYSIBM | DATE | 4 | 0 | N |
8 | DATDIPLOMIRANJA | SYSIBM | DATE | 4 | 0 | Y |
%sql describe select indeks, ime, prezime from dosije
SQLTYPE_ID | SQLTYPE | SQLLENGTH | SQLSCALE | SQLNAME_DATA | SQLNAME_LENGTH | SQLDATATYPE_NAME_DATA | SQLDATATYPE_NAME_LENGTH | |
---|---|---|---|---|---|---|---|---|
0 | 496 | INTEGER | 4 | 0 | INDEKS | 6 | 0 | |
1 | 448 | VARCHAR | 50 | 0 | IME | 3 | 0 | |
2 | 448 | VARCHAR | 50 | 0 | PREZIME | 7 | 0 |
Po standardu SQL-a svi podaci o bazi podataka moraju da budu sačuvani upravo u toj istoj bazi podataka. Štaviše, sve podatke o bazi podataka bi trebalo da možemo da pročitamo (ali ne i da izmenimo) upotrebom istih upitnih naredbi kojima čitamo i sadržaj naših podataka zapisanih u toj bazi podataka. To se ostvaruje tako što se u samoj bazi podataka prave tzv. sistemske tabele u kojima se nalaze podaci o organizaciji ali i implementaciji baze podataka.
U slučaju sistema DB2, ti podaci se nalaze zapisani u rezervisanim shemama SYSIBM
, SYSIBMADM
, SYSPUBLIC
, SYSCAT
, SYSSTAT
i SYSTOOLS
. Najkorisniji podaci su u shemi SYSCAT
, koja sadrži tzv. sistemski katalog:
%sql -a list tables for schema syscat
TABNAME | TABSCHEMA | DESCRIPTION | |
---|---|---|---|
0 | ATTRIBUTES | SYSCAT | View |
1 | AUDITPOLICIES | SYSCAT | View |
2 | AUDITUSE | SYSCAT | View |
3 | BUFFERPOOLDBPARTITIONS | SYSCAT | View |
4 | BUFFERPOOLEXCEPTIONS | SYSCAT | View |
... | ... | ... | ... |
150 | XSROBJECTCOMPONENTS | SYSCAT | View |
151 | XSROBJECTDEP | SYSCAT | View |
152 | XSROBJECTDETAILS | SYSCAT | View |
153 | XSROBJECTHIERARCHIES | SYSCAT | View |
154 | XSROBJECTS | SYSCAT | View |
155 rows × 3 columns
Na primer, tabela (zapravo je u pitanju pogled, ali o tome ćemo više kasnije) SYSCAT.TABLES
sadrži podatke o svim
tabelama, a SYSCAT.COLUMNS
podatke o svim kolonama u bazi podataka:
%%sql -a
select *
from syscat.tables
where tabschema = 'DA'
%%sql -a
select *
from syscat.columns
where tabschema = 'DA'
and tabname = 'DOSIJE'
TABSCHEMA | TABNAME | COLNAME | COLNO | TYPESCHEMA | TYPENAME | LENGTH | SCALE | TYPESTRINGUNITS | STRINGUNITSLENGTH | DEFAULT | NULLS | CODEPAGE | COLLATIONSCHEMA | COLLATIONNAME | LOGGED | COMPACT | COLCARD | HIGH2KEY | LOW2KEY | AVGCOLLEN | KEYSEQ | PARTKEYSEQ | NQUANTILES | NMOSTFREQ | NUMNULLS | TARGET_TYPESCHEMA | TARGET_TYPENAME | SCOPE_TABSCHEMA | SCOPE_TABNAME | SOURCE_TABSCHEMA | SOURCE_TABNAME | DL_FEATURES | SPECIAL_PROPS | HIDDEN | INLINE_LENGTH | PCTINLINED | IDENTITY | ROWCHANGETIMESTAMP | GENERATED | TEXT | COMPRESS | AVGDISTINCTPERPAGE | PAGEVARIANCERATIO | SUB_COUNT | SUB_DELIM_LENGTH | AVGCOLLENCHAR | IMPLICITVALUE | SECLABELNAME | ROWBEGIN | ROWEND | TRANSACTIONSTARTID | PCTENCODED | AVGENCODEDCOLLEN | QUALIFIER | FUNC_PATH | RANDDISTKEY | REMARKS | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | DA | DOSIJE | DATUPISA | 7 | SYSIBM | DATE | 4 | 0 | <NA> | <NA> | <NA> | N | 0 | <NA> | <NA> | 116 | '2020-10-01' | '2015-07-07' | 4 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | -1 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
1 | DA | DOSIJE | DATDIPLOMIRANJA | 8 | SYSIBM | DATE | 4 | 0 | <NA> | <NA> | <NA> | Y | 0 | <NA> | <NA> | 179 | '2020-09-30' | '2016-06-23' | 5 | <NA> | 0 | 20 | 10 | 3091 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | -1 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
2 | DA | DOSIJE | INDEKS | 0 | SYSIBM | INTEGER | 4 | 0 | <NA> | <NA> | <NA> | N | 0 | <NA> | <NA> | 3496 | 20200347 | 20150002 | 4 | 1 | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | -1 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
3 | DA | DOSIJE | IDPROGRAMA | 1 | SYSIBM | INTEGER | 4 | 0 | <NA> | <NA> | <NA> | N | 0 | <NA> | <NA> | 10 | 302 | 103 | 4 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | -1 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
4 | DA | DOSIJE | IME | 2 | SYSIBM | VARCHAR | 50 | 0 | OCTETS | 50 | <NA> | N | 1208 | SYSIBM | SYSTEM_1251 | 464 | 'Zorka' | 'Ada' | 10 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | 6 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
5 | DA | DOSIJE | PREZIME | 3 | SYSIBM | VARCHAR | 50 | 0 | OCTETS | 50 | <NA> | N | 1208 | SYSIBM | SYSTEM_1251 | 1472 | 'Zupcic' | 'Adam' | 12 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | 8 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
6 | DA | DOSIJE | POL | 4 | SYSIBM | CHARACTER | 1 | 0 | OCTETS | 1 | <NA> | Y | 1208 | SYSIBM | SYSTEM_1251 | 2 | 'z' | 'm' | 2 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | -1 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
7 | DA | DOSIJE | MESTORODJENJA | 5 | SYSIBM | VARCHAR | 50 | 0 | OCTETS | 50 | <NA> | Y | 1208 | SYSIBM | SYSTEM_1251 | 182 | 'Zrenjanin' | 'Arandjelovac' | 18 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | 13 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> | ||||
8 | DA | DOSIJE | IDSTATUSA | 6 | SYSIBM | SMALLINT | 2 | 0 | <NA> | <NA> | <NA> | N | 0 | <NA> | <NA> | 9 | 1 | -7 | 2 | <NA> | 0 | 20 | 10 | 0 | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | <NA> | 0 | -1 | N | N | <NA> | O | NaN | -1.0 | -1 | -1 | -1 | <NA> | <NA> | N | N | N | -1 | -1.0 | <NA> | <NA> | N | <NA> |
Ponavljanje i nedefinisane vrednosti¶
U osnovi priče o relacionim bazama podataka i SQL-u stoji priča o skupovima i relacijama. Međutim, postoje i veoma značajne razlike. Dva ključna odstupanja SQL-a od formalne definicije relacionog modela su:
- nedefinisane vrednosti i
- ponavljanje redova.
Da sve bude još zanimljivije, ova dva odstupanja donose sa sobom i mnoštvo specijalnih slučajeva, koji su uspostavljeni više prema praktičnosti primene nego prema teorijskom modelu.
Nedefinisane vrednosti¶
U nekim slučajevima može da bude korisno da u bazi podataka imamo tzv. nedefinisane vrednosti. Zapravo, kasnije ćemo videti da postoje i teorijski mnogo čistiji načini da se uradi isti posao, ali da ima i nekih opravdanja za njihovo postojanje.
Na primer, pogledajmo sve ispitne prijave studenta sa brojem indeksa 20180050 i obratimo posebnu pažnju na redove sa statusom "n" (nije izašao na ispit):
%%sql
-- Svi prijavljeni ispiti studenta sa brojem indeksa 20180050
select *
from ispit
where indeks = 20180050
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2018 | jan1 | 20180050 | 1594 | n | <NA> | <NA> | <NA> |
1 | 2018 | jan1 | 20180050 | 1595 | n | <NA> | <NA> | <NA> |
2 | 2018 | jan1 | 20180050 | 2168 | o | 2019-01-14 | 71 | 8 |
3 | 2018 | jan2 | 20180050 | 1595 | o | 2019-02-09 | 11 | 5 |
4 | 2018 | jun1 | 20180050 | 1595 | o | 2019-06-16 | 38 | 5 |
5 | 2018 | jun1 | 20180050 | 2167 | n | <NA> | <NA> | <NA> |
6 | 2018 | jun2 | 20180050 | 2166 | n | <NA> | <NA> | <NA> |
7 | 2018 | jun2 | 20180050 | 2167 | o | 2019-07-09 | 49 | 5 |
8 | 2018 | sep1 | 20180050 | 1595 | o | 2019-09-05 | 63 | 7 |
9 | 2018 | sep1 | 20180050 | 2167 | o | 2019-09-12 | 55 | 6 |
Ako student nije izašao na ispit, onda nema smisla govoriti o datumu polaganja, broju poena i oceni. Na svim tim mestima se nalaze tzv. nedefinisane vrednosti ili kako je to uobičajeno u SQL.u: NULL
. Nedefinisana vrednost NULL
nema tip, ali može da se tumači kao da je bilo kog tipa, prema kontekstu u kome se nalazi. Postoji specifična oznaka za nedefinisane vrednosti logičkog tipa: UNKNOWN
. (Vidimo da ih i ovaj alat prikazuje na različite načine, zavisno od tipa odgovarajuće kolone.)
Pokušajmo da izdvojimo sva uspešna i neuspešna polaganja ispita ovog studenta:
%%sql
-- Svi položeni ispiti studenta sa brojem indeksa 20180050
-- (nije do kraja ispravno, jer bi moralo da se proveri status='o')
select *
from ispit
where indeks = 20180050
and ocena > 5
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2018 | jan1 | 20180050 | 2168 | o | 2019-01-14 | 71 | 8 |
1 | 2018 | sep1 | 20180050 | 1595 | o | 2019-09-05 | 63 | 7 |
2 | 2018 | sep1 | 20180050 | 2167 | o | 2019-09-12 | 55 | 6 |
%%sql
-- Svi nepoloženi ispiti studenta sa brojem indeksa 20180050
select *
from ispit
where indeks = 20180050
and not ( ocena > 5 )
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2018 | jan2 | 20180050 | 1595 | o | 2019-02-09 | 11 | 5 |
1 | 2018 | jun1 | 20180050 | 1595 | o | 2019-06-16 | 38 | 5 |
2 | 2018 | jun2 | 20180050 | 2167 | o | 2019-07-09 | 49 | 5 |
Važno je da primetimo da restrikcija izdvaja samo one redove za koje je uslov restrikcije tačan. Redovi za koje nije tačan se ne izdvajaju. Međutim, postojanje nedefinisanih vrednosti uvodi nas u ternarnu logiku gde osim vrednosti tačno i netačno postoji i nepoznato (tj. UNKNOWN
). Poređenje nedefinisane ocene sa 5 ima za rezultat nepoznatu logičku vrednost pa niti je ona tačna, niti je njena negacija tačna.
Jedini način da poredimo neku potencijalno nedefinisanu vrednost sa nedefinisanom vrednošću je da proverimo da li je definisana. To radimo predikatima x IS NULL
i x IS NOT NULL
:
%%sql
-- Svi ispiti u kojima ocena nije definisana
select *
from ispit
where indeks = 20180050
and ocena is null
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2018 | jan1 | 20180050 | 1594 | n | <NA> | <NA> | <NA> |
1 | 2018 | jan1 | 20180050 | 1595 | n | <NA> | <NA> | <NA> |
2 | 2018 | jun1 | 20180050 | 2167 | n | <NA> | <NA> | <NA> |
3 | 2018 | jun2 | 20180050 | 2166 | n | <NA> | <NA> | <NA> |
Poređenja oblika x = NULL
nemaju smisla jer je njihova logička vrednost uvek nedefinisana:
%%sql
select *
from ispit
where indeks = 20180050
and ocena = null
No rows found
Command completed.
Zbog toga što poređenje predstavlja izraz logičkog tipa, ima smisla čak i nešto poput (x > 5) IS NULL
:
%%sql
-- ocena is null === ocena > 5 is null
select *
from ispit
where indeks = 20180050
and ocena > 5 is null
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2018 | jan1 | 20180050 | 1594 | n | <NA> | <NA> | <NA> |
1 | 2018 | jan1 | 20180050 | 1595 | n | <NA> | <NA> | <NA> |
2 | 2018 | jun1 | 20180050 | 2167 | n | <NA> | <NA> | <NA> |
3 | 2018 | jun2 | 20180050 | 2166 | n | <NA> | <NA> | <NA> |
%%sql
-- Ovde je drugi deo uslova uvek zadovoljen
select *
from ispit
where indeks = 20180050
and ocena = null is null
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2018 | jan1 | 20180050 | 1594 | n | <NA> | <NA> | <NA> |
1 | 2018 | jan1 | 20180050 | 1595 | n | <NA> | <NA> | <NA> |
2 | 2018 | jan1 | 20180050 | 2168 | o | 2019-01-14 | 71 | 8 |
3 | 2018 | jan2 | 20180050 | 1595 | o | 2019-02-09 | 11 | 5 |
4 | 2018 | jun1 | 20180050 | 1595 | o | 2019-06-16 | 38 | 5 |
5 | 2018 | jun1 | 20180050 | 2167 | n | <NA> | <NA> | <NA> |
6 | 2018 | jun2 | 20180050 | 2166 | n | <NA> | <NA> | <NA> |
7 | 2018 | jun2 | 20180050 | 2167 | o | 2019-07-09 | 49 | 5 |
8 | 2018 | sep1 | 20180050 | 1595 | o | 2019-09-05 | 63 | 7 |
9 | 2018 | sep1 | 20180050 | 2167 | o | 2019-09-12 | 55 | 6 |
Ponavljanje redova¶
Iako bi tabele u osnovi trebalo da budu skupovi redova, SQL je definisan tako da upiti izdvajaju sve redove koji zadovoljavaju uslov restrikcije, što može da uključi i više identičnih redova.
Na primer, izdvojimo imena i prezimena svih studentkinja koje se zovu Milena:
%%sql -a
-- Svi studenti koji se zovu Milena
select Ime, Prezime
from Dosije
where Ime = 'Milena'
order by prezime
IME | PREZIME | |
---|---|---|
0 | Milena | Bosnjakovic |
1 | Milena | Brankovic |
2 | Milena | Dimitrijevic |
3 | Milena | Filipovic |
4 | Milena | Marinkovic |
5 | Milena | Markovic |
6 | Milena | Markovic |
7 | Milena | Medvedev |
8 | Milena | Milovic |
9 | Milena | Miric |
10 | Milena | Mitrovic |
11 | Milena | Nenadic |
12 | Milena | Nikolic |
13 | Milena | Nikolic |
14 | Milena | Petrovic |
15 | Milena | Radisic |
16 | Milena | Rebic |
17 | Milena | Sajic |
18 | Milena | Stanic |
19 | Milena | Vidojevic |
20 | Milena | Zivic |
21 | Milena | Zulfikaric |
Primetimo da imamo ponovljene redove:
- dva puta se pojavljuje Milena Marković;
- dva puta se pojavljuje Milena Nikolić.
U nekim slučajevima to može da bude korisno, ali u nekim drugim nam može biti potrebno da se isključe ponavljanja i da se rezultat ponaša kao skup različitih redova. Ako želimo da se ponavljanja isključe, onda se neposredno iza ključne reči SELECT
navodi ključna reč DISTINCT
, čime se nalaže da se izbace ponovljeni redovi:
%%sql -a
-- Svi razliciti parovi Ime,Prezime studenata koji se zovu Milena
select distinct Ime, Prezime
from Dosije
where Ime = 'Milena'
order by prezime
IME | PREZIME | |
---|---|---|
0 | Milena | Bosnjakovic |
1 | Milena | Brankovic |
2 | Milena | Dimitrijevic |
3 | Milena | Filipovic |
4 | Milena | Marinkovic |
5 | Milena | Markovic |
6 | Milena | Medvedev |
7 | Milena | Milovic |
8 | Milena | Miric |
9 | Milena | Mitrovic |
10 | Milena | Nenadic |
11 | Milena | Nikolic |
12 | Milena | Petrovic |
13 | Milena | Radisic |
14 | Milena | Rebic |
15 | Milena | Sajic |
16 | Milena | Stanic |
17 | Milena | Vidojevic |
18 | Milena | Zivic |
19 | Milena | Zulfikaric |
Izbacivanje ponovljenih vrednosti radi na specifičan način u slučaju kada imamo nedefinisane vrednosti. Kao što smo već videli, poređenje nedefinisane vrednosti sa bilo kojom vrednošču, pa i sa drugom nedefinisanom vrednošću, ima za rezultat nepoznato logičku vrednost. Međutim, kada se radi o nedefinisanim vrednostima i izbacivanju ponavljanja, onda se sve nedefinisane vrednosti u okviru jedne kolone tretiraju kao da su iste.
Na primer, ako izdvojimo sve ocene koje je dobio student sa brojem indeksa 20180050, videćemo da ima ponavljanja:
%%sql
-- Sve ocene studenta
select ocena
from ispit
where indeks = 20180050
OCENA | |
---|---|
0 | <NA> |
1 | <NA> |
2 | 8 |
3 | 5 |
4 | 5 |
5 | <NA> |
6 | <NA> |
7 | 5 |
8 | 7 |
9 | 6 |
Ako navedemo klauzulu DISTINCT
, ponavljanja će biti izbačena. Štaviše, biće izbačene i ponovljene nedefinisane vrednosti, osim jedne:
%%sql
-- Sve razlicite ocene studenta
select distinct ocena
from ispit
where indeks = 20180050
OCENA | |
---|---|
0 | 5 |
1 | 6 |
2 | 7 |
3 | 8 |
4 | <NA> |
Ključna reč DISTINCT
ima još uloga, ali o tome ćemo nešto kasnije.
Izrazi i imenovanje kolona¶
U okviru izraza SELECT
osim postojećih kolona mogu da se navode i izrazi koji izračunavaju potrebne rezultate. SQL ima veliki broj ugrađenih operatora i funkcija, a svaki RSUBP dodaje i neke svoje. Štaviše, većina RSUBP omogućava i dodavanje tzv. korisnički definisanih funkcija:
%%sql
-- Izracunavamo kolonu spajanjem imena i prezimena
select ime, prezime, ime || ' ' || prezime
from dosije
--limit 5 -- o ovome kasnije...
IME | PREZIME | 3 | |
---|---|---|---|
0 | Nemanja | Nicic | Nemanja Nicic |
1 | Andrijana | Beara | Andrijana Beara |
2 | Milos | Milenkovic | Milos Milenkovic |
3 | Valentina | Stojanov | Valentina Stojanov |
4 | Aida | Babic | Aida Babic |
... | ... | ... | ... |
3491 | Tara | Bozinovic | Tara Bozinovic |
3492 | Ana | Ilic | Ana Ilic |
3493 | Tamara | Zejak | Tamara Zejak |
3494 | Uros | Stanojkov | Uros Stanojkov |
3495 | Milos | Djokic | Milos Djokic |
3496 rows × 3 columns
Kolone koje predstavljaju rezultat izračunavanja (ali i sve ostale izdvojene kolone) mogu da se imenuju: <exp> [AS] <ime>
%%sql
-- Imenujemo izracunatu kolonu
select ime || ' ' || prezime as ime_i_prezime
from dosije
-- limit 15
IME_I_PREZIME | |
---|---|
0 | Nemanja Nicic |
1 | Andrijana Beara |
2 | Milos Milenkovic |
3 | Valentina Stojanov |
4 | Aida Babic |
... | ... |
3491 | Tara Bozinovic |
3492 | Ana Ilic |
3493 | Tamara Zejak |
3494 | Uros Stanojkov |
3495 | Milos Djokic |
3496 rows × 1 columns
Uređivanje rezultata upita¶
ORDER BY {<ime> | <rbr> | <exp>} [ASC|DESC] [NULLS {FIRST|LAST}]
Navodi se posle klauzule WHERE
.
U narednom primeru rezultat se uređuje najpre po koloni SkGodina
u opadajućem redosledu, a zatim po izrazu - Semestar
u rastućem redosledu:
%%sql -a
-- Uređivanje rezultata upita
select *
from semestar
order by skgodina desc, -semestar
SKGODINA | SEMESTAR | |
---|---|---|
0 | 2020 | 2 |
1 | 2020 | 1 |
2 | 2019 | 2 |
3 | 2019 | 1 |
4 | 2018 | 2 |
5 | 2018 | 1 |
6 | 2017 | 2 |
7 | 2017 | 1 |
8 | 2016 | 2 |
9 | 2016 | 1 |
10 | 2015 | 2 |
11 | 2015 | 1 |
Kao što se za svaki kriterijum uređivanja zadaje smer uređenja (rastući ili opadajući), tako se za svaki kriteriju uređivanja posebno može odrediti i da li će nedefinisane vrednosti ići na početku (nulls first
) ili na kraju (nulls last
):
%%sql
select ocena, datpolaganja
from ispit
where indeks = 20180050
order by ocena nulls first, 2 desc
OCENA | DATPOLAGANJA | |
---|---|---|
0 | <NA> | <NA> |
1 | <NA> | <NA> |
2 | <NA> | <NA> |
3 | <NA> | <NA> |
4 | 5 | 2019-07-09 |
5 | 5 | 2019-06-16 |
6 | 5 | 2019-02-09 |
7 | 6 | 2019-09-12 |
8 | 7 | 2019-09-05 |
9 | 8 | 2019-01-14 |