250+ TOP MCQs on Implementation of Isolation Levels and Answers

Database Multiple Choice Questions on “Implementation of Isolation Levels”.

1. In concurrency control policy the lock is obtained on
a) Entire database
b) A particular transaction alone
c) All the new elements
d) All of the mentioned

Answer: a
Clarification: It is to avoid deadlock.

2. A concurrency-control policy such as this one leads to ______ performance since it forces transactions to wait for preceding transactions to finish before they can start.
a) Good
b) Average
c) Poor
d) Unstable

Answer: c
Clarification: It provides a poor degree of concurrency.

3. __________ are used to ensure that transactions access each data item in order of the transactions’ ____ if their accesses conflict.
a) Zone
b) Relay
c) Line
d) Timestamps

Answer: d
Clarification: When this is not possible, offending transactions are aborted and restarted with a new timestamp.

4. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
a)

UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;

b)

DELETE FROM empdet
WHERE ename LIKE 'J%';

c)

CREATE VIEW empvu
AS
SELECT * FROM empdept;

d)

CREATE INDEX
empdet_idx
ON empdet(empno);

View Answer

Answer: c
Clarification: View is the temporary space created for the database.

5. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
a) To find the groups forming the subtotal in a row
b) To create group-wise grand totals for the groups specified within a GROUP BY clause
c) To create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
d) To create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals

Answer: c
Clarification: View is the temporary space created for the database.

6.

Evaluate the following SQL statements executed in the given order:

ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED; INSERT
INTO cust VALUES (1,'RAJ'); --row 1
INSERT INTO cust VALUES (1,'SAM'); --row 2
COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1,'LATA'); --row 3
INSERT INTO cust VALUES (2,'KING'); --row 4
COMMIT;

Which rows would be made permanent in the CUST table?
a) row 4 only
b) rows 2 and 4
c) rows 3 and 4
d) rows 1 and 4

Answer: c
Clarification: View is the temporary space created for the database.

7. Which statement is true regarding external tables?
a) The default REJECT LIMIT for external tables is UNLIMITED
b) The data and metadata for an external table are stored outside the database
c) ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table
d) The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table

Answer: d
Clarification: This will replicate the table as in the select statement.

8. A non-correlated subquery can be defined as ______
a) A set of sequential queries, all of which must always return a single value
b) A set of sequential queries, all of which must return values from the same table
c) A SELECT statement that can be embedded in a clause of another SELECT statement only
d) A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

Answer: d
Clarification: This will replicate the table as in the select statement.

9. Evaluate the following SQL statements in the given order:

DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP;

Which statement is true regarding the above FLASHBACK operation?
a) It recovers only the first DEPT table
b) It recovers only the second DEPT table
c) It does not recover any of the tables because FLASHBACK is not possible in this case
d) It recovers both the tables but the names would be changed to the ones assigned in the RECYCLEBIN

Answer: b
Clarification: This will replicate the table as in the select statement.

10.

CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE');
UPDATE digits SET description ='TWO' WHERE id=1;
INSERT INTO digits VALUES (2,'TWO');
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

What would be the outcome of the above query?
a) It would not display any values
b) It would display the value TWO once
c) It would display the value TWO twice
d) It would display the values ONE, TWO, and TWO

Answer: c
Clarification: This will replicate the table as in the select statement.