create table odeljenje1(
sifraodelj number(7,0),
grad varchar(20),
nazivodelj varchar(20),
constraint so_pk primary key(sifraodelj)
);

create table radni_status1(
sifrars number(7,0),
nazivrs varchar(20),
constraint srf_pk primary key(sifrars)
);

create table obrazovni_profil1(
sifraprof number(7,0),
stepenstrucnespreme varchar(20),
nazivprof varchar(20),
constraint sp_pk primary key(sifraprof)
);

create table radno_mesto1(
sifrarm number(7,0),
nazivrm varchar(20),
sifraprof number(7,0),
constraint srm_pk primary key(sifrarm),
constraint sp_fk foreign key(sifraprof) references obrazovni_profil1(sifraprof)
);

create table zaposleni1(
sifrazap number(7,0),
plata number(7,0),
datrodj date,
ime varchar(20),
prezime varchar(20),
mlb numeric(13,0),
premija numeric(10,0),
datzap date,
sifraprof number(7,0),
sifraodelj number(7,0),
sifrarukov number(7,0),
constraint sz_pk primary key(sifrazap),
constraint spf_fk foreign key(sifraprof) references obrazovni_profil1(sifraprof),
constraint so_fk foreign key(sifraodelj) references odeljenje1(sifraodelj),
constraint sr_fk foreign key(sifrarukov) references zaposleni1(sifrazap)
);

alter table zaposleni1 add pol varchar(20);

create table angazovanje1(
datod date,
datdo date,
sifrarm number(7,0),
sifrazap number(7,0),
sifrars number(7,0),
constraint do_pk primary key(datod, sifrarm, sifrazap),
constraint srm_fk foreign key(sifrarm) references radno_mesto1(sifrarm),
constraint sz_fk foreign key(sifrazap) references zaposleni1(sifrazap),
constraint srs_fk foreign key(sifrars) references radni_status1(sifrars)
);

alter table radni_status1 add constraint rs1 check(nazivrs in('V', 'VI', 'VII'));

alter table zaposleni1 add constraint pol1 check(pol in('M', 'Z'));

drop table angazovanje1;

drop table obrazovni_profil1;
drop table odeljenje1;
drop table radni_status1;
drop table radno_mesto1;
drop table zaposleni1;

select * from odeljenje;

select nazivrs from radni_status;

select distinct sifraodelj from zaposleni;

select ime, prezime, datrodj from zaposleni where pol = 'Z';

select sifraodelj, nazivodelj from odeljenje where grad = 'Nis' or grad = 'Novi Sad';

select sifraodelj, nazivodelj from odeljenje where not grad = 'Beograd';

select ime, prezime from zaposleni where plata between 30000 and 50000;

select sifrazap, ime, prezime, mlb, datrodj from zaposleni where sifraodelj = 10 and prezime like 'M%';

select ime, prezime from zaposleni where premija is not null;
select ime, prezime from zaposleni where premija is null;

select ime, prezime, plata, premija from zaposleni where sifraodelj = 40 order by plata;
select ime, prezime, plata, premija from zaposleni where sifraodelj=40 order by premija desc;

select prezime, length(prezime), instr(prezime, 'a') from zaposleni;

select ime, substr(ime, 1, 3), prezime, concat(ime, prezime), trim('c' from prezime) from zaposleni;

select prezime, round((sysdate - datzap)/7, 2) from zaposleni where sifraodelj = 10;

select prezime, datzap, round(months_between(sysdate, datzap),2), add_months(datzap, 6),
next_day(datzap, 'FRIDAY'), last_day(datzap) from zaposleni where sysdate - datzap > 10;

select sifrazap, ime, prezime, datzap from zaposleni where datzap + interval '10' year < sysdate;

select * from zaposleni where datzap + interval '12-6' year to month < sysdate;

select prezime, plata,
case when sifraodelj = 10 then plata*1.1
     when sifraodelj = 20 then plata*1.2
     when sifraodelj = 30 then plata*1.3
     else plata end as rezultat
from zaposleni;

select ime, prezime, length(ime), length(prezime), nullif(length(ime), length(prezime)) as duzine from zaposleni;

select prezime, coalesce(premija, plata) from zaposleni where sifraodelj = 30;

select prezime, to_char(datzap, 'DD/MM/YY') as MesecIGOdina from zaposleni;

select prezime, ime, to_char(plata, '99,999.00') as plata from zaposleni where sifraodelj = 30;

update radno_mesto set nazivrm = 'Dizajner'
where sifraprof = (select sifraprof from obrazovni_profil where nazivprof = 'Srednja SS');

update zaposleni set plata = plata+5000
where sifrarukov = (select sifrarukov from zaposleni where ime = 'Janko' and prezime = 'Jankovic');

select ime, prezime, round(premija/plata*100, 2) as rezultat from zaposleni
where datzap + interval '15-6' year to month < sysdate;

select ime, prezime, datrodj,
case when sign(extract(month from sysdate) - extract(month from datrodj)) = -1 then 'Nije prosao'
    when sign(extract(month from sysdate) - extract(month from datrodj)) = 0 and sign(extract(day from sysdate) - extract(day from datrodj)) = -1 then 'Nije prosao'
    else 'Prosao je'
    end
from zaposleni;

--PROVERITI

select ime, prezime, concat(substr(ime, 1, 1), substr(prezime, 1, 1)), to_char(datzap, 'dd.MM.yyyy') from zaposleni
where ime like '%mar%' or prezime like '%mar%' or ime like'Mar%' or prezime like 'Mar%';

create table mesto(
mestoid number(7,0),
naziv varchar(20),
brstanovnika number(10,0),
postanskibr number(7,0),
constraint mid_pk primary key(mestoid)
);

create table student(
studentid number(7,0),
prosek decimal(7,2),
pol varchar(20),
godine number(7,0),
datupisa date,
visinaskolarine number(7,0),
mestoid number(7,0),
constraint sid_pk primary key(studentid),
constraint mid_fk foreign key(mestoid) references mesto(mestoid)
);

alter table student add constraint pol_ch check(pol in ('Z', 'M'));

insert into mesto values(1, 'Krusevac', 7000, 123);
insert into mesto values(2,'Beograd', 100000, 456);

insert into student values(11, 9.75, 'Z', 21, '17-JUN-2015', 15000, 1);
insert into student values(22, 9.75, 'M', 22, '17-JUN-2016', 15000, 2);

select * from student where prosek > 9;

insert into student values(33, 7.75, 'M', 22, '17-JUN-2016', 15000, 1);

alter table student add ime varchar(20);
alter table student add prezime varchar(20);

update student set ime = 'Ksenija' where studentid = 11;
update student set prezime = 'Grujic' where studentid = 11;

update student set ime = 'Branko' where studentid = 22;
update student set prezime = 'Miladinovic' where studentid = 22;
update student set ime = 'Marko' where studentid = 33;
update student set prezime = 'Markovic' where studentid = 33;

select ime, prezime from student where ime like 'M%' and length(prezime) <= 8 and prosek < 8;

select * from student where ime like '[^a e i o u]%[a e i o u]';

select * from mesto where brstanovnika between 3000000 and 800000000 or postanskibr in(123, 567, 987);

--Prikazati ime, prezime i prosecnu ocenu studenata koji su upisali fakultet 2015. godine.
select ime, prezime, prosek from student where extract(year from datupisa) = 2016;