Browse Oracle Certification Guides

Oracle Database 1Z0-071 Cheat Sheet

December 13, 2025

Oracle Database 1Z0-071 cheat sheet for key facts, traps, service mappings, and final review.

On this page

Use this for last-mile review. 1Z0-071 rewards precision about row flow, NULL semantics, grouping behavior, and the exact point where SQL changes from row filtering to result shaping.

1Z0-071 answer sequence

Use this when the stem mixes row flow, NULL semantics, grouping, set operators, or alias rules.

    flowchart TD
	  S["SQL stem"] --> F["Follow FROM/JOIN first"]
	  F --> W["Apply WHERE and null logic"]
	  W --> G["Apply GROUP BY and HAVING"]
	  G --> S2["Check SELECT, set operators, and ORDER BY"]

Read every SQL stem in this order

  1. Identify the row source: tables, joins, inline views, and subqueries.
  2. Apply row filters before aggregation.
  3. Apply grouping and aggregate filters.
  4. Apply the select list, aliases, expressions, and derived columns.
  5. Apply set operators, ordering, and row limiting when present.

Fast lane picker

If the question is mainly about… Start with… Usual winning move
why the row set changed query execution order follow the rows before reading the columns
missing or extra rows join shape and filter placement check row multiplication and outer-join filtering
weird predicate behavior NULL semantics do not trust ordinary equality thinking
display or conversion surprise function and datatype rules check implicit conversion and format masks
totals or aggregates grouping grain and HAVING aggregate at the right level first
membership or existence EXISTS, IN, or NOT EXISTS pick the operator that matches the shape and null behavior
mixed result sets set-operator rules align column count, datatype compatibility, and duplicate handling

Query reading order

    flowchart TD
	  From["FROM / JOIN"] --> Where["WHERE"]
	  Where --> Group["GROUP BY"]
	  Group --> Having["HAVING"]
	  Having --> Select["SELECT"]
	  Select --> SetOps["SET OPERATORS"]
	  SetOps --> Order["ORDER BY / FETCH"]

Fast rule: if you read SELECT first, you will often predict the wrong result set.

SELECT-list and alias traps

Trap Better reading
using a select-list alias in WHERE not available there because WHERE is evaluated earlier
assuming ORDER BY cannot see aliases ORDER BY can usually use select-list aliases
mixing aggregate and nonaggregate columns every nonaggregate selected expression must fit the grouping rule
relying on output formatting distinguish displayed text from stored datatype behavior

Predicate and operator reminders

Topic Fast rule
BETWEEN inclusive on both ends
LIKE pattern matching, not equality; % and _ matter
AND / OR use parentheses when precedence changes meaning
DISTINCT removes duplicate result rows after select-list expressions are formed
ORDER BY final presentation step, not a row-source filter

NULL rules that break answers

Rule Why it matters
NULL is not equal to anything, including another NULL equality logic breaks
use IS NULL or IS NOT NULL = NULL never does what beginners want
NOT IN can surprise you when the subquery contains NULL NOT EXISTS is often safer
outer-join filters can erase preserved rows a bad WHERE clause can quietly turn outer join logic into inner-join behavior
COUNT(col) ignores nulls it is not the same as COUNT(*)

NULL traps

Trap Better reading
col = NULL use IS NULL
NOT IN with nullable values prefer NOT EXISTS if nulls are possible
outer join plus right-table filter in WHERE move the filter into the join condition or handle nulls deliberately
NVL / COALESCE is always harmless replacement values can change datatype or comparison behavior

Conversion and function traps

Function lane Exam reminder
character functions know case conversion, substring, trim, length, and concatenation behavior
number functions rounding, truncation, and modulus-style logic are precision traps
date functions date arithmetic and format masks matter more than intuition
conversion functions TO_CHAR, TO_DATE, and TO_NUMBER depend on masks and session assumptions
null functions NVL, NVL2, NULLIF, and COALESCE solve different shapes
conditional expressions CASE is often clearer than nested function tricks

Date and string reminders

Topic Fast rule
date arithmetic adding a number to a date shifts days
format model controls conversion/display, not the underlying date concept
concatenation with null Oracle string behavior can surprise candidates coming from other databases
case-sensitive string comparison do not assume normalization unless the query does it

Join safety rules

Problem Symptom Better fix
duplicate amplification too many rows after the join fix join keys or aggregate before joining
wrong grain totals are too high or too low join at the correct level
wrong outer-join filtering preserved rows disappear filter in the join condition if appropriate

Left join trap

1SELECT a.id, b.status
2FROM a
3LEFT JOIN b
4  ON b.a_id = a.id
5 AND b.status = 'ACTIVE';

The join condition above preserves rows from a and only matches active b rows. Moving b.status = 'ACTIVE' into the WHERE clause usually changes the result set.

Join chooser

Requirement Strongest first pattern
only matching rows inner join
keep all rows from left side left outer join
keep unmatched rows from both sides full outer join
match every row to every row cross join
compare rows in same table self join
match by same-named columns with caution natural join or USING, but read the column list carefully

Join traps

Trap Better reading
filter preserved-side results in WHERE casually check whether it converts the outer join outcome
join two one-to-many tables before aggregating row multiplication may inflate totals
natural join is safer because it is shorter it can join on more columns than you intended
USING and ON are interchangeable in every expression column qualification and name visibility can differ

