USE AdventureWorksOBP

/*
Kreirajte pogled koji će se koristiti za dohvat svih računa kojima su kupci iz Njemačke.
Korištenjem kreiranog pogleda prikažite koliko je računa izdano u pojedinom gradu. 
*/

CREATE OR ALTER VIEW vRacuniIzNjemacke AS
	SELECT *
	FROM Racun as r
	LEFT JOIN Kupac as k ON r.KupacID = k.IDKupac
	INNER JOIN Grad as g ON g.IDGrad = k.GradID
	WHERE g.DrzavaID = 2

SELECT vRacuniIzNjemacke.Naziv, COUNT(vRacuniIzNjemacke.BrojRacuna) FROM vRacuniIzNjemacke 
GROUP BY vRacuniIzNjemacke.Naziv

DROP VIEW vRacuniIzNjemacke

/*
Kreirajte pogled koji vraća ID-eve svih komercijalista te ID-eve svih izdanih računa.
Pri tome omogućite da pogled ostane zaštićen od svih naknadnih promjena tablica o kojima ovisi. 
*/

CREATE OR ALTER VIEW vKomercijalisti WITH SCHEMABINDING, ENCRYPTION
AS
	SELECT k.IDKomercijalist, r.IDRacun
	FROM dbo.Komercijalist as k
	FULL OUTER JOIN dbo.Racun as r
	ON r.KomercijalistID = k.IDKomercijalist
	WITH CHECK OPTION

DROP VIEW vKomercijalisti

/*
Kreirajte novu tablicu Zapisnik sa stupcima IDZapisnik i Sadrzaj.
Napravite okidač na tablici Komercijalist i vežite ga uz sva tri događaja.
U okidaču saznajte koji događaj ga je pozvao i tu informaciju upišite u Zapisnik.
Napravite upis jednog novog retka, izmjenu jednog retka i brisanje jednog retka. Uklonite okidač. 
*/

CREATE TABLE Zapisnik (
	IDZapisnik int identity primary key,
	Sadrzaj nvarchar(25)
)

