--***Skalarne funkcije ***

-- Primer: Prikazati sifre predmeta i sifre u kojima je 
--   * 0 zamenjena sa 9
--   * 1 zamenjena sa 8
--   * 5 i 8 zamenjeni sa -

select sifra, translate (sifra,  '98', '0158', '-')
from predmet;

-- Primer rezultata  M105 	M89-

--Funkcije za rad sa datumima i vremenima 

--Primer: Prikazati danasnji datum u razlicitim formatima

values (char(current date, EUR), 'EUR'),
       (char(current date, USA), 'USA'),
       (char(current date, ISO),  'ISO'),
       (char(current date, JIS), 'JIS'),
       (char(current date, LOCAL), 'LOCAL');
       
 --Rezultat
-- 30.10.2022	EUR
-- 10/30/2022	USA
-- 2022-10-30	ISO
-- 2022-10-30	JIS
-- 10-30-2022	LOCAL

--Primer: Zapisati datum 5.5.2023.
       
values date('5.5.2023');
values cast('5.5.2023' as date);

-- 2023-05-05

--Primer: Zapisati kombinaciju datuma 5.5.2023. i vremena 1.02

values timestamp('05/05/2023','1.02');
-- 2023-05-05 01:02:00.0


--Primer: Izdvojiti za danasnji datum: redni broj nedelje u godini, godinu, mesec i dan

select week(current date) redni_broj_nedelje , 
       year(current date) godina, 
       month(current date) mesec,
       day(current date) dan
from sysibm.sysdummy1;

--Primer: Odrediti redni broj dana  u nedelji i ime za danasnji datum
--ponedeljak - nedelja
select dayofweek_iso(current date) dan_u_nedelji_iso,
       dayofweek(current date) dan_u_nedelji,
       dayname(current date) ime_dana
from sysibm.sysdummy1;

-- 7 1 Sunday


select CURRENT LOCALE LC_TIME from sysibm.sysdummy1;

--Primer: Ispisati ime dana za danasnji datum
select dayname(current_date),
       dayname(current_date,'CLDR181_sr_SR'),             -- "locale_name" u dayname funkciji - videti cldr.unicode.org   Unicode Common Locale Data Repository (CLDR) 
       dayname(current_date,'CLDR181_sr_Latn_SR') 
from sysibm.sysdummy1;

-- Sunday	недеља	nedelja

--Primer: Izdvojiti 
-- * dan u godini za danasnji datum
-- * poslednji dan u mesecu za tekuci mesec
-- * poslednji dan u mesecu za datum 31.12.2022.
-- * datum za prvu nedelju nakon danasnjeg dana 
-- * datum za prvi ponedeljak nakon 31.12.2022. 


select dayofyear(current date) dan_u_godini,
       last_day(current date) poslednji_u_mesecu, 
       last_day('31.12.2022') poslednji_u_mesecu2,
       next_day(current date,'SUN') sledeca_nedelja
from sysibm.sysdummy1;

-- 303	2022-10-31        	2022-12-31         	2022-11-06     	

   

   
-- Primer: Odrediti koliko je dana proslo od 1.1.0001. za 1.1.0001. i za danasnji dan. 
values (days('1.1.0001') , days(current date));

-- 1	738458


-- Primer: Odrediti 
-- * koji je datum bio pre godinu dana
-- * koje ce vreme biti za 3 sata 20 minuta i 10 sekundi  

select current date - 1 year,
       current time + 3 hour + 20 minute + 10 seconds
from sysibm.sysdummy1;

-- 2021-10-30	17:12:31



--Primer: Koliko vremena je proslo od 5.5.2020?
 values current date - date('5.5.2020');
 
-- 20525

 
--Primer: Prikazati trenutno vreme u razlicitim formatima

values (char(current time, EUR), 'EUR'),
       (char(current time, USA), 'USA'),
       (char(current time, ISO),  'ISO'),
       (char(current time, JIS), 'JIS'),
       (char(current time, LOCAL), 'LOCAL');


-- 13.44.04	EUR
-- 01:44 PM	USA
-- 13.44.04	ISO
-- 13:44:04	JIS
-- 13:44:04	LOCAL

--Primer: Izdvojiti trenutno vreme i prikazati vreme 10:24
select time(current timestamp), time('10:24:00')
from sysibm.sysdummy1;
-- 14:07:09	10:24:00


--Primer: Izdvojiti trenutno vreme i prikazati vreme 10:24
select time(current timestamp), time('10:24:00')
from sysibm.sysdummy1;
-- 14:07:09	10:24:00

-- Primer: Izdvojiti trenutne minute, sekunde i mikrosekunde
select minute(current time) minuta, 
       second(current time) sekundi,
       microsecond(current timestamp) mikrosekundi
