1Z0-071 — Oracle Database SQL Scenario Practice Guide

This independent scenario guide is for candidates preparing for Oracle Database SQL (1Z0-071). The exam often rewards precise reading: a scenario may describe tables, rows, constraints, required output, transaction state, or an error symptom, then ask you to choose the SQL statement or interpretation that best matches those facts.

The goal is not to memorize every possible SQL pattern. The goal is to slow down, identify the actual decision point, and select the answer that is most defensible under Oracle SQL behavior.

What a SQL scenario is really testing

A SQL scenario usually contains more information than you need. Your job is to extract the facts that control the answer.

In 1Z0-071-style preparation, scenarios commonly ask you to decide:

• Which query returns the required rows and columns.
• Which join preserves or excludes unmatched rows.
• Which filter belongs in WHERE, HAVING, or an outer join condition.
• How NULL values affect comparisons, aggregates, and subqueries.
• Which function or conversion handles dates, strings, or numbers correctly.
• Whether a statement is valid based on grouping, aliasing, data types, or set operator rules.
• Which DML statement changes the intended rows without changing too much.
• How COMMIT, ROLLBACK, SAVEPOINT, DDL, and constraints affect the database state.
• Which object definition, view, sequence, or constraint behavior satisfies the requirement.

The best answer is usually the one that satisfies the stated requirement with the least unsupported assumption.

The five-pass reading method

Use the same routine for every scenario, especially under time pressure.

1. Name the requested action

Before reading the answer choices, identify the verb in the question.

Ask:

• Are you being asked to display rows?
• Insert, update, or delete data?
• Create or modify an object?
• Predict the output?
• Identify which statement fails?
• Choose the statement that fixes a symptom?
• Determine transaction visibility or rollback behavior?

This matters because the same facts may lead to different decisions. For example, “employees without departments” is a join/filter problem in a query question, but a referential integrity problem in a DML question.

2. Determine the row grain

The row grain is the unit of the result.

Ask:

• Is the result one row per employee, department, customer, order, or product?
• Is the result one row per group?
• Is the result a single summary row?
• Are duplicate rows acceptable?
• Must rows with no matching child records still appear?

This step prevents you from choosing a statement that returns technically related data but at the wrong level of detail.

Examples:

• “Show each department and the number of employees” means one row per department, so GROUP BY department-level columns is likely needed.
• “Show employees who have no commission” means one row per employee, so an aggregate query is probably not needed.
• “Show departments even if they have no employees” means the department table must be preserved in an outer join.

3. Mark the controlling facts

Do not treat every sentence equally. Underline facts that change SQL behavior.

Controlling facts usually include:

• Table and column names.
• Data types, especially character, number, and date columns.
• Primary key, foreign key, unique, NOT NULL, and check constraints.
• Whether a column may contain NULL.
• Whether unmatched rows must be included.
• Whether duplicates must be removed or preserved.
• Whether the operation occurs before or after a COMMIT, ROLLBACK, or DDL statement.
• Required sort order.
• Whether a filter applies to individual rows or grouped results.
• Whether a comparison should use exact match, pattern match, range, or date logic.

Background facts may be useful, but they are not always decisive. A business story about “active customers” only matters if the scenario gives a column or condition that defines active status.

4. Predict the needed SQL shape

Before looking at options, sketch the kind of SQL you expect.

For a query, decide:

• Base table or tables.
• Join type.
• Row filters.
• Grouping, if any.
• Group filters, if any.
• Projection, aliases, and sort order.
• Whether DISTINCT, a set operator, or a subquery is needed.

For DML, decide:

• Target table.
• Columns affected.
• Required WHERE condition.
• Constraint impact.
• Transaction impact.

For DDL or database objects, decide:

• Object being created or changed.
• Required constraint, view, sequence, or column property.
• Whether the statement changes transaction state.

Then compare answer choices to your expected shape. This reduces the chance of being pulled toward an option that looks familiar but answers a different question.

5. Validate with Oracle SQL rules

Once an answer seems semantically right, check whether it is valid SQL.

Useful validation questions:

• Are non-aggregated selected columns compatible with the GROUP BY?
• Is a WHERE condition incorrectly using an aggregate?
• Is HAVING being used for a group-level condition?
• Is NULL handled with IS NULL or IS NOT NULL instead of equality?
• Are data types compatible, or is an explicit conversion needed?
• Is a SELECT-list alias referenced only where it is visible?
• Do set operator queries return the same number of compatible columns?
• Does a subquery return the number of values the outer expression expects?
• Does DML violate a constraint stated in the scenario?

