Views are one of the more nuanced topics on the 1Z0-071 exam. The questions are not just about CREATE VIEW syntax — they test whether you know when DML through a view is allowed, what WITH CHECK OPTION and WITH READ ONLY actually do, and how simple views differ from complex views in terms of what operations are permitted.
Get these rules clear and view questions become straightforward. Get them fuzzy and you will lose marks on questions that look simple on the surface.
01 — What a view is — and what it is not
A view is a stored SELECT statement. It has a name, it appears in the data dictionary, and you query it like a table. But it does not store data. Every time you query a view, Oracle runs the underlying SELECT against the base tables at that moment.
-- The view stores this definition CREATE VIEW emp_dept50 AS SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id = 50; -- Querying the view runs the stored SELECT against EMPLOYEES SELECT * FROM emp_dept50; -- Equivalent to running the full SELECT directly -- Data comes from EMPLOYEES — not stored in the view itself
02 — CREATE VIEW syntax
The basic syntax is straightforward. Column aliases in the view can be defined either inside the SELECT or in a column list after the view name.
-- Basic view CREATE VIEW emp_dept50 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 50; -- Column aliases defined in the view name header CREATE VIEW emp_summary (id, name, annual_sal) AS SELECT employee_id, last_name, salary * 12 FROM employees; -- Column aliases required in the header when the SELECT uses expressions -- because expressions have no default column name -- OR define aliases inline in the SELECT CREATE VIEW emp_summary AS SELECT employee_id AS id, last_name AS name, salary * 12 AS annual_sal FROM employees; -- CREATE OR REPLACE — modify the view definition without dropping first CREATE OR REPLACE VIEW emp_dept50 AS SELECT employee_id, last_name, salary, job_id FROM employees WHERE department_id = 50;
salary * 12 or a function call), that column has no default name. The view creation will fail unless you provide an alias — either in the column list after the view name or inline in the SELECT with AS.
03 — Simple views vs complex views
The exam distinguishes between simple and complex views based on what the underlying SELECT contains. This classification matters because it determines whether DML through the view is possible.
| Feature in the SELECT | Simple view | Complex view |
|---|---|---|
| Number of base tables | One | Two or more (JOIN) |
| GROUP BY / HAVING | Not present | Present |
| DISTINCT keyword | Not present | Present |
| Group functions (SUM, AVG etc.) | Not present | Present |
| DML operations allowed? | Generally yes | Generally no |
-- Simple view — one table, no aggregates, no DISTINCT CREATE VIEW simple_emp AS SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id = 50; -- Complex view — JOIN across two tables CREATE VIEW emp_with_dept AS SELECT e.employee_id, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; -- Complex view — aggregate function CREATE VIEW dept_summary AS SELECT department_id, COUNT(*) headcount, AVG(salary) avg_sal FROM employees GROUP BY department_id;
04 — DML through a view — when it is allowed
You can run INSERT, UPDATE, and DELETE through a view in some cases. The rules depend on what the view's SELECT contains. This is one of the most tested areas in the entire views topic.
| View contains | INSERT | UPDATE | DELETE |
|---|---|---|---|
| Simple single-table view | Yes | Yes | Yes |
| JOIN (multiple tables) | No | Sometimes — only the key-preserved table | No |
| GROUP BY / aggregate functions | No | No | No |
| DISTINCT | No | No | No |
| Expressions (e.g. salary * 12) | No — on that column | No — on that column | Yes |
| Pseudocolumn (ROWNUM) | No | No | No |
-- Simple view — DML is allowed UPDATE simple_emp SET salary = 6000 WHERE employee_id = 120; -- This updates the EMPLOYEES base table directly -- View with expression — cannot UPDATE the expression column CREATE VIEW emp_annual AS SELECT employee_id, last_name, salary * 12 AS annual_sal FROM employees; UPDATE emp_annual SET annual_sal = 72000 WHERE employee_id = 100; -- Error: virtual column not allowed here -- DELETE through this view is still allowed though
05 — WITH CHECK OPTION
WITH CHECK OPTION prevents DML through the view from creating or modifying rows that would no longer be visible through that view. It enforces that any row inserted or updated must still satisfy the view's WHERE clause.
CREATE VIEW emp_dept50 AS SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id = 50 WITH CHECK OPTION CONSTRAINT emp_d50_ck; -- This UPDATE is rejected — it would move the row out of dept 50 -- meaning the row would no longer be visible through the view UPDATE emp_dept50 SET department_id = 80 WHERE employee_id = 120; -- Error: new row does not satisfy the view's WHERE clause -- This UPDATE is allowed — salary change keeps the row in dept 50 UPDATE emp_dept50 SET salary = 7000 WHERE employee_id = 120; -- Allowed: row still visible through the view after the change
06 — WITH READ ONLY
WITH READ ONLY prevents all DML through the view — INSERT, UPDATE, and DELETE are all blocked. The view can still be queried. This is the strongest form of view protection.
CREATE VIEW emp_readonly AS SELECT employee_id, last_name, salary FROM employees WITH READ ONLY; -- Any DML raises ORA-42399 UPDATE emp_readonly SET salary = 5000 WHERE employee_id = 100; -- Error: ORA-42399: cannot perform a DML operation on a read-only view -- Queries work fine SELECT * FROM emp_readonly; -- allowed
| WITH CHECK OPTION | WITH READ ONLY | |
|---|---|---|
| Blocks INSERT? | Only if new row fails WHERE clause | Always |
| Blocks UPDATE? | Only if updated row fails WHERE clause | Always |
| Blocks DELETE? | No | Always |
| Blocks SELECT? | No | No |
| Error raised | ORA-01402 | ORA-42399 |
07 — Modifying and dropping views
Views can be replaced without dropping them first using CREATE OR REPLACE VIEW. Dropping a view removes its definition from the data dictionary but does not affect the underlying base tables or their data.
-- Replace an existing view definition CREATE OR REPLACE VIEW emp_dept50 AS SELECT employee_id, last_name, salary, job_id, department_id FROM employees WHERE department_id = 50; -- Overwrites the existing definition in place -- If the view did not exist, it is created fresh -- Drop a view DROP VIEW emp_dept50; -- Removes the view definition only -- The EMPLOYEES base table and its data are not affected
08 — Inline views vs stored views
An inline view is a subquery in the FROM clause — it behaves like a view for the duration of one query but is not stored anywhere. A stored view has a name, lives in the data dictionary, and persists across sessions.
-- Inline view — exists only for this query SELECT dept_avg.department_id, dept_avg.avg_sal FROM (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) dept_avg WHERE dept_avg.avg_sal > 7000; -- Stored view — persists in the data dictionary CREATE VIEW dept_avg_view AS SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id; SELECT * FROM dept_avg_view WHERE avg_sal > 7000;
09 — Quick reference
| Rule | Detail |
|---|---|
| Views store no data | Data lives in base tables — view is a stored SELECT only |
| Expressions need aliases | CREATE VIEW fails if a SELECT expression has no column name |
| CREATE OR REPLACE | Replaces existing view without dropping — preserves grants |
| Simple view — DML allowed | Single table, no GROUP BY, no DISTINCT, no aggregates |
| Complex view — DML blocked | JOIN, GROUP BY, DISTINCT, or aggregate functions present |
| Expression column — no INSERT/UPDATE | Cannot write to a derived column — DELETE still allowed |
| WITH CHECK OPTION | Blocks INSERT/UPDATE that would hide the row from the view |
| WITH READ ONLY | Blocks all DML — SELECT still works |
| DROP VIEW | Removes definition only — base table data untouched |
| Base table dropped | Dependent views become invalid — not automatically dropped |
10 — Practice questions
CREATE VIEW dept50_emp AS SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 50 WITH CHECK OPTION;A user runs:
UPDATE dept50_emp SET department_id = 80 WHERE employee_id = 121;What happens?
Chapter 14 covers every view scenario on the exam — simple vs complex, WITH CHECK OPTION, WITH READ ONLY, DML rules, and view management. Instant PDF download.
No comments:
Post a Comment