from sysibm.sysdummy1;
--7 48 501459


-- Primer: Prikazati broj u formatu 8.2
select   decimal('1234.5656',8,2)
from sysibm.sysdummy1;
 
 --Rezultat: 1234.56
 
--Primer: Prikazati samo cifre brojeva 1234.56 , broj 101 kao vrednost tipa double
select digits(1234.56),  float(101), double(101)
from sysibm.sysdummy1;
-- 123456	101.0	101.0
 
--Primer: Prikazati rezultat primene funkcija floor i ceil na broj 65.6
select floor(65.6), ceil(65.6)
from sysibm.sysdummy1;
-- 65	66


--Primer: Zaokruziti broj 873.726 na razlicite decimale.
select round(873.726,2), round(873.726,1),
       round(873.726,0),round(873.726,-1),
       round(873.726,-2), round(873.726,-3)
from sysibm.sysdummy1;

-- 873.730	873.700	874.000	870.000	900.000	1000.000

--***Agregatne funkcije***

--Primer: Naci ukupan broj studenata koji su upisani skolske 2013/2014. godine. 

select count(*)
from   dosije
where indeks/10000=2013;

-- Primetiti da moze

select 'Ukupan broj upisanih studenata skolske 2013/2014 godine je',
       count(*)
from   dosije
where indeks/10000=2013;

--ali ne moze
select indeks,
       count(*)
from   dosije
where indeks/10000=2013;

--ne moze ni  

select indeks/10000,
       count(*)
from   dosije
where indeks/10000=2013;


--ali moze

select indeks,
       count(*)
from   dosije
where indeks/10000=2013
group by indeks;


--Primer: Prikazati datume kada su polagani ispiti i za svaki datum ukupan broj studenata koji su tog datuma polagali neki od ispita

select datum_ispita,count(*) as "Broj studenata koji su polagali"
from   ispit
group  by datum_ispita;


--Primer: Prikazati ime i prezime studenta i ukupan broj bodova koje je student do sada sakupio.

select ime as "Ime", prezime as "Prezime", 
       sum(p.bodovi) as "Položio bodova"
from   dosije d join ispit i on  d.indeks=i.indeks
       join predmet p on i.id_predmeta=p.id_predmeta
where  ocena>5
group  by ime,prezime;


--Primer: Naci stvarnu duzinu januarskog ispitnog roka 2015. godine, tj. interval od kada do kada su polagani ispiti u tom roku.

select 'Ispiti u januarskom ispitnom roku 2015. godine su polagani od',
       min(datum_ispita),' do ',max(datum_ispita)
from   ispit
where  godina_roka=2015
and    oznaka_roka='jan';

--Primer: Ispitati da li postoji korelacija izmedju broja indeksa studenta i  ocene koju je dobio na ispitu.

select correlation(indeks,ocena)
from ispit;


--Primer: Za svakog studenta prikazati ime, prezime, broj indeksa, najmanju ocenu, najvecu ocenu, prosecnu ocenu i standardnu devijaciju 
--ocena koje je dobio na ispitima u skolskoj 2013/2014, ukljucujuci i ispite koje nije polozio. 

select ime, prezime, a.indeks,
       max(ocena) as "Najveca ocena",
       min(ocena) as "Najmanja ocena", 
       avg(ocena) as "Prosecna ocena",
       avg(ocena*1.0) as "Prosek ocena*1.0",
       dec(avg(ocena),7,2) as "dec(prosek ocena),7,2)",
       round(avg(dec(ocena,7,2)),2) as "Zaokruzena prosecna decimalna vrednost ocene" ,
       dec(round(avg(dec(ocena,7,4)),2),7,2) as "Zaokruzena prosecna decimalna vrednost ocene prikazana na dve decimale" ,       
       stddev(ocena) as "Stddev(ocena)"
from   dosije a join ispit b on  a.indeks=b.indeks
group  by ime,prezime,a.indeks;


--Primer: Neka je student obavezan da se pojavi na predavanjima najkasnije 
--4 dana posle zavrsetka poslednjeg ispita u nekom ispitnom roku. 
--Prikazati, za svaki rok u 2015. godini koji su bili datumi do kada je
--student mogao da ne dolazi na aktivnosti drzane posle tog ispitnog roka. 
--Ukoliko datum polaganja ispita za bar jedan ispit u roku nije poznat, u tom 
-- slucaju prikazati 
--'Nije mogao da odsustvuje'. Izvestaj urediti po ispitnim rokovima u 
--obrnutom redosledu i brojevima indeksa


select oznaka_roka,
       indeks,
       char(max(datum_ispita + 3 days)) 
       -- mogao je da odsustvuje 3 dana jer je morao da se 
       -- pojavi najaksnije 4 dana posle poslednjeg ispita
