-- 1. Ispišite prosječno trajanje svih filmova.
SELECT AVG(Trajanje) N'Prosječno trajanje filmova' FROM Film
GO

-- 2. Ispišite prosječno trajanje filmova žanra SF.
SELECT AVG(Trajanje) N'Prosječno trajanje filmova žanra SF' 
	FROM Film 
	INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
		WHERE Zanr.Naziv = 'SF'
GO

-- 3. Ispišite u kojim sve mjesecima su posuđivani filmovi.
SELECT DISTINCT
	MONTH(DatumPosudbe) AS N'Mjeseci u kojima su posuđivani filmovi'
	FROM Posudba
GO

-- 4. Ispišite koliko filmova je posuđeno u travnju.
SELECT 
	COUNT(*) AS N'Broj filmova posuđenih u travnju'
	FROM Posudba
		WHERE MONTH(DatumPosudbe)=4
GO

-- Zad 1: Vratite broj svih proizvoda.

select count(*) as UkupanBrojProizvoda
from proizvod as p

-- Zad 2: Vratite broj proizvoda koji imaju definiranu boju.

select count(*) as UkupanBrojProizvoda
from proizvod as p
where p.boja is not null

-- Zad 3: Vratite najvišu cijenu proizvoda.

select max(p.CijenaBezPDV) as NajvišaCijena
from proizvod as p

-- Zad 4: Vratite prosječnu cijenu proizvoda iz potkategorije 16.

select avg(p.CijenaBezPDV) as ProsječnaCijena
from proizvod as p
where p.PotkategorijaID=16

-- Zad 5: Vratite datume najstarijeg i najnovijeg računa izdanog kupcu 131.

select min(r.DatumIzdavanja) as NajstarijiRačun, max(r.DatumIzdavanja) as NajnovijiRačun
from racun as r
where r.KupacID=131


--Zad 1
-- Ispišite koliko filmova je snimljeno na koji medij.
SELECT 
	Medij.Naziv AS 'Medij',
	COUNT(Medij.Naziv) AS 'Broj filmova'
	FROM Film 
	INNER JOIN Medij ON Film.MedijID = Medij.ID
	GROUP BY Medij.Naziv

--Zad 2
-- Ispišite prosječno trajanje filmova koji su žanra SF ili Drama.
SELECT 
	Zanr.Naziv AS N'Žanr',
	AVG(Film.Trajanje) AS 'Prosječno trajanje filmova'
	FROM Film 
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
		WHERE Zanr.Naziv = 'SF' OR Zanr.Naziv = 'Drama'
	GROUP BY Zanr.Naziv

--Zad 3
-- Ispišite broj posuđenih filmova u mjesecu ožujku, složeno po žanru.
SELECT	Zanr.Naziv N'Žanr', 
		COUNT(*) N'Broj posuđenih filmova'
	FROM Film 
		INNER JOIN Posudba ON Film.ID = Posudba.FilmID
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
	WHERE MONTH(Posudba.DatumPosudbe) = 3
	GROUP BY Zanr.Naziv


	   
-- Zad 1: Ispišite sve boje proizvoda i pokraj svake napišite koliko proizvoda ima tu boju.

select p.boja, count(*) as KolikoProizvodaTeBoje
from proizvod as p
group by p.Boja

-- Zad 2: Promijenite prethodni upit tako da sortirate padajuće prema broju proizvoda.

select p.boja, count(*) as KolikoProizvodaTeBoje
from proizvod as p
group by p.Boja
order by KolikoProizvodaTeBoje desc

-- Zad 3: Promijenite prethodni upit tako da isključite nedefiniranu boju.

select p.boja, count(*) as KolikoProizvodaTeBoje
from proizvod as p
group by p.Boja
having p.boja is not null

-- Zad 4: Ispišite koliko proizvoda svake boje ima u svakoj od potkategorija. Sortirajte prema potkategoriji i prema boji.

select pk.Naziv, p.Boja, count(*) as KolikoProizvodaTeBoje
from Proizvod as p
inner join Potkategorija as pk
on p.PotkategorijaID=pk.IDPotkategorija
group by pk.naziv, p.Boja
order by pk.Naziv, p.Boja

-- Zad 5: Promijenite prethodni upit tako da ispišete 10 kategorija i boja s najviše proizvoda.

select top 10 p.PotkategorijaID, p.Boja, count(*) as KolikoProizvodaTeBoje
from Proizvod as p
group by p.PotkategorijaID, p.Boja
order by KolikoProizvodaTeBoje desc

-- Zad 6: Promijenite prethodni upit tako da umjesto ID potkategorije ispišete njen naziv.

select top 10 pk.Naziv, p.Boja, count(*) as KolikoProizvodaTeBoje
from Proizvod as p
left join Potkategorija as pk
on p.PotkategorijaID=pk.IDPotkategorija
group by pk.naziv, p.Boja
order by KolikoProizvodaTeBoje desc


