Stepwise-Lab

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);
  1. The row is inserted into employees with department_id 20.
  2. ORA-01402: view WITH CHECK OPTION where-clause violation
  3. ORA-01732: data manipulation operation not legal on this view
  4. 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';
  1. Every department that has at least one employee is renamed to Ops.
  2. Every row of departments is renamed to Ops, including departments without employees.
  3. ORA-01779: cannot modify a column which maps to a non key-preserved table
  4. 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?

  1. Deletes employee 108 from employees
  2. Raises ORA-42399: cannot perform a DML operation on a read-only view
  3. Raises ORA-01402: view WITH CHECK OPTION where-clause violation
  4. 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');
  1. Add department_id to the select list and insert the value 30 for it.
  2. Replace WITH CHECK OPTION with WITH READ ONLY.
  3. Use employee_id 109 instead of 900.
  4. 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.