USE AdventureWorksOBP

-- Ishod 4

-- Ispišite sve kupce iz tablice Kupac čije ime ne završava na slovo ‘a’

SELECT * FROM Kupac WHERE Ime NOT LIKE '%a'

-- Ispišite sve kupce kojima prezime započinje s ‘Van’.

SELECT * FROM Kupac WHERE Prezime LIKE 'Van%'

-- Dodajte kupcu Vargas Gary-ju sufiks ‘treći’

SELECT * FROM Kupac WHERE IDKupac = 892

UPDATE Kupac 
	SET Prezime = Prezime + ' treći'
	WHERE IDKupac = 892

/*
d) Ispišite
	a) sve kupce koji se zovu Ana ili Tamara i iz Osijeka su.
	b) sve kupce čije prezime započinje slovom "D" i prezime sadržava string "re“.
	c) samo prezimena polovice prvih osoba koje se zovu "Jack“
*/

-- d.a)

SELECT * FROM Kupac WHERE Ime IN('Ana', 'Tamara') AND GradID = '2'

-- d.b)

SELECT * FROM Kupac WHERE Prezime LIKE 'D%re'

-- d.c)

SELECT TOP 50 PERCENT Prezime FROM Kupac WHERE Ime = 'Jack'

-- e) Svim kupcima koji nemaju unesen sufiks, promijenite (dodajte) ga kao string ‘nema’.

SELECT Prezime FROM Kupac WHERE Prezime LIKE '%-%'

UPDATE Kupac
	SET Prezime = Prezime + '-' + 'nema'
WHERE Prezime NOT LIKE '%-%'

-- f) Ispišite sve kupce kojima prezime sadrži apostrof (‘)

SELECT * FROM Kupac WHERE Prezime LIKE '%''%'

/* g)  Ispišite sve proizvode kojima podatak BrojProizvoda sadrži znak postotka (%). Ukoliko nema takvih 
proizvoda, promijenite proizvodu ‘Adjustable Race’ broj proizvoda u ‘AR%5381’ */

SELECT * FROM Proizvod WHERE BrojProizvoda LIKE '%|%%' escape '|'

UPDATE Proizvod
	SET BrojProizvoda = 'AR%5381'
WHERE Proizvod.Naziv = 'Adjustable Race'

-- Ishod 4 Nastavak

-- a) Ispišite popis računa (datum, ukupan iznos, naziv kupca)

SELECT r.DatumIzdavanja, s.UkupnaCijena, CONCAT(k.Ime, ' ', k.Prezime) N'Ime i Prezime kupca' FROM Racun as r
INNER JOIN Stavka as s ON s.RacunID = r.IDRacun
INNER JOIN Kupac as k ON k.IDKupac = r.KupacID

-- b) Za nekog kupca ispišite svu robu (nazive proizvoda) koju je ikada kupio

SELECT p.Naziv FROM Proizvod as p
INNER JOIN Stavka as s ON p.IDProizvod = s.ProizvodID
INNER JOIN Racun as r ON r.IDRacun = s.RacunID
INNER JOIN Kupac as k on k.IDKupac =r.KupacID
WHERE k.IDKupac = 4

-- c) Za nekog kupca ispišite koje je sve kategorije proizvoda kupovao.

SELECT DISTINCT kat.Naziv FROM Proizvod as p
INNER JOIN Potkategorija as pot ON pot.IDPotkategorija = p.PotkategorijaID
INNER JOIN Kategorija as kat ON kat.IDKategorija = pot.KategorijaID
INNER JOIN Stavka as s ON p.IDProizvod = s.ProizvodID
INNER JOIN Racun as r ON r.IDRacun = s.RacunID
INNER JOIN Kupac as k on k.IDKupac =r.KupacID
WHERE k.IDKupac = 4

-- d) Za neki proizvod ispišite sve njegove potkategorije i kategorije

SELECT kat.Naziv, pot.Naziv FROM Proizvod as p
INNER JOIN Potkategorija as pot ON pot.IDPotkategorija = p.PotkategorijaID
INNER JOIN Kategorija as kat ON kat.IDKategorija = pot.KategorijaID
WHERE p.IDProizvod IS NOT NULL

