/*
 * 1. Napraviti tabelu TEST sa kolonama ID (ceo broj, primarni ključ) i SLOVO (karakter).
 * Obezbediti da se vrednost ID automatski generiše ukoliko nije navedena.
 *
 * Zatim izvršavati stavke po redu:
 *  U datu tabelu uneti vrednost SLOVO='a'
 *  U datu tabelu uneti vrednost ID=2, SLOVO='b'
 *  U datu tabelu uneti vrednost SLOVO='c'
 */

-- Pokušaj 1:

CREATE TABLE TEST (
    ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    SLOVO CHAR NOT NULL
);

INSERT INTO TEST (SLOVO)
VALUES ('a');

INSERT INTO TEST
VALUES (2, 'b');

DROP TABLE TEST;

-- Objašnjenje:
--
-- Želimo da se ID generiše automatski ukoliko ga navedemo. Ako se setimo
-- prvog zadatka sa prethodnog časa, automatsko generisanje vrednosti smo
-- postigli dodavanjem "GENERATED ALWAYS AS IDENTITY", pa bi verovatno bilo
-- za očekivati da će isto rešenje raditi i ovde.
--
-- Prva INSERT naredba se izvršava ispravno. Međutim, već u drugoj dobijamo
-- grešku. Šta se desilo? "GENERATED ALWAYS AS IDENTITY" znači da će se vrednost
-- UVEK generisati automatski, tj. time se lišavamo mogućnosti da je mi
-- eksplicitno navedemo. Ovo rešavamo upotrebom "GENERATED BY DEFAULT AS IDENTITY".

-- Pokušaj 2:

CREATE TABLE TEST (
    ID INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    SLOVO CHAR NOT NULL
);

INSERT INTO TEST (SLOVO)
VALUES ('a');

INSERT INTO TEST
VALUES (2, 'b');

SELECT * FROM TEST;

INSERT INTO TEST (SLOVO)
VALUES ('c');

DROP TABLE TEST;

-- Objašnjenje:
--
-- Ovde smo, u odnosu na prethodni pokušaj, zamenili "ALWAYS" sa "BY DEFAULT".
-- ID će sada biti generisan automatski, ali samo ukoliko ga mi eksplicitno
-- ne navedemo, što je upravo ono što želimo.
--
-- Prva dva INSERT-a prolaze, međutim, sada imamo problem sa trećim. Šta se sada desilo?
-- "GENERATED ... AS IDENTITY" zapravo kreira generator brojeva u pozadini. Taj generator
-- ima svoju početnu vrednost, korak za koji se uvećava i još dodatnih osobina (minimalna,
-- maksimalna vrednost, ciklično generisanje i slično). Svaki put kada se automatski
-- generiše novi broj pomoću generatora, taj broj se pamti kao poslednji generisani. Naredni
-- broj se računa u odnosu na njega.
--
-- Međutim, ovo se dešava samo kada se brojevi zaista automatski generišu - ne i kada
-- mi eksplicitno navedemo neki ID! Ovde se onda desilo sledeće:
--
-- 1) Prvi INSERT - automatski generišemo ID=1. Ovo se pamti kao stanje generatora.
-- 2) Drugi INSERT - mi smo naveli ID=2. Generator nije generisao ništa, te je njegovo stanje ostalo nepromenjeno.
-- 3) Treći INSERT - ponovo tražimo automatsko generisanje. Međutim, generator računa sledeći ID
--                   u odnosu na prethodni generisani ID, tj. sada generiše 2. Međutim, mi već imamo ID=2
--                   u tabeli - narušili smo ograničenje primarnog ključa!

-- Rešenje:

CREATE TABLE TEST (
    ID INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 2),
    SLOVO CHAR NOT NULL
);

INSERT INTO TEST (SLOVO)
VALUES ('a');

INSERT INTO TEST
VALUES (2, 'b');

INSERT INTO TEST (SLOVO)
VALUES ('c');

SELECT * FROM TEST;

DROP TABLE TEST;

