-- Primjer 1
SELECT Posudba.FilmID
	FROM Posudba 
	WHERE ClanID = 1

-- Nakon toga, izabiremo filmove koje smo dobili tim popisom (2, 1, 3, 4, 5):
SELECT Naziv 
	FROM Film
	WHERE ID = 2 OR ID = 1 OR ID = 3 OR ID = 4 OR ID = 5

-- Ovo postaje jako komplicirano za napisati što više filmova član posudi. Stoga, ovo možemo napisati korištenjem operatora IN:
SELECT Naziv 
	FROM Film
	WHERE ID IN (2, 1, 3, 4, 5)

-- Primjer 2
SELECT Naziv 
	FROM Film
	WHERE ID IN 
		(SELECT Posudba.FilmID
			FROM Posudba WHERE ClanID = 1)

-- Primjer 3
SELECT Naziv 
	FROM Film
	WHERE ID NOT IN 
	(
		SELECT Film.ID FROM Film 
			INNER JOIN Medij ON Film.MedijID = Medij.ID
			WHERE Medij.Naziv = 'CD'
	)

-- Primjer 4
SELECT Clan.ImePrezime 
	FROM Clan
	WHERE ID NOT IN 
	(
		SELECT Posudba.ClanID FROM Posudba
	)

	
-- Zadatak 1
SELECT * FROM Film WHERE Film.ID IN 
(	
	SELECT
		Posudba.FilmID
		FROM Posudba
)

-- Zadatak 2
SELECT * FROM Clan WHERE  Clan.ID IN
(
	SELECT	
		Posudba.ClanID
	FROM Posudba
)

--Zadatak 3
SELECT Clan.ImePrezime FROM Clan WHERE Clan.ID IN
(
	SELECT 
		p.ClanID
	FROM Posudba as p
	INNER JOIN Film as f
		ON p.FilmID = f.ID
	INNER JOIN Medij as m
		ON f.MedijID = m.ID
	WHERE m.Naziv = 'DVD'
)

-- ILI
SELECT Clan.ImePrezime
	FROM Clan
	WHERE Clan.ID IN 
	(
		SELECT Posudba.ClanID From Posudba WHERE Posudba.FilmID IN
		(
			SELECT Film.ID FROM Film WHERE Film.MedijID IN
			(
				SELECT Medij.ID FROM Medij WHERE Medij.Naziv = 'DVD'
			)
		)
	)

-- Zadatak 4
SELECT * FROM Film WHERE Film.ID IN 
(	
	SELECT
		Posudba.FilmID
		FROM Posudba
		WHERE Posudba.ClanID > 2
)



-- Primjer 5
SELECT 
	Film.Naziv, Trajanje
	FROM Film
	WHERE Trajanje > 
		(SELECT AVG(Trajanje) FROM Film)



-- Zadatak 5
SELECT Clan.ImePrezime,
	COUNT(Posudba.FilmID) N'Broj filmova'
	FROM Clan
	INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Clan.ImePrezime
	HAVING COUNT(*) > 
	(	
		SELECT COUNT(*) 
			FROM Posudba
			WHERE Posudba.ClanID = 5
	)

-- Zadatak 6 v1
SELECT Clan.ImePrezime,
	COUNT(Posudba.FilmID) N'Broj filmova'
	FROM Clan
	INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Clan.ImePrezime
	HAVING COUNT(*) > 
	(	
		SELECT AVG(broj_filmova_po_clanu)
			FROM
			(
				SELECT COUNT(Posudba.FilmID) AS broj_filmova_po_clanu
					FROM Clan
					INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
					GROUP BY Clan.ImePrezime
			) AS temp
	)


-- Zadatak 6 v2

select 
	Posudba.ClanID,
	COUNT(Posudba.ClanID) as BrojPosudbi
from Posudba
group by Posudba.ClanID
having COUNT(Posudba.ClanID) >
(
	select 
	COUNT(*)/COUNT(DISTINCT Posudba.ClanID) as ProsjekPosudbi
	from Posudba
)

-- Primjer 6	
SELECT 
	Film.Naziv, 
	(
		SELECT COUNT(*) FROM Posudba 
			WHERE Posudba.FilmID = Film.ID
	) AS 'Broj posudbi'
	FROM Film
	ORDER BY 'Broj posudbi' DESC

	