/* e) Ispišite sve tipove kreditnih kartica kojima su plaćani proizvodi skuplji od 3000 kn. Svaki tip kreditne
kartice treba biti prikazan samo jednom. */

SELECT DISTINCT kk.Tip FROM KreditnaKartica as kk
INNER JOIN Racun as r ON kk.IDKreditnaKartica = r.KreditnaKarticaID
INNER JOIN Stavka as s ON s.RacunID = r.IDRacun
INNER JOIN Proizvod as p ON p.IDProizvod = s.ProizvodID
WHERE p.CijenaBezPDV >= 3000

-- f) Ispišite jedinstvene boje proizvoda plaćanih gotovinom.

SELECT DISTINCT p.Boja FROM Proizvod AS p
INNER JOIN Stavka as s ON s.ProizvodID = p.IDProizvod
INNER JOIN Racun as r ON r.IDRacun = s.RacunID
WHERE r.KreditnaKarticaID IS NULL

-- g) Koristeći lijevo vanjsko spajanje dohvatiti imena svih kupaca koji nisu ništa kupili.

SELECT k.Ime FROM Kupac as k
LEFT OUTER JOIN Racun as r ON r.KupacID = k.IDKupac
WHERE r.KupacID IS NULL

-- h) Koristeći desno vanjsko spajanje dohvatiti imena svih kupaca koji nisu ništa kupili

SELECT k.Ime FROM Racun as r
RIGHT OUTER JOIN Kupac as k ON k.IDKupac = r.KupacID
WHERE r.KupacID IS NULL

-- i) Koristeći puno vanjsko spajanje ispisati nazive država i nazive gradova.

SELECT g.Naziv, d.Naziv FROM Grad as g
FULL OUTER JOIN Drzava as d ON g.DrzavaID = d.IDDrzava

/* j) Upišite državu Indiju te grad London, bez navođenja kojoj državi pripada. Koristeći puno vanjsko 
spajanje ispisati nazive država i nazive gradova. Ispisati samo one gradove koji nemaju definiranu 
državu i one države koji nemaju upisanih gradova. */

INSERT INTO Drzava
(Naziv) VALUES ('Indija')

INSERT INTO Grad
(Naziv, DrzavaID) VALUES ('London', NULL)

SELECT g.Naziv N'Grad', d.Naziv N'Drzava' FROM Grad as g
FULL OUTER JOIN Drzava as d ON g.DrzavaID = d.IDDrzava
WHERE g.DrzavaID IS NULL

-- k) Ispišite sve kupce koji su kupovali proizvode (ime, prezime, datum prodaje i ukupan iznos prodaje)

SELECT k.Ime, k.Prezime, r.DatumIzdavanja, s.UkupnaCijena FROM Kupac as k
INNER JOIN Racun as r ON k.IDKupac = r.KupacID
INNER JOIN Stavka as s ON s.RacunID = r.IDRacun
WHERE r.IDRacun IS NOT NULL

/* l) Ispišite za svaku državu u koju je išao neki račun popis kategorija proizvoda koji su tamo prodani, 
sortirano po državama. */

SELECT d.Naziv, kat.Naziv FROM Drzava AS d
INNER JOIN Grad as g ON g.DrzavaID = d.IDDrzava
INNER JOIN Kupac as k ON  k.GradID = g.IDGrad
INNER JOIN Racun as r ON r.KupacID = k.IDKupac
INNER JOIN Stavka as s ON s.RacunID = r.IDRacun
INNER JOIN Proizvod as p ON p.IDProizvod = s.ProizvodID
LEFT OUTER JOIN Potkategorija as pot ON pot.IDPotkategorija = p.PotkategorijaID
LEFT OUTER JOIN Kategorija as kat ON kat.IDKategorija = pot.KategorijaID
ORDER BY 1



-- Ishod 5

