--Ponavljanje za ispit
USE AdventureWorksOBP

SELECT * FROM Stavka

-- Moramo znati CAST i CONVERT funkcije
SELECT BrojRacuna, CAST(DatumIzdavanja as CHAR(11)) as DatumIzdavanja
FROM Racun WHERE KupacID = 287

SELECT BrojRacuna, CONVERT() as DatumIzdavanja
FROM Racun WHERE KupacID = 287

--Na testu će rijetko biti CASE i WHILE

--TRY i CATCH --> ERROR_MEESSAGE(), ERROR_NUMBER()
--Općenito unutar procedura se piše TRY/CATCH

/*
ZADATAK 1 [POGLEDI]:
Napravite pogled koji prikazuje države i broj kupaca iz svake države. Pogled treba vratiti stupce: NazivDrzave i BrojKupaca. 
Iskoristite pogled za dohvaćanje države s najviše kupaca. Napišite naredbu za uklanjanje pogleda.
*/

	CREATE OR ALTER VIEW vDrzaveKupci AS
	SELECT d.Naziv as NazivDrzave, COUNT(k.IDKupac) as BrojKupaca FROM Drzava as d
	LEFT JOIN Grad as g ON g.DrzavaID = d.IDDrzava
	FULL OUTER JOIN Kupac as k ON k.GradID = g.IDGrad
	GROUP BY d.Naziv

	SELECT TOP 1 * FROM vDrzaveKupci ORDER BY vDrzaveKupci.BrojKupaca DESC


	DROP VIEW vDrzaveKupci

/* 
ZADATAK 2 [POGLEDI]:
Napravite pogled koji vraća formatiran datum na hrvatski način te broj proizvoda koji su prodani u tom razdoblju. 
Iskoristite pogled za dohvaćanje 3 mjeseca s najvećom prodajom proizvoda. Osmislite dodatne mjere kako biste zaštitili
definiciju od neovlaštenog pregleda sadržaja pogleda. Napišite naredbu za uklanjanje pogleda.
*/

		CREATE OR ALTER VIEW vDatumProdano WITH ENCRYPTION AS
		SELECT CONVERT(CHAR(10), r.DatumIzdavanja, 104) as DatumIzdavanja, SUM(s.Kolicina) as BrojProizvoda FROM Stavka as s
		INNER JOIN Racun as r on r.IDRacun = s.IDStavka
		GROUP BY CONVERT(CHAR(10), r.DatumIzdavanja, 104)

		SELECT TOP 3 * FROM vDatumProdano ORDER BY BrojProizvoda DESC
		
		DROP VIEW vDatumProdano

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

-- ovaj zadatak ima 100% jedna i druga grupa

	CREATE TABLE Zapisnik(
		IDZapisnik int IDENTITY PRIMARY KEY,
		Poruka nvarchar(255),
		Vrijeme datetime DEFAULT GETDATE()

	)
	GO
	CREATE OR ALTER TRIGGER trZapisnikGrad
	ON Grad
	AFTER INSERT,UPDATE,DELETE
	AS
		BEGIN
			DECLARE @Poruka nvarchar(255)
			IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
				BEGIN
					SET @Poruka = 'Grad je ažuriran u tablici'
				END
			ELSE IF EXISTS(SELECT 1 FROM inserted)
				BEGIN
					SET @Poruka = 'Grad je dodan u tablicu'
				END
			ELSE IF EXISTS(SELECT 1 FROM deleted)
				BEGIN
					SET @Poruka = 'Grad je obrisan iz tablice'
				END

		INSERT INTO Zapisnik (Poruka) VALUES (@Poruka)
	END
GO
INSERT INTO Grad (Naziv, DrzavaID) VALUES('Karlovac', 1)
/*
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?
*/
	DBCC TRACEON(3604)
	DBCC IND('AdventureWorksOBP', 'Kupac', 1)
/*
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.
*/
SET STATISTICS IO ON

SELECT s.IDStavka, s.RacunID, s.Kolicina FROM Stavka as s
WHERE s.Kolicina<20 and s.PopustUPostocima>0.2

CREATE NONCLUSTERED INDEX iStavkaKolicinaPopust
ON Stavka (PopustUPostocima) INCLUDE (RacunID)

DROP INDEX iStavkaKolicinaPopust ON Stavka

SET STATISTICS IO OFF
/*
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.
*/

CREATE OR ALTER FUNCTION fnProizvodCijena(@cijena money)
RETURNS @res TABLE (
	Naziv nvarchar(50),
	Cijena money
)
AS
	BEGIN
		IF @cijena IS NULL
			BEGIN 
				INSERT INTO @res (Naziv, Cijena)
				SELECT Naziv, CijenaBezPDV from Proizvod
			END
		ELSE
			BEGIN
				INSERT INTO @res (Naziv, Cijena)
				SELECT Naziv, CijenaBezPDV from Proizvod
				WHERE CijenaBezPDV > @cijena
			END
		RETURN
	END

	SELECT * FROM fnProizvodCijena(NULL)
	SELECT * FROM fnProizvodCijena(3000.00)

/*
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.
*/
CREATE OR ALTER PROC spGradIU
	@Operacija CHAR(1),
	@IDGrad int = NULL,
	@Naziv nvarchar(50) = NULL,
	@DrzavaID INT = NULL
AS
	BEGIN
		IF @Operacija = 'I'
			BEGIN
				INSERT INTO Grad(Naziv, DrzavaID) VALUES (@Naziv, @DrzavaID)
			END
		ELSE IF @Operacija = 'U'
			BEGIN
				UPDATE Grad SET Naziv=@Naziv, DrzavaID=@DrzavaID WHERE IDGrad=@IDGrad

			END
		ELSE
			BEGIN
				SELECT 'Neispravna operacija'
			END
	END

CREATE OR ALTER PROC spGradSelect @IDGrad INT = NULL
AS
	BEGIN
		IF @IDGrad IS NULL
			BEGIN
				SELECT * FROM Grad as g
				LEFT JOIN Drzava as d
				ON g.DrzavaID = d.IDDrzava
			END
		ELSE
			BEGIN
				SELECT g.IDGrad, g.Naziv, d.Naziv as Drzava FROM Grad as g
				LEFT JOIN Drzava as d
				ON g.DrzavaID = d.IDDrzava
				WHERE g.IDGrad=@IDGrad
			END
	END

CREATE OR ALTER PROC spGradDelete @IDGrad INT
AS
	BEGIN
		DELETE FROM Grad WHERE IDGrad=@IDGrad
	END

EXEC spGradIU
	@Operacija = 'I',
	@Naziv = 'Split',
	@DrzavaID = 1

EXEC spGradIU
	@Operacija = 'U',
	@IDGrad = 7,
	@Naziv = 'Dubrovnik',
	@DrzavaID = 1

EXEC spGradSelect
EXEC spGradSelect 1
EXEC spGradDelete 1
GO

DROP spGradIU
DROP spGradSelect
DROP spGradDelete
/* --------------------------------------------------------------------- */