AIT 524 - Wk 14 (Additional Database Objects and Views)

A NEXTVAL call generates the sequence value of 5. After the value is generated, what value will be stored in CURRVAL?

5

Which of the following is not a valid statement concerning identity columns. a. ​ Only one may be created in a table b. ​ A unique constraint is automatically applied c. ​ A DEFAULT clause may be assigned d. ​ Must have a numeric data type

A DEFAULT clause may be assigned

The keyword ____________________ must be used in a CREATE INDEX command to create a Bitmap index.

BITMAP

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

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?​ a. CREATE INDEX books_profit_idx ON books "retail-cost"; b. CREATE INDEX books_title_idx ON books (INITCAP(title));​

CREATE INDEX books_title_idx ON books (INITCAP(title));​

Based on the structure of the CUSTOMERS table, which of the following is a valid SQL statement? a. CREATE PUBLIC INDEX customers_name_idx ON customers (lastname); b. CREATE INDEX customers_name_idx ON customers (lastname, firstname);​

CREATE INDEX customers_name_idx ON customers (lastname, firstname);​

Which of the following commands is used to create a function-based index? a. CREATE FUNCTION INDEX...ON b. CREATE INDEX...ON c. CREATE INDEX....FOR d. CREATE INDEX...FUNCTION

CREATE INDEX...ON

Which of the following commands is used to create a function-based index? a. CREATE FUNCTION INDEX...ON b. CREATE INDEX...ON c. CREATE INDEX....FOR d. CREATE INDEX...FUNCTION

CREATE INDEX...ON

Which command will create a synonym for a table?

CREATE SYNONYM synonymname FOR tablename;

The ____________________ option is used to indicate that the values assigned by a sequence can be used after the maximum value is reached.

CYCLE

DML operations on a view created with the ____________________ keyword that is used to suppress duplicate data are not permitted.

DISTINCT

The ____________________ SEQUENCE command can be used to permanently delete a sequence from a database.

DROP

The ____________________ SYNONYM command is used to permanently remove a synonym from a database

DROP

Which of the following commands can be used to remove a sequence from a database?​ a. ​ DROP SEQ b. ​ DELETE SEQ c. ​ DROP SEQUENCE d. ​ REMOVE SEQUENCE

DROP SEQUENCE

DML operations can be performed on a view created with the DISTINCT keyword.​ TRUE FALSE

FALSE

Rows can be added through a complex view that is based upon grouped data. TRUE FALSE

FALSE

The CURRVAL pseudocolumn is used to generate the next value in a sequence. TRUE FALSE

FALSE

The ____________________ keyword can be used to create a view based upon a table that does not yet exist.

FORCE

A large table containing a particular column that is frequently referenced by a WHERE clause would probably benefit from a(n) sequence . True False

False

A view name must be enclosed in single quotation marks if it is referenced in the FROM clause of a SELECT statement.​ True False

False

Rows can be added through a complex view that is based upon grouped data.​ True False

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.​ True False

False

The CURRVAL pseudocolumn is used to generate the next value in a sequence. True False

False

The CURRVAL pseudocolumn is used to generate the next value in a sequence. _________________________ True False

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 NEXTVALUE pseudocolumn is used to actually generate the next value in a sequence. ​ True False

False

The pseudo column ROW can be used to perform a "TOP-N" analysis. True False

False

When a query references a view, the query in the view is processed, and the results are treated as a(n) permanent table. True False

False

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

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 ____________________ pseudo column is used to generate the next sequence value. ​

NEXTVAL

Which of the following statements about NEXTVAL and CURRVAL is incorrect?​

NEXTVAL and CURRVAL are pseudocolumns that store unique names for database objects.

If a view needs to be modified, it must be re-created using the ____________________ keywords in the CREATE VIEW command.​

OR REPLACE

An inline view for "TOP-N" analysis is created by sorting data in descending order through the use of the ____________________ clause in the subquery.

ORDER BY

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

DML operations are not allowed on views that include the pseudo column ____________________

ROWNUM

