Oracle 1Z0-071 case studies

Oracle 1Z0-071 · Put your knowledge to work in context.

The Oracle Database SQL exam, exam code 1Z0-071, is the certification test behind the Oracle Database SQL Certified Associate credential. It is aimed at developers, database administrators, data analysts and students who want to demonstrate that they can write and reason about SQL against an Oracle Database rather than only read about it. The Oracle 1Z0-071 case studies are 5 cases and about 4,341 words.

Left Join Predicate Placement and the NOT IN NULL Trap

In the HR reporting schema, EMPLOYEES holds 107 rows (employee id, last name, salary, department id, manager id) and DEPARTMENTS holds 27 rows (department id, department name). Five departments currently have no employees, and three employees have a NULL department id.

884 words, about 4 min 2

Session Time Zone Drift in a TIMESTAMP WITH TIME ZONE Report

ORDERS in the GLOBAL ORDERS schema stores order id, customer id and order ts TIMESTAMP WITH TIME ZONE; roughly 4.2 million rows exist for 2024, with a B-tree index on order ts. Application sessions connect with ALTER SESSION SET TIME ZONE='America/New York', while the London finance team connects with 'Europe/London'.

788 words, about 4 min 3

ORA-30926 Caused by a Duplicate Source Feed in a Nightly MERGE

A nightly load moves PRODUCT STAGE (12,431 rows) into PRODUCTS (2.1 million rows, primary key product id). PRODUCT STAGE is populated by two warehouse feeds and has no unique constraint; three product id values appear twice, each time with a different name and modified ts.

924 words, about 5 min 4

WITH CHECK OPTION Violation and Sequence Gaps Under CACHE

SALES APP owns CUSTOMERS(customer id primary key, name, status VARCHAR2(8) with CHECK status IN ('ACTIVE','INACTIVE','HOLD'), credit limit NUMBER) and the view: CREATE OR REPLACE VIEW v active customers AS SELECT customer id, name, status, credit limit FROM customers WHERE status = 'ACTIVE' WITH CHECK OPTION CONSTRAINT v active chk; A support developer runs UPDATE v active customers SET status='INACTIVE' WHERE customer id=1042 and receives ORA-01402. Separately, order loading uses SEQ ORDER ID, created with CACHE 20 ORDER.

876 words, about 4 min 5

Definer's Rights Procedure Fails with ORA-00942 Despite a Role Grant

APP owns BONUS and CALC BONUS, a definer's-rights procedure that reads HR.EMPLOYEES and inserts into BONUS. APP was granted SELECT on HR.EMPLOYEES only through the role HR READER, which is enabled by default for the APP account.

869 words, about 4 min