Database Multiple Choice Questions on “Snapshot Isolation”.
1. Snapshot isolation is a particular type of ______________ scheme.
a) Concurrency-control
b) Concurrency-allowance
c) Redirection
d) Repetition-allowance
Answer: a
Clarification: It has gained wide acceptance in commercial and open-source systems, including Oracle, PostgreSQL, and SQL Server.
2. Snapshot isolation is used to give
a) Transaction a snapshot of the database
b) Database a snapshot of the transaction
c) Database a snapshot of committed values in the transaction
d) Transaction a snapshot of the database and Database a snapshot of committed values in the transaction
Answer: d
Clarification: The data values in the snapshot consist only of values written by committed transactions.
3. Lost update problem is
a) Second update overwrites the first
b) First update overwrites the second
c) The updates are lost due to conflicting problem
d) None of the mentioned
Answer: a
Clarification: Lost update problem has to be resolved.
4. Under first updater wins the system uses a __________ mechanism that applies only to updates.
a) Close
b) Read
c) Locking
d) Beat
Answer: c
Clarification: Reads are unaffected by this, since they do not obtain locks.
5. When a transaction Ti attempts to update a data item, it requests a _________ on that data item.
a) Read lock
b) Update lock
c) Write lock
d) Chain lock
Answer: c
Clarification: Reads are unaffected by this, since they do not obtain locks.
6. Each of a pair of transactions has read data that is written by the other, but there is no data written by both transactions, is referred to as
a) Read skew
b) Update skew
c) Write lock
d) None of the mentioned
Answer: d
Clarification: Write skew is the issue addressed here.
7. An application developer can guard against certain snapshot anomalies by appending a ______ clause to the SQL select query.
a) For update
b) For read
c) For write
d) None of the mentioned
Answer: a
Clarification: Adding the for update clause causes the system to treat data that are read as if they had been updated for purposes of concurrency control.
8. Evaluate the CREATE TABLE statement:
CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
a) It would be created only if a unique index is manually created first
b) It would be created and would use an automatically created unique index
c) It would be created and would use an automatically created no unique index
d) It would be created and remains in a disabled state because no index is specified in the command
Answer: b
Clarification: Syntax: create table table_name(name constraint).
9. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
a) 1
b) 10
c) 100
d) an error
Answer: a
Clarification: Sequence is used to generate a series of values.
10. 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: Sequence is used to generate a series of values.