-- Objašnjenje:
--
-- Kako da zaobiđemo kolizije u ID-jevima koji su automatski generisani i onim
-- koje smo mi ručno odredili? Jedna opcija je da promenimo opseg automatski generisanih
-- identifikatora. To možemo uraditi sa dodatnim "(START WITH 100 INCREMENT BY 2)".
-- Identifikatori će se sada generisati od broja 100, sa uvećanjem od 2. Nakon svih
-- INSERT naredbi, u tabeli imamo redove (ID=100, SLOVO='a'), (ID=2, SLOVO='b'), (ID=102, SLOVO='c').

/*
 * 2. Za svakog studenta smera Informatika na osnovnim akademskim studijama,
 * koji je fakultet upisao 2015. godine, pronaći koje obavezne predmete nije položio.
 * Izdvojiti ime i prezime studeta i naziv predmeta.
 */

-- Rešenje:

SELECT D.IME, D.PREZIME, P.NAZIV
FROM DA.DOSIJE AS D JOIN
     DA.STUDIJSKIPROGRAM AS SP ON D.IDPROGRAMA = SP.ID JOIN
     DA.NIVOKVALIFIKACIJE AS NK ON SP.IDNIVOA = NK.ID JOIN
     DA.PREDMETPROGRAMA AS PP ON SP.ID = PP.IDPROGRAMA JOIN
     DA.PREDMET AS P ON PP.IDPREDMETA = P.ID
WHERE SP.NAZIV = 'Informatika' AND
      NK.NAZIV = 'Osnovne akademske studije' AND
      YEAR(D.DATUPISA) = 2015 AND
      PP.VRSTA = 'obavezan' AND
      (D.INDEKS, P.ID) NOT IN ( -- Izbacujemo one već položene predmete za tog studenta iz skupa
          SELECT I.INDEKS, I.IDPREDMETA
          FROM DA.ISPIT AS I
          WHERE I.OCENA > 5 AND I.STATUS = 'o'
      );

/*
 * 3. Napraviti pogled kojim se izdvajaju svi ispitni rokovi održani od 2018/2019. školske godine,
 * a zatim uraditi sledeće:
 * - Uneti ispitni rok jan1 2022. godine u pogled.
 * - Uneti u tabelu ispitnirok rok jan2 2022
 * - U pogled uneti rok sa oznakom 'xxx' održan 2015. godine.
 * - Promeni oznaku roka xxx 2015 u kreiranom pogledu u 'test'.
 * - Obrisati rok sa oznakom 'xxx' iz pogleda.
 * - Obrisati ispitne rokove održane 2022 iz pogleda.
 * - Obrisati pogled.
 */

-- Rešenje:

-- Kreiranje pogleda:

CREATE VIEW NOVI_ISPITNI_ROKOVI AS
SELECT *
FROM DA.ISPITNIROK
WHERE SKGODINA >= 2018;

-- Poglede kreiramo sa naredbom CREATE VIEW <naziv pogleda> AS <upit koji definiše pogled>;.

-- Najjednostavnije objašnjeno, pogledi su imenovani upiti na koje kasnije,
-- u nekim drugim upitima, možemo referisati kao na tabele. Svaki put kada
-- to uradimo, u pozadini će se zapravo izvršiti upit kojim je pogled definisan!

SELECT * FROM NOVI_ISPITNI_ROKOVI;

-- Prethodni upit se izvršava suštinski isto kao da smo napisali sledeće:

SELECT *
FROM (
    SELECT *
    FROM DA.ISPITNIROK
    WHERE SKGODINA >= 2018
);

-- Ovo je izuzetno važno razumeti - iako na poglede referišemo kao na tabele, u njima
-- se ne čuvaju nikakvi duplirani podaci (osim ako nije reč o materijalizovanim pogledima,
-- ali o tome više na predavanjima). Međutim, ako se referisanjem na pogled zaista samo
-- izvršava u pozadini, kako uopšte ima smisla pričati o "unošenju redova u pogled", što
-- se traži od nas u zadatku?

