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
- Identify the row source: tables, joins, inline views, and subqueries.
- Apply row filters before aggregation.
- Apply grouping and aggregate filters.
- Apply the select list, aliases, expressions, and derived columns.
- 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
- Decide the row set after
FROM and WHERE.
- Determine the grouping grain.
- Compute aggregate values per group.
- Apply
HAVING.
- 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
- Trace rows through
FROM, joins, and WHERE.
- Resolve null-sensitive predicates before trusting comparison intuition.
- Check grouping grain before aggregate output.
- Choose subquery, set-operator, or analytic-function behavior by shape.
- 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