--------------------- ZAD1 ----------------------
select substring(ime, 1, 1) + '.' + substring(prezime, 1, 1) + '.' as 'inicijali', year(datRod) as 'godina_rodjenja'
from student;

select *
from student
order by datRod asc;

select top 1 ime, prezime
from student
where spol = 'F'
order by datRod asc;

--------------------- ZAD2 ----------------------
select count(mbr) as 'ukupno_studenata'
from student;

select count(distinct pbrPreb) as 'razlicita mjesta prebivalista'
from student;

--------------------- ZAD3 ----------------------
select sifPredmeta, avg(cast(ocjena as float)) as 'prosjek predmeta'
from ispit
where ocjena > 1
group by sifPredmeta;

--------------------- ZAD4 ----------------------
select ime, prezime, avg(ocjena) as 'prosjek'
from student, ispit
where ocjena > 1 and mbrStud = mbr
group by ime, prezime
order by prosjek desc;

select mbrStud, avg(cast(ocjena as float)) as 'prosjek > 2.5'
from ispit
where ocjena > 1
group by mbrStud
having avg(ispit.ocjena) > 2.5

--------------------- ZAD5 ----------------------
create view moj_pogled as
select s.ime, s.prezime, p.naziv, i.mbrStud, i.datIspita, i.ocjena
from student s, predmet p, ispit i
where i.mbrStud = s.mbr and p.sifra = i.sifPredmeta;

select * from moj_pogled;