-----------------------------------------------------------------------------------
/*
Vježbe se rade na bazi AdventureWorksOBP. 
*/
USE AdventureWorksOBP
GO
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 1.
/*
PROCEDURE BEZ PARAMETARA
Napišite proceduru koja dohvaća sve retke iz tablice Drzava. 
Pozovite proceduru. 
Promijenite proceduru tako da vraća rezultate poredane padajuće po nazivu države. 
Uklonite proceduru.
*/
CREATE PROC p7
AS
SELECT * FROM Drzava
GO

EXEC p7
GO

ALTER PROC p7
AS
SELECT * FROM Drzava ORDER BY Naziv DESC
GO

EXEC p7
GO

DROP PROC p7
GO
-----------------------------------------------------------------------------------
-- Zadatak 2.
/*
PROCEDURE BEZ PARAMETARA
Napišite proceduru koja dohvaća prvih 5 redaka iz tablice Racun, prvih 5 redaka iz tablice Stavka i prvih 5 redaka iz tablice Proizvod. 
Pozovite proceduru. 
Uklonite proceduru.
*/
CREATE PROC p8
AS
SELECT TOP 5 * FROM Racun
SELECT TOP 5 * FROM Stavka
SELECT TOP 5 * FROM Proizvod
GO

EXEC p8
GO

DROP PROC p8
GO
-----------------------------------------------------------------------------------
-- Zadatak 3.
/*
PROCEDURE S ULAZNIM PARAMETRIMA
Napišite proceduru koja prima @ID proizvoda i vraća samo taj proizvod iz tablice Proizvod. 
Pozovite proceduru na oba načina. 
Uklonite proceduru.
*/
CREATE PROC p9
	@IDPr int
AS
SELECT * FROM Proizvod WHERE IDProizvod = @IDPr
GO

EXEC p9 1
EXEC p9 @IDPr = 1
GO

DROP PROC p9
GO
-----------------------------------------------------------------------------------
-- Zadatak 4.
/*
PROCEDURE S ULAZNIM PARAMETRIMA
Napišite proceduru koja prima dvije cijene i vraća nazive i cijene svih proizvoda čija je cijena u zadanom rasponu. 
Pozovite proceduru na oba načina. 
Uklonite proceduru.
*/
CREATE PROC p10
	@Cijena1 money,
	@Cijena2 money
AS
SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV BETWEEN @Cijena1 AND @Cijena2
GO

EXEC p10 500, 1000
EXEC p10 @Cijena1 = 500, @Cijena2 = 1000
EXEC p10 @Cijena2 = 1000, @Cijena1 = 500
GO

DROP PROC p10
GO
-----------------------------------------------------------------------------------
-- Zadatak 5.
/*
PROCEDURE S ULAZNIM PARAMETRIMA
Napišite proceduru koja prima naziv države i naziv grada. 
Neka procedura umetne grad koji pripada zadanoj državi. 
Pazite na to postoji li već država upisana u tablicu Drzava ili ne postoji. 
Ako postoji, nemojte je umetati ponovno. 
Pozovite proceduru za državu “Japan” i grad “Osaka”. 
Pozovite proceduru za državu “Japan” i grad “Tokyo” i potvrdite da radi ispravno. 
Uklonite proceduru.
*/
CREATE PROC p11
	@Drzava nvarchar(50),
	@Grad nvarchar(50)
AS
DECLARE @IDDrzava int
SELECT @IDDrzava = IDDrzava FROM Drzava WHERE Naziv = @Drzava

IF @IDDrzava IS NOT NULL BEGIN
	INSERT INTO Grad VALUES (@Grad, @IDDrzava)
END
ELSE BEGIN
	INSERT INTO Drzava VALUES (@Drzava)
	SET @IDDrzava = SCOPE_IDENTITY()
	INSERT INTO Grad VALUES (@Grad, @IDDrzava)
END
GO

EXEC p11 'Japan', 'Osaka'
SELECT * FROM Grad
SELECT * FROM Drzava

EXEC p11 'Japan', 'Tokyo'
SELECT * FROM Grad
SELECT * FROM Drzava
GO