-- Prvo, kreiraćemo školsku godinu 2022 da bi mogli da zadovoljimo uslove stranog ključa:

INSERT INTO DA.SKOLSKAGODINA
VALUES (2022, '2022-10-01', '2023-09-30');

-- Sada probajmo da unesemo novi red kroz pogled:

INSERT INTO NOVI_ISPITNI_ROKOVI
VALUES (2022, 'jan1', 'Januar1 2022', '2023-01-05', '2023-01-25');

-- Iako smo unos izvršili nad pogledom, nismo dobili nikakvu grešku. Hajde da se uverimo
-- da je novi ispitni rok zaista unet u bazu:

SELECT * FROM NOVI_ISPITNI_ROKOVI; -- Proverimo šta se nalazi u pogledu - zaista je tu!

SELECT * FROM DA.ISPITNIROK; -- Proverimo šta se nalazi u baznoj tabeli nad kojom je pogled definisan - i ovde je!

-- Šta se ovde zapravo desilo? Kako je moguće da smo uneli novi red kroz pogled, ako je
-- pogled samo imenovani upit? Ispostavlja se da DB2 zapravo omogućava manipulaciju podacima
-- (INSERT, DELETE i UPDATE) kroz poglede ukoliko je definicija pogleda relativno jednostavna.
-- Na primer, da bi bilo moguće vršiti ove operacije kroz pogled, on ne sme imati spajanje
-- tabela u svojoj definiciji.
--
-- Ovo je moguće zato što SUBP, iz ovakve definicije pogleda, može jednostavno da odredi
-- baznu tabelu u koju treba da ubaci novi red.

-- Unesimo sada novi red za jan2 2022, ali ovaj put kroz samu tabelu:

INSERT INTO DA.ISPITNIROK
VALUES (2022, 'jan2', 'Januar2 2022', '2023-02-05', '2023-02-25');

SELECT * FROM NOVI_ISPITNI_ROKOVI; -- Zaista, ovaj novi red će se pojaviti i u pogledu!

-- Pokušajmo sada ubacivanje još jednog roka kroz pogled. Razlika u odnosu na prethodno
-- ubacivanje roka kroz pogled je što u ovom slučaju novi rok ne odgovara definiciji pogleda.
-- Pogled NOVI_ISPITNI_ROKOVI prikazuje samo rokove od 2018. godine, a rok koji sada ubacujemo
-- je održan 2015. godine:

INSERT INTO NOVI_ISPITNI_ROKOVI
VALUES (2015, 'xxx', 'Dodatni rok 2015', '2015-11-11', '2015-11-18');

-- Upit se zaista izvršio bez bilo kakve greške. Hajde da proverimo da li je zaista došlo do bilo kakvih
-- promena u bazi:

SELECT * FROM NOVI_ISPITNI_ROKOVI; -- Gledamo stanje u pogledu - ovde se, očekivano, ne vidi rok 'xxx'.

SELECT * FROM DA.ISPITNIROK; -- Međutim, ako pogledamo baznu tabelu, primetićemo da je novi rok dodat u nju!

-- Iako novi rok ne odgovara definiciji pogleda, ipak je moguće dodati ga u baznu tabelu
-- kroz pogled. Ovo je moguće sprečiti dodavanjem "WITH CHECK OPTION" specifikatora prilikom
-- kreiranja pogleda. Primer toga ćemo videti u zadatku 8.

-- Da li možemo i da ažuriramo ovaj novi red kroz pogled?

UPDATE NOVI_ISPITNI_ROKOVI
SET OZNAKAROKA = 'test'
WHERE OZNAKAROKA = 'xxx';

-- Naredba se izvršila uspešno...

SELECT * FROM DA.ISPITNIROK;

