Oracle Database 1Z0-071 Sample Questions with Explanations

These original sample questions are designed to help you check how the exam topics appear in decision-style prompts. They are not taken from the live exam.

Use these sample questions as a guided self-assessment for Oracle Database SQL (1Z0-071) topics such as join preservation, NULL behavior, grouping order, HAVING, subqueries, set operators, and function-driven filtering. The prompts focus on reading the SQL in execution order instead of guessing from the result shape.

Where these questions fit in the 1Z0-071 guide

The sample set below is part of the Oracle Database SQL guide path:

• Oracle certification hub
• Oracle Database guides
• SQL and development guides
• 1Z0-071 exam guide
• 1Z0-071 cheat sheet
• 1Z0-071 study plan
• 1Z0-071 FAQ
• 1Z0-071 resources
• 1Z0-071 glossary

1Z0-071 SQL sample questions

Work through each prompt before opening the explanation. For SQL questions, trace row preservation, filter placement, grouping, and null behavior before choosing an answer.

Question 1

Topic: Outer join filter placement

A report must return every department, including departments with no active employees. Which query shape preserves departments with no matching active employee row?

• A. FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.status = 'ACTIVE'
• B. FROM departments d INNER JOIN employees e ON d.department_id = e.department_id AND e.status = 'ACTIVE'
• C. FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id AND e.status = 'ACTIVE'
• D. FROM employees e RIGHT JOIN departments d ON d.department_id = e.department_id WHERE e.status = 'ACTIVE'

Best answer: C

Explanation: The active-status condition belongs in the outer join condition when unmatched departments must be preserved. Rows with no active employees still produce a department row with null employee columns.

Why the other choices are weaker:

• A applies the employee filter in WHERE, eliminating null-extended rows and effectively undoing the preservation.
• B uses an inner join, so departments without active employees disappear.
• D has the same problem as A: the WHERE filter removes rows where employee columns are null.

What this tests: Outer join preservation and the difference between ON and WHERE filtering.

Related topics: Outer joins; Filter placement; Null-extended rows; Query order

Question 2

Topic: Null-safe exclusion

A subquery can return employee IDs and occasionally returns NULL. You need customers whose assigned employee is not in that subquery. Which pattern is usually safest?

• A. WHERE assigned_employee_id NOT IN (SELECT employee_id FROM blocked_employees)
• B. WHERE assigned_employee_id <> (SELECT employee_id FROM blocked_employees)
• C. WHERE NOT EXISTS (SELECT 1 FROM blocked_employees b WHERE b.employee_id = c.assigned_employee_id)
• D. WHERE assigned_employee_id != NULL

Best answer: C

Explanation: NOT EXISTS tests correlated row existence and avoids the classic NOT IN plus NULL trap, where one null in the subquery can prevent expected matches.

Why the other choices are weaker:

• A is vulnerable when the subquery returns NULL.
• B assumes the subquery returns exactly one row and still does not solve null semantics cleanly.
• D is invalid logic for null testing; SQL uses IS NOT NULL.

What this tests: Three-valued logic, null-safe anti-join reasoning, and subquery choice.

Related topics: NULL; NOT EXISTS; NOT IN; Subqueries

Question 3

Topic: Group filter timing

You need departments whose total approved expense amount is greater than 10000. Which query structure applies the threshold at the right stage?

• A. WHERE SUM(amount) > 10000 GROUP BY department_id
• B. GROUP BY department_id WHERE SUM(amount) > 10000
• C. HAVING amount > 10000 GROUP BY department_id
• D. GROUP BY department_id HAVING SUM(amount) > 10000

Best answer: D

Explanation: Aggregate conditions are applied after grouping with HAVING. A row-level WHERE filter cannot directly use SUM(amount).

Why the other choices are weaker:

• A tries to use an aggregate in WHERE.
• B puts clauses in an invalid order and still misuses WHERE.
• C filters individual row amounts, not grouped totals, and orders clauses incorrectly.

What this tests: Grouping order, aggregate filters, and WHERE versus HAVING.

Related topics: GROUP BY; HAVING; Aggregates; Clause order

Independent study note

Tech Exam Lexicon and IT Mastery are independent study tools. They are not affiliated with, endorsed by, or sponsored by Oracle or any certification body.