4. Write PL/SQL procedures to accomplish the following tasks:

a. Obtain the first name and last name of the author whose number currently is stored in I_AUTHOR_NUM. Place these values in the variables I_AUTHOR_FIRST and I_AUTHOR_LAST. Output the contents of I_AUTHOR_NUM, I_AUTHOR_FIRST, and I_AUTHOR_LAST.

b. Obtain the book title, publisher code, and publisher name for every book whose code currently is stored in I_BOOK_CODE. Place these values in the variables I_TITLE, I_PUBLISHER_CODE, and I_PUBLISHER_NAME, respectively. Output the contents of I_TITLE, I_PUBLISHER_CODE, and I_PUBLISHER_NAME.

c. Add a row to the AUTHOR table.

d. Change the last name of the author whose number is stored in I_AUTHOR_NUM to the value currently found in I_AUTHOR_LAST.

e. Delete the author whose number is stored in I_AUTHOR_NUM.

5. Write a PL/SQL or T-SQL procedure to retrieve and output the book code, title, book type, and price for every book whose publisher code is stored in I_PUBLISHER_CODE.

6. Write Access functions to accomplish the following tasks:

a. Delete the author whose number is stored in I_AUTHOR_NUM.

b. Change the last name of the author whose number is stored in I_AUTHOR_NUM to the value currently found in I_AUTHOR_LAST.

c. Retrieve and output the book code, title, book type, and price for every book whose publisher code is stored in I_PUBLISHER_CODE.

7. Write a stored procedure in PL/SQL or T-SQL that will change the price of a book with a given book code. How would you use this stored procedure to change the price of book 0189 to $8.49?

8. Assume the BOOK table contains a column called TOTAL_ON_HAND that represents the total units on hand in all branches for that book. Following the style shown in the text, write the code in PL/SQL or T-SQL for the following triggers:

a. When inserting a row in the INVENTORY table, add the ON_HAND value to the

TOTAL_ON_HAND value for the appropriate book.

b. When updating a row in the INVENTORY table, add the difference between the new ON_HAND value and the old ON_HAND value to the TOTAL_ON_HAND value for the appropriate book.

c. When deleting a row in the INVENTORY table, subtract the ON_HAND value from the TOTAL_ON_HAND value for the appropriate book.