-- Ali izmene nema u baznoj tabeli - oznaka roka je ostala 'xxx'. Zašto?
-- UPDATE je prvo probao da nađe odgovarajući red u pogledu (što nije uspeo,
-- jer u pogledu nema roka sa oznakom 'xxx'). Kada ga nije pronašao, tu se
-- i završio pokušaj ažuriranja. Međutim, kada bi pronašao odgovarajući red
-- u pogledu (kada bi, na primer, 'xxx' rok bio održan 2024. godine), onda
-- bi se jednostavno odredio i odgovarajući red u baznoj tabeli DA.ISPITNIROK
-- i ažuriranje bi se izvršilo. Opet, ovo je moguće samo ukoliko je definicija
-- pogleda "jednostavna".

-- Pokušajmo sada sa brisanjem umesto ažuriranja:

DELETE FROM NOVI_ISPITNI_ROKOVI
WHERE OZNAKAROKA = 'xxx';

SELECT * FROM DA.ISPITNIROK;

-- Opet, naredba se izvršila "uspešno", ali izmena nema. Razlog je isti kao i
-- za ažuriranje.

-- Ako probamo da obrišemo neke redove koji se zaista nalaze u pogledu:

DELETE FROM NOVI_ISPITNI_ROKOVI
WHERE SKGODINA = 2022;

SELECT * FROM NOVI_ISPITNI_ROKOVI;

-- Ovaj put je bilo uspešno! Rokova iz 2022. više nema.

DROP VIEW NOVI_ISPITNI_ROKOVI; -- Brisanje pogleda

/*
 * 4. Napraviti pogled sa svim prijavljenim ispitima Marka Petrovića.
 * A zatim svim ispitima iz pogleda staviti datum polaganja na null.
 */

-- Kreiramo kopiju podataka da bi izbegli menjanje originalnih:

CREATE TABLE ISPIT_P AS (
    SELECT *
    FROM DA.ISPIT
) WITH DATA;

-- Prvi pokušaj:

CREATE VIEW ISPITI_MARKA_PETROVICA AS
SELECT I.*
FROM ISPIT_P AS I JOIN
     DA.DOSIJE AS D ON I.INDEKS = D.INDEKS
WHERE D.IME = 'Marko' AND D.PREZIME = 'Petrovic';

SELECT * FROM ISPITI_MARKA_PETROVICA;

UPDATE ISPITI_MARKA_PETROVICA
SET DATPOLAGANJA = NULL;

DROP VIEW ISPITI_MARKA_PETROVICA;

-- Objašnjenje:
--
-- Ovaj pokušaj nije uspeo - iako su rezultati koje pogled prikazuje korektni,
-- pokušaj izvršavanja UPDATE naredbe nad pogledom je proizveo grešku. Razlog
-- za ovo je to što smo u definiciji pogleda spojili dve tabele. SUBP sada ne
-- može automatski da odredi koje redove u kojim baznim tabelama (tabelama nad
-- kojim je definisan) treba da ažurira, te se prijavljuje greška. Da li možemo
-- nekako da zaobiđemo ovaj problem?

-- Rešenje:

CREATE VIEW ISPITI_MARKA_PETROVICA AS
SELECT *
FROM ISPIT_P
WHERE INDEKS IN (
    SELECT INDEKS
    FROM DA.DOSIJE
    WHERE IME = 'Marko' AND PREZIME = 'Petrovic'
);

SELECT * FROM ISPITI_MARKA_PETROVICA;

UPDATE ISPITI_MARKA_PETROVICA
SET DATPOLAGANJA = NULL;

SELECT * FROM ISPITI_MARKA_PETROVICA;

DROP VIEW ISPITI_MARKA_PETROVICA;

DROP TABLE ISPIT_P;

-- Objašnjenje:
--
-- Prethodni problem smo prevazišli tako što smo, umesto spajanja tabela u FROM-u,
-- upotrebili podupit da izdvojimo samo ispite studenta Marka Petrovića. Pošto
-- sada imamo samo jednu tabelu u FROM-u, ažuriranje kroz pogled je uspešno.