-- Zadatak 7	
SELECT 
	Film.Naziv, 
	(
		SELECT COUNT(*) FROM Posudba 
			WHERE Posudba.FilmID = Film.ID
	) AS 'Broj posudbi'
	FROM Film
	ORDER BY 'Broj posudbi'

-- Zadatak 8
SELECT 
	Mjesto.Naziv,
	COUNT(Mjesto.Naziv) AS 'Broj posuđenih filmova'
	FROM Posudba
		INNER JOIN Clan ON Clan.ID = Posudba.ClanID
		INNER JOIN Mjesto ON Mjesto.ID = Clan.MjestoID
	GROUP BY Mjesto.Naziv

-- Zadatak 9
SELECT Clan.ImePrezime,
	COUNT(Posudba.FilmID) N'Broj filmova'
	FROM Clan
	LEFT JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Clan.ImePrezime

-- Zadatak 10
SELECT Medij.Naziv,
	COUNT(*) N'Broj filmova'
	FROM Medij
	LEFT JOIN Film ON Film.MedijID = Medij.ID
	GROUP BY Medij.Naziv

-- Zadatak 11
SELECT Zanr.Naziv,
	COUNT(DISTINCT Posudba.FilmID) N'Broj filmova'
	FROM Zanr
	LEFT JOIN Film ON Film.ZanrID = Zanr.ID
	LEFT JOIN Posudba ON Posudba.FilmID = Film.ID
	GROUP BY Zanr.Naziv

-- Zadatak 12
SELECT Zanr.Naziv,
	COUNT(DISTINCT Clan.ID) N'Broj članova koji su posudili film tog žanra'
	FROM Zanr
	LEFT JOIN Film ON Film.ZanrID = Zanr.ID
	LEFT JOIN Posudba ON Posudba.FilmID = Film.ID
	LEFT JOIN Clan ON Posudba.ClanID = Clan.ID
	GROUP BY Zanr.Naziv


-- ZADATAK 8
-- Ispišite popis mjesta u koja su posuđivani filmovi 
-- te uz svako mjesto ispišite i broj posuđenih filmova.

select m.Naziv, 
(
	select count(*) 
	from posudba as p
	where p.ClanID in
	(
		select c.ID 
		from clan as c
		where c.MjestoID=m.ID
	)

) as BrojPosudbi
from mjesto as m
where m.id in
(
	select c.MjestoID
	from clan as c
	where c.id in
	(
		select p.ClanID
		from posudba as p
	)
)


-- ZADATAK 9
-- Ispišite popis članova te uz svakoga koliko je posudbi napravio.
select c.ImePrezime, 
(
	select count(*) from Posudba as p where p.ClanID=c.ID
) as BrojPosudbi
from clan as c

-- ZADATAK 10
-- Ispišite popis medija te uz svaki medij koliko filmova je snimljeno na takav medij.
select m.Naziv, 
(
	select count(*) from film as f where f.MedijID=m.ID
) as BrojFilmova
from medij as m

-- ZADATAK 11
-- Ispišite popis žanrova te uz svaki žanr koliko je filmova tog žanra posuđeno.
select z.Naziv, 
(
	select count(*) from film as f where f.ZanrID=z.ID
) as BrojFilmova
from zanr as z

-- ZADATAK 12
-- Ispišite popis žanrova te uz svaki žanr koliko članova je posudilo film tog žanra.
select z.Naziv,
(
	select count(*)
	from film as f
	inner join posudba as p
	on f.ID=p.FilmID
	where f.ZanrID=z.ID
) as BrojPosudbi
from zanr as z


-- 1. Ispišite sve potkategorije i za svaku ispišite broj proizvoda u njoj.

select pk.Naziv,
	(
		select count(*)
		from proizvod as p
		where p.PotkategorijaID=pk.IDPotkategorija
	) as BrojProizvoda
from Potkategorija as pk
order by BrojProizvoda desc


-- 2. Riješite prethodni zadatak pomoću spajanja.