Aggregation and grouping

Concern Better rule
row filter before aggregation WHERE
group filter after aggregation HAVING
wrong totals after join check grain before grouping
count logic know the difference between COUNT(*) and nullable expressions

Conditional aggregation

1SELECT
2  dept_id,
3  SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_count
4FROM employees
5GROUP BY dept_id;

GROUP BY decision order

  1. Decide the row set after FROM and WHERE.
  2. Determine the grouping grain.
  3. Compute aggregate values per group.
  4. Apply HAVING.
  5. Only then interpret select-list aliases or ordering.

Aggregate quick map

Aggregate Fast reminder
COUNT(*) counts rows
COUNT(col) counts non-null values
COUNT(DISTINCT col) counts distinct non-null values
SUM / AVG ignore null inputs but return null if no non-null input exists in the group
MIN / MAX work on datatype ordering, not just numeric intuition

Aggregation traps

Trap Better reading
putting aggregate filters in WHERE aggregate filters belong in HAVING
grouping after multiplying rows with the wrong join fix the join grain first
trusting counts without checking null and duplicate behavior inspect row shape before aggregate logic

Subquery chooser

Need Strongest first pattern Why
check whether related rows exist EXISTS existence, not value set
null-safe anti-match NOT EXISTS safer than NOT IN with nullable values
simple membership with trusted non-null values IN compact set-membership form
per-group ranking logic analytic or window approach when available often clearer than layered subqueries

Subquery traps

Trap Better reading
scalar subquery returns more than one row runtime error shape, not a valid comparison
IN and EXISTS are always equivalent nulls and correlation can change meaning
correlated subquery runs once conceptually read it as evaluated per candidate row for logic
multi-row operator omitted = (subquery) requires exactly one value
anti-match with nullable values prefer NOT EXISTS reasoning

Set operators

Operator Fast rule
UNION combines and removes duplicates
UNION ALL combines and preserves duplicates
INTERSECT rows common to both result sets
MINUS rows in the first result set not in the second
all set operators column count and compatible datatypes must align

Set-operator traps

Trap Better reading
assuming UNION preserves duplicates use UNION ALL for that
applying ORDER BY inside each branch without a valid subquery shape final ordering normally belongs at the end
mismatched datatypes compatible positions matter, not column names
wrong branch order for MINUS A MINUS B is not B MINUS A

Analytic function reminders

Topic Fast rule
OVER marks analytic/window calculation
PARTITION BY divides rows for the window calculation
ORDER BY inside OVER controls window order, not necessarily final result display
analytic vs aggregate analytic functions can return a value for each input row
ranking ties and gaps depend on the specific ranking function

DDL and DML reminders

Topic What to remember
constraints PK, UK, FK, CHECK, and NOT NULL enforce integrity
transactions COMMIT persists, ROLLBACK reverts uncommitted work
updates and deletes be deliberate about row scope
schema change questions read the exact constraint and dependency behavior

Transaction and DML traps

Trap Better rule
DELETE and TRUNCATE are equivalent they differ in transaction/logging/DDL-style behavior and trigger implications
missing WHERE in UPDATE or DELETE affects all qualifying rows, possibly every row
MERGE is just insert syntax it combines match and not-match logic
COMMIT is optional trivia transaction boundary changes what can be rolled back
constraint failure affects one statement only in every case read the exact statement and transaction behavior

Constraint and object map

Object or constraint Fast reminder
primary key unique and not null identifier
unique constraint prevents duplicates but null behavior matters
foreign key enforces referenced parent relationship
check constraint validates allowed values
not null column-level absence prevention
view stored query interface, not a copied table by default
sequence generates numbers independently of transaction rollback expectations
synonym alternate name, not a permission grant by itself

Row limiting and sorting

Topic Fast rule
deterministic top-N needs meaningful ORDER BY before limiting
FETCH FIRST row-limiting clause after ordering logic
ties read whether the query includes tie-handling behavior
pagination offset/limit logic depends on final sorted result, not base-table storage order

Decision order that usually wins

  1. Trace rows through FROM, joins, and WHERE.
  2. Resolve null-sensitive predicates before trusting comparison intuition.
  3. Check grouping grain before aggregate output.
  4. Choose subquery, set-operator, or analytic-function behavior by shape.
  5. Confirm transaction, constraint, and object side effects last.

Last 15-minute review

If you only keep one thing from each lane… Remember this
query order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
NULLs NULL breaks ordinary equality reasoning
joins check row multiplication before trusting totals
grouping WHERE filters rows, HAVING filters groups
subqueries NOT EXISTS is often the safer anti-match when nulls are possible
set operators duplicates and branch order are part of the answer
transactions COMMIT, ROLLBACK, and DDL-style operations change recovery options

What strong 1Z0-071 answers usually do

  • trace the query in execution order instead of guessing from the SELECT list
  • check whether a join changes row count before trusting totals
  • treat NULL as a source of edge cases first, not a detail to remember later
  • prefer the operator or subquery form that matches the exact question shape
  • separate display formatting from stored datatype behavior
  • choose the simplest SQL construct that matches the row shape
Revised on Sunday, May 10, 2026
Study Plan
Sample Questions