/* --------------------------------------------------------------------- */
-- Ponavljanje
/* --------------------------------------------------------------------- */

/* 
------------------------------------------------------------------------
MEĐUISPIT #1
ISHOD 1 
------------------------------------------------------------------------
*/

/*
ZADATAK 1 [POGLEDI]:
Napravite pogled koji će prikazivati sve potkategorije koje imaju pripadajućih proizvoda. 
Napišite naredbu za promjenu pogleda kako biste dodali i stupac koji će sadržavati točan broj proizvoda pojedine potkategorije.
*/

/* 
ZADATAK 2 [POGLEDI]:
Napravite pogled koji prikazuje godine i mjesece (u obliku npr. 2005-12) u kojima je kupovano te uz svaku prikazanu godinu i mjesec prikažite i koliko je kupaca kupovalo 
(treba brojati samo jedinstvene kupce). Iskoristite pogled za dohvaćanje 3 godine i mjeseca u kojima je kupovalo najmanje kupaca. 
Nakon nekog vremena stiže pritužba od korisnika: „Vaš pogled je prestao raditi i javlja grešku da ne može pronaći tablicu Račun! Upomoć!“ 
Nakon izvida, primjećujete da je netko uklonio tablicu iz baze pa je vraćate iz zaštitne kopije. Napišite naredbe kojima ćete spriječiti ovakvu vrstu problema u budućnosti.
*/

/*
ZADATAK 3 [OKIDAČI]:
Napravite novu tablicu Zapisnik sa stupcima IDZapisnik, Poruka i Vrijeme. Napravite okidač na tablici Grad koji će pri
svakom događaju INSERT, UPDATE ili DELETE upisati novi zapis s odgovoarajućom porukom i vremenom u tablicu Zapisnik.
Napišite naredbu za uklanjanje okidača.
Primjer poruka:
- INSERT: Grad je dodan u tablicu.
- UPDATE: Grad je ažuriran u tablici.
- DELETE: Grad je obrisan iz tablice.
*/


/* 
------------------------------------------------------------------------
ISHOD 2 
------------------------------------------------------------------------
*/

/*
ZADATAK 4 [ANALIZA PODATAKA NA DISKU]:
Analizirajte tablicu Kupac i odgovorite na sljedeća pitanja:
1. Na koliko stranica su smješteni podaci iz tablice Kupac?
2. Napišite redne brojeve prvih 5 stranica (PagePID) i redne brojeve datoteke u kojoj su smještene stranice (PageFID).
3. Na kojoj stranici se nalazi kupac s IDKupac = 35?
4. Kolika je duljina IDKupac = 10 u bajtovima?
*/

/*
ZADATAK 5 [INDEKSI]:
Optimizirajte upit što je bolje moguće:
"""
SELECT S.IDStavka, S.RacunID, S.Kolicina
FROM Stavka AS S
WHERE S.Kolicina<20 AND S.PopustUPostocima>0.2
"""
Napišite naredbu za uklanjanje indeksa.
*/

/* 
------------------------------------------------------------------------
ISHOD 3 
------------------------------------------------------------------------
*/

/*
ZADATAK 6 [FUNKCIJE]:
Napišite složenu tabličnu funkciju koja prima cijenu. Ako je cijena NULL, vratite nazive i cijene svih proizvoda iz tablice Proizvod. ​
Ako nije, vratite nazive i cijene samo onih proizvoda čija cijena je veća od zadane cijene. Iskoristite funkciju s NULL i s cijenom od 3000.
Uklonite funkciju.
*/

/*
ZADATAK 7 [PROCEDURE]:
Napravite procedure koje rade CRUD operacije na tablici Grad tako da operacije umetanja i izmjene obavite u jednoj proceduri, 
a druge dvije operacije obavite u posebnim procedurama. Iskoristite procedure za umetanje, izmjenu, dohvaćanje i brisanje zapisa.
Uklonite procedure.
*/


/* 
------------------------------------------------------------------------
MEĐUISPIT #2
ISHOD 4 
------------------------------------------------------------------------
*/

/*
ZADATAK 8 [JSON]:
Osmislite JSON podatak u kojem je moguće proceduri odjednom poslati naziv jedne kategorije i nazive pripadajućih potkategorija. 
Kreirajte proceduru koja prima takav JSON podatak i upisuje poslanu kategoriju i poslane pripadajuće potkategorije. 
Prije upisa kategorije procedura treba provjeriti je li poslana kategorija već upisana. 
Ako je, ne treba upisivati ponovo niti kategoriju niti potkategorije. Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure.
*/

	CREATE OR ALTER PROC spUpisiKategorijuPotkategorija
	@jsonPodatak NVARCHAR(MAX)
	AS
		BEGIN
			DECLARE @NazivKategorije NVARCHAR(50)
			DECLARE @IDKategorija INT

			SELECT @NazivKategorije=NazivKategorije FROM OPENJSON(@jsonPodatak) WITH (NazivKategorije NVARCHAR(50))

			IF NOT EXISTS(SELECT 1 FROM Kategorija WHERE Naziv = @NazivKategorije)
			BEGIN
				INSERT INTO Kategorija(Naziv) VALUES(@NazivKategorije)
			END

			SELECT @IDKategorija=IDKategorija FROM Kategorija WHERE Naziv=@NazivKategorije

			INSERT INTO Potkategorija (KategorijaID, Naziv)
			SELECT @IDKategorija, NazivPotkategorije FROM OPENJSON(@jsonPodatak, '$.Potkategorije')
			WITH (NazivPotkategorije NVARCHAR(50))
			WHERE NOT EXISTS (
				SELECT 1 FROM Potkategorija
				WHERE Naziv = NazivPotkategorije AND KategorijaID = @IDKategorija
			)


		END


	DECLARE @jsonPodatak NVARCHAR(MAX) = '
	{
		"NazivKategorije": "Elektronika",
		"Potkategorije": [
			{ "NazivPotkategorije": "Moblini telefoni" },
			{ "NazivPotkategorije": "Televizori" }
		]
	}
	'