/*
 * 5. Napraviti pogled kojim se izdvajaju svi prijavljeni ispiti studenata
 * koji se zovu Marko ili Marija, a u ispitnom roku jan1 2018/2019. šk. godine
 * su položili barem jedan ispit.
 *
 * Pogled napisati tako da je kroz njega moguće dodavanje novih ispita koji
 * zadovoljavaju uslove pogleda.
 *
 * Pokušati dodati sledeća dva reda:
 * - Marko: indeks 20150013, idpredmeta 2027, skgodina 2018, oznakaroka sep2, status o, 40 poena, ocena 5
 * - Jovana: indeks 20150001, idpredmeta 2027, skgodina 2018, oznakaroka sep2, status o, 40 poena, ocena 5.
 * Ukoliko neko ubacivanje ne uspe pokušati sa unosom reda direktno u tabelu ispit.
 * Za dva uneta reda pokušati izmenu datuma polaganja nad pogledom na današnji datum.
 * Ukoliko izmena nad pogledom ne uspe odraditi je direktno na tabeli.
 * Na kraju pokušati brisanje ova dva reda preko pogleda.
 */

CREATE TABLE ISPIT_P AS (
    SELECT * FROM DA.ISPIT
) WITH DATA;

-- Rešenje:

CREATE VIEW ISPITI_MARKA_I_MARIJE AS
SELECT *
FROM ISPIT_P
WHERE INDEKS IN ( -- Ograničimo se na ispite nekog Marka i neke Marije
    SELECT INDEKS
    FROM DA.DOSIJE
    WHERE IME IN ('Marko', 'Marija')
) AND INDEKS IN ( -- Ograničimo se samo na ispite onih koji su položili nešto u januaru 1 2018.
    SELECT INDEKS
    FROM ISPIT_P
    WHERE SKGODINA = 2018 AND
          OZNAKAROKA = 'jan1' AND
          STATUS = 'o' AND OCENA > 5
) WITH CHECK OPTION; -- Želimo mogućnost dodavanja samo onih redova koji zadovoljavaju definiciju pogleda!

SELECT * FROM ISPITI_MARKA_I_MARIJE;

-- Probajmo ubacivanje prvog reda (Markovog ispita):

INSERT INTO ISPITI_MARKA_I_MARIJE(INDEKS, IDPREDMETA, SKGODINA, OZNAKAROKA, STATUS, POENI, OCENA)
VALUES (20150013, 2027, 2018, 'sep2', 'o', 40, 5);

SELECT * FROM ISPITI_MARKA_I_MARIJE;

-- Ubacivanje novog reda je bilo uspešno. Probajmo sada sa ubacivanjem Jovaninog ispita:

INSERT INTO ISPITI_MARKA_I_MARIJE(INDEKS, IDPREDMETA, SKGODINA, OZNAKAROKA, STATUS, POENI, OCENA)
VALUES (20150001, 2027, 2018, 'sep2', 'o', 40, 5);

-- Ovaj pokušaj nije bio uspešan! Razlog za ovo je što smo pri definiciji pogleda dodali "WITH CHECK OPTION".
-- Pošto je u pitanju ispit neke Jovane, taj ispit se sigurno ne bi prikazao u pogledu, pa ga time ne
-- možemo ni dodati kroz pogled. Moraćemo do da uradimo nad baznom tabelom:

INSERT INTO ISPIT_P(INDEKS, IDPREDMETA, SKGODINA, OZNAKAROKA, STATUS, POENI, OCENA)
VALUES (20150001, 2027, 2018, 'sep2', 'o', 40, 5);

SELECT *
FROM ISPIT_P
WHERE INDEKS IN (20150013, 20150001) AND
      IDPREDMETA = 2027 AND
      SKGODINA = 2018 AND
      OZNAKAROKA = 'sep2' AND
      POENI = 40;

UPDATE ISPITI_MARKA_I_MARIJE
SET DATPOLAGANJA = CURRENT_DATE
WHERE INDEKS IN (20150013, 20150001) AND
      IDPREDMETA = 2027 AND
      SKGODINA = 2018 AND
      OZNAKAROKA = 'sep2' AND
      POENI = 40;

