create table eh_nastavnici (id_nastavnik int primary key, ime_p varchar (30))
create table eh_kandidati (id_kandidat int primary key, ime_p varchar (30))
create table eh_glasovanje (id_kandidat int, id_nastavnik int, glas float, primary key (id_kandidat,id_nastavnik))

alter table eh_glasovanje add foreign key (id_kandidat) references eh_kandidati (id_kandidat)
alter table eh_glasovanje add foreign key (id_nastavnik) references eh_nastavnici (id_nastavnik)

insert into eh_nastavnici values (1, 'Vjeran Strahonja')
insert into eh_nastavnici values (2, 'Bozidar Klicek')
insert into eh_nastavnici values (3, 'Blazenka Divjak')
insert into eh_nastavnici values (4, 'Danijel Fabijanovic')
insert into eh_nastavnici values (5, 'Mario Klacmer')

insert into eh_kandidati values (1, 'Vjeran Strahonja')
insert into eh_kandidati values (2, 'Bozidar Klicek')
insert into eh_kandidati values (3, 'Blazenka Divjak')
insert into eh_kandidati values (4, 'Danijel Fabijanovic')
insert into eh_kandidati values (5, 'Mario Klacmer')

insert into eh_glasovanje values (1, 2, 1)
insert into eh_glasovanje values (1, 3, 1)
insert into eh_glasovanje values (1, 4, 1)
insert into eh_glasovanje values (1, 5, 1)
insert into eh_glasovanje values (2, 3, 1)
insert into eh_glasovanje values (2, 4, 1)
insert into eh_glasovanje values (2, 5, 1)
insert into eh_glasovanje values (2, 1, 1)
insert into eh_glasovanje values (3, 2, 1)
insert into eh_glasovanje values (3, 4, 1)
insert into eh_glasovanje values (3, 5, 1)
insert into eh_glasovanje values (4, 1, 1)
insert into eh_glasovanje values (4, 2, 1)
insert into eh_glasovanje values (4, 3, 1)
insert into eh_glasovanje values (5, 4, 1)
insert into eh_glasovanje values (5, 2, 1)
insert into eh_glasovanje values (5, 1, 1)

select * from eh_glasovanje
select * from eh_kandidati
select * from eh_nastavnici
drop table eh_glasovanje


select eh_kandidati.ime_p as 'Ime i prezime kandidata', sum (eh_glasovanje.glas) as 'Broj ostvarenih glasova'
from eh_kandidati,eh_glasovanje
where eh_glasovanje.id_kandidat=eh_kandidati.id_kandidat
group by eh_kandidati.ime_p order by 2 desc