Views, sequences, indexes: 1Z0-071 practice questions
Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Managing indexes
- Managing synonyms
- Managing sequences
- Creating tables
- Creating, modifying and dropping views
Practice questions
1. What happens when this statement runs?
INSERT INTO (SELECT employee_id, last_name, hire_date, department_id
FROM employees
WHERE department_id = 30
WITH CHECK OPTION)
VALUES (900, 'Test', DATE '2024-01-01', 20);- The row is inserted into employees with department_id 20.
- ORA-01402: view WITH CHECK OPTION where-clause violation
- ORA-01732: data manipulation operation not legal on this view
- The row is inserted, but it is not visible through the subquery.
Show the answer
Answer: B
WITH CHECK OPTION works the same for a view and for a subquery used as a DML target: a new row with department_id 20 could not be selected by WHERE department_id = 30, so Oracle raises ORA-01402. Without the option the row would be inserted and simply be invisible through the view. ORA-01732 is for views that are not updatable at all (DISTINCT, GROUP BY ...).
2. What happens when this statement runs?
UPDATE (SELECT e.salary, d.department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id)
SET department_name = 'Ops';- Every department that has at least one employee is renamed to Ops.
- Every row of departments is renamed to Ops, including departments without employees.
- ORA-01779: cannot modify a column which maps to a non key-preserved table
- ORA-01732: data manipulation operation not legal on this view
Show the answer
Answer: C
In a join view only key-preserved tables can be changed: employees is key-preserved (each employee appears at most once), departments is not (a department repeats once per employee). Updating department_name raises ORA-01779. Updating e.salary through the same join works. (Oracle 26ai relaxed the rule: there this statement fails with ORA-30926, because each department row would be updated several times, and an update that changes each department row only once is allowed.)
3. View emp_ro is created with WITH READ ONLY. What does DELETE FROM emp_ro WHERE employee_id = 108 do in Oracle 19c?
- Deletes employee 108 from employees
- Raises ORA-42399: cannot perform a DML operation on a read-only view
- Raises ORA-01402: view WITH CHECK OPTION where-clause violation
- Deletes nothing and reports 0 rows deleted
Show the answer
Answer: B
WITH READ ONLY blocks INSERT, UPDATE, DELETE and MERGE through the view; 19c reports ORA-42399 (older releases raised ORA-01733 for the same case). ORA-01402 belongs to WITH CHECK OPTION. The base table can still be changed directly. A subquery used as a DML target accepts the same clause, as the demo shows.
4. This insert fails with ORA-01402. Which change makes it succeed while keeping WITH CHECK OPTION?
INSERT INTO (SELECT employee_id, last_name, hire_date
FROM employees
WHERE department_id = 30
WITH CHECK OPTION)
VALUES (900, 'Test', DATE '2024-01-01');- Add department_id to the select list and insert the value 30 for it.
- Replace WITH CHECK OPTION with WITH READ ONLY.
- Use employee_id 109 instead of 900.
- Write the date as TO_DATE('2024-01-01', 'YYYY-MM-DD').
Show the answer
Answer: A
department_id is not in the view, so the new row gets NULL there, and WHERE department_id = 30 cannot select it: ORA-01402. Exposing department_id and inserting 30 satisfies the check. WITH READ ONLY blocks every insert (ORA-42399). The key value and the date literal have nothing to do with the view's condition.