Oracle 12c: SQL Chapter 13

a ROWNUM value less than or equal to N.

A TOP-N analysis is performed by determining the rows with:

WITH READ ONLY option

How do you ensure that no user can change the data displayed by a view?

Simple View: a view based on a subquery that references only one table and doesn't include group functions, expressions, or GROUP BY clauses. Complex View: a view based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

How is a simple view different from a complex view?

Virtual column not allowed here.

If you try to add a row to a complex view that includes a GROUP BY clause, you get which error?

DML operations that violate a constraint aren't permitted. A value can't be added to a column containing an arithmetic expression. DML operations aren't permitted on non-key-preserved tables. DML operations aren't permitted on views that include group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM psuedocolumn.

List the guidelines for DML operations on complex views:

List them after the VIEW keyword inside of parentheses. The number of names listed must match the number of returned by the SELECT statement.

To assign names to columns in a view, you can do which of the following?

As long as the view was not created with the READ ONLY option and the DML operation doesn't violate an existing constraint in an underlying table.

Under that circumstances is a DML operation not allowed on simple view?

A view cannot be modified. To change view, it must be dropped or re-created or the CREATE OR REPLACE VIEW command can be used.

What command can be used to modify a view?

The data remains in the underlying tables.

What happens to the data displayed by a view when the view is deleted?

Ensures that any DML operations performed on the view (such as adding rows or changing data) don't prevent the view from accessing the row because it no longer meets the condition in the WHERE clause.

What is the purpose of the WITH CHECK OPTION constraint?

A key-preserved table is the table containing the primary key that view is using to uniquely identify each record it displays. A non-key-preserved table does not allow any DML operations.

What's the difference between a key-preserved and non-key-preserved table?

Enables storage of data retrieved by the view query and reuse the data without executing the view query again. The replication of data.

What's unique about materialized views compared to other views?

When a new database is being developed and the data hasn't yet been loaded or entered into database objects.

When should the FORCE keyword be used in the CREATE VIEW command?

No operations can be performed on a view with a group function.

Which of the following DML operations can't be performed on a view containing a group function?