Views, sequences, indexes: 1Z0-171 practice questions
Questions in the style of exam 1Z0-171 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 on Oracle 26ai?
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.
- It fails with ORA-30926, because Sales and IT appear several times in the join and would be updated more than once.
- ORA-01732: data manipulation operation not legal on this view
Show the answer
Answer: C
departments is not key-preserved in this join: a department repeats once per employee. Oracle 26ai allows an update of its columns only when each department row is changed once; here Sales and IT would be changed several times, so the statement fails with ORA-30926 and nothing is renamed. Restricted to a department with one employee, such as Administration, it would work. In 19c every such update was refused with ORA-01779, the answer older material gives. Updating e.salary through the same join always works.
3. View emp_ro is created with WITH READ ONLY. What does DELETE FROM emp_ro WHERE employee_id = 108 do in Oracle 26ai?
- 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; Oracle reports ORA-42399 (releases before 12c 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.