from   ispit i1
where  godina_roka=2015
and    not exists (select * 
                   from ispit i2
                   where i2.indeks=i1.indeks and 
                         i2.oznaka_roka=i1.oznaka_roka and
                         godina_roka=2015 and
                         datum_ispita is  null)
group  by oznaka_roka,indeks

union all 

select oznaka_roka,indeks,'Nije mogao da odsustvuje'
from   ispit
where  godina_roka=2015
and    datum_ispita is null
group  by oznaka_roka,indeks
order by indeks, oznaka_roka;


--Primer: Prikazati imena i prezimena studenata koji su polozili bar dva ispita sa
--ocenom vecom od 7. Izvestaj urediti po imenima i prezimenima studenata

select ime,prezime,a.indeks
from   dosije a, ispit b
where  a.indeks=b.indeks
and    ocena>7
group  by ime,prezime,a.indeks
having count(*)>1
order by ime,prezime;


--Primer: Prikazati broj indeksa, ime i prezime studenta koji je u 2015 godini imao prosecnu ocenu na ispitima vecu od 7.5.

select indeks,ime,prezime
from   dosije a
where  7.5 < (
              select avg(ocena*1.0)
              from   ispit b
              where  b.indeks=a.indeks
              and    godina_roka=2015
             ); 

             
--Primer: Prikazati uredjene cetrvorke (naziv_predmeta_1, naziv_roka_1, naziv_predmeta_2, naziv_roka_2)
--tako da vazi da je predmet sa nazivom naziv_predmeta_1 u ispitnom roku naziv_roka_1 polozilo 
--vise studenata nego predmet sa nazivom naziv_predmeta_2 u ispitnom roku naziv_roka_2 
select a.naziv, c.naziv, b.naziv, d.naziv
from   predmet a, predmet b, ispitni_rok c, ispitni_rok d
where  (select count(*)
        from   ispit 
        where  id_predmeta=a.id_predmeta
        and    oznaka_roka=c.oznaka_roka
        and    godina_roka=c.godina_roka
        and    ocena>5
        having count(*)>0
       )
       > 
       (select count(*)
        from   ispit 
        where  id_predmeta=b.id_predmeta
        and    oznaka_roka=d.oznaka_roka
        and    godina_roka=d.godina_roka
        and    ocena>5
        having count(*)>0
       );
       
       
--Primer: Prikazati nisku koja sadrzi imena studenata uredjena prema godini njihovog rodjenja.

select listagg(ime,' ,') within group (order by datum_rodjenja)
from   dosije a;


-- *** WITH ***

--Primer: Izdvojiti parove imena i prezimena studenata cije su prosecne ocene vece od polovine prosecne ocene svih studenata u tabeli ispit.


select A.ime,A.prezime, B.ime, B.prezime, C.prosek
from
    (select x.indeks,ime, prezime, avg(ocena*1.0) as prosek
     from   ispit x, dosije y 
     where  x.indeks=y.indeks
     group  by x.indeks, ime, prezime
    ) as    A,
    (select x.indeks,ime, prezime, avg(ocena*1.0) as prosek
     from   ispit x, dosije y 
     where  x.indeks=y.indeks
     group  by x.indeks, ime, prezime
    ) as    B,    
    (select avg(ocena*1.0) as prosek
     from   ispit
    ) as    C
where  A.prosek>C.prosek/2
and    B.prosek>C.prosek/2
and    A.indeks  <B.indeks;

--ili



with  student_prosek(indeks,ime,prezime,prosek) 
as
(select x.indeks,ime, prezime, avg(ocena*1.0) 
 from   ispit x, dosije y 
 where  x.indeks=y.indeks
 group  by x.indeks, ime, prezime
),  
prosek as
(select avg(ocena*1.0) as prosek
 from   ispit
) 
select A.ime,A.prezime, B.ime, B.prezime, C.prosek
from   student_prosek A, student_prosek B, prosek C
where  A.prosek>C.prosek/2
and    B.prosek>C.prosek/2
and    A.indeks  <B.indeks;

-- *** CASE ***

--Primer: Prikazati tabelu koja sadrzi spisak predmeta i identifikacije grupa kojoj ti predmeti pripadaju pri cemu predmeti cija sifra pocinje sa M
--pripadaju grupi Matematickih, sa P i R grupi racunarskih, a sa O grupi opsteobrazovnih predmeta. 
--Ostali predmeti pripadaju grupi predmeta sa identifikacijom 'Nepoznato'

select naziv, case substr(sifra,1,1)
               when 'M'  then 'Matematicki'
               when 'P'  then 'Racunarki'
               when 'O'  then 'Opsteobrazovni'
               when 'R'  then 'Racunarski'
               else           'Nepoznato'
             end      as "Grupe Predmeta"