/*
34. Ispišite sve boje proizvoda i pokraj svake napišite koliko proizvoda ima tu boju
35. Promijenite prethodni upit tako da sortirate padajuće prema broju proizvoda.
36. Promijenite prethodni upit tako da isključite nedefiniranu boju.
*/

SELECT p.Boja, COUNT(*) N'Broj' FROM Proizvod as p WHERE p.Boja IS NOT NULL
GROUP BY p.Boja ORDER BY COUNT(*) DESC

/*
37. Ispišite koliko proizvoda svake boje ima u svakoj od potkategorija. Sortirajte prema 
potkategoriji i prema boji.
38. Promijenite prethodni upit tako da ispišete 10 kategorija i boja s najviše proizvoda.
39. Promijenite prethodni upit tako da umjesto ID potkategorije ispišete njen naziv.
*/

SELECT TOP 10 p.Boja, pot.Naziv, COUNT(pot.Naziv) FROM Potkategorija AS pot
INNER JOIN Proizvod as p ON p.PotkategorijaID = pot.IDPotkategorija
GROUP BY p.Boja, pot.Naziv

/*
40. Ispišite nazive svih kategorija i pokraj svake napišite koliko ima potkategorija.
41. Ispišite nazive svih kategorija i pokraj svake napišite koliko proizvoda je u njoj.
*/

SELECT k.Naziv, COUNT(p.Naziv) N'Broj potkategorija' FROM Kategorija as k
INNER JOIN Potkategorija as p ON p.KategorijaID = k.IDKategorija
GROUP BY k.Naziv

SELECT k.Naziv, COUNT(pr.IDProizvod) N'Broj Proizvoda' FROM Kategorija as k
INNER JOIN Potkategorija as p ON p.KategorijaID = k.IDKategorija
INNER JOIN Proizvod as pr ON pr.PotkategorijaID = p.IDPotkategorija
GROUP BY k.Naziv

/*
42. Ispišite sve različite cijene proizvoda i napišite koliko proizvoda ima svaku cijenu.
43. Ispišite koliko je računa izdano koje godine.
44. Ispišite brojeve svih račune izdane kupcu 377 i pokraj svakog ispišite ukupnu cijenu 
po svim stavkama.
*/

SELECT DISTINCT p.CijenaBezPDV, COUNT(p.IDProizvod) FROM Proizvod as p
GROUP BY p.CijenaBezPDV

SELECT YEAR(r.DatumIzdavanja) N'Godina', COUNT(r.IDRacun) N'Broj izdanih racuna' FROM Racun AS r
GROUP BY YEAR(r.DatumIzdavanja)
ORDER BY YEAR(r.DatumIzdavanja) DESC

SELECT r.BrojRacuna, COUNT(r.BrojRacuna) N'Broj izdanih racuna', SUM(s.UkupnaCijena) N'Ukupna Cijena' FROM Racun AS r
INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
WHERE r.KupacID = 377
GROUP BY r.BrojRacuna

/*
49. Ispišite sve boje koje imaju više od 40 proizvoda.
50. Ispišite nazive svih potkategorija koje sadržavaju više od 10 proizvoda.
*/

SELECT p.Boja, COUNT(p.IDProizvod) N'Broj proizvoda' FROM Proizvod AS p
GROUP BY p.Boja
HAVING COUNT(p.IDProizvod) > 40

SELECT pot.Naziv, COUNT(p.IDProizvod) N'Broj Proizvoda' FROM Potkategorija as pot
INNER JOIN Proizvod AS p ON pot.IDPotkategorija = p.PotkategorijaID
GROUP BY pot.Naziv
HAVING COUNT(p.IDProizvod) > 10