select pk.Naziv, count(*) as BrojProizvoda
from Potkategorija as pk
inner join proizvod as p
on p.PotkategorijaID=pk.IDPotkategorija
group by pk.naziv
order by BrojProizvoda desc


-- 3. Ispišite sve kategorije i za svaku ispišite broj proizvoda u njoj.

-- spajanjima
select k.Naziv, count(*) as BrojProizvoda
from kategorija as k
inner join Potkategorija as pk
on pk.KategorijaID=k.IDKategorija
inner join Proizvod as p
on p.PotkategorijaID=pk.IDPotkategorija
group by k.Naziv
order by BrojProizvoda desc

-- kombinacijom spajanja i podupita
select k.naziv,
(
	select count(*)
	from Potkategorija as pk
	inner join proizvod as p
	on p.PotkategorijaID=pk.IDPotkategorija
	where pk.KategorijaID=k.IDKategorija
) as BrojProizvoda
from kategorija as k
order by BrojProizvoda desc

-- isključivo podupitima
select k.naziv,
(
	select count(*)
	from proizvod as p
	where p.PotkategorijaID IN
		(
			select pk.IDPotkategorija 
			from Potkategorija as pk
			where pk.KategorijaID=k.IDKategorija
		)
) as BrojProizvoda
from kategorija as k
order by BrojProizvoda desc


-- 4. Ispišite sve proizvode i pokraj svakog ispišite zarađeni iznos, od najboljih prema lošijim.

select p.Naziv, 
(
	select sum(s.ukupnacijena)
	from stavka as s
	where s.ProizvodID=p.IDProizvod
) as Zarada
from proizvod as p
order by Zarada desc


-- 5. Dohvatite sve proizvode, uz svaki proizvod ispišite prosječnu cijenu svih proizvoda te razliku prosječne cijene svih proizvoda i cijene tog proizvoda. U obzir uzmite samo proizvode s cijenom većom od nule.

select p.naziv, p.cijenabezpdv,
(
	select avg(p1.CijenaBezPDV)
	from proizvod as p1
) as ProsječnaCijenaSvihProizvoda,
(
	select avg(p1.CijenaBezPDV)
	from proizvod as p1
)-p.CijenaBezPDV as Razlika
from proizvod as p
where p.CijenaBezPDV>0


-- 6. Dohvatite imena i prezimena 5 komercijalista koji su izdali najviše računa.

-- spajanjima
select top 5 kom.ime, kom.prezime, count(*) as BrojIzdanihRačuna
from racun as r
inner join Komercijalist as kom
on kom.IDKomercijalist=r.KomercijalistID
group by kom.ime, kom.Prezime
order by BrojIzdanihRačuna desc

-- podupitom
select top 5 kom.Ime, kom.Prezime,
(
	select count(*)
	from racun as r
	where r.KomercijalistID=kom.IDKomercijalist
) as BrojIzdanihRačuna
from Komercijalist as kom
order by BrojIzdanihRačuna desc


-- 7. Dohvatite imena i prezimena 5 najboljih komercijalista, te uz svakog dohvatite i iznos prodane robe.

-- spajanjima
select top 5 kom.ime, kom.prezime, count(distinct r.IDRacun) as BrojIzdanihRačuna, SUM(s.ukupnacijena) as UkupanIznos
from racun as r
inner join Komercijalist as kom
on kom.IDKomercijalist=r.KomercijalistID
inner join stavka as s 
on s.RacunID=r.IDRacun
group by kom.ime, kom.Prezime
order by BrojIzdanihRačuna desc

-- podupitima
select top 5 kom.ime, kom.Prezime, 
(
	select count(*)
	from racun as r
	where kom.IDKomercijalist=r.KomercijalistID
) as BrojIzdanihRačuna,
(
	select SUM(s.UkupnaCijena)
	from stavka as s
	where s.RacunID IN
		(
			select r.IDRacun
			from racun as r
			where r.KomercijalistID=kom.IDKomercijalist
		)
) as Zarada
from Komercijalist as kom
order by BrojIzdanihRačuna desc


-- 8. Dohvatite sve boje proizvoda. Uz svaku boju pomoću podupita dohvatite broj proizvoda u toj boji.

