Do not use the COMMIT or ROLLBACK command until instructed to do so.
1. Describe the publisher table
2. Write INSERT statements to add the following data to the publisher table:
PUBID NAME CONTACT PHONE
6 READ WITH US GEORGE OBAMA 866-888-9999
7 KNOWLEDGE FOR ALL BARAK BUSH 800-1212-5555
8 CHEAP PUBLISHING ROBERT FORD 000-321-4071
9 TEXT AND MORE JOHN CARTER 032-100-7777
10 BOOK BUSTER KENNEDY NIXON 777-555-1212
3. Write a single SELECT statement that will verify your inserts. (Hint: Use the OR modifier in the WHERE clause.)
4. Write a DELETE statement to remove only the new BOOK BUSTER record.
5. Write a SELECT statement to display the distinct shipstate in the orders table. List the result in alphabetical order.
6. Write an SQL statement to update the publisher name from READ WITH US to READ FOR US record.
7. Write a SELECT statement to verify the updated name field for the publisher with id6.
8. Rollback all changes to the publisher table for this transaction.
9. Write a single SELECT statement that will verify that all changes to the publisher table have been rolled back.
10. Open Notepad. Repeat task 2, typing the INSERT statements in Notepad. Save the file as publisher_table_inserts.sql in the same folder that you spool your output to. You have just built a script to insert data into the publisher table.
11. Execute the publisher_table_inserts.sql script from SQL*Plus. Recall the execute symbol (@) and make sure you specify the correct path to the script's folder.
12. Issue a SAVEPOINT command. Name the savepointpublisher_ok.
13. Write SQL statements to make the following updates to the publisher table (be careful with WHERE):
a. Make contact = ‘JOHN TRAVOLTA’ for publisher with id number 4.
b. Make phone = 888-999-7777 for Printing Is Us.
14. Write a SELECT statement to display all modified fields and verify the changes. Display only the modified records.
15. Rollback the changes made to your savepoint.
16. Commit the changes from this transaction.
17. Write DELETE statements to remove the records inserted by your publisher_table_inserts.sql script.
18. Ensure the publisher table is now back to its original condition. Write a SELECT statement to verify that the new records added by running publisher_table_inserts.sql have been removed.
Here is the solution... View the full answer