UPDATE ISPIT_P
SET DATPOLAGANJA = CURRENT_DATE
WHERE INDEKS = 20150001 AND
      IDPREDMETA = 2027 AND
      SKGODINA = 2018 AND
      OZNAKAROKA = 'sep2' AND
      POENI = 40;

DELETE FROM ISPITI_MARKA_I_MARIJE
WHERE INDEKS IN (20150013, 20150001) AND
      IDPREDMETA = 2027 AND
      SKGODINA = 2018 AND
      OZNAKAROKA = 'sep2' AND
      POENI = 40;

DROP VIEW ISPITI_MARKA_I_MARIJE;

DROP TABLE ISPIT_P;

/*
 * 6. Napraviti indeks tabele dosije po imenu i prezimenu u opadajućem redosledu. Zatim ga obrisati.
 */

-- Ukratko o indeksima:
--
-- Zamislimo sledeću situaciju - radimo na platformi za e-trgovinu (npr. ebay, KupujemProdajem, ...)
-- gde je potrebno voditi evidenciju o milionima (verovatno čak i milijardama) različitih oglasa.
-- Podaci o postavljenim oglasima se onda čuvaju u nekoj tabeli "OGLAS", pri čemu je svakom oglasu
-- dodeljen jedinstveni identifikator (koji je ujedno i primarni ključ tabele OGLAS):
--
-- ID       | NAZIV_OGLASA                   | CENA     | ID_KORISNIKA | ...
-- 42       | Proces - Franc Kafka           | 800.00   | 124465       | ...
-- 12345678 | Yamaha F310 - akustična gitara | 21000.00 | 123          | ...
-- 465546   | Lenovo ThinkBook 16 G7         | 79699.00 | 56513        | ...
-- ...
--
-- Prilikom otvaranja stranice oglasa, iz baze je potrebno, na osnovu identifikatora oglasa,
-- dovući sve podatke o njemu - naziv, cenu, opis i ostalo i sve to prikazati posetiocu stranice
-- (suštinski, potrebno je izvršiti upit SELECT * FROM OGLAS WHERE ID = <neki_id>).
--
-- Baza ovo najjednostavnije može izvršiti potpunim skeniranjem tabele OGLAS - prolazi se kroz
-- celu tabelu, red po red, pri čemu se proverava da li je ID trenutnog reda jednak traženom.
-- Međutim, u ovakvom rešenju postoji veliki problem - u tabeli OGLAS imamo milione redova...
-- Potpuno skeniranje jedne takve tabele prilikom otvaranja stranice oglasa je nedopustivo
-- (kako zbog vremena izvršavanja, tako i zbog utrošenih resursa). Potreban nam je način da
-- nađemo odgovarajući red (sa traženim identifikatorom) bez da potencijalno moramo da prođemo
-- kroz celu tabelu. Rešenje za ovo su indeksi!
--
-- Indeksi su posebne strukture, kreirane nad određenim kolonama tabele, koje omogućavaju efikasne
-- upite nad tim kolonama, tj. efikasno pronalaženje redova koji zadovoljavaju neki uslov nad nekom
-- kolonom. Kako oni suštinski rade?
--
-- Zamislimo da uz našu tabelu OGLAS imamo jedan indeks u vidu SORTIRANOG niza identifikatora svih
-- oglasa, pri čemu se uz svaki od tih identifikatora čuva i pokazivač na odgovarajući red u tabeli:
--
-- Indeks nad OGLAS.ID:                 Tabela OGLAS:
--
-- | ID       | Ptr                     | ID       | NAZIV_OGLASA                   | CENA     | ID_KORISNIKA | ...
-- | 1        | ...                     | ...      | ...                            | ...      | ...          | ...
-- | 2        | ...     +-------------> | 42       | Proces - Franc Kafka           | 800.00   | 124465       | ...
-- | ...      | ...     |               | ...      | ...                            | ...      | ...          | ...
-- | 42       | --------+     +-------> | 12345678 | Yamaha F310 - akustična gitara | 21000.00 | 123          | ...
-- | ...      | ...           |         | ...      | ...                            | ...      | ...          | ...
-- | 465546   | --------------|-------> | 465546   | Lenovo ThinkBook 16 G7         | 79699.00 | 56513        | ...
-- | ...      | ...           |         | ...      | ...                            | ...      | ...          | ...
-- | 12345678 | --------------+
-- | ...      | ...
--
-- Pošto su identifikatoru u našem indeksu (nizu) sortirani, to omogućava binarnu pretragu, koja je
-- logaritamske složenosti! Sada, ako želimo da nađemo oglas sa identifikatorom 465546, potrebno je
-- samo da izvršimo binarnu pretragu u okviru indeksa, što je, ako bi imali oko 10,000,000 oglasa
-- samo oko 24 provera. Nakon što u indeksu pronađemo odgovarajući identifikator, dovoljno je ispratiti
-- prateći pokazivač ka odgovarajućem redu u tabeli OGLAS i našli smo traženi red bez skeniranja cele tabele!
--
-- Ovde je prikazan indeks nad jednom kolonom, ali se priča lako uopštava i nad indeksima kreiranim nad
-- više kolona. Ono što je bitno naglasiti je da, iako indeksi mogu da ubrzaju dohvatanje redova, isto
-- tako mogu i usporiti izmene (ubacivanje novih redova, brisanje starih) nad tabelama nad kojim su kreirani
-- jer je sada potrebno održavati i njih. Da bi i ovo bilo efikasno, u praksi indeksi nisu implementirani
-- kao obični nizovi, već kao nešto složenije strukture balansiranih binarnih stabala (B i B+ stabla) ili
-- heš tabela, ali o tome (i o različitim vrstama indeksa) više reči na predavanjima.
--
-- Iako postoji kreiran indeks nad nekim kolonama neke tabele, od optimizatora upita zavisi da li će taj
-- indeks zaista biti i upotrebljen - u nekim situacijama, optimizator može doći do zaključka da je zapravo
-- jeftinije skenirati celu tabelu (naručito ako je ona relativno mala) nego da upotrebi indeks.
--
-- DB2 implicitno kreira indeks nad kolonama koje čine primarni ključ tabele.