DML operations cannot be performed on non key-preserved tables through a complex view. TRUE FALSE

TRUE

A basic CREATE INDEX command will create a B-Tree index. True False

True

A(n) sequence can be used to generate a series of numeric values.

True

An inline analysis can be used to find the "Top-N" values. _________________________​ True False

True

By default, the START WITH clause has a value of one.​

True

Rows cannot be added to a table through a complex view that is based on a group function.

True

The NEXTVAL pseudocolumn can be referenced in an INSERT command to add the value to a database table.

True

The START WITH clause of a sequence cannot be reset with the ALTER SEQUENCE command. True False

True

Values cannot be inserted through a view into columns that are based on arithmetic expressions.​

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 False

True

When a view includes columns from more than one table, updates can only be applied to the table that includes the primary key for the view.​ True False

True

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 can be used to ensure data cannot be changed through the view.

WITH READ ONLY

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.

A function-based index can be created based on which of the following?​ a. ​ UPPER(lastname) b. ​ (retail-cost) c. ​ UPPER(firstname) d. ​ all of the above

all of the above

The OR REPLACE clause is not required if ____.​ a. ​ the view is not based on a group function b. ​ the CREATE command does not specify the WITH CHECK OPTION option c. ​ another view does not exist with the same name d. ​ the view does not contain data from more than one table

another view does not exist with the same name

A(n) ____________________ view can contain grouped data.​

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#; a. ​ simple b. ​ inline c. ​ complex d. ​ partial

complex

A sequence can be used to speed up row retrieval.​ True False

false

All synonyms are PUBLIC. True False

false

DML operations cannot be performed on a key-preserved table.​ True False

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.​ True False

false

Rows in an underlying table cannot be deleted through a complex view that contains an arithmetic operation.​ True False

false

Views are database objects that store data.​ True False

false

Which of the following serves the same basic purpose as an index in a book, by allowing users to quickly locate specific records? ​ a. synonym b. index c. view d. sequence

index

A(n) ____ table is the table that contains the primary key the view uses to uniquely identify each record being displayed by the view.​ a. ​ non key-preserved b. ​ primary c. ​ non primary-keyed d. ​ key-preserved

key-preserved

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.​ a. ​ simple b. ​ materialized c. ​ inline d. ​ complex

materialized

If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.​ a. ​ objective b. ​ private c. ​ PUBLIC d. ​ functional

private

If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.​ a. ​ private b. ​ objective c. ​ PUBLIC d. ​ functional

private

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; a. ​ simple b. ​ complex c. ​ inline d. ​ none of the above

simple

Which of the following describes a type of view that is based upon a subquery that only references one table and does not include any group functions, expressions, or GROUP BY clauses?​ a. ​ "TOP-N" analysis b. ​ complex view c. ​ simple view d. ​ inline view

simple view

Which of the following types of views cannot include a GROUP BY clause?​ a. ​ simple view b. ​ inline view c. ​ complex view d. ​ all of the above clause

simple view

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? a. sequence b. synonym c. index d. schema alias

synonym

A database index allows users and application programs to quickly locate specific records.​ True False

true

A view can be thought of as the result of a(n) stored query. _________________________​ True False

true

A(n) index can be used to quickly determine whether a value already exists in a specific column. _________________________ ​ True False

true

If the minimum value for a sequence is not specified, then NOMINVALUE will be assumed as the default.​ True False

true

The USER_INDEXES data dictionary view can be used to verify existing indexes. ________________________ True False

true

A(n) ____ stores a query and is used to access data in the underlying tables.​ a. ​ view b. ​ constraint c. ​ function d. ​ argument

view

Which of the following statements is incorrect?​ a. ​ A view can be created with the CREATE VIEW command. b. ​ A view cannot be modified; if you need to change it, you must use the CREATE OR REPLACE VIEW keywords. c. ​ Views can be modified by using the ALTER VIEW...MODIFY command. d. ​ A view cannot be given the same name as another database object in the same schema.

​Views can be modified by using the ALTER VIEW...MODIFY command.