Review Questions – Chapter 6

                                Review Questions – Chapter 6

1.	Which of the following statements will succeed? merge into product_descriptions p using (select product_id, language_id              ,translated_name from products_for_2003) p2003 where (p.product_id = p2003.product_id) when matched then update set p.language=p2003.language_id    ,p.translated_name = p2003.translated_name when not matched then insert    (p.product_id, p.language_id     ,p.translated_name)    values (p2003.product_id,p2003.language_id           ,p2003.translated_name); merge into product_descriptions p using (select product_id, language_id              ,translated_name from products_for_2003) p2003 on (p.product_id = p2003.product_id) when matched then update set p.language=p2003.language_id    ,p.translated_name = p2003.translated_name when not matched then insert    (p.product_id, p.language_id     ,p.translated_name)    values (p2003.product_id,p2003.language_id           ,p2003.translated_name); merge into product_descriptions p using (select product_id, language_id              ,translated_name from products_for_2003) p2003 join on (p.product_id = p2003.product_id) when matched then update set p.language=p2003.language_id    ,p.translated_name = p2003.translated_name when not matched then insert    (p.product_id, p.language_id     ,p.translated_name)    values (p2003.product_id,p2003.language_id           ,p2003.translated_name); A.    Statement 1 B.    Statement 2 C.    Statement 3 D.   They all fail.
2.	Which of the following statements will not implicitly begin a transaction? A.    INSERT B.    UPDATE C.    DELETE D.   SELECT FOR UPDATE E.    None of the above; they all implicitly begin a transaction.
3.	If Julio executes a LOCK TABLE IN SHARE ROW EXCLUSIVE MODE statement, with which of the following statements will Marisa not wait for Julio's commit or rollback? A.    INSERT B.    SELECT FOR UPDATE C.    LOCK TABLE IN SHARE MODE D.   LOCK TABLE IN EXCLUSIVE MODE E.    None of the above; all will wait.
4.	Which of the following statements does not end a transaction? A.    LOCK TABLE IN EXCLUSIVE MODE B.    COMMIT C.    ALTER USER D.   CREATE INDEX
5.	Choose the maximum number of tables into which rows can be inserted via a single INSERT statement. A.    1 B.    2 C.    No more than 16 D.   Unlimited
6.	Can you execute an ALTER INDEX REBUILD while there are uncommitted updates on a table? A.    No, it will always fail with a resource busy error. B.    Yes, but you must specify the keyword WAIT to wait for the commit or rollback. C.    Yes, the row exclusive locks from the UPDATE statements only block other changes to the same rows. D.   Yes, but only if the updates do not change the indexed columns.
7.	Which of the following statements will begin a transaction using transaction-level read consistency? A.    ALTER SESSION USE TRANSACTION CONSISTENCY; B.    BEGIN TRANSACTION USING TRANSACTION CONSISTENCY; C.    BEGIN SERIALIZABLE TRANSACTION; D.   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
8.	Which of the following statements will improve the performance of a full-table scan on the PROCESS_ORDER_STAGE table? A.    DELETE FROM process_order_stages; B.    TRUNCATE TABLE process_order_stage; C.    CREATE INDEX ord_idx2 ON process_order_stage (customer_id); D.   ALTER SESSION SET hash_area_size 16613376;
9.	The following table shows two concurrent transactions. What happens at time point 9?
	Session A		Time		Session B
	UPDATE customers SET region='H' WHERE state='43' and county='046';		6
			7		UPDATE customers SET mgr=4567 WHERE state='47' and county='072';
	UPDATE customers SET region='H' WHERE state='47' and county='072';		8
			9		UPDATE customers SET mgr=4567 WHERE state='43' and county='046';
	A.    Session B will wait for session A to commit or roll back. B.    Session A will wait for session B to commit or roll back. C.    A deadlock will occur, and both sessions will hang until the DBA kills one or until one of the users cancels their statement. D.   A deadlock will occur, and Oracle will cancel one of the statements. E.    Both sessions are not updating the same column, so no waiting or deadlocks will occur.
10.	The following table shows two concurrent transactions. Which statement about the result returned in session A at time point 16 is most true?
	Session A					Time	Session B
	SELECT SUM(deposit_amt) FROM transaction_log WHERE deposit_date > TRUNC(SYSDATE);					12
						13	INSERT INTO transaction_log (deposit_date, deposit_amt) VALUES (SYSDATE, 6247.00);
						14	COMMIT;
	Table scan for the active SELECT reaches the data block where session B's row was inserted.					15
	Table scan complete results returned.					16
	A.    The results would include the changes committed by transaction B at time point 14. B.    The results would not include the changes committed by transaction B at time point 14. C.    The results would include the changes committed by transaction B at time point 14 if the two sessions were connected to the database as the same user. D.   Session A would raise a "snapshot too old" exception.
