I85PREP
⌘ K
1Z0-909 study materials
1Z0-909 study resources

1Z0-909: Views and Temporary Tables

1Z0-909 · Work through your exam, one topic at a time.

All guide sections

Views and Temporary Tables

What the domain tests

This domain checks how to define and use views and temporary tables, including algorithm selection, updatability, check options, and session scope. Expect questions about which view definitions can be merged, which are updatable, and when a temporary table hides a base table.

Core rules

  • CREATE VIEW defines a stored query. ALGORITHM=MERGE merges the view into the outer query; ALGORITHM=TEMPTABLE materializes it; UNDEFINED lets the server choose.
  • A view is updatable if it maps rows one-to-one to a base table and avoids constructs such as DISTINCT, GROUP BY, HAVING, UNION, aggregate functions, and some subqueries. Updatable views can support INSERT, UPDATE, and DELETE.
  • WITH CHECK OPTION rejects inserts or updates that would produce rows not visible through the view's WHERE clause. LOCAL checks the view's own condition; CASCADED checks underlying views too.
  • The SQL SECURITY clause controls whether the view runs with DEFINER or INVOKER privileges.
  • TEMPORARY tables are visible only to the creating session, are dropped automatically at disconnect, and can have the same name as a permanent table. In that case the temporary table shadows the base table for that session.
  • Temporary tables can be created with CREATE TEMPORARY TABLE ... AS SELECT and can have indexes.

Worked example

CREATE VIEW active_users AS SELECT id, name FROM users WHERE active = 1 WITH CHECK OPTION; INSERT INTO active_users (id, name, active) VALUES (5, 'Ana', 0) fails because the inserted row would not satisfy active = 1. The reasoning is that WITH CHECK OPTION validates the row against the view predicate after the insert is mapped to the base table.

Traps

  • Assuming every view is updatable; aggregate and UNION views are not.
  • Expecting an ORDER BY inside a view to guarantee outer-query order.
  • Using ON DUPLICATE KEY or REPLACE against a view without checking updatability rules.
  • Forgetting that a temporary table with the same name as a base table hides it until dropped or the session ends.
  • Granting privileges on a view without considering DEFINER versus INVOKER security.