Implementation of Isolation Levels - DBMS Questions and Answers

1.

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 one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query
   C.) A set of sequential queries, all of which must return values from the same table
   D.) A SELECT statement that can be embedded in a clause of another SELECT statement only

Answer: Option 'B'

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

2.

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: Option '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

3.

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: Option 'C'

It would display the value TWO twice
This will replicate the table as in the select statement.

4.

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: Option 'B'

​It recovers only the second DEPT table

5.

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

Answer: Option 'C'

View is the temporary space created for the database.

Implementation of Isolation Levels - DBMS Questions and Answers Download Pdf