This assignment will demonstrate the use of transactions. Specifically, changes

Question

This assignment will demonstrate the use of transactions. Specifically, changes will be rolled back to a previous state prior to performing a commit. The assignment will be composed of SQL Plus DML and DCL statements. Write a script that performs the following.

Login to SQL Plus as user SCOTT

Issue a statement to display the employee number, employee name and salary from the "emp" table

Issue a statement to begin a transaction

Issue statement(s) to insert 4 new records into the "emp" table.

Issue a statement that repeats Step 2 (to verify that all changes have been added).

Issue a statement to rollback the changes

Issue a statement that repeats Step 2 ( to verify that all changes have been undone)       

                    Save the script as Project10.sql

Notes:

The Oracle Universal Installer (OUI) creates the EMP, DEPT, .BONUS, SALGRADE, CUSTOMER, ORD, ITEM, and PRODUCT.

The following query will display all tables defined in a user's schema.

SELECT table_name FROM user_tables;

The EMP table has a FK constraint on the DEPTNO field (i.e. that value must exist in the DEPT table)

Explanation / Answer

Answer:

Project10.sql:

SELECT e.empno, e.ename, e.sal from EMP e;
BEGIN TRANSACTION T1

INSERT INTO emp VALUES(50, 'CHRIS', 'SALESMAN', null,to_date('24-03-2018','dd-mm-yyyy'),50000, null, 30 );
INSERT INTO emp VALUES( 51, 'RON', 'SALESMAN', null, to_date('24-03-2018','dd-mm-yyyy'), 50000, null, 30);
INSERT INTO emp VALUES( 52, 'VICKIE', 'MANAGER', null, to_date('24-03-2018','dd-mm-yyyy'), 50000, null, 30);
INSERT INTO emp VALUES( 53, 'NORMAN', 'MANAGER', 7839, to_date('24-03-2018','dd-mm-yyyy'), 50000, null, 30);

SELECT e.empno, e.ename, e.sal from EMP e;

ROLLBACK TRANSACTION T1

SELECT e.empno, e.ename, e.sal from EMP e;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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