DROP PROC p11
GO
-----------------------------------------------------------------------------------
-- Zadatak 6.
/*
PROCEDURE S IZLAZNIM PARAMETRIMA
Napišite proceduru koja prima parametre @IDProizvod i @Boja. Parametar @Boja neka bude izlazni parametar. 
Neka procedura za zadani proizvod vrati njegovu boju pomoću izlaznog parametra. 
Pozovite proceduru i ispišite vraćenu vrijednost. 
Uklonite proceduru.
*/
CREATE PROC p12
	@IDProizvod int,
	@Boja nvarchar(15) OUTPUT
AS
SELECT @Boja = Boja FROM Proizvod WHERE IDProizvod = @IDProizvod
GO

DECLARE @Farba nvarchar(15)
EXEC p12 320, @Farba OUTPUT
PRINT @Farba
GO

DROP PROC p12
GO
-----------------------------------------------------------------------------------
-- Zadatak 7.
/*
PROCEDURE S IZLAZNIM PARAMETRIMA
Napišite proceduru koja prima kriterij po kojemu ćete filtrirati prezimena iz tablice Kupac. 
Neka procedura pomoću izlaznog parametra vrati broj zapisa koji zadovoljavaju zadani kriterij. 
Neka procedura vrati i sve zapise koji zadovoljavaju kriterij. 
Pozovite proceduru i ispišite vraćenu vrijednost. 
Uklonite proceduru.
*/
CREATE PROC p13
	@Filter nvarchar(50),
	@BrojZapisa int OUTPUT
AS
SELECT @BrojZapisa = COUNT(*) FROM Kupac WHERE Prezime LIKE @Filter
SELECT * FROM Kupac WHERE Prezime LIKE @Filter
GO

DECLARE @Cnt int
EXEC p13 '%hu%', @Cnt OUTPUT
PRINT @Cnt
GO

DROP PROC p13
GO
-----------------------------------------------------------------------------------
-- Zadatak 8.
/*
PROCEDURE S IZLAZNIM PARAMETRIMA
Napišite proceduru koja za zadanog komercijalistu pomoću izlaznih parametara vraća njegovo ime 
i prezime te ukupnu zaraðenu količinu novaca.
*/
CREATE PROC p14
	@KomercijalistID int,
	@Ime nvarchar(50) OUTPUT,
	@Prezime nvarchar(50) OUTPUT,
	@Zaradio money OUTPUT
AS
SELECT @Ime = Ime, @Prezime = Prezime
FROM Komercijalist
WHERE IDKomercijalist = @KomercijalistID

SELECT @Zaradio = SUM(s.UkupnaCijena) 
FROM Racun AS r
INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
WHERE r.KomercijalistID = @KomercijalistID
GO

DECLARE @Ime nvarchar(50), @Prezime nvarchar(50), @Zaradio money
EXEC p14 276, @Ime OUTPUT, @Prezime OUTPUT, @Zaradio OUTPUT
PRINT @Ime
PRINT @Prezime
PRINT @Zaradio
GO

DROP PROC p14
GO
-----------------------------------------------------------------------------------
-- Zadatak 9. jedno moguće rješenje (bolje).
/*
DODATNI ZADACI
Napišite proceduru koja umeće novu kategoriju i kroz izlazni parametar vraća generiranu IDENTITY vrijednost. 
Ako već postoji kategorija zadanog imena, ne treba je upisivati ponovno i u tom slučaju kroz izlazni parametar vratite vrijednost -1. 
Pozovite proceduru i ispišite vraćenu vrijednost. 
Uklonite proceduru.
*/
CREATE PROC p15
	@Kategorija nvarchar(50),
	@ID int OUTPUT
AS
IF NOT EXISTS(SELECT * FROM Kategorija WHERE Naziv = @Kategorija) 
	BEGIN
		INSERT INTO Kategorija VALUES (@Kategorija)
		SET @ID = SCOPE_IDENTITY()
	END
ELSE 
	BEGIN
		SET @ID = -1
	END
