aaa

USE pubs

--ZAD 1
CREATE PROCEDURE usp_Authors_Insert_Karlo
(
	@au_id varchar(11),
	@au_lname varchar(40),
	@au_fname varchar(20),
	@phone char(12),
	@adress varchar(40) = NULL,
	@city varchar(20) = NULL,
	@state char(2) = NULL,
	@zip char(5) = NULL,
	@contract bit

)
AS
BEGIN
	INSERT INTO authors
	VALUES(@au_id,@au_lname,@au_fname,@phone,@adress,@city,@state,@zip,@contract)
END

EXEC usp_Authors_Insert_Karlo @au_id = '555-46-1111',
								@au_lname = 'Kresic',
								@au_fname = 'Karlo',
								@phone = '54321',
								@adress = 'Nesto 123',
								@city = 'Capljina',
								@state = 'BA',
								@zip = '88307',
								@contract = 1

SELECT * FROM authors
--zad2

CREATE PROCEDURE usp_Authors_Update_Karlo
(
	@au_id varchar(11),
	@au_lname varchar(40),
	@au_fname varchar(20),
	@phone char(12),
	@adress varchar(40) = NULL,
	@city varchar(20) = NULL,
	@state char(2) = NULL,
	@zip char(5) = NULL,
	@contract bit

)
AS
BEGIN
	UPDATE authors SET au_lname = @au_lname,
						au_fname = @au_fname,
						phone = @phone,
						address = @adress,
						city = @city,
						state = @state,
						zip = @zip,
						contract = @contract
	WHERE au_id = @au_id
END

EXEC usp_Authors_Update_Karlo @au_id = '555-46-1111',
								@au_lname = 'Kresic',
								@au_fname = 'Karlo',
								@phone = '654321',
								@adress = 'Nesto 123',
								@city = 'Sarajevo',
								@state = 'BA',
								@zip = '88307',
								@contract = 1

								SELECT * FROM authors
--3 del

CREATE PROCEDURE usp_Authors_Delete_Karlo
(
	@au_id varchar(11)
)
AS
BEGIN
	DELETE FROM authors
	WHERE au_id = @au_id
END

EXEC usp_Authors_Delete_Karlo @au_id = '555-46-1111'

--4
ALTER PROCEDURE usp_Authors_Insert_Karlo
(
	@au_id varchar(11),
	@au_lname varchar(40),
	@au_fname varchar(20),
	@phone char(12),
	@adress varchar(40) = NULL,
	@city varchar(20) = NULL,
	@state char(2) = NULL,
	@zip char(5) = NULL,
	@contract bit

)
AS
BEGIN
	INSERT INTO authors
	VALUES(@au_id,@au_lname,@au_fname,@phone,@adress,@city,@state,@zip,@contract)

	INSERT INTO titleauthor (au_id,title_id,au_ord)
	SELECT TOP 2 @au_id, title_id, 3
	FROM titles
END

EXEC usp_Authors_Insert_Karlo @au_id = '555-46-1111',
								@au_lname = 'Kresic',
								@au_fname = 'Karlo',
								@phone = '54321',
								@adress = 'Nesto 123',
								@city = 'Capljina',
								@state = 'BA',
								@zip = '88307',
								@contract = 1
--5
CREATE VIEW view_ProdajaKnjiga_Karlo AS
SELECT P.pub_name, A.au_fname, A.au_lname, T.title, SUM(T.price * S.qty) AS 'Zarada'
FROM publishers AS P JOIN titles AS T
	ON P.pub_id = T.pub_id JOIN titleauthor as TA
	ON TA.title_id = T.title_id JOIN authors AS A
	ON A.au_id  = TA.au_id JOIN sales AS S
	ON T.title_id = S.title_id
GROUP BY P.pub_name, A.au_fname, A.au_lname, T.title

SELECT * FROM view_ProdajaKnjiga_Karlo

--6
CREATE PROCEDURE usp_ProdajaKnjiga_SelectByParameters_Karlo
(
	@au_fname varchar(20) = NULL,
	@au_lname varchar(40) = NULL,
	@pub_name varchar(50) = NULL,
	@title varchar(80) = NULL
)
AS
BEGIN
	SELECT title, SUM(Zarada)
	FROM view_ProdajaKnjiga_Karlo
	WHERE (au_fname = @au_fname OR @au_fname IS NULL) AND
			(au_lname = @au_lname OR @au_lname IS NULL) AND
			(pub_name = @pub_name OR @pub_name IS NULL) AND
			(title = @title OR @title IS NULL)
	GROUP BY title
END

EXEC usp_ProdajaKnjiga_SelectByParameters_Karlo @pub_name = 'New Moon Books',
												@au_fname = 'Marina',
												@title = 'Is Anger the Enemy?'

--7
CREATE TRIGGER tr_TitleAuthor_IO_Delete_Karlo
ON titleauthor INSTEAD OF DELETE AS
BEGIN
	PRINT 'Ne moze se brisati autor sa knjige'
	ROLLBACK
END

--8
CREATE PROCEDURE usp_TitleAuthors_Delete_Karlo
(
	@au_id varchar(11),
	@au_lname varchar(40),
	@au_fname varchar(20),
	@phone char(12),
	@adress varchar(40) = NULL,
	@city varchar(20) = NULL,
	@state char(2) = NULL,
	@zip char(5) = NULL,
	@contract bit

)
AS
BEGIN
	DELETE FROM titleauthor
	WHERE au_id = @au_id

	DELETE FROM authors
	WHERE au_id = @au_id
END

EXEC usp_TitleAuthors_Delete_Karlo