-- Zad 7: Ispišite nazive svih kategorija i pokraj svake napišite koliko potkategorija je u njoj.

select k.Naziv, count(pk.IDPotkategorija) as KolikoPotkategorijaUKategoriji
from Kategorija as k
inner join Potkategorija as pk
on pk.KategorijaID=k.IDKategorija
group by k.Naziv

-- Zad 8: Ispišite nazive svih kategorija i pokraj svake napišite koliko proizvoda je u njoj.

select k.Naziv, count(p.IDProizvod) as KolikoProizvodaUKategoriji
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

-- Zad 9: Ispišite sve različite cijene proizvoda i napišite koliko proizvoda ima svaku cijenu.

select p.CijenaBezPDV, count(*) as KolikoProizvodaSTomCijenom
from proizvod as p
group by p.CijenaBezPDV
order by 1

-- Zad 10: Ispišite koliko je računa izdano koje godine.

select year(r.DatumIzdavanja) as MjesecRačuna, count(*) as KolikoRačuna
from Racun as r
group by year(r.DatumIzdavanja)
order by 1

-- Zad 11: Ispišite brojeve svih račune izdane kupcu 377 i pokraj svakog ispišite ukupnu cijenu po svim stavkama.

select r.BrojRacuna, sum(s.UkupnaCijena) as UkupnaCijenaPoRačunu
from racun as r
inner join stavka as s
on s.RacunID=r.IDRacun
where r.KupacID=377
group by r.BrojRacuna


--Zad 5
-- Ispišite popis mjesta u koja su posuđivani filmovi te koliko je filmova posuđeno u koje mjesto.
SELECT
	Mjesto.Naziv N'Mjesto', 
	COUNT(Posudba.FilmID) N'Broj posuđenih filmova'
	FROM Mjesto
		INNER JOIN Clan ON Mjesto.ID = Clan.MjestoID
			INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Mjesto.Naziv
				
--Zad 6
-- Ispišite samo mjesta u koja je posuđeno više od jednog filma.
SELECT
	Mjesto.Naziv N'Mjesto', 
	COUNT(Posudba.FilmID) N'Broj posuđenih filmova'
	FROM Mjesto
		INNER JOIN Clan ON Mjesto.ID = Clan.MjestoID
			INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Mjesto.Naziv
		HAVING COUNT(Posudba.FilmID) > 1

--Zad 7
-- Ispišite popis žanrova kod kojih je maksimalno trajanje filma (strogo) veće od 138 minuta.
SELECT
	Zanr.Naziv, 
	max(Film.Trajanje)
	FROM Film
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
	GROUP BY
		Zanr.Naziv
--		Film.Trajanje 
			HAVING MAX(Film.Trajanje) > 138	
		
--Zad 8
-- Ispišite popis žanrova kojima je prosječno trajanje veće za barem 2% od minimalnog trajanja filma u tom žanru.
SELECT
	Zanr.Naziv, 
	AVG(Trajanje) 'Prosječno trajanje', 
	MIN(Trajanje) 'Min. tr.',
	MAX(Trajanje)'Max. tr.', 
	1.02 * MIN(Trajanje) 'Min. tr. uvećano za 2%',
	COUNT(*)
	FROM Film
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
	GROUP BY Zanr.Naziv
		HAVING AVG(Trajanje) > (1.02 * MIN(Trajanje))

-- Zad 12: Ispišite sve boje koje imaju više od 40 proizvoda.

select p.Boja, count(*) as KolikoProizvoda
from Proizvod as p
group by p.Boja
having count(*)>40

-- Zad 13: Ispišite nazive svih potkategorija koje sadržavaju više od 10 proizvoda.

select pk.Naziv, count(*) as KolikoProizvoda
from Potkategorija as pk
group by pk.Naziv
having count(*)>10

-- Zad 14: Ispišite ukupno zarađene iznose i broj prodanih primjeraka za svaki od ikad prodanih proizvoda.

select p.Naziv, sum(s.UkupnaCijena) as Zarađeno, sum(s.Kolicina) as ProdanoPrimjeraka
from Proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
group by p.Naziv

-- Zad 15: Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2000 primjeraka.

select p.Naziv, sum(s.UkupnaCijena) as Zarađeno, sum(s.Kolicina) as ProdanoPrimjeraka
from Proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
group by p.Naziv
having sum(s.Kolicina)>2000

-- Zad 16: Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2.000 primjeraka ili je zaradio više od 2.000.000 dolara.

select p.Naziv, sum(s.UkupnaCijena) as Zarađeno, sum(s.Kolicina) as ProdanoPrimjeraka
from Proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
group by p.Naziv
having sum(s.Kolicina)>2000
or sum(s.UkupnaCijena)>2000000