Identify the environment and system state

SQL scenario facts describe an environment. Build a quick mental map before solving.

Table structure

Look for:

• Which table contains the requested attribute.
• Which table contains the filtering condition.
• Which table connects the others.
• Which columns are keys or foreign keys.
• Which columns allow NULL.

Example reasoning:

If the required output is employee name and department name, the employee table may contain the foreign key, while the department table contains the department name. If the scenario requires employees without departments, the join direction and NULL handling become the decision point.

Existing data

Sample rows are not just decoration. Use them to test the answer choices.

Check:

• Are there duplicate values?
• Are there NULL values?
• Are there rows with no matching parent or child?
• Are there dates with time components?
• Are there values that test boundary conditions, such as equality at the start or end of a range?

You do not need to calculate every row immediately. First identify which rows are designed to expose the difference between choices.

Transaction state

For DML and transaction scenarios, track the timeline.

Ask:

• What changed before the current statement?
• Was there a COMMIT?
• Was there a SAVEPOINT?
• Was there a ROLLBACK, and did it roll back everything or to a savepoint?
• Did a DDL statement occur?

In Oracle Database, DDL statements such as CREATE, ALTER, DROP, and TRUNCATE have transaction implications. If a scenario mixes DML and DDL, do not assume all prior changes remain freely rollbackable.

Match the clause to the decision point

Many SQL scenarios are solved by placing the requirement in the correct clause.

SELECT: what must be shown

Use SELECT for expressions, aliases, calculated values, and displayed columns.

Ask:

• Does the result need raw column values or derived values?
• Should NVL, COALESCE, CASE, ROUND, TRUNC, UPPER, LOWER, or date functions transform the output?
• Is DISTINCT required because duplicates must be removed?
• Is an alias required for readability or sorting?

Be careful not to solve a filtering requirement in the SELECT list. A CASE expression can display categories, but it does not remove rows unless it is also used in a filtering condition.

FROM and joins: which rows are eligible

Joins decide which rows can appear before filtering and grouping.

Use this reasoning sequence:

• If only matching rows are needed, expect an inner join.
• If all rows from one table must be retained, expect an outer join preserving that table.
• If the scenario says “with no,” “even if none,” or “including departments without employees,” look for an outer join or a subquery pattern.
• If multiple tables are involved, confirm that each join condition connects the correct keys.
• If duplicate rows appear, ask whether the join path is multiplying rows because of one-to-many relationships.

A small example:

• Requirement: “List all departments, including departments that have no employees.”
• Likely shape: departments preserved, employees optionally matched.
• Defensible approach: DEPARTMENTS LEFT OUTER JOIN EMPLOYEES on the department key.
• Key check: a filter on employee columns in the WHERE clause can accidentally remove the null-extended rows unless the logic accounts for that.

WHERE: row-level filtering

WHERE filters individual rows before grouping.

Use WHERE for:

• Exact comparisons.
• Ranges.
• Pattern matching with LIKE.
• IN, EXISTS, and correlated conditions.
• IS NULL and IS NOT NULL.
• Date filters when applied to individual rows.

Ask whether the condition is row-level or group-level. “Employees hired after a date” is row-level. “Departments with more than five employees” is group-level.

GROUP BY: the result grain changes

GROUP BY changes the result from individual rows to grouped rows.

Use it when the scenario asks for:

• Count per department.
• Total by customer.
• Average salary by job.
• Minimum or maximum date per group.

Validation checks:

• Every selected non-aggregate expression must be compatible with the grouping.
• Aggregate functions summarize rows in each group.
• Most aggregate functions ignore NULL values.
• COUNT(*) counts rows, while COUNT(column) counts non-null values in that column.

HAVING: group-level filtering

Use HAVING when the filter depends on an aggregate or grouped result.

Examples:

• “Departments with average salary greater than 10000.”
• “Customers with more than three orders.”
• “Products whose total sales exceed a threshold.”

If the condition can be applied before grouping, WHERE may be the cleaner and more efficient logical choice. If the condition uses an aggregate, HAVING is normally required.

ORDER BY: final presentation

ORDER BY controls result order. It does not decide which rows qualify.

Check:

• Ascending vs descending.
• Multiple sort keys.
• Whether NULL placement is specified or relevant.
• Whether an alias or expression is used appropriately.

If the question asks which statement “displays” rows in a particular order, do not ignore the final ORDER BY.