11.	The following table shows two concurrent transactions. Which statement about the results returned in session A at time points 16 and 18 is most true?
	Session A					Time	Session B
	SET TRANSACTION ISOLATION LEVEL READ CONSISTENT;					11
	SELECT SUM(deposit_amt) FROM transaction_log WHERE deposit_date > TRUNC(SYSDATE);					12
						13	INSERT INTO transaction_log (deposit_date, deposit_amt) VALUES (SYSDATE, 6247.00);
						14	COMMIT;
	Table scan for the active SELECT reaches the data block where session B's row was inserted.					15
	Table scan complete, results returned.					16
	SELECT SUM(deposit_amt) FROM transaction_log WHERE deposit_date > TRUNC(SYSDATE);					17
	Table scan complete, results returned.					18
	A.    The results would be identical. B.    The results would be different. C.    The results would be identical only if the two sessions were connected to the database as the same user. D.   Both statements would include the data committed by transaction B at time point 14.
12.	The following table shows two concurrent transactions. Which statement about the results returned in session A at time point 16 and 18 is most true?
	Session A					Time	Session B
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;					11
	SELECT SUM(deposit_amt) FROM transaction_log WHERE deposit_date > TRUNC(SYSDATE);					12
						13	INSERT INTO transaction_log (deposit_date, deposit_amt) VALUES (SYSDATE, 6247.00);
						14	COMMIT;
	Table scan for the active SELECT reaches the data block where session B's row was inserted.					15
	Table scan complete results returned.					16
	SELECT SUM(deposit_amt) FROM transaction_log WHERE deposit_date > TRUNC(SYSDATE);					17
	Table scan complete results returned.					18
	A.    The results would be identical. B.    The results would be different. C.    The results would be identical only if the two sessions were connected to the database as the same user. D.   Both statements would include the data committed by transaction B at time point 14.
13.	You have a DELETE statement that will generate a large amount of undo. One rollback segment, named RB_LARGE, is larger than the others. How would you force the use of this rollback segment for the DELETE operation? A.    ALTER SESSION USE ROLLBACK SEGMENT rb_large; B.    SET TRANSACTION USE ROLLBACK SEGMENT rb_large; C.    BEGIN WORK USING ROLLBACK SEGMENT rb_large D.   You cannot force the use of a specific rollback segment.
14.	The following table describes the DEPARTMENTS table.
	Column Name	dept_id		dept_name		mgr_id	location_id
	Key Type	pk
	NULLs/Unique	NN
	FK Table
	Datatype	NUMBER		VARCHAR2		NUMBER	NUMBER
	Length	4		30		6	4
	Default Value	None		None		None	None
	Which of the following INSERT statements will raise an exception? INSERT INTO departments (dept_id, dept_name, location_ id) VALUES(280,'Security',1700); INSERT INTO departments VALUES(280,'Security',1700); INSERT INTO departments VALUES(280,'Corporate Giving',266,1700); D.   None of these statements will raise an exception.
15.	The SALES table contains the following data: SELECT channel_id, COUNT(*) FROM sales WHERE channel_id IN ('T','I') GROUP BY channel_id; C   COUNT(*) - ---------- T     12000 I     24000 How many rows will be inserted into the NEW_CHANNEL_SALES table with the following SQL statement? INSERT FIRST  WHEN channel_id ='C' THEN    INTO catalog_sales (prod_id,time_id,promo_id                        ,amount_sold)        VALUES (prod_id,time_id,promo_id,amount_sold)  WHEN channel_id ='I' THEN    INTO internet_sales (prod_id,time_id,promo_id                         ,amount_sold)        VALUES (prod_id,time_id,promo_id,amount_sold)  WHEN  channel_id IN ('I','T') THEN    INTO new_channel_sales (prod_id,time_id,promo_id                            ,amount_sold)        VALUES (prod_id,time_id,promo_id,amount_sold) SELECT channel_id,prod_id,time_id,promo_id,amount_sold FROM sales; A.    0 B.    12,000 C.    24,000 D.   36,000
16.	How many rows will be counted in the last SQL statement that follows? SELECT COUNT(*) FROM emp;   120 returned INSERT INTO emp (emp_id)    VALUES (140); SAVEPOINT emp140; INSERT INTO emp (emp_id)    VALUES (141); INSERT INTO emp (emp_id)    VALUES (142); INSERT INTO emp (emp_id)    VALUES (143); TRUNCATE TABLE emp; INSERT INTO emp (emp_id)    VALUES (144); ROLLBACK; SELECT COUNT(*) FROM emp; A.    121 B.    1 C.    0 D.   143
17.	Which of the following statements will raise an exception in a transaction that starts with SET TRANSACTION READ ONLY? A.    ALTER SYSTEM B.    SELECT C.    ALTER USER D.   SET ROLE
18.	Which of the following statements will raise an exception? A.    LOCK TABLE SALES IN EXCLUSIVE MODE; B.    LOCK TABLE SALES IN ROW SHARE EXCLUSIVE MODE; C.    LOCK TABLE SALES IN SHARE ROW EXCLUSIVE MODE; D.   LOCK TABLE SALES IN ROW EXCLUSIVE MODE;
19.	Which of the following INSERT statements will raise an exception? INSERT INTO EMP SELECT * FROM NEW_EMP; INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO EMP SELECT * FROM NEW_EMP; INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP; INSERT INTO ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP;
20.	What will the salary of employee Arsinoe be at the completion of the following SQL statements? UPDATE emp   SET salary = 1000   WHERE name = 'Arsinoe'; SAVEPOINT Point_A UPDATE emp   SET salary = salary * 1.1   WHERE name = 'Arsinoe'; SAVEPOINT Point_B; UPDATE emp   SET salary = salary * 1.1   WHERE name = 'Berenike'; SAVEPOINT point_C; ROLLBACK TO SAVEPOINT point_b; COMMIT; UPDATE emp   SET salary = 1500   WHERE name = 'Arsinoe'; SAVEPOINT point_d; ROLLBACK TO point_d; COMMIT; A.    1000 B.    1100 C.    1111 D.   1500

