Database Questions and Answers – Implementation of Isolation Levels

This set of Database Multiple Choice Questions & Answers (MCQs) focuses 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
View Answer

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
View Answer

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
View Answer

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);

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
View Answer

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
View Answer

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
View Answer

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
View Answer

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
View Answer

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
View Answer

Sanfoundry Global Education & Learning Series – Database Management System.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]