-- Rešenje:

CREATE INDEX DOSIJE_IME_DESC_PREZIME_DESC ON DA.DOSIJE (IME DESC, PREZIME DESC);

SELECT *
FROM DA.DOSIJE
WHERE IME = 'Petar' AND PREZIME = 'Knezevic';

DROP INDEX DOSIJE_IME_DESC_PREZIME_DESC;

-- Objašnjenje:
--
-- Kreiranje indeksa se realizuje kroz naredbu
-- CREATE INDEX <naziv indeksa> ON <tabela> (<kolone nad kojima se kreira indeks>);
--
-- Uz kolone se može naglasiti i kako treba sortirati indeks - rastuće ili opadajuće.
-- Ovo u nekim slučajevima može biti korisno za izbegavanje sortiranja prilikom upotrebe
-- ORDER BY u upitu.
--
-- Napomena - ovaj indeks je kreiran nad dve kolone - prvo nad imenom, a onda nad prezimenom.
-- Ovo ne znači da imamo dve odvojene sortirane strukture, već da imamo samo jednu koja je
-- prvo sortirana po imenu studenta, a u okviru istih grupa (više studenata sa istim imenom)
-- je sortirana po prezimenu!

/*
 * 7. Napisati korisnički definisanu funkciju koja vraća cenu predmeta
 * za samofinansirajuće studente. Kao argument se zadaje broj espb.
 * Cena jednog espb boda je 2000 dinara.
 */

-- Rešenje:

CREATE FUNCTION CENA_PREDMETA(ESPB SMALLINT) RETURNS REAL
RETURN ESPB * 2000.0;

SELECT ID, NAZIV,
       CENA_PREDMETA(ESPB) AS CENA
FROM DA.PREDMET;