Separate constraints from preferences

Scenarios often contain both hard requirements and nice-to-have wording. Treat them differently.

Hard constraints sound like:

• “Must include departments with no employees.”
• “Must return only one row per customer.”
• “Must not delete orders.”
• “Must preserve duplicate rows.”
• “Must roll back only the last change.”
• “Must prevent negative values.”
• “The column contains NULL values.”

Preferences or context sound like:

• “The report is used by managers.”
• “The table is large.”
• “The application is new.”
• “Users prefer readable output.”

Preferences can matter if an answer choice directly addresses them, but hard constraints usually decide the answer.

Reason through common SQL decision points

Inner join, outer join, or subquery

When the scenario involves related tables, identify whether the requirement is about matching rows, missing rows, or existence.

Use this guide:

• Matching parent and child rows: inner join.
• All parent rows, with child data if present: outer join preserving parent.
• Parents with no child rows: outer join with a null check, or NOT EXISTS.
• Rows where a related row exists: EXISTS, IN, or an inner join depending on required output.
• Rows where no related row exists: NOT EXISTS is often safer than NOT IN when the subquery column may contain NULL.

Do not choose a join only because it uses the right tables. Check whether it preserves the correct side.

NULL logic

NULL means unknown or missing, not a normal value. This affects many scenario answers.

Use these checks:

• Equality comparisons with NULL do not work as ordinary comparisons.
• Use IS NULL or IS NOT NULL for null tests.
• COUNT(column) ignores nulls; COUNT(*) counts rows.
• Arithmetic or concatenation involving nulls should be evaluated according to Oracle SQL behavior and the functions used.
• NVL or COALESCE may be needed when the scenario requires a replacement value.
• NOT IN can behave unexpectedly if the subquery result contains NULL; verify the facts before choosing it.

If a scenario explicitly states that a column can contain NULL, expect that fact to affect the answer.

Date and time facts

Date scenarios require careful reading because a date column may include time information.

Ask:

• Is the scenario comparing dates only, or date and time?
• Is the required range inclusive or exclusive?
• Is the statement relying on implicit conversion?
• Is a format model needed with TO_DATE or TO_CHAR?
• Would applying a function to the column change the comparison semantics?

For final review, prefer reasoning that compares like data types. If the fact pattern gives a string date and the answer choice uses an explicit format model, check whether it matches the stated format.

Character matching and case

For string scenarios, identify the exact matching requirement.

Look for:

• Exact match with =.
• Pattern match with LIKE.
• Multiple possible values with IN.
• Prefix, suffix, or contains logic using %.
• Single-character wildcard logic using _.
• Case transformation with UPPER or LOWER if the requirement demands case-insensitive matching.

Do not assume a pattern match when the scenario asks for exact values, and do not assume exact match when the wording says “starts with,” “contains,” or “ends with.”

Aggregates and grouped reports

When a scenario asks for counts, totals, averages, minimums, or maximums, first decide the group.

Example:

Requirement: “Display each department that has more than five employees.”

Expected shape:

• Join or read employee rows as needed.
• Group by department.
• Count employees in each department.
• Filter groups with HAVING COUNT(...) > 5.

Then validate details:

• Should departments with zero employees appear?
• Should the count include all rows or only non-null employee IDs?
• Are departments identified by ID, name, or both?
• Are selected columns all grouped or aggregated?

Set operators

Set operators solve scenarios that combine results from separate queries.

Use the scenario wording:

• UNION combines and removes duplicates.
• UNION ALL combines and preserves duplicates.
• INTERSECT returns rows common to both result sets.
• MINUS returns rows from the first query that are not in the second.

Validation checks:

• Each query must return the same number of columns.
• Corresponding columns must have compatible data types.
• Final sorting belongs to the combined result, not to each individual query unless a supported subquery structure is used.
• Column names for the final result are generally determined by the first query.

If the scenario says duplicate rows must be retained, UNION ALL is usually the key phrase.

Subqueries

Subquery scenarios often turn on cardinality and correlation.

Ask:

• Does the outer query compare to one value or many values?
• Should the subquery return one column or multiple columns?
• Is the subquery correlated to each row of the outer query?
• Does the condition require IN, EXISTS, ANY, ALL, or a scalar comparison?
• Could NULL values in the subquery affect the result?

Examples:

