sql vjezba 9

    USE pubs


    /*
    Kreirati proceduru za upis podataka u tabelu authors.
     Izvršiti proceduru, tj. dodati novog autora sa testnim podacima. */

     CREATE PROCEDURE usp_authors_insert
     (
            @au_ID VARCHAR(11),
            @au_Iname 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,
            @contact bit
     )
     AS
     BEGIN
     INSERT INTO authors
     VALUES (@au_ID, @au_Iname, @au_Fname, @phone, @adress, @city, @state, @zip, @contact)
     END

     EXEC usp_authors_insert '123-12-1234', 'Blaho','Autor','123123',NULL,NULL,NULL,NULL,1

     SELECT * FROM authors WHERE au_fname LIKE 'Autor'

     /* Kreirati proceduru za izmjenu podataka u tabeli authors.
     Izvršiti proceduru, tj. izmijeniti određene podatke prethodno dodanog autora.*/

     DROP PROCEDURE usp_authors

     CREATE PROCEDURE usp_authors_update
     (
            @au_ID VARCHAR(11),
            @au_Iname 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,
            @contact bit
     )
     AS
     BEGIN
            UPDATE authors
            SET au_lname = @au_Iname, au_fname = @au_Fname, phone = @phone,
                    [address] = @adress, city = @city, [state] = @state, zip = @zip, [contract] = @contact
            WHERE au_id = @au_ID
     END

      EXEC usp_authors_update '123-12-1234', 'Blaho','Autor','666 666',NULL,NULL,NULL,NULL,1

     SELECT * FROM authors WHERE au_fname LIKE 'Autor'

     /*Kreirati proceduru za brisanje zapisa iz tabele authors.
     Izvršiti proceduru, tj. obrisati prethodno dodanog autora.*/

     CREATE PROCEDURE usp_authors_delete
     (
            @au_id VARCHAR(11)
     )
     AS
     BEGIN
            DELETE FROM authors
            WHERE au_id = @au_id
     END

     EXEC usp_authors_delete '123-12-1234'

     SELECT * FROM authors WHERE au_fname LIKE 'Autor'

     /* Izmijeniti proceduru kreiranu u zadatku 1.
      U istoj proceduri, dodanog autora pridružiti kao koautora na dvije postojeće knjige.
      U proceduri koristiti dvije INSERT komande (Tabele: authors, titleauthor).
      Izvršiti proceduru, tj. dodati novog autora sa njegovim knjigama. */

      CREATE PROCEDURE usp_authors_insert2
     (
            @au_ID VARCHAR(11),
            @au_Iname 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,
            @contact bit,
            @au_ord tinyint,
            @royaltyper INT,
            @title_ID nvarchar(6)
     )
     AS
     BEGIN
     INSERT INTO authors
     VALUES (@au_ID, @au_Iname, @au_Fname, @phone, @adress, @city, @state, @zip, @contact)

     INSERT INTO titleauthor
     VALUES (@au_ID, @title_ID, @au_ord,@royaltyper)
     END

       EXEC usp_authors_insert2 '123-12-1234', 'Blaho','Autor',
       '666 666',NULL,NULL,NULL,NULL,1,2,1,'PS3333'

     SELECT * FROM titleauthor

     /*Kreirati pogled (view) sa sljedećim podacima:
     naziv izdavača, ime i prezime autora (odvojeno),
      naziv knjige, zarada od prodaje knjige/a. */

      CREATE VIEW pogled_izdavac_autor_zarada
      AS
      SELECT [pub_name],[au_lname] + ' ' + [au_fname] AS autor, [title], SUM([qty]*[price]) AS zarada
       FROM titles AS T JOIN titleauthor AS TA ON T.title_id = TA.title_id
      JOIN authors AS A ON A.au_id = TA.au_id JOIN sales AS S ON S.title_id = T.title_id
      JOIN publishers AS P ON P.pub_id = T.pub_id
      GROUP BY [pub_name],[au_lname],[au_fname],[title]

      SELECT * FROM pogled_izdavac_autor_zarada

      /*
      Kreirati proceduru koja prima sljedeće parametre:
      ime i prezime autora, naziv izdavača, naziv knjige.
      U zavisnosti od proslijeđenog parametra procedura treba da vrati
       zaradu od prodaje knjige/a. Koristiti view kreiran u zadatku 5. */

       CREATE PROCEDURE usp_izdavac_autor_zarada
       (
                    @auname VARCHAR(61) = NULL,
                    @pubname VARCHAR(40) = NULL,
                    @title VARCHAR(80) = NULL
       )
      AS
      SELECT [pub_name],[au_lname] + ' ' + [au_fname] AS autor, [title], SUM([qty]*[price]) AS zarada
       FROM titles AS T JOIN titleauthor AS TA ON T.title_id = TA.title_id
      JOIN authors AS A ON A.au_id = TA.au_id JOIN sales AS S ON S.title_id = T.title_id
      JOIN publishers AS P ON P.pub_id = T.pub_id
      WHERE ([au_lname] + ' ' + [au_fname] LIKE @auname OR @auname IS NULL) AND
                    ([pub_name] LIKE @pubname OR @pubname IS NULL) AND
                    ( [title] LIKE @title OR @title IS NULL)
      GROUP BY [pub_name],[au_lname],[au_fname],[title]

      EXEC usp_izdavac_autor_zarada @title = 'The Gourmet Microwave'

/*Kreirati trigger (INSTEAD OF) koji će spriječiti izvršavanje DELETE komande nad tabelom titleauthor.
Ukoliko se pokuša obrisati zapis u tabeli titleauthor ispisati odgovarajuću poruku.
Koristiti komandu PRINT za ispis poruke. */

CREATE TRIGGER tr_delete
ON [dbo].[titleauthor]
instead OF DELETE
AS
print ' nema brisanja!'

SELECT * FROM titleauthor

DELETE FROM titleauthor WHERE title_id = 'PS3333'

/*Kreirati proceduru koja briše autora dodanog u zadatku 4, zajedno sa svim njegovim knjigama.
Izvršiti proceduru, tj. pokušati obrisati autora.
Trigger kreiran u zadatku 7 treba da spriječi izvršavanje ove procedure.*/

CREATE PROCEDURE usp_titleautor_delete
(
    @auid VARCHAR(11)
)
AS
BEGIN
DELETE FROM titleauthor WHERE au_id = @auid

DELETE FROM authors WHERE au_id = @auid

END

EXEC usp_titleautor_delete '123-12-1234'