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

Answer: a
Explanation: It is to avoid deadlock.
advertisement

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

Answer: c
Explanation: 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
View Answer

Answer: d
Explanation: 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)

advertisement
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
Explanation: 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
View Answer

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

6.

Name Null? Type
Cust_id Not null Number(2)
Cust_Name Varchar2(15)

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

Answer: c
Explanation: 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
View Answer

Answer: d
Explanation: 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
View Answer

Answer: d
Explanation: 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
View Answer

Answer: b
Explanation: 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
View Answer

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

Sanfoundry Global Education & Learning Series – Database Management System.

advertisement
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He is Linux Kernel Developer & SAN Architect and is passionate about competency developments in these areas. He lives in Bangalore and delivers focused training sessions to IT professionals in Linux Kernel, Linux Debugging, Linux Device Drivers, Linux Networking, Linux Storage, Advanced C Programming, SAN Storage Technologies, SCSI Internals & Storage Protocols such as iSCSI & Fiber Channel. Stay connected with him @ LinkedIn