Quizzuri Oracle

SQL Programming #16

Posted by Feroviarul on

Oracle Database Programming with SQL Section 16

1.         Steven King’s row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;
UPDATE employees
SET salary = 30000
WHERE employee_id = 100;
The user’s database session now ends abnormally. What is now King’s salary in the table?
♦78000
♦30000
♦24000
♦48000 (*)
2.         Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES (‘A’);
INSERT INTO mytab VALUES (‘B’);
COMMIT;
INSERT INTO mytab VALUES (‘C’);
ROLLBACK;
Which rows does the table now contain?
♦A, B, and C
♦A and B (*)
♦C
♦None of the above
3.         If Oracle crashes, your changes are automatically rolled back. True or False?
♦True (*)
♦False
4.         Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;
You want to retain all the employees with a salary of 15000; What statement would you execute next?
♦ROLLBACK;
♦ROLLBACK TO SAVEPOINT upd1_done; (*)
♦ROLLBACK TO SAVEPOINT upd2_done;
♦ROLLBACK TO SAVE upd1_done;
♦There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.
5.         COMMIT saves all outstanding data changes? True or False?
♦True (*)
♦False
6.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; — 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; — 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = ‘Smith’;
How would you undo the last Update only?
♦ROLLBACK to SAVEPOINT Del_Done; (*)
♦COMMIT Del_Done;
♦There is nothing you can do.
♦ROLLBACK UPDATE;
7.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; — 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = ‘Smith’;
What happens if you issue a Rollback statement?
♦The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)
♦Both the UPDATE and the INSERT will be rolled back.
♦The INSERT is undone but the UPDATE is committed.
♦Nothing happens.
8.         Which SQL statement is used to remove all the changes made by an uncommitted transaction?
♦REVOKE;
♦ROLLBACK TO SAVEPOINT;
♦ROLLBACK; (*)
♦UNDO;
9.         You need not worry about controlling your transactions. Oracle does it all for you. True or False?
♦True
♦False (*)
10.       When you logout of Oracle, your data changes are automatically rolled back. True or False?
♦True
♦False (*)
11.       User BOB’s CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;
What result will JANE see?
♦22
♦20 (*)
♦JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
♦2
12.       Which of the following best describes the term “read consistency”?
♦It prevents other users from querying a table while updates are being executed on it
♦It prevents other users from seeing changes to a table until those changes have been committed (*)
♦It prevents users from querying tables on which they have not been granted SELECT privilege
♦It ensures that all changes to a table are automatically committed
13.       A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?
♦An object privilege
♦An update statement
♦A savepoint (*)
♦A sequence
♦A database link
14.       If a database crashes, all uncommitted changes are automatically rolled back. True or False?
♦True (*)
♦False
15.       If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?
♦True
♦False (*)
16.         Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES (‘A’);
INSERT INTO mytab VALUES (‘B’);
COMMIT;
INSERT INTO mytab VALUES (‘C’);
ROLLBACK;
Which rows does the table now contain?
♦A, B, and C
♦A and B (*)
♦C
♦None of the above
17.         If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?
♦True
♦False (*)
18.         User BOB’s CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;
What result will JANE see?
♦JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
♦2
♦20 (*)
♦22
19.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; — 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = ‘Smith’;
What happens if you issue a Rollback statement?
♦The update of last_name is undone, but the insert was committed by the CREATE INDEX
statement. (*)
♦Both the UPDATE and the INSERT will be rolled back.
♦The INSERT is undone but the UPDATE is committed.
♦Nothing happens.
20.         Steven King’s row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;
UPDATE employees
SET salary = 30000
WHERE employee_id = 100;
The user’s database session now ends abnormally. What is now King’s salary in the table?
♦24000
♦30000
♦48000 (*)
♦78000
21.         When you logout of Oracle, your data changes are automatically rolled back. True or False?
♦True
♦False (*)

22.         Which of the following best describes the term “read consistency”?

♦It prevents users from querying tables on which they have not been granted SELECT privilege

♦It prevents other users from querying a table while updates are being executed on it
♦It prevents other users from seeing changes to a table until those changes have been committed (*)
♦It ensures that all changes to a table are automatically committed
23.         Examine the following statements:
INSERT INTO emps SELECT * FROM employees; — 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; — 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = ‘Smith’;
How would you undo the last Update only?
♦ROLLBACK UPDATE;
♦There is nothing you can do.
♦COMMIT Del_Done;
♦ROLLBACK to SAVEPOINT Del_Done; (*)
24.         If a database crashes, all uncommitted changes are automatically rolled back. True or False?
♦True (*)
♦False
25.       A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?
♦An update statement
♦A savepoint (*)
♦A database link
♦An object privilege
♦A sequence
26.       You need not worry about controlling your transactions. Oracle does it all for you. True or False?
♦True
♦False (*)
27.       If Oracle crashes, your changes are automatically rolled back. True or False?
♦True (*)
♦False
28.       Which SQL statement is used to remove all the changes made by an uncommitted transaction?
♦ROLLBACK; (*)
♦UNDO;
♦REVOKE;
♦ROLLBACK TO SAVEPOINT;
29.       COMMIT saves all outstanding data changes? True or False?
♦True (*)
♦False
30.       Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;
You want to retain all the employees with a salary of 15000; What statement would you execute next?
♦ROLLBACK;
♦ROLLBACK TO SAVEPOINT upd1_done; (*)
♦ROLLBACK TO SAVEPOINT upd2_done;
♦ROLLBACK TO SAVE upd1_done;
♦There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.

Leave a comment