from   predmet;



--Primer: Prikazati tabelu koja sadrži spisak imena i prezimena studenata i godine upisa.
--Ako je godina upisa tekuća ili prošla godina, prikazati trenutni prosek ocena; 
--U slucaju da je godina upisa za 6 manja od tekuće godine, prijaviti grešku.
--U svim ostalim slucajevima prikazati ukupan broj položenih ispita. 


with  student_prosek(indeks,ime,prezime,prosek) 
as
(select x.indeks,ime, prezime, avg(ocena*1.0) 
 from   ispit x, dosije y 
 where  x.indeks=y.indeks
 group  by x.indeks, ime, prezime
)
select ime, prezime, case 
               when (year(current_date)>= indeks/10000 - 1)  then prosek
               when (year(current_date)> indeks/10000+6)  then RAISE_ERROR('70014','Provera - da li je izgubljeno pravo 
                                                                                  na studiranje')
               else  (select count(*) from ispit where indeks=a.indeks and ocena>5)
            end as "Podatak"
from student_prosek a;	


-- *** Formiranje korisnicki definisane funkcije (UDF)***

--Primer: Napisati korisnički definisanu funkciju koja za predmet sa zadatim broj espb bodova kao argument 
-- računa cenu slušanja za samofinansirajuće studente. Cena jednog espb boda je 2000 rsd.

create function cena(espb_predmeta smallint)
returns float
return espb_predmeta*2000.0;

--primer upotrebe
values cena(10);


--Primer: Napisati funkciju koja za zadati id predmeta vraca broj studenata koji su
--taj predmet polozili

create function br_polozenih (id int)
returns integer
return select count(distinct indeks)  
       from ispit 
       where ocena>5 and id=id_predmeta;


select id_predmeta, br_polozenih(id_predmeta)
from predmet;

--Primer rezultata: 1001	7


--*** UNOS-AZURIRANJE-BRISANJE ***

--Primer: Uneti u tabelu predmet podatke o predmetu Razvoj softvera, koji ima id 4005, sifru R103 i 6 espb.
insert into predmet 
values (4005, 'R103', 'Razvoj softvera', 6);

--Primer: Uneti u tabelu predmet podatke o predmetu Uvod u relacione baze podataka,  koji ima id 4006, sifru R105 i 6 espb.

insert into predmet (sifra, naziv, id_predmeta)
values ('R105','Uvod u relacione baze podataka',  4006);
--greska: pokusaj dodele null vrednosti koloni koja ne moze da je sadrzi


insert into predmet (sifra, naziv, id_predmeta, bodovi)
values ('R105','Uvod u relacione baze podataka',  4006, 6);

--Primer: U tabeli ispit uneti podatke o polaganju ispita iz
--predmeta Razvoj softvera (id 4005) za student iz Beograda koji su
--polagani u poslednjem odrzanom roku i na kojima su studenti dobili 
--ocenu 9.

insert into ispit (indeks, id_predmeta, godina_roka, oznaka_roka, ocena)
with poslednji_rok as(
select distinct godina_roka, oznaka_roka
from ispit
where datum_ispita = (select max(datum_ispita)
                      from ispit))
select indeks, 4005, godina_roka, oznaka_roka, 9
from dosije, poslednji_rok
where mesto_rodjenja='Beograd';

--Primer: Azurirati ispite na kojima je polagan predmet Razvoj softvera (id 4005)
-- i postaviti da je dobijeni broj bodova 85.
update ispit
set bodovi = 85
where id_predmeta=4005;

--Primer: Azurirati ispite na kojima je polagan predmet 
-- Razvoj softvera (id 4005) od strane studenata koji su rodjeni
--pre vise od 20 godina i postaviti da je na tim ispitima 
--nepoznat broj dobijenih bodova i da su oni polagani 3 dana nakon
--posledjnjeg ispita u roku u kome su polagani.

update ispit as i
set (bodovi, datum_ispita) = 
    (null, (select max(datum_ispita)+ 3 days
            from ispit i1
            where i1.oznaka_roka=i.oznaka_roka
                  and i1.godina_roka=i.godina_roka))
where id_predmeta=4005
      and indeks in (select indeks
                     from dosije
                     where datum_rodjenja < current date - 20 years);
                     
--Primer: Obrisati sve ispite na kojima je polagan predmet 
-- Razvoj softvera (id 4005).
                     
delete from ispit
where id_predmeta=4005;

--Primer: Obrisati sve ispite na kojima je polagan predmet 
-- Razvoj softvera (id 4005) od strane studenata koji su rodjeni
--pre vise od 20 godina.                     

delete from ispit
where id_predmeta=4005
      and indeks in (select indeks
                     from dosije
                     where datum_rodjenja < current date - 20 years);