This assignment will demonstrate the use of transactions. Specifically, changes
ID: 3736939 • Letter: T
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
Please find my answer:
Q1) Issue a statement to display the employee number, employee name and salary from the "emp" table
SELECT EMP_NO, EMP_NAME, SALARY FROM EMP;
Q2) Issue a statement to begin a transaction Issue statement(s) to insert 4 new records into the "emp" table.
INSERT INTO EMP (EMP_NO, EMP_NAME, SALARY) VALUES (437227, 'GOPAL MALAKER', 45000);
COMMIT;
INSERT INTO EMP (EMP_NO, EMP_NAME, SALARY) VALUES (123456, 'JOHN KUHL', 78965);
COMMIT;
INSERT INTO EMP (EMP_NO, EMP_NAME, SALARY) VALUES (87654, 'JOHN BLACK', 32000);
INSERT INTO EMP (EMP_NO, EMP_NAME, SALARY) VALUES (90087, 'ED FENTON', 123456);
Q3) Issue a statement that repeats Step 2 (to verify that all changes have been added). Issue a statement to rollback the changes
SELECT EMP_NO, EMP_NAME, SALARY FROM EMP; -- By select query we can note that all changes have been successfully inserted into DB
ROLLBACK; -- By issuing ROLLBACK all uncommitted changes are roll backed till the previous commit was issued.
Q3) Issue a statement that repeats Step 2 ( to verify that all changes have been undone)
SELECT EMP_NO, EMP_NAME, SALARY FROM EMP; -- By select query we can note that all changes have been successfully rolled back from the DB
Please let me know in case of any clarifications required. Thanks!