Views and Additional Database Objects
If more than ____________________ percent of a table is expected to be returned in the results of a query, then an index will probably not speed up the process.
10 OR ten
A NEXTVAL call generates the sequence value of 5. After the value is generated, what value will be stored in CURRVAL?
5
The ____________________ index structure can be particularly beneficial in queries involving compound conditions (AND and OR operator usage).
BITMAP
Structure of the BOOKS table Based on the structure of the BOOKS table, which of the following is a valid SQL statement?
CREATE INDEX books_title_idx ON books (INITCAP(title));
Which of the following commands is used to create a function-based index?
CREATE INDEX...ON
The ____________________ option is used to indicate that the values assigned by a sequence can be used after the maximum value is reached.
CYCLE
A DEFAULT sequence value will override any value provided for the column in an INSERT statement.
FALSE
Update operations cannot be performed through a complex view that contains an arithmetic expression.
FALSE
Views are database objects that store data.
FALSE
You cannot create a view if the underlying table does not exist at the time the view is created.
FALSE
A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.
INCREMENT BY
The ____________________ clause is used to indicate the interval that must exist between two values generated in a sequence.
INCREMENT BY
A(n) ____________________ can be used to quickly locate specific records in a large table.
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
What is the default mode for the CREATE VIEW command?
NOFORCE
An index is not implicitly created when which type of constraint is created?
NOT NULL
DML operations are not allowed on a view that includes the pseudo column ____.
ROWNUM
A simple view cannot contain a simple arithmetic operation.
TRUE
A(n) existing view cannot be modified.
TRUE
A(n) index can be used to quickly determine whether a value already exists in a specific column. _________________________
TRUE
An inline view is a temporary table that only lasts for the duration of the SELECT statement used to create it.
TRUE
When a sequence is created for internal control purposes, the values should not be cached otherwise gaps may appear in the sequence.
TRUE
The USER_INDEXES data dictionary view can be used to verify existing indexes. ________________________
TRUE
The ____________________ view can be used to verify the existence of an index owned by a user.
USER_INDEXES
Structure of the BOOKS table Which of the following methods is not used in an INSERT statement to enable the use of a DEFAULT sequence value defined for a column?
Use NEXTVAL to call the value
The ____ constraint ensures that any DML operations performed on the view (e.g., adding rows, changing data) will not prevent the row from being accessed by the view because it no longer meets the condition in the WHERE clause.
WITH CHECK OPTION
Which constraint ensures that the data in a view cannot be changed?
WITH READ ONLY
The ____________________ keywords can be used to ensure data cannot be changed through the view.
WITH READ ONLY
Who can remove a public synonym from the database?
a user with DBA privileges
A function-based index can be created based on which of the following?
all of the above
The ____________________ index structure can be particularly beneficial in queries involving compound conditions (AND and OR operator usage).
bitmap
A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view.
complex
A synonym is an alternate name assigned to database instances . _________________________
false
A view can be dropped or deleted using the DELETE VIEW command.
false
A view name must be enclosed in single quotation marks if it is referenced in the FROM clause of a SELECT statement.
false
FORCE is the default mode for the CREATE VIEW command. _________________________
false
Rows can be added through a complex view that is based upon grouped data.
false
Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
false
Rows in an underlying table cannot be deleted through a complex view that contains an arithmetic operation.
false
The NOFORCE keyword can be used to create a view based upon a table that has not yet been created. _________________________
false
The START WITH clause is used to identify the starting numeric value for a synonym.
false
The USER_SEQ data dictionary view can be used to verify sequence settings. _________________________
false
The WITHOUT UPDATE keywords prevent DML operations from being performed through a view. _________________________
false
When a query references a view, the query in the view is processed, and the results are treated as a(n) permanent table.
false
If you want to create a view based upon a table or tables that do not yet exist, or are currently unavailable (e.g.,off-line), what keyword can you use to avoid receiving an error message?
force
The ____________________ clause is used to indicate the interval that must exist between two values generated in a sequence.
increment by
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
Which of the following describes a subquery used in a FROM clause to create a "temporary" table that can be referenced by the SELECT and WHERE clauses of the outer query?
inline view
A(n) ____ table is the table that contains the primary key the view uses to uniquely identify each record being displayed by the view.
key-preserved
The type of view that actually replicates data is called a(n) ____ view.
materialized
What is the default mode for the CREATE VIEW command?
noforce
A(n) ____________________ table is a table that does not contain the primary key being used by a complex view to uniquely identify each row displayed by the view.
non key preserved
A(n) ____________________ table is a table that does not contain the primary key being used by a complex view to uniquely identify each row displayed by the view.
non key-preserved
Which of the following is a valid SQL statement when referencing a sequence?
none of the above
An index is not implicitly created when which type of constraint is created?
not null
When a view includes columns from more than one table, updates can only be applied to the table that contains the ____________________ for the view.
primary key
A(n) ____ synonym is used by an individual to reference objects owned by that person.
private
To remove a PUBLIC synonym, you must include the ____ keyword in the DROP command.
public
A(n) ____________________ view cannot be created using a GROUP BY clause.
simple
Which of the following types of views cannot include a GROUP BY clause?
simple view
Which of the following types of views cannot include a group function?
simple view
Which of the following can be used to provide an alternative, simplified name, to identify database objects?
synonym
Which of the following can be created so other users will not need to prefix a table owned by user Jeff with his schema name?
synonyms
A complex view can retrieve data from more than one table.
true
A database index allows users and application programs to quickly locate specific records.
true
A sequence can be deleted from the database using the DROP SEQUENCE command. _________________________
true
A simple view cannot contain a simple arithmetic operation.
true
A view can be thought of as the result of a(n) stored query. _________________________
true
A view cannot be given the same name as another database object.
true
A(n) UPDATE operation can be performed on a complex view that contains an arithmetic operation. _________________________
true
A(n) index can be used to quickly determine whether a value already exists in a specific column. _________________________
true
An inline view is a temporary table that only lasts for the duration of the SELECT statement used to create it.
true
Column aliases can be used in the subquery to assign new column names to the columns contained in a view.
true
If the minimum value for a sequence is not specified, then NOMINVALUE will be assumed as the default.
true
Only one identity column may be defined on a table.
true
The OR REPLACE clause is only necessary if another view may already exist with the same name. _________________________
true
The WITH READ ONLY option can be used to prevent changes from being made to the data displayed by the view.
true
The highest possible value for a descending sequence is -1.
true
To change the starting value for a sequence, the sequence must be dropped and then re-created.
true
Using an identity column is particularly suited to use for a primary key when only random values are needed to uniquely identify rows.
true
Values cannot be inserted through a view into columns that are based on arithmetic expressions.
true
When a sequence is created for internal control purposes, the values should not be cached otherwise gaps may appear in the sequence.
true
The highest possible value for a sequence can be specified through the MAXVALUE clause.
true
Structure of the BOOKS table Which of the following is not a valid statement concerning identity columns.
A DEFAULT clause may be assigned
Which command will modify a sequence?
ALTER SEQUENCE sequencename
Which of the following statements about complex views is incorrect?
All DML operations can be performed on complex views, just like simple views.
Which command will create an index?
CREATE INDEX indexname ON tablename (columnname)
Which command will create a synonym for a table?
CREATE SYNONYM synonymname FOR tablename;
Which of the following statements about performing DML operations on complex views is correct?
DML operations cannot be performed on a non key-preserved table.
Which of the following is used to establish a minimum or maximum value, respectively, for a sequence?
MINVALUE | MAXVALUE
____ aren't allowed in the CREATE VIEW command.
ORDER BY clauses
Which statement is true about the view created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
The shipping date of an order cannot be changed through the view.
When working with cached sequence values, which of the following is correct?
When a value has been generated and cached, that value has been assigned and cannot be regenerated until the sequence begins a new cycle.