• “Employees whose salary is greater than the average salary” suggests a scalar subquery if the average is for all employees.
• “Employees whose salary is greater than the average salary in their own department” suggests a correlated subquery or analytic-style reasoning, depending on available choices.
• “Departments that have at least one employee” can be solved with EXISTS.

Functions and conversions

Function questions are usually about using the right tool for the required transformation.

For number scenarios:

• Rounding vs truncating.
• Absolute value, modulus, or arithmetic.
• Aggregate vs scalar calculation.

For character scenarios:

• Substring extraction.
• Padding and trimming.
• Case conversion.
• Concatenation.
• Replacing or locating characters.

For date scenarios:

• Current date/time functions.
• Adding days or months.
• Extracting parts of a date.
• Formatting for display with TO_CHAR.
• Converting strings to dates with TO_DATE.

For null-handling scenarios:

• NVL for a simple Oracle-specific replacement.
• COALESCE for first non-null expression logic.
• NULLIF when equal values should produce null.
• CASE for conditional output.

Always confirm whether the function is being used to change displayed output, filter rows, or create a stored value. Those are different decisions.

Choose the least disruptive DML answer

For INSERT, UPDATE, DELETE, and transaction scenarios, the best answer is the one that makes the required change without unintended side effects.

INSERT

Check:

• Are the columns listed?
• Do values match the listed column order?
• Are data types compatible?
• Are required NOT NULL columns supplied or covered by defaults?
• Are primary key and unique constraints respected?
• Are foreign key values valid based on the stated parent rows?

If a scenario gives only some columns, ask what happens to omitted columns: they receive defaults if defined, otherwise NULL if allowed.

UPDATE

Check:

• Is the target table correct?
• Is the SET clause changing the required column?
• Is the WHERE clause selective enough?
• Does a subquery return the right value or rows?
• Could the update violate a constraint?

An update without a proper WHERE clause may affect all rows. In a scenario, that is acceptable only if the requirement truly says all rows should change.

DELETE

Check:

• Is the scenario asking to delete rows or remove the table definition?
• Does the WHERE clause identify only the intended rows?
• Are child rows or foreign key constraints mentioned?
• Is the operation supposed to be rollbackable?

DELETE removes rows and participates in transaction control. TRUNCATE is different and should only be chosen when its behavior matches the scenario.

Transactions

Track transaction control precisely.

Use a timeline:

1. DML statement occurs.
2. Optional savepoint is created.
3. More DML occurs.
4. ROLLBACK, ROLLBACK TO SAVEPOINT, or COMMIT occurs.
5. A query asks what remains visible or permanent.

Key reasoning points:

• COMMIT makes current transaction changes permanent.
• ROLLBACK undoes uncommitted changes.
• ROLLBACK TO SAVEPOINT undoes changes after that savepoint, while earlier uncommitted changes remain pending.
• DDL has implicit transaction effects in Oracle Database.

If answer choices differ only by what can be rolled back, build the timeline before choosing.

Read object and constraint scenarios carefully

1Z0-071 preparation may involve SQL objects and schema definitions. The decision point is often whether the object enforces the stated rule.

Constraints

Match the business rule to the constraint type:

• Required value: NOT NULL.
• Unique value: UNIQUE.
• Entity identifier: PRIMARY KEY.
• Valid relationship to parent table: FOREIGN KEY.
• Allowed values or range: CHECK.

Then validate details:

• Does the constraint apply to the correct column or column combination?
• Does existing data violate the new rule?
• Does the scenario require parent rows before child rows?
• Does the scenario mention delete behavior, such as cascading effects?

Do not infer constraints that are not stated. If a scenario says only that column names are similar, that does not automatically mean a foreign key exists.

Views

For view scenarios, ask:

• Is the view used to simplify a query?
• Is it used to restrict columns or rows visible to users?
• Does the requirement prevent DML through the view?
• Is WITH CHECK OPTION needed to keep changes within the view condition?
• Is WITH READ ONLY needed to prevent changes through the view?

If security or least exposure is part of the scenario, a view may be more appropriate than giving direct access to all base table columns, depending on the available choices.

Sequences

For sequence scenarios, track session and statement behavior.

Ask:

• Is the scenario asking for a new generated value?
• Has NEXTVAL been referenced before CURRVAL in the session?
• Does the statement need one generated value per inserted row?
• Does the requirement assume gap-free numbering?

Do not assume sequences are gap-free unless the scenario explicitly states a mechanism that guarantees it.

Troubleshooting SQL scenarios

Some questions describe a statement that fails or returns the wrong rows. Use a diagnosis sequence.