GO
-----------------------------------------------------------------------------------
-- Zadatak 9. drugo moguće rješenje (lošije).
/*
DODATNI ZADACI
Napišite proceduru koja umeće novu kategoriju i kroz izlazni parametar vraća generiranu IDENTITY vrijednost. 
Ako već postoji kategorija zadanog imena, ne treba je upisivati ponovno i u tom slučaju kroz izlazni parametar vratite vrijednost -1. 
Pozovite proceduru i ispišite vraćenu vrijednost. 
Uklonite proceduru.
*/
CREATE PROC p15
	@Kategorija nvarchar(50),
	@ID int OUTPUT
AS
DECLARE @Broj int
SELECT @Broj = COUNT(*) FROM Kategorija WHERE Naziv = @Kategorija

IF @Broj = 0 BEGIN
	INSERT INTO Kategorija VALUES (@Kategorija)
	SET @ID = SCOPE_IDENTITY()
END
ELSE BEGIN
	SET @ID = -1
END
GO

DECLARE @ID int
EXEC p15 'Glačala', @ID OUTPUT
PRINT @ID
GO

DECLARE @ID int
EXEC p15 'Glačala', @ID OUTPUT
PRINT @ID
GO

DROP PROC p15
GO
-----------------------------------------------------------------------------------
-- Zadatak 10.
/*
DODATNI ZADACI
Napišite proceduru koja kroz tri izlazna parametra vraća najmanju, najveću i prosječnu cijenu proizvoda iz tablice Proizvod. 
Neka procedura vrati i sve proizvode koji imaju cijenu veću od 0 i manju od prosječne. 
Pozovite proceduru, ispišite vraćene vrijednosti i uklonite proceduru.
*/
CREATE PROC p16
	@Najmanja money OUTPUT,
	@Najveca money OUTPUT,
	@Prosjecna money OUTPUT
AS
SELECT 
	@Najmanja = MIN(CijenaBezPDV),
	@Najveca = MAX(CijenaBezPDV),
	@Prosjecna = AVG(CijenaBezPDV)
FROM Proizvod

SELECT * FROM Proizvod WHERE CijenaBezPDV > 0 AND CijenaBezPDV < @Prosjecna
GO

DECLARE @Najmanja money, @Najveca money, @Prosjecna money
EXEC p16 @Najmanja OUTPUT, @Najveca OUTPUT, @Prosjecna OUTPUT
PRINT @Najmanja
PRINT @Najveca
PRINT @Prosjecna
GO

DROP PROC p16
GO
-----------------------------------------------------------------------------------
-- Zadatak 11.
/*
PROCEDURE S RETURN PARAMETRIMA
Napišite proceduru koja prima ime i prezime osobe 
i vraća 0 kao RETURN parametar ako osoba postoji u tablici, 
odnosno 200 ako osoba ne postoji. 
Pozovite proceduru i ispišite RETURN vrijednost. 
*/
CREATE PROC p15
	@Ime nvarchar(50),
	@Prezime nvarchar(50)
AS
DECLARE @BrojOsoba int
SELECT @BrojOsoba = Count(*) FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime

IF @BrojOsoba = 0
	RETURN 200
ELSE
	RETURN 0
GO

DECLARE @RetVal int
EXEC @RetVal = p15 'Amy', 'Albertss'
PRINT @RetVal
GO
-----------------------------------------------------------------------------------
-- Zadatak 12.
/*
PROCEDURE S RETURN PARAMETRIMA
Promijenite proceduru iz prethodnog zadatka tako da bude zaštićena.
*/
ALTER PROC p15
	@Ime nvarchar(50),
	@Prezime nvarchar(50)
WITH ENCRYPTION
AS
DECLARE @BrojOsoba int
SELECT @BrojOsoba = Count(*) FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime

IF @BrojOsoba = 0
	RETURN 200
ELSE
	RETURN 0
GO
-----------------------------------------------------------------------------------
-- Zadatak 13.
/*
PROCEDURE S RETURN PARAMETRIMA
Uklonite proceduru.
*/
DROP PROC p15
GO
-----------------------------------------------------------------------------------