Creating Stored Procedures and User Defined Functions

This HW uses the books database from the book "SQL, Visual Quickstart Guide, 2nd edition" by Fehily. The following is the ERD for the database. See page 39 in the book for more information about the tables.

1. Write a stored proceudre called "createBackupTables" that uses "select into" to create the following tables. Each "Backup" table should have the same structure as the original table. If the backup table already exists, it should be dropped first.
   
   
1. title_authors_Backup
2. publishers_Backup
3. titles_backup
4. royalties_backup
   
   
   
   
2. Write a stored procedure named "removePublisher" that removes a publisher record and all associated records from the title_authors, titles and royalties tables.
   
   The stored procedure should place copies of the deleted records in the Backup tables (created in the last assignment). If any backup table does not exist, the procedure should call "createBackupTables" (from the previous question).
   
   The procedure should take a single input parameter for the id of the publisher. The procedure should have 3 OUTPUT parameters that will be assigned the number of records removed from each of the 3 tables, title_authors, titles and royalties.
   
   Make sure that all relevant SQL statements are coded inside a transaction.
   
   
   
3. Write a stored procdure named unremovePublisher that undoes the effects of the previous procedure. Make sure that all relevant SQL statements are coded inside a transaction.
   
   

4. Write a user defined function that returns the total number of pages that a specific author has written in all his books.
   
   The function should take a single parameter for the author id (au_id) and return an integer.