/*
51. Ispišite ukupno zarađene iznose i broj prodanih primjeraka za svaki od ikad prodanih proizvoda.
52. Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2000 primjeraka.
53. 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, COUNT(s.Kolicina) N'Ukupna Kolicina', SUM(s.UkupnaCijena) N'Ukupna Cijena' FROM Proizvod as p
INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
GROUP BY p.Naziv
ORDER BY COUNT(s.Kolicina) DESC

SELECT p.Naziv, COUNT(s.Kolicina) N'Ukupna Kolicina', SUM(s.UkupnaCijena) N'Ukupna Cijena' FROM Proizvod as p
INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
GROUP BY p.Naziv
HAVING COUNT(s.Kolicina) > 2000
ORDER BY COUNT(s.Kolicina) DESC

SELECT p.Naziv, COUNT(s.Kolicina) N'Ukupna Kolicina', SUM(s.UkupnaCijena) N'Ukupna Cijena' FROM Proizvod as p
INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
GROUP BY p.Naziv
HAVING COUNT(s.Kolicina) > 2000 OR SUM(s.UkupnaCijena) > 2000000
ORDER BY COUNT(s.Kolicina) DESC

/*
1. Ispišite sve potkategorije i za svaku ispišite broj proizvoda u njoj.
2. Riješite prethodni zadatak pomoću spajanja.
*/

SELECT pk.Naziv, (
	SELECT COUNT(*) FROM Proizvod as p
		WHERE pk.IDPotkategorija = p.PotkategorijaID
	) 
FROM Potkategorija as pk

SELECT pk.Naziv, COUNT(*) N'Broj proizvoda' FROM Proizvod as p
INNER JOIN Potkategorija AS pk ON pk.IDPotkategorija = p.PotkategorijaID
GROUP BY Pk.Naziv

/*
3. Ispišite sve kategorije i za svaku ispišite broj proizvoda u njoj.
4. Ispišite sve proizvode i pokraj svakog ispišite zarađeni iznos, od najboljih prema lošijim.
*/

SELECT k.Naziv, (
	SELECT COUNT(p.IDProizvod) FROM Proizvod as p
		INNER JOIN Potkategorija as pk ON pk.IDPotkategorija = p.PotkategorijaID
		WHERE k.IDKategorija = pk.KategorijaID
) AS BrojProizvoda FROM Kategorija as k
ORDER BY BrojProizvoda DESC

SELECT p.Naziv, (
	SELECT SUM(s.UkupnaCijena) FROM Stavka as s
		WHERE s.ProizvodID = p.IDProizvod
) AS UkupanProfit FROM Proizvod as p
ORDER BY UkupanProfit 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, (
	SELECT AVG(CijenaBezPDV) FROM Proizvod
) AS ProsjecnaCijena,
(
	SELECT AVG(CijenaBezPDV) FROM Proizvod
)-p.CijenaBezPDV FROM Proizvod AS p
WHERE CijenaBezPDV > 0

-- 6. 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 BrojRacuna FROM Komercijalist as k
ORDER BY BrojRacuna DESC

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

SELECT TOP 5 k.Ime, k.Prezime, 
(
	SELECT SUM(s.Kolicina) FROM Stavka as s
	WHERE s.RacunID IN (
		SELECT IDRacun FROM Racun AS r
		WHERE r.KomercijalistID = k.IDKomercijalist
	)
) AS UkupnaKolicina,
(
	SELECT SUM(s.UkupnaCijena) FROM Stavka as s
	WHERE s.RacunID IN (
		SELECT IDRacun FROM Racun AS r
		WHERE r.KomercijalistID = k.IDKomercijalist
	)
) AS UkupniProfit,
(
	SELECT COUNT(*) FROM Racun as r
	WHERE r.KomercijalistID = k.IDKomercijalist
) AS IzdaniRacuni
FROM Komercijalist as k
ORDER BY IzdaniRacuni DESC

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

SELECT DISTINCT ISNULL(Boja, 'Nema Boje'),
(
	SELECT COUNT(pr.IDProizvod) FROM Proizvod AS pr
	WHERE pr.Boja = p.Boja
)
AS Boja FROM Proizvod as p

/* 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. */

SELECT k.Ime, k.Prezime,
(
	SELECT COUNT(r.BrojRacuna) FROM Racun as r
	WHERE r.KupacID = k.IDKupac AND
	r.KreditnaKarticaID IS NOT NULL
)
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
)

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