Review Questions – Chapter 9
|
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.
|
|
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.
|
|
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.)
'Rebuild
Indexes');
module_name=>'Monthly Load'
,action_name=>'Rebuild
Indexes');
,'Monthly
Load');
module_name->'Monthly Load'
,action_name->'Rebuild
Indexes');
|
|
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?
from
hr.employees
where
hire_date < sysdate -90;
from
hr.employees
where
salary > 90000;
set
salary = salary * 1.05
where
department_id = 102;
D. None of these
statements would benefit from the index.
|
|
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;
|
|
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.
|
|
7.
|
Which type of stored program must
return a value?
A. PL/SQL procedure
B. PL/SQL function
C. Java trigger
D. Java procedure
|
|
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
|
|
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;
|
|
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;
|
|
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.
|
|
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
|
|
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?
where
make='Ford' and model = 'Taurus';
where
make='Ford' and year = 1998;
where
year = 1998 and owner = 'Dahlman';
where
make='Ford' and model = 'Taurus';
|
|
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
|
|
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?
START
WITH 1000
INCREMENT
BY 1
NOCACHE;
START
WITH 1000
NOCACHE;
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.
|
|
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.
|
|
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;
|
|
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
|
|
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
|
|
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
Theta
NUMBER DEFAULT my_sine(45);
BEGIN
…
D. my_sine(45);
|
Answers
|
1.
|
A. 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.
|
|
2.
|
D. Synonyms do not have a status.
The CASCADE CONSTRAINTS option does not drop synonyms. Synonyms can point to
nonexisting objects.
|
|
3.
|
B. 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.
|
|
4.
|
A. 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.
|
|
5.
|
A. 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.
|
|
6.
|
C. 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.
|
|
7.
|
B. Functions must include a RETURN
statement and must return a value.
|
|
8.
|
C. 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.
|
|
9.
|
D. 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.
|
|
10.
|
C. 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.
|
|
11.
|
C. 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.
|
|
12.
|
D. 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.
|
|
13.
|
A. 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.
|
|
14.
|
A, C. 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.
|
|
15.
|
D. 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.
|
|
16.
|
B. 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.
|
|
17.
|
A. 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.
|
|
18.
|
D. 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.
|
|
19.
|
D. You can only grant EXECUTE
privileges on the entire package, not on individual packaged programs.
|
|
20.
|
D. Functions cannot be called as
stand-alone statements; only procedures can be called this way.
|
No comments:
Post a Comment