EXEC spUpisiKategorijuPotkategorija @jsonPodatak

/*
ZADTAK 9 [XML]:
Napišite proceduru koja prima XML dokument oblika:
<Gradovi Drzava="Hrvatska">
		<Grad>Sinj</Grad>
		<Grad>Gospić</Grad>
		<Grad>Vinkovci</Grad>
</Gradovi>
Neka procedura upiše državu i gradove iz XML-a u pripadne tablice Drzava i Grad. Napravite poziv procedure kojim prikazujete funkcionalnost kreirane procedure. 
Za provjeru napisati upit kojim prikazujete državu i gradove upisane kreiranom procedurom.
*/



/* 
------------------------------------------------------------------------
ISHOD 5 
------------------------------------------------------------------------
*/

/*
ZADATAK 10 [TRANSAKCIJE]:
Napišite proceduru s jednim parametrom IDKategorija koja unutar transakcije ažurira sve cijene proizvoda poslane kategorije na nulu (CijenaBezPDV=0). 
Napravite poziv procedure kojim prikazujete zadanu funkcionalnost procedure.
*/

	CREATE PROC spAzurirajCijeneNaNulu
	@IDKategorije INT
	AS
		BEGIN
			BEGIN TRAN

			BEGIN TRY
				UPDATE Proizvod SET CijenaBezPDV=0
				WHERE PotkategorijaID IN (
					SELECT IDPotkategorija FROM Potkategorija WHERE KategorijaID = @IDKategorije
				)

				COMMIT TRAN
			END TRY

			BEGIN CATCH
				ROLLBACK TRAN
			END CATCH
		END

/*
ZADATAK 11 [TRANZAKCIJE]:
Opišite i na tablici Kupac demonstrirajte kako nastaje problem neponovljivog čitanja. Naznačite gdje se problem pojavljuje i na koji način i gdje ste ga riješili.
*/

/*	Problem neponovljivog čitanja nastaje kada jedna transakcija dva puta pročita iste podatke, a druga transakcija izmjeni te podatke između čitanja
Kao rezultat, prva transakcija dobiva različite vrijednosti za isti skup podataka.
*/

SET TRANSACTION ISOLATION LEVEL READ COMMITED
BEGIN TRAN
SELECT * FROM Kupac WHERE IDKupac = 1
SELECT * FROM Kupac WHERE IDKupac = 1
END TRAN

/* 
------------------------------------------------------------------------
ISHOD 6 
------------------------------------------------------------------------
*/

/*
ZADATAK 12 [GROUP BY]:
Napišite jedan upit za prikaz naziva potkategorija i broja koliko ima proizvoda iz pojedine potkategorije. 
Potkategorije treba rangirati s nekom od funkcija (po izboru) kako bi na vrhu bile potkategorije u kojima ima najviše proizvoda.
*/
	SELECT pk.Naziv as 'Naziv Proizvoda',
	COUNT(p.IDProizvoda) AS 'BrojProizvoda',
	RANK() OVER (ORDER BY COUNT(p.IDProivzod)) AS 'Rang' FROM Potkategorija as pk
	LEFT JOIN Proizvod as p ON pk.IDPotkategorija = p.PotkategorijaID
	GROUP BY pk.Naziv
/*
ZADATAK 13 [GROUP BY]:
Napišite jedan upit za izračunavanje ukupne plaćene cijene (SUM(Stavka.UkupnaCijena)) po kupcima i godinama računa. Imena i prezimena kupaca neka budu ispisana u prvom (jednom) stupcu, 
godine izdavanja računa u drugom stupcu, a ukupna plaćena cijena u trećem stupcu. Treba biti vidljivo i koliko je ukupna zarada od pojedinog kupca te grand total.
*/
	SELECT k.Ime + ' ' + k.Prezime as 'Kupac'
	YEAR(r.DatumIzdavanja) as 'Godina',
	SUM (s.UkupnaCijena) AS 'UkupnaCijena' FROM Racun as r
	INNER JOIN Stavka as s ON r.IDRacun = s.RacunID
	INNER JOIN Kupac as k ON r.KupacID = k.IDKupac
	GROUP BY ROLLUP(k.Ime + ' ' + k.Prezime, YEAR(r.DatumIzdavanja))
/* 
------------------------------------------------------------------------
ISHOD 7 
------------------------------------------------------------------------
*/

/*
ZADATAK 14 [NOSQL]:
- Kreirajte novu bazu podataka proizvoljnog naziva i u njoj novu kolekciju naziva Student.
- Upišite sebe u novokreiranu kolekciju: upišite svoje ime, prezime, email adresu, JMBAG te naziv svoje grupe.
- Napišite upit kojim ćete ažurirati svoj dokument iz kolekcije Student na način da si promijenite email adresu u ime.prezime@algebra.hr.
- Napišite upit kojim ćete izbrisati ranije kreiranu bazu podataka.
*/

/* --------------------------------------------------------------------- */
