USE AdventureWorksOBP

DROP TABLE Zapisnik
/*
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 vDrzavaKupci AS
	SELECT d.Naziv as Drzava, COUNT(k.IDKupac) as BrojKupaca FROM Drzava as d
	LEFT JOIN Grad as g on g.DrzavaID = d.IDDrzava
	INNER JOIN Kupac as k on k.GradID = g.IDGrad
	GROUP BY d.Naziv

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

	DROP VIEW vDrzavaKupci
/* 
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 vProdanoMjesecno AS
	SELECT CONVERT(nvarchar(25), r.DatumIzdavanja, 104) as Datum, COUNT(p.IDProizvod) AS KolicinaProizvoda FROM Proizvod as p
	INNER JOIN Stavka as s ON s.ProizvodID = p.IDProizvod
	INNER JOIN Racun as r ON r.IDRacun = s.RacunID
	GROUP BY r.DatumIzdavanja
	
	SELECT TOP 3 * FROM vProdanoMjesecno ORDER BY vProdanoMjesecno.KolicinaProizvoda DESC

	DROP VIEW vProdanoMjesecno
/*
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.
*/
CREATE TABLE Zapisnik (
		IDZapisnik int identity primary key,
		Poruka nvarchar(50),
		Vrijeme datetime
)

CREATE OR ALTER TRIGGER trGrad
ON Grad AFTER INSERT,UPDATE,DELETE
AS
	DECLARE @Poruka nvarchar(50)
	DECLARE @Vrijeme datetime
	SET @Vrijeme = GETDATE()
	BEGIN
		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 deleted) 
		BEGIN
			SET @Poruka = 'Grad je izbrisan u tablici.'
		END
		ELSE IF EXISTS (SELECT 1 FROM inserted) 
		BEGIN
			SET @Poruka = 'Grad je dodan u tablicu.'
		END

		INSERT INTO Zapisnik(Poruka, Vrijeme) VALUES (@Poruka, @Vrijeme)
	END


SELECT * FROM Zapisnik

DELETE FROM Grad WHERE Naziv = 'Paris'

INSERT INTO Grad(Naziv) VALUES('Paris')

UPDATE Grad
SET Naziv = 'Budapest'
WHERE Naziv = 'Paris'

DROP TRIGGER trGrad

DROP TABLE Zapisnik
DISABLE TRIGGER trGrad ON Grad

DROP TRIGGER trGrad

-- ovaj zadatak ima 100% jedna i druga grupa

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

	DBCC PAGE('AdventureWorksOBP', 1, 656, 3) WITH TABLERESULTS


/* 1. 338 */


/*
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 iStavka ON
	Stavka(PopustUPostocima) INCLUDE (Kolicina, IDStavka, RacunID)

	DROP INDEX iStavka 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 fnCijenaProizvod (@Cijena money = NULL)
RETURNS @tbl TABLE (
		IDTbl int identity primary key,
		Naziv nvarchar(50),
		Cijena money
) AS
	BEGIN
		IF @Cijena IS NULL 
		BEGIN
			INSERT INTO @tbl(Naziv, Cijena)
			SELECT Naziv, CijenaBezPDV FROM Proizvod
		END
		ELSE IF @Cijena IS NOT NULL 
		BEGIN
			INSERT INTO @tbl(Naziv, Cijena)
			SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
		END
		RETURN;
	END

SELECT * FROM dbo.fnCijenaProizvod(NULL)

SELECT * FROM dbo.fnCijenaProizvod(500)

DROP FUNCTION  dbo.fnCijenaProizvod

/*
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 pGradUmetniIzmjeni
	@Naziv nvarchar(50) = null,
	@DrzavaID int = null,
	@IDGrad int = null,
	@Operand char(1)
AS
	IF @Operand = 'U' AND @Naziv IS NOT NULL
	BEGIN
		INSERT INTO Grad(Naziv, DrzavaID) VALUES(@Naziv, @DrzavaID)
	END
	ELSE IF @Operand = 'I'
	BEGIN
		UPDATE Grad SET Naziv = @Naziv, DrzavaID = @DrzavaID WHERE IDGrad = @IDGrad
	END

CREATE OR ALTER PROC pGradIzbrisi
	@IDGrad int
AS
	DELETE FROM Grad WHERE IDGrad = @IDGrad

CREATE OR ALTER PROC pGradDohvati
	@IDGrad int = NULL
AS
	IF @IDGrad IS NULL
	BEGIN
		SELECT * FROM Grad
	END
	ELSE IF @IDGrad IS NOT NULL
	BEGIN
		SELECT * FROM Grad WHERE IDGrad = @IDGrad
	END

EXEC pGradUmetniIzmjeni 'Paris', 2, NULL, 'U'

EXEC pGradIzbrisi 17

EXEC pGradDohvati 7

SELECT * FROM Grad

DROP PROC pGradDohvati, pGradIzbrisi, pGradUmetniIzmjeni
