--Koliko je svake godine odigrano utakmica. (godina, broj_odigranih_utakmica)

select * from utakmice
select * from sezona

select s.godina, count(*)
from utakmice u join sezona s
on u.id_sezone = s.id
group by s.godina

--Igrač koji je sklopio ugovor na najduži period. 
--(id_igraca, period_u_danima)

go
create view trajanje_ugovora as
select id_igraca, DATEDIFF(day, datum1, datum2) period_u_danima
from ugovori
go


select t1.id_igraca, t1.period_u_danima
from trajanje_ugovora t1 left join
	 trajanje_ugovora t2 on 
	 t1.period_u_danima < t2.period_u_danima
where t2.id_igraca is null 

select *
from trajanje_ugovora
where period_u_danima = 
	(select max(period_u_danima) from trajanje_ugovora)


go
create view maximum_dana as
select max(period_u_danima) maximim
from trajanje_ugovora
go

select tu.* 
from (select max(period_u_danima) maximim
from trajanje_ugovora) md join trajanje_ugovora tu
on md.maximim = tu.period_u_danima

--Za svakog igrača koliko je postigao ukupno golova 
--računajući i autogolove (ugnježdeni).
--(ime, broj_golova) - nije dozvoljeno koristiti join mehanizam.

select * from igraci
select * from golovi

select * 
from timovi,igraci

select i.ime,count(*)
from igraci i join golovi g on i.id=g.id_igraca
group by i.id,i.ime



select *, (select count(*) 
			from golovi g 
			where g.id_igraca = i.id
		   ) broj_golova
from igraci i



go
create view utakmice_po_godinama as
select u.id_domacina, u.id_gosta, s.godina 
from utakmice u join sezona s
on u.id_sezone = s.id
go
/*
Spisak fudbalera koji su odigrali više od 3 utakmice u 
nekoj od sezona. (godina, id_igraca, broj_utakmica)
*/
select  upg.godina, ug.id_igraca, count(*) broj_utakmica
from utakmice_po_godinama upg join 
	ugovori ug on (upg.id_domacina = ug.id_tima or 
					upg.id_gosta = ug.id_tima)
					and
				   upg.godina between datepart(year,ug.datum1) 
										and datepart(year,ug.datum2)
group by upg.godina, ug.id_igraca 
having count(*) > 3
select * from utakmice
select * from ugovori
/*
1, 2, 2018, 1
1, 2, 2018, 3
1, 2, 2018, 5 
1, 3, 2019, 1
*/

--Rezultat svake utakmice.
--(id_utakmice, nazivTimaDomacina, nazivTimaGosta, rezultat)

/*id_utakmice, idTima, brojGolova
1,			1,        2
1,          2,        0
2,			1,		  2

*/

select * from utakmice
select * from golovi


--za svaki tim, koliko je postigao golova na utakmici
go
create view broj_golova as
select u.id id_utakmice, g.id_tima, count(g.id_tima) br_golova
from utakmice u left join golovi g
	on u.id = g.id_utakmice
group by u.id, g.id_tima
go

--(id_utakmice, nazivTimaDomacina, nazivTimaGosta, rezultat)
create view rezultati as
select  id,
		(select naziv from timovi where id = u.id_domacina) nazivTimaDomacina,
		(select naziv from timovi where id = u.id_gosta) nazivTimaGosta,
		(select br_golova from broj_golova 
			where id_utakmice = u.id and id_tima = u.id_domacina) broj_golova_domacina,
		(select br_golova from broj_golova 
			where id_utakmice = u.id and id_tima = u.id_gosta) broj_golova_gosta
from utakmice u

select id, nazivTimaDomacina, nazivTimaGosta,CONCAT( case 
												when broj_golova_domacina is null then 0
												else broj_golova_domacina
											  end, ':' , 
											  case 
												when broj_golova_gosta is null then 0
												else broj_golova_gosta
											  end) rezultat 
from rezultati

--(id_utakmice, nazivTimaDomacina, nazivTimaGosta, rezultat)