
-- Zadatak 1

	select t.Naziv, t.Prosjecna_cijena, t.CijenaBezPDV
	, (t.Prosjecna_cijena-t.CijenaBezPDV) as Razlika_od_prosjeka
	from 
	(
		select p.Naziv,
		(
			select avg(pr.CijenaBezPDV) 
			from Proizvod as pr
		) as Prosjecna_cijena,
		p.CijenaBezPDV
		from proizvod as p
		where p.CijenaBezPDV>0
	) as t


	
-- Zadatak 2

	select top 5 k.Ime, k.Prezime,
	(
		select count(r.IDRacun) 
		from racun as r
		where r.KomercijalistID=k.IDKomercijalist
	) as Broj_izdanih_racuna
	from Komercijalist as k
	order by 3 desc



-- Zadatak 3

	select top 5 k.Ime, k.Prezime, 
	(
		select count(r.IDRacun) 
		from racun as r
		where r.KomercijalistID=k.IDKomercijalist
	) as Broj_izdanih_racuna,
	(
		select sum(s.CijenaPoKomadu*s.Kolicina)
		from racun as rc 
		inner join Stavka as s
		on rc.IDRacun=s.RacunID
		where rc.KomercijalistID=k.IDKomercijalist
	) as Iznos_prodane_robe
	from Komercijalist as k
	order by 3 desc



-- Zadatak 4

	select p.Boja,
	(
		select count(pr.IDProizvod)
		from Proizvod as pr	
		where pr.Boja=p.Boja
	) as Broj_proizvoda
	from Proizvod as p
	group by p.Boja
	having p.boja is not null

	

-- Zadatak 5

	select k.IDKupac, k.Ime, k.Prezime, k.GradID, r.BrojRacuna, r.KreditnaKarticaID, r.DatumIzdavanja
	from Kupac as k
	inner join Racun as r
		on r.KupacID=k.IDKupac
	where k.GradID = 
	(
		select g.IDGrad
		from Grad as g
		where g.Naziv='Frankfurt'
	)
	and r.KreditnaKarticaID is not null
	order by 1 desc, 5 desc



-- Zadatak 1

	select p.Boja,
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		inner join Proizvod as pr
			on pr.IDProizvod=s.ProizvodID
		where pr.Boja=p.Boja
	) as Zaradjeno
	from proizvod as p
	group by p.Boja
	having 
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		inner join Proizvod as pr
			on pr.IDProizvod=s.ProizvodID
		where pr.Boja=p.Boja
	) > 20000000


-- Zadatak 2

	select p.Naziv, p.IDProizvod
	,(
		select sum(s.Kolicina)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) as Prodana_kolicina,
	(
		select kt.Naziv
		from Potkategorija as pk
		inner join Kategorija as kt
			on kt.IDKategorija=pk.KategorijaID
		where p.PotkategorijaID=pk.IDPotkategorija
	) as Kategorija
	from proizvod as p
	where p.PotkategorijaID is not null
	and
	(
		select sum(s.Kolicina)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	)>5000



-- Zadatak 3

	select p.Naziv,
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) as Ukupno_zaradjeno,
	(
		select sum(st.kolicina)
		from stavka as st
		where st.ProizvodID=p.IDProizvod
	) as Broj_prodanih_primjeraka
	from proizvod as p
	where 
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) is not null
	order by 2 desc


-- Zadatak 4

	select p.Naziv,
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) as Ukupno_zaradjeno,
	(
		select sum(st.kolicina)
		from stavka as st
		where st.ProizvodID=p.IDProizvod
	) as Broj_prodanih_primjeraka
	from proizvod as p
	where 
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) is not null
	and
	(
		select sum(st.kolicina)
		from stavka as st
		where st.ProizvodID=p.IDProizvod
	)>2000
	order by 3 desc



-- Zadatak 5

	select p.Naziv,
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) as Ukupno_zaradjeno,
	(
		select sum(st.kolicina)
		from stavka as st
		where st.ProizvodID=p.IDProizvod
	) as Broj_prodanih_primjeraka
	from proizvod as p
	where 
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	) is not null
	and
	(
	(
		select sum(st.kolicina)
		from stavka as st
		where st.ProizvodID=p.IDProizvod
	)>2000
	or
	(
		select sum(s.UkupnaCijena)
		from stavka as s
		where s.ProizvodID=p.IDProizvod
	)>2000000
	)
	order by 3 desc