-- 1. 
SELECT	
	IDRacun, 
	CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, 
	BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
FROM Racun AS r
WHERE YEAR(DatumIzdavanja) = 2003

-- 2. 
SELECT 
	Naziv + ' (' + CAST(IDProizvod AS nvarchar(20)) + ')' AS Naziv
FROM Proizvod

-- ili

SELECT 
	Naziv + ' (' + CONVERT(nvarchar(50), IDProizvod) + ')' AS Naziv
FROM Proizvod

-- 3.
SELECT
	*,
	CASE
		WHEN CijenaBezPDV < 100 THEN 'Jeftino'
		WHEN CijenaBezPDV BETWEEN 100 AND 1000 THEN 'Srednje skupo'
		ELSE 'Skupo'
	END AS CijenaKomentar
FROM Proizvod

-- 4.
SELECT 
	Naziv, 
	CASE
		WHEN Boja IS NULL THEN 'Nije definirana'
		ELSE Boja
	END AS Boja
FROM Proizvod

-- ili 

SELECT 
	Naziv, 
	ISNULL(Boja, 'Nije definirana') AS Boja
FROM Proizvod

-- 5.
DECLARE @a int = 51
DECLARE @b int = 0

BEGIN TRY
	PRINT @a / @b
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
	PRINT ERROR_NUMBER()
	PRINT ERROR_SEVERITY()
	PRINT ERROR_PROCEDURE()
	PRINT ERROR_LINE()
END CATCH
GO

-- 6.
CREATE TABLE Vrsta
(
	IDVrsta int CONSTRAINT PK_Vrsta PRIMARY KEY,
	Naziv nvarchar(50)
)
GO

CREATE PROC InsertVrsta
	@IDVrsta int,
	@Naziv nvarchar(50)
AS
INSERT INTO Vrsta (IDVrsta, Naziv) VALUES (@IDVrsta, @Naziv)
GO

EXEC InsertVrsta 1, 'Pingvin'
EXEC InsertVrsta 1, 'Pingvin'
GO

BEGIN TRY
	EXEC InsertVrsta 2, 'Slon'
	EXEC InsertVrsta 2, 'Slon'
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
	PRINT ERROR_NUMBER()
	PRINT ERROR_SEVERITY()
	PRINT ERROR_LINE()
	PRINT ERROR_PROCEDURE()
END CATCH
GO

ALTER PROC InsertVrsta
	@IDVrsta int,
	@Naziv nvarchar(50)
AS
BEGIN TRY
	INSERT INTO Vrsta (IDVrsta, Naziv) VALUES (@IDVrsta, @Naziv)
END TRY
BEGIN CATCH
	PRINT 'Desila se greška: ' + ERROR_MESSAGE()
	PRINT 'Vrsta nije upisana.'
END CATCH
GO

EXEC InsertVrsta 3, 'Ovca'
EXEC InsertVrsta 3, 'Ovca'
GO

-- 7.
CREATE TABLE Student
(
	IDStudent int CONSTRAINT PK_Student PRIMARY KEY IDENTITY,
	Ime nvarchar(50),
	Prezime nvarchar(50),
	JMBAG char(11)
)
GO

CREATE PROC InsertStudent
	@IDStudent int OUTPUT, 
	@Ime nvarchar(50), 
	@Prezime nvarchar(50), 
	@JMBAG char(11)
AS
INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
SET @IDStudent = SCOPE_IDENTITY()
GO

CREATE PROC UpdateStudent
	@IDStudent int, 
	@Ime nvarchar(50), 
	@Prezime nvarchar(50), 
	@JMBAG char(11)
AS
UPDATE Student 
SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
WHERE IDStudent = @IDStudent
GO

CREATE PROC DeleteStudent
	@IDStudent int
AS
DELETE FROM Student WHERE IDStudent = @IDStudent
GO

CREATE PROC SelectStudent
	@IDStudent int
AS
SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

DECLARE @NoviIDStudenta int
EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, @Ime = 'Ana', @Prezime = 'Aniæ', @JMBAG = '11224451253'
PRINT @NoviIDStudenta

SELECT * FROM GetStudent(1)

EXEC UpdateStudent 1, 'Ana', 'Anić Mirić', '11224451253'

EXEC SelectStudent 1

EXEC DeleteStudent 1


-- 8.
CREATE PROC MergeStudent
	@IDStudent int OUTPUT, 
	@Ime nvarchar(50), 
	@Prezime nvarchar(50), 
	@JMBAG char(11)
AS
IF Exists(SELECT * FROM Student WHERE IDStudent = @IDStudent)
	UPDATE Student 
	SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
	WHERE IDStudent = @IDStudent
ELSE
	INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
	SET @IDStudent = SCOPE_IDENTITY()
GO

CREATE PROC DeleteStudent
	@IDStudent int
AS
DELETE FROM Student WHERE IDStudent = @IDStudent
GO

CREATE PROC GetStudent
	@IDStudent int
AS
SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

DECLARE @NoviIDStudenta int
EXEC MergeStudent @NoviIDStudenta OUTPUT, 'Ana', 'Anić', '11224451253'
PRINT @NoviIDStudenta

EXEC GetStudent 2

EXEC MergeStudent 2, 'Ana', 'Anić Mirić', '11224451253'

EXEC GetStudent 2

EXEC DeleteStudent 2

EXEC GetStudent 2
GO

-- 9.
CREATE PROC ChangeStudent
	@Operacija char(1),
	@IDStudent int OUTPUT, 
	@Ime nvarchar(50), 
	@Prezime nvarchar(50), 
	@JMBAG char(11)
AS
IF @Operacija = 'U'
	UPDATE Student 
	SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
	WHERE IDStudent = @IDStudent
ELSE IF @Operacija = 'I' BEGIN
	INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
	SET @IDStudent = SCOPE_IDENTITY()
END
ELSE IF @Operacija = 'D'
	DELETE FROM Student WHERE IDStudent = @IDStudent
GO

CREATE PROC GetStudent
	@IDStudent int
AS
SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

DECLARE @NoviIDStudenta int
EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Anić', '11224451253'
PRINT @NoviIDStudenta

EXEC GetStudent 3

EXEC ChangeStudent 'U', 3, 'Ana', 'Anić Mirić', '11224451253'

EXEC GetStudent 3

EXEC ChangeStudent 'D', 3, null, null, null

EXEC GetStudent 3
GO

-- 10.

CREATE FUNCTION dbo.GetStudent
(
    @IDStudent int
)
RETURNS TABLE
AS
    RETURN SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

SELECT * FROM GetStudent(1)
GO