USE AdventureWorksOBP
GO

/* 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(pr.CijenaBezPDV) FROM Proizvod as pr
) AS N'Prosjecna Cijena', (
	SELECT AVG(pr.CijenaBezPDV) FROM Proizvod as pr
	)-p.CijenaBezPDV AS N'Razlika cijene'
FROM Proizvod as p
WHERE p.CijenaBezPDV > 0


/* Dohvatite imena i prezimena 5 komercijalista koji su izdali najviše računa */

SELECT TOP 5 k.Ime, k.Prezime, (
	SELECT COUNT(*) FROM Racun as r
	WHERE r.KomercijalistID = k.IDKomercijalist
) AS BrojIzdanihRacuna FROM Komercijalist as k
ORDER BY BrojIzdanihRacuna DESC

/* Dohvatite imena i prezimena 5 najboljih komercijalista gledano po broju izdanih računa te uz svakog 
dohvatite i iznos prodane robe. */

SELECT TOP 5 k.Ime, k.Prezime, (
	SELECT COUNT(*) FROM Racun AS r
	WHERE r.KomercijalistID = k.IDKomercijalist
) AS BrojIzdanihRacuna, (
	SELECT SUM(s.UkupnaCijena) FROM Stavka AS s
	INNER JOIN Racun as r ON r.IDRacun = s.RacunID
	WHERE r.KomercijalistID = k.IDKomercijalist
) AS UkupnaZarada FROM Komercijalist AS k
ORDER BY BrojIzdanihRacuna DESC

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

SELECT DISTINCT pr.Boja, (
	SELECT COUNT(*) FROM Proizvod as p
	WHERE pr.Boja = p.Boja
) AS BrojProizvoda FROM Proizvod AS pr
ORDER BY BrojProizvoda DESC

/* Dohvatite imena i prezimena svih kupaca iz Frankfurta i uz svakog ispišite broj računa (polje 
BrojRacuna) koje je platio karticom, od višeg prema nižem. */

SELECT ku.Ime, ku.Prezime, ru.BrojRacuna, ru.DatumIzdavanja, kk.Tip
FROM Kupac AS ku
INNER JOIN Racun AS ru ON ku.IDKupac = ru.KupacID
INNER JOIN KreditnaKartica AS kk ON kk.IDKreditnaKartica = ru.KreditnaKarticaID
WHERE ku.GradID = (
	SELECT g.IDGrad FROM Grad AS g
	WHERE g.Naziv = 'Frankfurt'
) AND ru.KreditnaKarticaID IS NOT NULL

/* 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 pr.Boja, (
	SELECT SUM(s.UkupnaCijena) FROM Proizvod AS p
	INNER JOIN Stavka AS s ON p.IDProizvod = s.ProizvodID
	WHERE p.Boja = pr.Boja
) FROM Proizvod as pr
GROUP BY pr.Boja
HAVING (
	SELECT SUM(s.UkupnaCijena) FROM Proizvod AS p
	INNER JOIN Stavka AS s ON p.IDProizvod = s.ProizvodID
	WHERE p.Boja = pr.Boja
) > 20000000


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

SELECT pr.Naziv, pr.BrojProizvoda, (
	SELECT kt.Naziv FROM Potkategorija as pt
	INNER JOIN Kategorija AS kt ON kt.IDKategorija = pt.KategorijaID
	WHERE pt.IDPotkategorija = pr.PotkategorijaID
) AS NazivKategorije, (
	SELECT SUM(s.Kolicina) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) AS ProdanaKolicina FROM Proizvod AS pr
WHERE (
	SELECT SUM(s.Kolicina) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) > 5000
AND pr.PotkategorijaID IS NOT NULL

/* Ispišite ukupno zarađene iznose i broj prodanih primjeraka za svaki od ikad prodanih proizvoda. */

SELECT pr.Naziv, (
	SELECT SUM(s.UkupnaCijena) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) AS UkupnaZarada, (
	SELECT SUM(s.Kolicina) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) AS UkupnaKolicina FROM Proizvod AS pr
WHERE (
	SELECT SUM(s.UkupnaCijena) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) IS NOT NULL

/* Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2.000 primjeraka. */

SELECT pr.Naziv, (
	SELECT SUM(s.UkupnaCijena) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) AS UkupnaZarada FROM Proizvod AS pr
WHERE (
	SELECT SUM(s.Kolicina) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) > 2000
ORDER BY UkupnaZarada DESC

/* 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 pr.Naziv, (
	SELECT SUM(s.UkupnaCijena) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) AS UkupnaZarada FROM Proizvod AS pr
WHERE ((
	SELECT SUM(s.Kolicina) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) > 2000
OR
(
	SELECT SUM(s.UkupnaCijena) FROM Stavka AS s
	WHERE s.ProizvodID = pr.IDProizvod
) > 2000000)
ORDER BY UkupnaZarada DESC
