4)Write PL/SQL or T-SQL procedures to accomplish the following tasks: a. Obtain

Question

4)Write PL/SQL or T-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.

Explanation / Answer

a) CREATE PROCEDURE I_Author_Num_Proc As @AUTHOR_NUM int Begin declare @I_AUTHOR_FIRST varchar(20) declare @I_AUTHOR_LAST varchar(20) set @I_AUTHOR_FIRST = select I_AUTHOR_FIRST from I_AUTHOR_NUM where AUTHOR_NUM=@AUTHOR_NUM set @I_AUTHOR_LAST = select I_AUTHOR_LAST from I_AUTHOR_NUM where AUTHOR_NUM=@AUTHOR_NUM select @I_AUTHOR_FIRST,@I_AUTHOR_LAST End go b) CREATE PROCEDURE I_BOOK_CODE_Proc As @Publisher_Code int Begin declare @I_TITLE varchar(20) declare @I_PUBLISHER_CODE varchar(20) declare @I_PUBLISHER_NAME varchar(20) set @I_TITLE = select TITLE from I_BOOK_CODE where PUBLISHER_CODE=@Publisher_Code set @I_PUBLISHER_CODE = select PUBLISHER_CODE from I_BOOK_CODE where PUBLISHER_CODE=@Publisher_Code set @I_PUBLISHER_NAME = select PUBLISHER_NAME from I_BOOK_CODE where PUBLISHER_CODE=@Publisher_Code select @I_TITLE,@I_PUBLISHER_CODE,@I_PUBLISHER_NAME End go c) Table Defination is not provided. As Instance.. INSERT INTO AUTHOR Values(Author_Number,'FirstName','LastName',PublishedAuthor) 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 Update I_AUTHOR_NUM set I_AUTHOR_LAST='ChangeValue' where Auth_Num='Number' e. Delete From I_AUTHOR_NUM where Auth_Num='Number'

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.