Chapter 6 - Additional Database Objects

B. CREATE PUBLIC SYNONYM the table FOR mytable;

Given the table created in Question 6, which of the following commands can Juan use to create a synonym that allows anyone to access the table without having to identify his schema in the table reference? A. CREATE SYNONYM thetable FOR juan.mytable; B. CREATE PUBLIC SYNONYM the table FOR mytable; C. CREATE SYNONYM juan FOR mytable; D. none of the above

B. B-tree

If a column has high selectivity or cardinality, which index type is most appropriate? A. IOT B. B-tree C. bitmap D. function-based index

B. The column contains a small number of distinct values.

If a column has low selectivity, this means: A. The column contains many distinct values. B. The column contains a small number of distinct values. C. A WHERE clause is always used in a query on the column. D. The selectivity of a column can't be determined.

A. Oracle 11g generates 20 integers automatically and stores them in memory.

If the CACHE and NOCACHE options aren't included in the CREATE SEQUENCE command, which of the following statements is correct? A. Oracle 11g generates 20 integers automatically and stores them in memory. B. No integers are cached by default C. Only one integer is cached at a time D. The command will fail. E. Oracle 11g generates 20 three-digit decimal numbers automatically and stores them in memory.

G. only b and d

Oracle 11g automatically creates an index for which type of constraints? A. NOT NULL B. PRIMARY KEY C. FOREIGN KEY D. UNIQUE KEY E. none of the above F. only a and b G. only b and d

D. 3

Suppose the user Juan creates a table called MYTABLE with four columns. The first column has a PRIMARY KEY constraint, the second column has a NOT NULL constraint, the third column has a CHECK constraint, and the fourth has a FOREIGN KEY constraint. Given this information, how many indexes does Oracle 11g create automatically when the table and constraints are created? A. 0 B. 1 C. 2 D. 3 E. 4

C. explain plan

What can be referenced to determine whether an index is used to perform a query? A. USER_INDEXES view B. query source code C. explain plan D. database access plan

D. all of the above

When is creating an index manually inappropriate? A. when queries return a large percentage of rows in the results B. when the table is small C. when the majority of table operations are updates D. all of the above E. only a and c

C. leaf blocks

Which node of the b-tree index contains ROWIDs? A. branch blocks B. root block C. leaf blocks D. None of the above because the primary key is used to identify rows.

B. the USER_INDEXES view

Which of the following can be used to determine whether an index exists? A. DESCRIBE indexname; B. the USER_INDEXES view C. the INDEXES view D. the USER_INDEX view E. all of the above F. none of the above

E. none of the above

Which of the following commands can you use to modify an index? A. ALTER SESSION B. ALTER TABLE C. MODIFY INDEX D. ALTER INDEX E. none of the above

C. CREATE SYNONYM

Which of the following commands creates a private synonym? A. CREATE PRIVATE SYNONYM B. CREATE NONPUBLIC SYNONYM C. CREATE SYNONYM D. CREATE PUBLIC SYNONYM

C. a sequence

Which of the following generates a series of integers that can be stored in a database? A. a number generator B. a view C. a sequence D. an index E. a synonym

A. NEXTVAL

Which of the following generates an integer in a sequence? A. NEXTVAL B. CURVAL C. NEXT_VALUE D. CURR_VALUE E. NEXT_VAL F. CUR_VAL

E. only a and c

Which of the following is a valid SQL statement? A. INSERT INTO publisher VALUES (pubsequence.nextvalue, 'HAPPY PRINTING', 'LAZY LARRY', NULL); B. CREATE INDEX a_new_index ON (firstcolumn*.02); C. CREATE SYNONYM pub FOR publisher; D. all of the above E. only a and c F. none of the above

B. CREATE INDEX book_profit_idx ON (retail-cost);

Which of the following is a valid command? A. CREATE INDEX book_profit_idx ON (retail-cost) WHERE (retail-cost) > 10; B. CREATE INDEX book_profit_idx ON (retail-cost); C. CREATE FUNCTION INDEX book_profit_idx ON books WHERE (retail-cost) > 10; D. both a and c E. none of the above

C. MINIMUMVAL

Which of the following isn't a valid option for the CREATE SEQUENCE command? A. ORDER B. NOCYCLE C. MINIMUMVAL D. NOCACHE E. All of the above are valid options

C. START WITH

Which of the following settings can't be modified with the ALTER SEQUENCE command? A. INCREMENT BY B. MAXVALUE C. START WITH D. MINVALUE E. CACHE

C. DROP PUBLIC SYNONYM synonymname;

Which syntax is correct for removing a public synonym? A. DROP SYNONYM synonymname; B. DELETE PUBLIC SYNONYM synonymname; C. DROP PUBLIC SYNONYM synonymname; D. DELETE SNYONYM synonymnname;