OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
Review Questions
1. Which statement will create a sequence that starts with 0 and gets smaller one whole number at a time?
A. create sequence desc_seq start with 0 increment by -1 maxvalue 1;
B. create sequence desc_seq increment by -1;
C. create sequence desc_seq start with 0 increment by -1;
D. Sequences can only increase.
----
For a descending sequence, the default START WITH value is -1, and the default MAXVALUE value is 0. To start the sequence with 0, you must explicitly override both of these defaults.
Ans A.
2. Which statement is most correct in describing what happens to a synonym when the underlying object is dropped?
A. The synonym's status is changed to INVALID.
B. You can't drop the underlying object if a synonym exists unless the CASCADE clause is used in the DROP statement.
C. The synonym is automatically dropped with the underlying object.
D. Nothing happens to the synonym.
----
Synonyms do not have a status. The CASCADE CONSTRAINTS option does not drop synonyms. Synonyms can point to nonexisting objects.
Ans: D.
3. The built-in packaged procedure DBMS_APPLICATION_INFO.SET_MODULE has, in the package specification, the following declaration:
PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE
(module_name IN VARCHAR2
,action_name IN VARCHAR2);
Which of the following statements will successfully call this procedure passing 'Monthly Load' and 'Rebuild Indexes' for the MODULE_NAME and ACTION_NAME, respectively? (Choose all that apply.)
A.
dbms_application_info('Monthly Load'
'Rebuild Indexes');
B.
dbms_application_info(
module_name=>'Monthly Load'
,action_name=>'Rebuild Indexes');
C.
dbms_application_info('Rebuild Indexes'
,'Monthly Load');
D.
dbms_application_info(
module_name->'Monthly Load'
,action_name->'Rebuild Indexes');
----
Option A almost uses the correct positional notation, except the delimiting comma is missing. Option B uses the correct named notational style. Option C transposes the module and action name using positional notation. Option D uses the wrong assignment syntax.
Ans: B.
4. With which of the following statements could you expect improved performance over a full-table scan, when a B-tree index is created on the two columns HIRE_DATE and SALARY in the HR.EMPLOYEES table?
A.
select max(salary)
from hr.employees
where hire_date < sysdate -90;
B.
select last_name, first_name
from hr.employees
where salary > 90000;
C.
update hr.employees
set salary = salary * 1.05
where department_id = 102;
D.
None of these statements would benefit from the index.
----
The index could be used if a leading subset of columns in the index is referenced. Options B and C do not reference the leading subset of columns in their WHERE clauses.
Ans: A
5. Which of the following statements will raise an exception?
A. alter sequence emp_seq nextval 23050;
B. alter sequence emp_seq nocycle;
C. alter sequence emp_seq increment by -5;
D. alter sequence emp_seq maxvalue 10000;
----
You cannot explicitly change the next value of a sequence. You can set the MAXVALUE or INCREMENT BY value to a negative number, and NOCYCLE tells Oracle to not reuse a sequence number.
Ans: A.
6. Rajiv has created a private synonym NEW_PRODUCTS for the MEG.PRODUCTS table. Who can select from RAJIV.NEW_PRODUCTS?
A. The users that Rajiv has granted SELECT on NEW_PRODUCTS to and Meg has granted SELECT on PRODUCTS to.
B. The users that Rajiv has granted SELECT on NEW_PRODUCTS to.
C. The users that Meg has granted SELECT on PRODUCTS to, even if Rajiv does not grant privileges to his synonym.
D. The users that Rajiv has granted SELECT on NEW_PRODUCTS to, if Meg has granted him SELECT WITH ADMIN OPTION.
----
Private synonyms can be referenced by anyone who has privileges on the underlying objects. You cannot grant privileges on synonyms, only on the underlying object. Option D is close, but the WITH ADMIN OPTION is only for roles and system privileges, not for table privileges.
Ans: C.
7. Which type of stored program must return a value?
A. PL/SQL procedure
B. PL/SQL function
C. Java trigger
D. Java procedure
-----
Functions must include a RETURN statement and must return a value.
Ans: B.
8. What does the following SQL statement enable all users in the database to do?
create public synonym plan_table
for system.plan_table;
A. Use the EXPLAIN PLAN feature of the database
B. Save execution plans in the system repository
C. Reference a table as PLAN_TABLE instead of SYSTEM.PLAN_TABLE
D. Turn on SQL tracing
---------
This statement creates a public synonym or global alias, which allows users to reference the underlying table without needing to explicitly specify the owner. A table named PLAN_TABLE is needed to use the EXPLAIN PLAN feature, but the statement above creates a public synonym. Also, the existence of a public synonym does not grant to public any privileges on the underlying object. An ALTER SESSION statement is used to enable and disable SQL tracing.
Ans: C
9. There is a public synonym named PLAN_TABLE for SYSTEM.PLAN_TABLE. Which of the following statements will remove this public synonym from the database?
A. drop table system.plan_table;
B. drop synonym plan_table;
C. drop table system.plan_table cascade;
D. drop public synonym plan_table;
----
To remove a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP TABLE statement will remove a table from the database, but will not affect any synonyms on the table.
Ans: D.
10. A developer reports that she is receiving the following error:
SELECT key_seq.currval FROM dual;
ERROR at line 1:
ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined
Which of the following statements does the developer need to run to fix this condition?
A. create sequence key_seq;
B. create synonym key_seq;
C. select key_seq.nextval from dual;
D. grant create sequence to public;
----
A sequence is not yet defined if NEXTVAL has not yet been selected from it within the current session. It has nothing to do with creating a sequence, creating a synonym, or granting privileges.
Ans: C.
11. A power user is running some reports and has asked you to put two new B-tree indexes on a large table so that her reports will run faster. You acknowledge that the indexes would speed up her reports. Can the proposed indexes slow other processes? (Choose the best answer.)
A. No, indexes only speed up queries.
B. Yes, the indexes will make the optimizer take longer to decide the best execution plan.
C. Yes, DML will run more slowly.
D. Yes, table reorganization operations will be slower.
----
This one's a little tricky. B, C, and D are all true, but C is the best answer. Two additional indexes should not appreciably slow the optimizer, and table reorganization in Oracle (unlike in other databases) is usually not needed. DML (INSERT, UPDATE, and DELETE) operations will definitely be slowed, as the new indexes will need to be maintained.
Ans: C.
12. Bitmapped indexes are best suited for which type of environment?
A. High-cardinality columns
B. Online transaction processing (OLTP) applications
C. Full-table scan access
D. Low- to medium-cardinality columns
----
Bitmapped indexes are not suited for high-cardinality columns (those with highly selective data). OLTP applications tend to need row-level locking, which is not available with bitmap indexes. Full-table scans do not use indexes. Bitmap indexes are best suited for multiple combinations of low- to medium-cardinality columns.
Ans: D
13. The INSURED_AUTOS table has one index on the columns YEAR, MAKE, and MODEL, and one index on VIN. Which of the following SQL statements could not benefit from using these indexes?
A.
select vin from insured_autos
where make='Ford' and model = 'Taurus';
B.
select count(*) from insured_autos
where make='Ford' and year = 1998;
C.
select vin from insured_autos
where year = 1998 and owner = 'Dahlman';
D.
select min(year) from insured_autos
where make='Ford' and model = 'Taurus';
----
Option A does not use a leading subset of columns in an index, nor do all of the columns come from the index. A full-table scan on the table will be needed. Options B and C use a leading subset of the three-column index, so that index could be used. Option D uses data that is found completely in the three-column index, and a full scan of this index would likely be faster than a full scan of the larger table.
Ans: A.
14. Which clauses in a SELECT statement can an index be used for? (Choose all that apply.)
A. SELECT
B. FROM
C. WHERE
D. HAVING
----
The obvious answer is C, but an index also can be used for the SELECT clause. If an index contains all of the columns needed to satisfy the query, the table does not need to be accessed.
Ans: A, C.
15. You need to generate artificial keys for each row inserted into the PRODUCTS table. You want the first row to use a sequence value of 1000, and you want to make sure that no sequence value is skipped. Which of the following statements will meet these requirements?
A.
CREATE SEQUENCE product_key2
START WITH 1000
INCREMENT BY 1
NOCACHE;
B.
CREATE SEQUENCE product_key2
START WITH 1000
NOCACHE;
C.
CREATE SEQUENCE product_key2
START WITH 1000
NEXTVAL 1
NOCACHE;
D. Options A and B meet the requirements.
E. None of the above statements meet all of the requirements.
-----
Both options A and B produce identical results, because the INCREMENT BY 1 clause is the default if it is not specified. Option C is invalid because NEXTVAL is not a valid keyword within a CREATE SEQUENCE statement.
Ans: D.
16. Which statement will display the last number generated from the EMP_SEQ sequence?
A. select emp_seq.curr_val from dual;
B. select emp_seq.currval from dual;
C. select emp_seq.lastval from dual;
D. select last_number from all_sequences where sequence_name ='EMP_SEQ';
E. You cannot get the last sequence number generated.
-----
Option D is close, but it shows the greatest number in the cache, not the latest generated. The correct answer is from the sequence itself, using the pseudo-column CURRVAL.
Ans: B.
17. Which statement will create a sequence that will rotate through 100 values in a round-robin manner?
A. create sequence roundrobin cycle maxvalue 100;
B. create sequence roundrobin cycle to 100;
C. create sequence max_value 100 roundrobin cycle;
D. create rotating sequence roundrobin min 1 max 100;
----
The keyword CYCLE will cause the sequence to wrap and reuse numbers. The keyword MAXVALUE will set the largest value the sequence will cycle to. The name roundrobin is there to confuse to you.
Ans: A.
18. The following statements are executed:
create sequence my_seq;
select my_seq.nextval from dual;
select my_seq.nextval from dual;
rollback;
select my_seq.nextval from dual;
What will be selected when the last statement is executed?
A. 0
B. 1
C. 2
D. 3
----
The CREATE SEQUENCE statement will create an increasing sequence that will start with 1, increment by 1, and be unaffected by the rollback. A rollback will never stuff vales back into a sequence.
Ans: D.
19. Which of the following can you not do with a package?
A. Overload procedures and functions
B. Hide data
C. Retain data across commits
D. Grant EXECUTE privileges on one procedure in a package
----
You can only grant EXECUTE privileges on the entire package, not on individual packaged programs.
Ans: D.
20. Which of the following calls to the stored function my_sine() will raise an exception?
A. Theta := my_sine(45);
B. IF (my_sine(45) > .3 ) THEN
C. DECLARE
Theta NUMBER DEFAULT my_sine(45);
BEGIN ...
D. my_sine(45);
----
Functions cannot be called as stand-alone statements; only procedures can be called this way
Ans: D.
No comments:
Post a Comment