CREATE OR ALTER TRIGGER tInfoZapisnik ON Komercijalist AFTER INSERT,UPDATE,DELETE
AS
	DECLARE @Poruka NVARCHAR(25)
	IF EXISTS (SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
		BEGIN
			SET @Poruka = 'Komercijalist je ažuriran u tablici'
		END
	ELSE IF EXISTS (SELECT 1 FROM inserted)
		BEGIN
			SET @Poruka = 'Komercijalist je umetnut u tablicu'
		END
	ELSE IF EXISTS (SELECT 1 FROM deleted)
		BEGIN
			SET @Poruka = 'Komercijalist je izbrisan iz tablice'
		END
	INSERT INTO Zapisnik(Sadrzaj) VALUES (@Poruka)

DROP TABLE Zapisnik

DROP TRIGGER tInfoZapisnik

/*
Korisnički zahtjevi kažu da se u tablicu Osoba u stupac OIB uvijek obavezno mora unijeti podatak duljine 11 znamenaka.
Osigurajte navedeno korištenjem okidača. 
Onemogućite kreirani okidač.
Omogućite korištenje rekurzivnih okidača.
*/


/*
Na koliko stranica su smješteni podaci iz tablice Potkategorija? 
Na koliko stranica su smješteni indeksi iz tablice Potkategorija?
Koja je vrijednost IDPotkategorija prve potkategorije na prvoj podatkovnoj stranici?
*/

DBCC TRACEON(3604)
DBCC IND('AdventureWorksOBP', 'Podkategorija', -1)

SET STATISTICS IO ON

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

SELECT * FROM Potkategorija


SET STATISTICS IO OFF

/*
Optimizirajte upit što je bolje moguće: 
SELECT DISTINCT PotkategorijaID FROM 
Proizvod WHERE Naziv LIKE 'P%'
*/

SELECT DISTINCT PotkategorijaID FROM 
Proizvod WHERE Naziv LIKE 'P%'

CREATE NONCLUSTERED INDEX iPotkategorija
ON Proizvod (Naziv) INCLUDE (PotkategorijaID)

DROP INDEX iPotkategorija ON Proizvod
/*
Kreirajte proceduru koja će za primljeni IDKupac izbrisati dotičnog kupca.
Ukoliko dotični kupac ne postoji, procedura treba kroz RETURN parametar vratiti -1, inače 0.
Zanemarite odnose tablice Kupac s ostalim tablicama.
Pozovite kreiranu proceduru kako biste prikazali zadanu funkcionalnost.
*/

CREATE OR ALTER PROC pKupac
	@IDKupac int
AS
	IF NOT EXISTS(SELECT * FROM Kupac WHERE IDKupac = @IDKupac)
		BEGIN
			RETURN -1
		END
	ELSE IF EXISTS(SELECT * FROM Kupac WHERE IDKupac = @IDKupac)
		BEGIN
			DELETE FROM Kupac WHERE IDKupac = @IDKupac
			RETURN 0
		END

SELECT * FROM Kupac WHERE IDKupac = 90

EXEC pKupac 90

DROP PROC pKupac

/*
Kreirajte proceduru koja prima naziv potkategorije i pripadajuće IDKategorije, upisuje novu potkategoriju u pripadajuću
kategoriju i kroz izlazni parametar vraća vrijednost primarnog ključa upisanog retka.
Ako već postoji potkategorija zadanog imena za tu kategoriju, ne treba je upisivati ponovno
i u tom slučaju kroz izlazni parametar vratite vrijednost 0. 
Pozovite proceduru dva puta s istim nazivom potkategorije i IDKategorije te ispišite vraćene vrijednosti.
*/

CREATE OR ALTER PROC pKategorijaPotkategorija
	@NazivPK nvarchar(50),
	@KategorijaID int,
	@Upisano int OUTPUT
AS
	BEGIN
		IF EXISTS (
			SELECT *
			FROM Potkategorija
			WHERE @NazivPK = Naziv AND @KategorijaID = KategorijaID
		)
			BEGIN
				SET @Upisano = 0
			END
		ELSE 
			BEGIN
				INSERT INTO Potkategorija(Naziv, KategorijaID) VALUES (@NazivPK, @KategorijaID)
				SET @Upisano = SCOPE_IDENTITY()
			END
	END

SELECT * FROM Potkategorija


DECLARE @pkOut int

EXEC pKategorijaPotkategorija 'Testiranje', 4, @pkOut OUTPUT

PRINT @pkOut

DELETE FROM Potkategorija WHERE Naziv = 'Testiranje'

DROP PROC pKategorijaPotkategorija

/*
Kreirajte skalarnu funkciju koja prima boju i računa koliko ima proizvoda dotične boje. 
Prikažite primjenu kreirane funkcije tako da je iskoristite u prikazu svih proizvoda,
za prikaz rezultata funkcije u dodatnom stupcu.
*/

CREATE OR ALTER FUNCTION fnProizvodBoje (@Boja nvarchar(15)) 
RETURNS INT
AS
	BEGIN
		DECLARE @otps INT
		SELECT @otps = COUNT(*) FROM Proizvod WHERE Boja = @Boja
		RETURN @otps
	END


SELECT * FROM Proizvod

PRINT dbo.fnProizvodBoje('Crna')

DROP FUNCTION fnProizvodBoje

/*
Kreirajte tabličnu funkciju koja prima podatak IDKomercijalist i prikazuje sve račune dotičnog komercijaliste.
Prikažite primjenu kreirane funkcije tako da je iskoristite za prikaz naziva gradova iz kojih su kupci tih računa.
*/

SELECT * FROM Racun
SELECT * FROM Komercijalist

CREATE OR ALTER FUNCTION fnKomercijalist (@IDKomercijalist int)
RETURNS TABLE
AS
	RETURN SELECT * FROM Racun WHERE KomercijalistID = @IDKomercijalist

SELECT g.Naziv FROM dbo.fnKomercijalist('280') as f
LEFT JOIN Kupac as k ON k.IDKupac = f.KupacID
INNER JOIN Grad as g ON g.IDGrad = k.GradID


DROP FUNCTION fnKomercijalist

/*
Implementirajte CRUD operacije na tablici Kategorija,
ako znate da će se baza podataka koristiti iz programskog jezika koji ne podržava rad s funkcijama na bazi podataka. 
Neka svaka od CRUD operacija bude riješena zasebnim objektom. Demonstrirajte korištenje napravljenih objekata.
*/

CREATE OR ALTER PROC pKategorijaCreate
	@Naziv nvarchar(50)
AS
	BEGIN
		INSERT INTO Kategorija(Naziv) VALUES (@Naziv)
	END

CREATE OR ALTER PROC pKategorijaDelete
	
AS