If the statement fails to execute

Classify the likely issue:

• Syntax: clause order, missing keyword, invalid alias reference.
• Name resolution: wrong table, column, alias, or object.
• Grouping: selected column not grouped or aggregated.
• Cardinality: scalar subquery returns more than one row.
• Data type: incompatible comparison or conversion.
• Constraint: DML violates primary key, foreign key, unique, check, or not-null rule.
• Privilege or object access: scenario states the user lacks access.

Then choose the option that fixes the specific cause. Avoid selecting an answer that rewrites the statement but does not address the stated error.

If the statement returns the wrong rows

Find the row that proves the issue.

Check:

• A row with no match in another table.
• A row with NULL.
• A row on a date boundary.
• A duplicate row.
• A row that belongs to a group just above or below a threshold.
• A row filtered before grouping when it should have been filtered after grouping.

Use sample data as test cases. You usually need only one counterexample to eliminate an answer.

Security and least privilege in SQL scenarios

Although 1Z0-071 is centered on SQL rather than administration, some scenarios may include access, views, synonyms, or object privileges.

Use a least-privilege reading habit:

• Identify what the user or application needs to do: read, insert, update, delete, or execute.
• Prefer access to the needed object or view, not broader access than required.
• If only selected columns or rows should be visible, consider whether a view addresses the requirement.
• Do not assume administrative privileges unless the scenario states them.
• Do not choose a destructive statement when a read-only or restricted object satisfies the need.

The principle is simple: match the access mechanism to the stated task, and avoid granting or exposing more than the scenario requires.

A compact scenario checklist for final review

Before selecting an answer, ask these questions in order:

1. What exact action is requested?
2. What is the required result grain?
3. Which table must be preserved, if any?
4. Are there NULL values that change comparison or aggregation behavior?
5. Is the filter row-level or group-level?
6. Are duplicates supposed to be removed or retained?
7. Are data types compatible, especially dates and strings?
8. Does the statement obey grouping and subquery cardinality rules?
9. Does DML affect only the intended rows?
10. Do transaction facts change what can be committed or rolled back?
11. Does the answer satisfy the requirement without assuming facts not given?
12. If more than one answer looks valid, which one best matches every constraint in the scenario?

Mini examples of defensible reasoning

Example 1: Departments with no employees

Scenario clue: “Display all departments, including departments that currently have no employees.”

Reasoning:

• Required grain: one row per department.
• Controlling fact: departments with no matching employees must appear.
• SQL shape: preserve departments with an outer join.
• Answer check: avoid a WHERE filter on employee columns that removes unmatched departments unless it explicitly allows the null-extended rows.

Example 2: Group filter

Scenario clue: “List job IDs where the average salary is greater than a specified value.”

Reasoning:

• Required grain: one row per job.
• Aggregate needed: AVG(salary).
• Grouping needed: GROUP BY job_id.
• Filter phase: group-level, so use HAVING AVG(salary) > ....
• Answer check: a WHERE AVG(salary) > ... condition is not the right clause.

Example 3: Date boundary

Scenario clue: “Find orders placed on 15-JUN-2026.”

Reasoning:

• Required grain: one row per order.
• Controlling fact: the order date column may include time.
• SQL shape: use a date-aware comparison that captures the whole day.
• Answer check: equality to a date value at midnight may miss rows with later times on the same date, depending on stored values.

Example 4: Transaction timeline

Scenario clue: An update occurs, a savepoint is created, another update occurs, then ROLLBACK TO SAVEPOINT is issued.

Reasoning:

• The first update occurred before the savepoint.
• The second update occurred after the savepoint.
• Rolling back to the savepoint undoes the second update, not the first.
• The first update is still uncommitted unless a later COMMIT occurs.

Practice strategy for scenario questions

For final review, practice in short, focused sets rather than only long sessions.

A useful sequence:

1. Drill one topic, such as joins or grouping, until you can identify the decision point quickly.
2. Review missed questions by writing the controlling fact that determined the answer.
3. Mix topics so you must decide whether the scenario is about joins, nulls, functions, DML, or transactions.
4. Take timed mock exams to practice reading accurately under pressure.
5. After each timed set, classify misses by reasoning step: misunderstood goal, wrong row grain, missed null, wrong clause, invalid syntax, or transaction timeline error.

Your next step: choose a focused 1Z0-071 scenario practice set, read each question using the five-pass method, and write one sentence explaining why the selected answer is the most defensible choice from the facts provided.