1Z0-149: Managing Views and Temporary Tables

What this domain tests

Views and temporary tables provide controlled or session-specific access to data. The exam tests view creation, DML through views, and temporary table scope.

Core rules

CREATE OR REPLACE VIEW defines a stored query. A simple view uses one table and can often support DML; a complex view with joins, group functions, or DISTINCT usually cannot.
WITH CHECK OPTION prevents inserts or updates that would make rows invisible through the view's WHERE clause.
WITH READ ONLY blocks DML through the view.
An inline view is a subquery in FROM; it is not stored in the data dictionary.
A global temporary table stores rows private to a session. Its definition is permanent, but its data is session-specific.
ON COMMIT DELETE ROWS clears rows at commit; ON COMMIT PRESERVE ROWS keeps them for the session.
Private temporary tables are session-private and use a prefix; they disappear according to their definition clause.

Worked example

CREATE OR REPLACE VIEW high_pay AS SELECT employee_id, last_name, salary FROM employees WHERE salary > 10000 WITH CHECK OPTION;

The view exposes only high earners. An update that lowers a salary to 9000 through the view fails because the row would no longer satisfy the view's WHERE clause. The check option protects the view's predicate, not the base table's other constraints.

Traps

Expecting a complex view with GROUP BY to accept arbitrary DML.
Confusing WITH CHECK OPTION with a base-table check constraint.
Assuming global temporary table rows are visible across sessions.
Forgetting that a temporary table's definition persists after the session ends.
Adding ORDER BY inside a view and expecting the outer query to preserve that order.