Answers

1.	B. The correct syntax uses an ON clause as in option B. The WHERE in option A and the JOIN ON clause in option C are not valid.
2.	E. If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction.
3.	B. The row share exclusive mode will block other share, exclusive, and row exclusive locks, but not row share locks.
4.	A. COMMIT, ROLLBACK, and any DDL statement ends a transaction—DDL is automatically committed. LOCK TABLE is DML, like INSERT, UPDATE, DELETE, or MERGE, and requires a commit or rollback.
5.	D. A single INSERT statement can insert data into an unlimited number of tables. This multiple-table insert capability is new in Oracle9i.
6.	A. The row exclusive locks from the update will block all DDL, including DDL on the indexes—it does not matter which columns the index is on. You cannot specify WAIT on DDL.
7.	D. Transaction-level consistency is obtained with a serializable isolation level. An isolation level of read committed identifies statement-level read consistency.
8.	B. A TRUNCATE operation will reset the high-water mark on a table, so when a full-table scan (that scans to the high-water mark) is executed against the table, it will run very fast. Delete operations do not affect the high-water mark or full-scan performance. Indexes and hash_area_size do not affect full-scan performance.
9.	D. At time point 8, session A will wait for session B. At time point 9, a deadlock will occur; Oracle will recognize it and cancel one of the statements. Oracle locks to the granularity of a row, so even though the columns are different, the locks will still block each other.
10.	B. Statement-level read consistency would ensure that the data visible to each statement does not change while the statement is executing. The "snapshot too old" exception might be raised if there were a lot of other transactions committing to the database between time points 12 and 16, but if this exception were raised, the table scan would neither complete nor return results.
11.	B. The read-consistent isolation level is statement-level read consistency, so each statement sees the committed data that existed at the beginning of the statement. The committed data at time point 17 includes session B's commit at time point 14.
12.	A. The serializable isolation level is transaction-level read-consistency, so both of session A's SELECT statements see the same data image. Neither would include the changes committed at time point 14.
13.	B. The SET TRANSACTION statement can be used to force the use of a specific rollback segment, provided that the SET TRANSACTION statement begins the transaction.
14.	B. Option B will raise an exception because there are not enough column values for the implicit column list (all columns).
15.	A. The FIRST clause tells Oracle to execute only the first WHEN clause that evaluates to TRUE. This statement will insert 24,000 rows into the INTERNET_SALES table and 0 rows into the NEW_CHANNEL_ SALES table. If the ALL clause were used, 36,000 rows would be inserted into the NEW_CHANNEL_SALES table.
16.	C. The TRUNCATE statement is DDL and performs an implicit commit. After the TRUNCATE statement, there are 0 rows in the table. The one row that was inserted was removed when the ROLLBACK statement was executed.
17.	C. A read-only transaction will raise an exception if data is changed. Altering a user will change data.
18.	B. There are five types of table locks: row share, row exclusive, share, share row exclusive, and exclusive. Row share exclusive mode does not exist.
19.	B. The keywords INSERT INTO are required in single-table INSERT statements, but are not valid in multiple-table INSERT statements.
20.	D. The final rollback (to point_d) will roll the changes back to just after setting the salary to 1500.