CHAPTER 12 & 6
Which of the following must be used to separate a subquery from the outer query?
()
If a sequence is set to pre-generate sequential values, ____ values will be generated at one time by default.
20
The ____________________ operator indicates that a value must be less than the highest value returned by a subquery.
<ANY
When the subquery is executed first and the value is passed back as input to the outer query, the subquery is known as an uncorrelated subquery.
=ANY
A function-based index can be created based on which of the following?
ALL OF THE ABOVE
Which of the following can be used in a WHERE clause?
ALL OF THE ABOVE
Which of the following operators is used with a multiple-row subquery?
ALL OF THE ABOVE
The starting value for a sequence cannot be changed using the ____________________ SEQUENCE command.
ALTER
Which of the following statements about indexes is incorrect?
An index is only beneficial if a large percentage of a table is expected to be returned in query results.
The ____________________ index structure can be particularly beneficial in queries involving compound conditions (AND and OR operator usage).
BITMAP
The keyword ____________________ must be used in a CREATE INDEX command to create a Bitmap index.
BITMAP
Which option is used to have Oracle11 g pre-generate a set of values and store those values in the server's memory?
CACHE
Which of the following commands is used to create a function-based index?
CREATE INDEX...ON
Which command will create a sequence named NEWSEQUENCE to generate a series of integers?
CREATE SEQUENCE newsequence;
Which command will create a synonym for a table?
CREATE SYNONYM synonymname FOR tablename;
After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
CURRVAL
Even if you created a PUBLIC synonym, you must have ____________________ privileges to delete a PUBLIC synonym.
DBA
Which of the following can be used to remove an index that currently exists on the column of a database table
DROP INDEX
A group function cannot be included in the SELECT clause of a single-row subquery.
FALSE
A multiple-column subquery nested in the SELECT clause of the outer query is known as an inline view.
FALSE
A sequence can be used to speed up row retrieval.
FALSE
A subquery must be enclosed in a set of quotation marks.
FALSE
A subquery nested in a WHERE clause can only be on the left side of the comparison operator.
FALSE
All synonyms are PUBLIC.
FALSE
An index is implicitly created whenever a(n) CHECK constraint is created for a table column.
FALSE
Single-row operators can be used with multiple-row subqueries that return only one column of results.
FALSE
The CYCLE option prevents a sequence from regenerating previous values.
FALSE
The EXISTED operator is used to determine whether a condition is present in a subquery.
FALSE
The NEXTVAL pseudocolumn can be referenced in an INSERT command to add the value to a database table.
FALSE
The NEXTVALUE pseudocolumn is used to actually generate the next value in a sequence.
FALSE
The USER_ALL data dictionary view displays information about all the relevant objects owned by the user.
FALSE
The USER_SEQ data dictionary view can be used to verify sequence settings.
FALSE
The default value for the INCREMENT BY clause is two.
FALSE
The greater than operator, >, is a valid operator for multiple-row subqueries.
FALSE
The results of the outer query are passed to the inner query.
FALSE
If a subquery's result must be compared with a group function, you must nest the inner query in the ____________________ clause of the outer query.
HAVING
When a multiple-column subquery is included in the WHERE or HAVING clause of the outer query, which operator is used by the outer query to evaluate the results of the subquery?
IN
A(n) ____________________ can be used to quickly locate specific records in a large table.
INDEX
If a column contains a large number of NULL values, a(n) ____________________ can quickly help determine the rows that contain NULL values and those that do not.
INDEX
The USER_INDEXES data dictionary view can be used to verify existing indexes. Which of the following contains the ROWID of referenced rows?
INDEX
Which of the following contains the ROWID of referenced rows?
INDEX
Which of the following serves the same basic purpose as an index in a book, by allowing users to quickly locate specific records?
INDEX
An _________________________ is a variant of the B-tree index structure and is used as an alternative to the conventional heap-organized table.
INDEX ORGANIZED TABLE
The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;
INLINE VIEW
The ____________________ value of a sequence cannot be changed if the sequence has already generated a number greater than that value.
MAXVALUE
In Oracle11 g, a(n) ____ allows a series of DML actions to occur.
MERGE STATEMENT
A(n) ____________________ subquery is a nested query that returns more than one row of results.
MULTIPLE-ROW
A(n) ____________________ subquery returns more than one column to the outer query.
MULTIPLE-ROW
Which of the following subqueries returns more than one row of results to the outer query?
MULTIPLE-ROW SUBQUERY
The ____________________ pseudocolumn is used to generate the next sequence value.
NEXTVAL
Which of the following is a pseudocolumn?
NEXTVAL
Which of the following statements about NEXTVAL and CURRVAL is incorrect?
NEXTVAL and CURRVAL are pseudocolumns that store unique names for database objects.
The EXISTS operator must be listed ____.
NONE OF THE ABOVE
Which of the following commands can be used to modify an index?
NONE OF THE ABOVE
An index is not implicitly created when which type of constraint is created?
NOT NULL
Which of the following is defined in Oracle11 g as simply anything that has a name and a defined structure?
OBJECT
The results of a subquery are passed back to the ____________________ query.
OUTER
An outer query is also referred to as a(n) ____ query.
PARENT QUERY
A subquery must be enclosed in a set of ____________________.
PARENTHESES
To remove a PUBLIC synonym, you must include the ____ keyword in the DROP command.
PUBLIC
The <= operator is referred to as a(n) ____ operator.
SINGLE-ROW
The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;
SINGLE-ROW
The only type of subquery that can be used in a SELECT clause is a(n) ____________________ subquery.
SINGLE-ROW
The ____________________ clause of the CREATE SEQUENCE command is used to identify the starting value for a sequence.
START WITH
The outer query receives its input from the ____.
SUBQUERY
Which of the following can be used to provide an alternative, simplified name, to identify database objects?
SYNONYM
A Bitmap index is useful for improving queries on columns that have low cardinality.
TRUE
A multiple-row subquery can be nested in a HAVING clause.
TRUE
A subquery nested in a SELECT clause cannot contain an ORDER BY clause.
TRUE
A(n) GROUP BY clause can be used in a subquery.
TRUE
A(n) correlated subquery references one or more columns from the outer query.
TRUE
An explicitly created index can be dropped with the DROP INDEX command.
TRUE
By default, the START WITH clause has a value of one.
TRUE
IN is a valid multiple-row subquery operator.
TRUE
If the minimum value for a sequence is not specified, then NOMINVALUE will be assumed as the default.
TRUE
Indexes are usually created for frequently referenced or searched columns.
TRUE
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
TRUE
Sequence settings can be altered using the ALTER SEQUENCE command.
TRUE
The IN operator can be used with single-row, multiple-row, or multiple-column subqueries.
TRUE
The NEXTVAL pseudocolumn can be referenced in an INSERT command to add the value to a database table.
TRUE
The USER_INDEXES data dictionary view can be used to verify existing indexes.
TRUE
The USER_SEQUENCES view of the data dictionary can be used to determine the current values and settings for a sequence.
TRUE
The lowest possible value for an increasing sequence is 1.
TRUE
Valid single-row operators include =, >, <, >=, <=.
TRUE
When a multiple-column subquery is used in the outer query's FROM clause, it creates a temporary table, called an inline view, that can be referenced by other clauses of the outer query.
TRUE
When a negative value is assigned to the INCREMENT BY clause of the CREATE SEQUENCE command, numeric values are generated in descending order.
TRUE
When a sequence is created for internal control purposes, the values should not be cached otherwise gaps may appear in the sequence.
TRUE
When the subquery is executed first and the value is passed back as input to the outer query, the subquery is known as an uncorrelated subquery.
TRUE
When used with a multiple-row subquery, the IN operator indicates that the records processed by the outer query must match one of the values returned by the subquery.
TRUE
re-generated sequence numbers that have not been used when the database is shut down will no longer be available when the database is restarted.
TRUE
The following SQL statement contains which type of subquery? SELECT title, retail, category, cataverage FROM books NATURAL JOIN (SELECT category, AVG(retail) cataverage FROM books GROUP BY category);
UNCORRELATED
To verify individual settings for the clauses of a sequence, you can query the ____ table in the data dictionary.
USER_SEQUENCES
If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.
private
A(n) ____________________ synonym can be referenced by other database users.
public
A(n) ____ generates sequential integers that can be used by organizations to assist with internal controls or simply to serve as a primary key for a table.
sequence
A(n) ____________________ can be used to generate values for a column designated as the primary key for a table.
sequence
Pre-generated sequence numbers that have not been used when the database is shut down will no longer be available when the database is restarted.
true