-- spajanjima
select distinct p.Boja, COUNT(*) as KolikoProizvoda
from proizvod as p
group by p.boja
order by KolikoProizvoda desc

-- popdupitima
select distinct p.Boja, 
(
	select count(*)
	from Proizvod as pr
	where pr.Boja=p.Boja
) as KolikoProizvoda
from Proizvod as p
order by KolikoProizvoda desc


-- 9. Dohvatite imena i prezimena svih kupaca iz Frankfurta i uz svakog ispišite broj računa koje je platio karticom, od višeg prema nižem.

-- spajanjima
select k.ime, k.Prezime, COUNT(*) as BrojPlaćenihKarticama
from kupac as k
inner join grad as g
on g.IDGrad=k.GradID
inner join racun as r
on r.KupacID=k.IDKupac
where g.naziv='frankfurt'
and r.KreditnaKarticaID is not null
group by k.Ime, k.Prezime
order by BrojPlaćenihKarticama desc

-- podupitima
select k.ime, k.Prezime,
(
	select count(*)
	from racun as r
	where r.KupacID=k.IDKupac
	and
	r.KreditnaKarticaID is not null
) as BrojPlaćenihKarticama
from Kupac as k
where k.GradID=
(
	select g.IDGrad
	from grad as g
	where g.naziv='frankfurt'
)
AND
k.IDKupac IN
(
	select r.KupacID
	from racun as r
	where r.KreditnaKarticaID is not null
)
order by BrojPlaćenihKarticama desc


-- 10. Vratite proizvode s cijenom pet ili više puta većom od prosjeka.

select p.Naziv, p.CijenaBezPDV
from proizvod as p
where p.CijenaBezPDV > 5 * (
	select avg(pr.cijenabezpdv)
	from proizvod as pr
)


-- 11. Vratite proizvode koji su prodavani u količini manjoj od 5.

-- spajanjima
select distinct p.naziv 
from proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
where s.Kolicina<5

-- podupitom
select p.Naziv
from Proizvod as p
where exists 
(
	select * 
	from stavka as s
	where s.ProizvodID=p.IDProizvod
	and s.Kolicina<5
)


-- 12. Vratite sve proizvode koji nikad nisu prodani:

-- Pomoću spajanja

select p.Naziv
from Proizvod as p
left join stavka as s
on s.ProizvodID=p.IDProizvod
where s.IDStavka is null


-- Pomoću IN ili NOT IN

select p.Naziv
from proizvod as p
where p.IDProizvod not in
(
	select s.ProizvodID
	from stavka as s
)

-- Pomoću EXISTS ili NOT EXISTS

select p.Naziv
from Proizvod as p
where not exists
(
	select s.ProizvodID
	from stavka as s
	where s.ProizvodID=p.IDProizvod
)


-- 13. Vratite količinu zarađenog novca za svaku boju proizvoda.

select distinct 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 Zarada
from Proizvod as p


-- 14. Vratite količinu zarađenog novca za svaku boju proizvoda, ali samo za one boje koje su zaradile više od 20.000.000.

select distinct 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 Zarada
from Proizvod as p
where 
(
	select sum(s.UkupnaCijena)
	from stavka as s
	inner join proizvod as pr
	on pr.IDProizvod=s.ProizvodID
	where pr.Boja=p.Boja
)>20000000


-- 15. Vratiti sve proizvode koji imaju dodijeljenu potkategoriju i koji su prodani u količini većoj od 5000. 
-- Uz svaki proizvod vratiti prodanu količinu i naziv kategorije.

select p.Naziv,
(
	select sum(s.Kolicina)
	from stavka as s 
	where p.IDProizvod=s.ProizvodID
) as ProdanaKoličina,
(
	select kat.Naziv
	from Potkategorija as pk2
	inner join kategorija as kat
	on kat.IDKategorija=pk2.KategorijaID
	where p.PotkategorijaID=pk2.IDPotkategorija
) as Kategorija
from Proizvod as p
where p.PotkategorijaID in
(
	select pk.IDPotkategorija
	from Potkategorija as pk
)
and
(
	select sum(s.Kolicina)
	from stavka as s 
	where p.IDProizvod=s.ProizvodID
)>5000

---------------------------------------------------------------------------------