--1 Zadatak
select b.naziv,m.ime,m.prezime
from sastav_benda s
	join bendovi b on s.idBenda = b.idBenda
		join muzicari m on m.idMuzicara = s.idMuzicara
where s.datum2 is null


--2 Zadatak
select p.ime,SUM(k.cena)
from karte k
	join posetioci p on p.idPosetioca = k.idPosetioca
group by k.idPosetioca,p.ime


--3 Zadatak
select k.idFestivala,MAX(k.cena)
from karte k
where k.cena not in (
	select MAX(k1.cena)
	from karte k1
	group by k1.idFestivala
)
group by k.idFestivala


--4 Zadatak
create view GODINE_SVIRANJA_ZA_MUZICARE as
select *,case
when datum2 is null then DATEDIFF(year,datum1,getdate())
else DATEDIFF(year,datum1,datum2)
end as br_godina
from sastav_benda


select idMuzicara,idBenda,case
	when br_godina < 5 then 'Guster'
	when br_godina between 5 and 10 then 'Dzomba'
	else 'Stara kajla'
end as Status
from GODINE_SVIRANJA_ZA_MUZICARE

--5 Zadatak
create view zarada_i_br as
select l.idFestivala,l.godina,sum(k.cena) as zarada,count(k.idPosetioca) as br_posetilaca
from lineup_festivala l
	join karte k on l.idFestivala = k.idFestivala  and l.godina = k.godina
where l.dan >= 2
group by l.idFestivala,l.godina



select z.idFestivala,z.godina,(select count(*)+1
								from zarada_i_br z1
								where z.godina = z1.godina
								and z.zarada < z1.zarada
								or(z.zarada = z1.zarada and z.br_posetilaca < z1.br_posetilaca)

								) as rang
from zarada_i_br z
order by z.godina desc

--6 Zadatak
create view tabelica as
select distinct sb1.idBenda,count(*)+1 as br
from sastav_benda sb1 join sastav_benda sb2 on
					sb1.idBenda = sb2.idBenda and sb1.idMuzicara <> sb2.idMuzicara
					and sb1.datum1 >= sb2.datum1 and (
						sb1.datum1 <= sb2.datum2 or sb2.datum2 is null)
group by sb1.idBenda,sb1.datum1


select idBenda
from tabelica
where br in (select max(br) from tabelica)

--7 Zadatak