AIT 524 Quiz 11
If a sequence is set to pre-generate sequential values, ____ values will be generated at one time by default.
20
A NEXTVAL call generates the sequence value of 5. After the value is generated, what value will be stored in CURRVAL?
5
The starting value for a sequence cannot be changed using the ____________________ SEQUENCE command.
ALTER
The ____________________ option of a sequence indicates that a set of values are to be pre-generated by the sequence rather than wait for a user request.
CACHE
Which option is used to have Oracle12c pre-generate a set of values and store those values in the server's memory?
CACHE
Which option is used to have Oracle12c pre-generate a set of values and store those values in the server's memory?
CACHE
An index can be created with the ____________________ INDEX command.
CREATE
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 command will create an index?
CREATE INDEX indexname ON tablename (columnname)
Which of the following commands is used to create a function-based index?
CREATE INDEX...ON
When creating a sequence, which of the following is not an optional clause?
CREATE SEQUENCE
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
The last value generated by a sequence is stored in the ____ pseudocolumn.
CURRVAL
The ____ options are used to determine whether Oracle12c should begin reissuing values from the sequence once the minimum or maximum value has been reached.
CYCLE | NOCYCLE
Even if you created a PUBLIC synonym, you must have ____________________ privileges to delete a PUBLIC synonym.
DBA
Even if you created a PUBLIC synonym, you must have ____________________ privileges to delete a PUBLIC synonym.
DBA
DML operations on a view created with the ____________________ keyword that is used to suppress duplicate data are not permitted.
DISTINCT
Indexes can speed up row retrieval, but can slow down ____________________ operations because the index must also be updated.
DML
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.
The ____________________ SYNONYM command is used to permanently remove a synonym from a database
DROP
Which of the following can be used to remove an index that currently exists on the column of a database table?
DROP INDEX
Which of the following commands can be used to remove a sequence from a database?
DROP SEQUENCE
Which of the following commands can be used to remove a sequence from a database?
DROP SEQUENCE
Which command will delete a sequence?
DROP SEQUENCE sequencename
The ____________________ command can be used to permanently remove a view from a database.
DROP VIEW
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
"TOP-N" analysis can be used to find the highest values in a column by sorting the data in ascending order.
False
A complex view cannot contain any group functions.
False
A large table containing a particular column that is frequently referenced by a WHERE clause would probably benefit from a(n) sequence
False
A view can be dropped or deleted using the DELETE VIEW command.
False
A view can be modified using the MODIFY clause of the ALTER VIEW command.
False
A(n) index can be used to generate a series of unique numbers.
False
After the highest possible value for a sequence has been reached, previous values can be reused if the NOCYCLE option has been specified.
False
An inline view is used to create a(n) permanent table.
False
By default, the lowest value that can be generated by a sequence is 0.
False
DML operations can be performed on a view created with the DISTINCT keyword.
False
DML operations can be performed on a view created with the DISTINCT keyword.
False
DML operations can be performed through a simple view even if the operation will violate constraints on the underlying table.
False
DML operations cannot be performed on a key-preserved table.
False
FORCE is the default mode for the CREATE VIEW command.
False
Rows can be deleted 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 can be deleted 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 cannot be added to a table through a complex view that was created with the ORDER BY clause.
False
The CYCLE option prevents a sequence from regenerating previous values
False
The GENERATE option can be used to have a sequence pre-generate a set of numbers before they are requested by a user.
False
The INCREMENT BY setting for a sequence cannot be changed with the ALTER SEQUENCE command.
False
The NEXTVALUE pseudocolumn is used to actually generate the next value in a sequence.
False
The NEXTVALUE pseudocolumn is used to actually generate the next value in a sequence.
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_ALL data dictionary view displays information about all the relevant objects owned by the user.
False
The WITHOUT UPDATE keywords prevent DML operations from being performed through a view.
False
The column names used in the view must be the same names as the column names in the underlying table(s).
False
The default value for the INCREMENT BY clause is two
False
The pseudo column ROW can be used to perform a "TOP-N" analysis.
False
Update operations cannot be performed through a complex view that contains an arithmetic expression.
False
Views are database objects that store data.
False
Views are not database objects.
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
The USER_ALL data dictionary view displays information about all the relevant objects owned by the user.
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
Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books
INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);
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 ____________________ value of a sequence cannot be changed if the sequence has already generated a number greater than that value.
MAXVALUE
If the last values generated by a sequence were 277 and 278, which of the following changes cannot be initiated by the ALTER SEQUENCE command?
MAXVALUE 200
The ____________________ clause identifies the lowest value a sequence can generate.
MINIVALUE
Which of the following is used to establish a minimum or maximum value, respectively, for a sequence?
MINVALUE | MAXVALUE
The ____________________ pseudocolumn is used to generate the next sequence value.
NEXTVAL
Which of the following is a pseudocolumn?
NEXTVAL
Which of the following keywords is used to actually generate a sequence value?
NEXTVAL
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
Which statement about the view created from the following SQL statement is correct? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
No DML operations can be performed through the view.
If a view needs to be modified, it must be re-created using the ____________________ keywords in the CREATE VIEW command.
OR REPLACE CREATE OR REPLACE VIEW
____ aren't allowed in the CREATE VIEW command.
ORDER BY clauses
Which options are used in application cluster environments where multiple users may be requesting sequence values at the same time?
ORDER | NOORDER
Which options are used in application cluster environments where multiple users mAfter the highest possible value for a sequence has been reached, previous values can be reused if the NOCYCLE option has been specified. ay be requesting sequence values at the same time?
ORDER | NOORDER
DML operations are not allowed on a view that includes the pseudo column ____.
ROWNUM
Oracle12c will begin each sequence with the value of one, unless another value is specified in the ____ clause.
START WITH
The ____________________ clause of the CREATE SEQUENCE command is used to identify the starting value for a sequence.
START WITH
Which of the following sequence settings cannot be changed with the ALTER SEQUENCE command?
START WITH
____________________ analysis could be used to find the top five values stored in a column.
TOP-N
"TOP-N" analysis is performed by sorting values in ascending or descending order in an inline view.
True
A Bitmap index is useful for improving queries on columns that have low cardinality.
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 sequence value may be set as a DEFAULT value for a column.
True
A simple view cannot contain a simple arithmetic operation.
True
A simple view is based upon a subquery that references only one table and doesn't contain any group functions, expressions, or a GROUP BY clause.
True
A synonym can be created for any database object such as a table or sequence.
True
A synonym is an alternate name assigned to a database object.
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) 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
A(n) sequence can be used to generate a series of numeric values.
True
A(n) view is a database object, but it does not actually store data.
True
An ORDER BY clause can be used to perform "TOP-N" analysis.
True
An ORDER BY clause can be used to perform "TOP-N" analysis.
True
An inline analysis can be used to find the "Top-N" values.
True
An inline view is a temporary table that only lasts for the duration of the SELECT statement used to create it.
True
Any DML operation can be performed on a simple view that was not created with the WITH READ ONLY option, unless it violates an existing constraint.
True
Both a minimum and maximum value can be defined for a(n) sequence
True
By default, the START WITH clause has a value of one.
True
Column aliases can be used in the subquery to assign new column names to the columns contained in a view.
True
DML operations cannot be performed on non key-preserved tables through a complex 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
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
Rows cannot be added to a table through a complex view that is based on a group function.
True
Rows cannot be deleted from a table through a complex view that is based on a group function.
True
Rows cannot be deleted if the complex view was created with a(n) GROUP BY clause.
True
Sequence settings can be altered using the ALTER SEQUENCE command.
True
The DELETE operation can be performed through a complex view that contains an arithmetic operation.
True
The NEXTVAL pseudocolumn can be referenced in an INSERT command to add the value to a database table.
True
The NOCACHE option specifies that the sequence can generate a number only after a request has been made by a user.
True
The OR REPLACE clause is only necessary if another view may already exist with the same name.
True
The START WITH clause of a sequence cannot be reset with the ALTER SEQUENCE command.
True
The USER_INDEXES data dictionary view can be used to verify existing indexes.
True
The WITH CHECK OPTION can be used when a view is created to ensure that any DML operations performed on the view will not prevent the row from being accessible by the view at a later time.
True
The WITH READ ONLY option can be used to prevent changes from being made to the data displayed by the view.
True
The default beginning value for a sequence is one.
True
The highest possible value for a sequence can be specified through the MAXVALUE clause.
True
The lowest possible value for an increasing sequence is 1
True
To change the starting value for a sequence, the sequence must be dropped and then re-created.
True
To create a PUBLIC synonym, the PUBLIC keyword must be used in the CREATE SYNONYM command.
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
Views can be used to restrict a user's access to sensitive data.
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 positive value is assigned to the INCREMENT BY clause of the CREATE SEQUENCE command, numeric values are generated in ascending 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
A basic CREATE INDEX command will create a B-Tree index.
True
A function-based index can be used for queries that include searches based upon arithmetic expressions or functions.
True
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
The ____________________ keywords are used to make certain any DML operations do not prevent a row from being accessible to the view after the DML operation is executed.
WITH CHECK OPTION
The ____________________ keywords are used to make certain any DML operations do not prevent a row from being accessible to the view after the DML operation is executed.
WITH CHECK OPTION
Which constraint ensures that the data in a view cannot be changed?
WITH READ ONLY
Who can remove a public synonym from the database?
a user with DBA privileges
A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?
all of the above
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH READ ONLY;
all of the above
Which statement is true about the view created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
all of the above
The OR REPLACE clause is not required if ____.
another view does not exist with the same name
An index can be created ____.
both a and b
A(n) ____________________ view can contain group functions.
complex
Which type of view is created from the following SQL command? CREATE OR REPLACE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit, name FROM books NATURAL JOIN publisher;
complex
Which type of view is created from the following SQL statement? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
complex
Which type of view is created from the following command? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
complex
Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data?
complex view
Rows cannot be added through a complex view that is based on ____________________ data.
grouped
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 contains the ROWID of referenced rows?
index
An ORDER BY clause can be included in a(n) ____________________ view to perform "TOP-N" analysis.
inline
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 a table that contains the primary key being used by the view to uniquely identify each row displayed by the view.
key preserved
A(n) ____________________ view is used to replicate data which can be called a snapshot.
materialized
The type of view that actually replicates data is called a(n) ____ view.
materialized
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 commands can be used to modify an index?
none of the above
Which of the following is a valid SQL statement when referencing a sequence?
none of the above
Which of the following is defined in Oracle12 c as simply anything that has a name and a defined structure?
object
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
A(n) ____________________ synonym can be referenced by other database users.
public
A(n) ____________________ synonym is an alternate name that can be referenced by all users.
public
If a view was created with the WITH CHECK OPTION constraint, to remove the constraint you will need to ____.
re-create the view without the option
A(n) ____________________ can be used for internal control purposes by generating a series of numbers.
sequence
A(n) ____________________ can be used to generate values for a column designated as the primary key for a table.
sequence
A(n) ____________________ view cannot contain grouped data.
simple
Which type of view is created by the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
simple
Which type of view is created with the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH READ ONLY;
simple
Which type of view is created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
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 an arithmetic expression?
simple view
An inline view is created by a SELECT statement that contains a(n) ____________________ in the FROM clause.
subquery
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?
synonym
Which of the following can be used to provide an alternative, simplified name, to identify database objects?
synonym
A(n) ____________________ can be used to restrict a user's access to sensitive data.
view
Which of the following is not a valid statement concerning identity columns.
A DEFAULT clause may be assigned
Which of the following statements about sequences is incorrect?
A sequence can be used to generate unique names for database objects.
Which statement is true about the view created with the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
all of the above
Which of the following is not a valid statement concerning identity columns.
A DEFAULT clause may be assigned
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
A database object named INVENTORY may already exist.
Which of the following statements about complex views is incorrect?
All DML operations can be performed on complex views, just like simple views.
Which statement is not true about the view created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books;
DML operations are not allowed on the data displayed by the view.
Which statement about the view created from the following SQL command is correct, assuming ISBN from the BOOKS table is the primary key used by the view? CREATE OR REPLACE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit, name FROM books NATURAL JOIN publisher;
DML operations can only be performed on the columns belonging to the BOOKS table.
Which of the following SQL statements will delete the private synonym named MYSYNON?
DROP SYNONYM mysynon;
What is the definition of an inline view?
It is a temporary data source that exists only while a command is being executed.
Which statement about the view created from the following SQL statement is correct? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
No DML operations can be performed through the view.
____ 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.
Which of the following statements about performing DML operations on complex views is correct?
Values cannot be inserted into columns that are based on arithmetic expressions.
Which of the following statements is incorrect?
Views can be modified by using the ALTER VIEW...MODIFY command.
A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view.
non key-preserved
If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____.
re-create the view without the option
