OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
Review Questions
1. Which of the following statements will succeed?
(1)
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);
(2)
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);
(3)
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.
----
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.
Ans: B.
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.
----
If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction.
Ans: E.
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.
---
The row share exclusive mode will block other share, exclusive, and row exclusive locks, but not row share locks.
Ans: B
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
----
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.
Ans: A.
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
----
A single INSERT statement can insert data into an unlimited number of tables. This multiple-table insert capability is new in Oracle9i.
Ans: D.
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.
----
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.
Ans: A
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;
----
Transaction-level consistency is obtained with a serializable isolation level. An isolation level of read committed identifies statement-level read consistency.
Ans: D.
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;
-----
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.
Ans: B.
9. The following table shows two concurrent transactions. What happens at time point 9?
-------------------------------------------------
Session A Time Session B
UPDATE customers SET 6
region='H' WHERE
state='43' and
county='046';
7 UPDATE customers
SET mgr=4567
WHERE state='47' and
county='072';
UPDATE customers SET 8
region='H' WHERE
state='47' and
county='072';
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.
-----
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.
Ans: D
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) 12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);
13 INSERT INTO transaction_log
(deposit_date, deposit_amt)
VALUES (SYSDATE, 6247.00);
14 COMMIT;
Table scan for the active 15
SELECT reaches the data
block where session B's row
was inserted.
Table scan complete results 16
returned
---------------------------------------------------------------------
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.
----------
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.
Ans: B.
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 11
LEVEL READ CONSISTENT;
SELECT SUM(deposit_amt) 12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);
13 INSERT INTO transaction_log
(deposit_date, deposit_amt)
VALUES (SYSDATE, 6247.00);
14 COMMIT;
Table scan for the active 15
SELECT reaches the data block
where session B's row was
inserted.
Table scan complete, 16
results returned.
SELECT SUM(deposit_amt) 17
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);
Table scan complete, 18
results returned.
--------------------------------------------------------------------------
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.
-------
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.
Ans: B.
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 11
LEVEL SERIALIZABLE;
SELECT SUM(deposit_amt) 12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);
13 INSERT INTO transaction_log
(deposit_date, deposit_amt)
VALUES (SYSDATE, 6247.00);
14 COMMIT;
Table scan for the active 15
SELECT reaches the data block
where session B's row was
inserted.
Table scan complete results 16
returned.
SELECT SUM(deposit_amt) 17
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);
Table scan complete results 18
returned.
---------------------------------------------------------------------------
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.
---
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.
Ans: A.
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.
----
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.
Ans: B.
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?
A.
INSERT INTO departments (dept_id, dept_name, location_
id) VALUES(280,'Security',1700);
B.
INSERT INTO departments
VALUES(280,'Security',1700);
C.
INSERT INTO departments
VALUES(280,'Corporate Giving',266,1700);
D.
None of these statements will raise an exception.
----
Option B will raise an exception because there are not enough column values for the implicit column list (all columns).
Ans: B
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
-----
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.
Ans: A.
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
----
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.
Ans: C
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
---
A read-only transaction will raise an exception if data is changed. Altering a user will change data.
Ans: C
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;
---
There are five types of table locks: row share, row exclusive, share, share row exclusive, and exclusive. Row share exclusive mode does not exist.
Ans: B.
19. Which of the following INSERT statements will raise an exception?
A.
INSERT INTO EMP SELECT * FROM NEW_EMP;
B.
INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO
EMP SELECT * FROM NEW_EMP;
C.
INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP
SELECT * FROM NEW_EMP;
D.
INSERT INTO ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP
SELECT * FROM NEW_EMP;
----
The keywords INSERT INTO are required in single-table INSERT statements, but are not valid in multiple-table INSERT statements.
Ans: B.
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
----
The final rollback (to point_d) will roll the changes back to just after setting the salary to 1500.
Ans: D.
No comments:
Post a Comment