DML and transactions: 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
- INSERT, UPDATE and DELETE operations
- Multi-table inserts
- MERGE statements
- Managing database transactions
- Controlling transactions
- Truncating tables
Practice questions
1. Which statement about MERGE is true?
- Columns referenced in the ON clause cannot be updated in the WHEN MATCHED clause.
- MERGE must include both WHEN MATCHED and WHEN NOT MATCHED clauses.
- The USING source must be a table; subqueries are not allowed.
- MERGE is DDL and commits implicitly.
Show the answer
Answer: A
Updating a column used in the ON condition raises ORA-38104. Either WHEN clause may be omitted, USING accepts a table, view or subquery, and MERGE is ordinary DML that commits nothing by itself.
2. order_items.order_id references orders with ON DELETE CASCADE. Customer 1 has three orders, 1001, 1002 and 1007, with 2, 1 and 0 lines; order_items holds 9 rows. What happens when DELETE FROM orders WHERE customer_id = 1 runs?
- Oracle reports 3 rows deleted, and order_items holds 6 rows.
- Oracle reports 6 rows deleted, the three orders plus their three lines, and order_items holds 6 rows.
- Oracle reports 3 rows deleted, and order_items still holds 9 rows, with a NULL order_id in three of them.
- It fails with ORA-02292 (child record found), because orders 1001 and 1002 have lines.
Show the answer
Answer: A
ON DELETE CASCADE deletes the child rows together with their parent, so the 3 lines of orders 1001 and 1002 go too, and 6 of the 9 remain. The reported count covers only the table the DELETE names: 3 orders. Setting the children to NULL is ON DELETE SET NULL, and ORA-02292 is what a foreign key without a delete rule raises (DELETE FROM customers WHERE customer_id = 1 fails with it, because orders.customer_id has no ON DELETE clause).
3. The employees table is defined as shown (no column has a DEFAULT), and employee 200 does not exist. What happens when the INSERT runs?
-- CREATE TABLE employees (
-- employee_id NUMBER(6) PRIMARY KEY,
-- first_name VARCHAR2(20),
-- last_name VARCHAR2(25) NOT NULL,
-- email VARCHAR2(50) UNIQUE,
-- hire_date DATE NOT NULL,
-- job_title VARCHAR2(30),
-- salary NUMBER(8,2) CHECK (salary > 0),
-- commission_pct NUMBER(3,2),
-- manager_id NUMBER(6) REFERENCES employees,
-- department_id NUMBER(4) REFERENCES departments);
INSERT INTO employees (employee_id, last_name, email)
VALUES (200, 'Novak', 'ANOVAK');- One row is inserted; hire_date is NULL.
- One row is inserted; hire_date is set to SYSDATE.
- It fails with ORA-01400: cannot insert NULL into hire_date.
- It fails with ORA-00947: not enough values.
Show the answer
Answer: C
Columns left out of the column list receive their DEFAULT, or NULL when there is none. hire_date has no default and a NOT NULL constraint, so ORA-01400 names the column. ORA-00947 only occurs when the VALUES list is shorter than the column list (or the table, if no list is given).
4. Departments 20 and 30 each have several employees (department_id is not unique in employees). What happens when this MERGE runs?
MERGE INTO departments d
USING employees e
ON (d.department_id = e.department_id)
WHEN MATCHED THEN UPDATE SET d.location = e.last_name;- Each department gets the last_name of the last matching employee.
- It fails with ORA-30926: unable to get a stable set of rows in the source tables.
- Each department gets the last_name of the first matching employee.
- It fails with ORA-38104 because location is in the ON clause.
Show the answer
Answer: B
Several employees match the same department, so the same target row would be updated more than once with different values. Oracle refuses with ORA-30926 instead of picking a row. The USING source must deliver at most one row per target row, typically by aggregating it first. ORA-38104 concerns updating ON-clause columns, which this statement does not do.