-- Objašnjenje:
--
-- Prilikom kreiranja funkcije, prvo navodimo njenu deklaraciju - njeno ime,
-- parametre (naziv i tip) i, konačno, tip povratne vrednosti. Nakon deklaracije
-- koristimo RETURN naredbu praćenu izrazom.
--
-- U ovim primerima imaćemo samo najjednostavnije funkcije koje imaju jednu RETURN
-- naredbu. U realnim slučajevima, moguće je kreirati dosta složenije funkcije
-- sa naredbama grananja, petljama i slično.

/*
 * 8. Napisati korisnički definisanu funkciju koja vraća cenu predmeta
 * za samofinansirajuće studente. Kao argument se zadaje id predmeta.
 * Cena jednog espb boda je 2000 dinara.
 */

-- Rešenje:

CREATE FUNCTION CENA_PREDMETA(IDPREDMETA INT) RETURNS REAL
RETURN (SELECT ESPB FROM DA.PREDMET WHERE ID = IDPREDMETA) * 2000.0;

SELECT ID, NAZIV,
       CENA_PREDMETA(ID) AS CENA
FROM DA.PREDMET;

DROP FUNCTION CENA_PREDMETA (SMALLINT);

DROP FUNCTION CENA_PREDMETA (INT);

-- Objašnjenje:
--
-- Princip je isti kao i u prethodnom primeru, samo što sada koristimo podupit da
-- izvučemo ESPB odgovarajućeg predmeta.
--
-- Primetimo da je moguće napraviti više funkcija sa istim imenom, sve dok se tipovi
-- njihovih parametara razlikuju. Prilikom poziva funkcije, izbor koja varijanta će
-- biti pozvana vrši se na osnovu tipa argumenta - tip kolone ESPB je SMALLINT, pa
-- će u prvom slučaju biti pozvana funkcija CENA_PREDMETA(ESPB SMALLINT), dok je tip
-- kolone ID INT, pa će u ovom slučaju biti pozvana funkcija CENA_PREDMETA(IDPREDMETA INT).
-- Ovo se zove "preopterećenje funkcija".
--
-- Brisanje funkcija se vrši sa DROP FUNCTION naredbom. Ukoliko su funkcije preopterećene,
-- potrebno je navesti i tipove parametara one varijante funkcije koju želimo da obrišemo.

/*
 * 9. Napisati korisnički definisnu funkciju koja vraća prosek studenta
 * sa zadatim indekom. Zaokružiti prosek na dve decimale.
 */

-- Rešenje:

CREATE FUNCTION PROSEK(INDEKS_STUDENTA INT) RETURNS REAL
RETURN SELECT DECIMAL(ROUND(AVG(OCENA * 1.0), 2), 4, 2)
       FROM DA.ISPIT
       WHERE INDEKS = INDEKS_STUDENTA AND
             OCENA > 5 AND STATUS = 'o';

SELECT INDEKS, IME, PREZIME,
       PROSEK(INDEKS) AS PROSEK
FROM DA.DOSIJE;

DROP FUNCTION PROSEK;

-- Objašnjenje:
--
-- Ovde prilikom brisanja funkcije nismo morali da navedemo tipove parametara (PROSEK (INT))
-- jer ova funkcija nije preopterećena.

/*
 * 10. Napisati korisnički definisanu funkciju koja vraća koji je danas dan.
 * Vraća naziv dana u nedelju ukoliko je radni dan, inače vraća 'WEEKEND'.
 */

-- Rešenje:

CREATE FUNCTION TELL_ME_THE_DAY() RETURNS VARCHAR(10)
RETURN CASE
           WHEN DAYOFWEEK(CURRENT_DATE) IN (7, 1) THEN 'WEEKEND'
           ELSE DAYNAME(CURRENT_DATE)
       END;

VALUES TELL_ME_THE_DAY();

DROP FUNCTION TELL_ME_THE_DAY;

-- Objašnjenje:
--
-- Prisetimo se - indeks subote je 7, a indeks nedelje je 1. Zato imamo proveru
-- DAYOFWEEK(CURRENT_DATE) IN (7, 1)...
