Browse Exams — Mock Exams & Practice Tests

1Z0-071 Cheatsheet — Oracle SQL: Joins, NULLs, Aggregations, Subqueries

Last-mile 1Z0-071 review: high-yield Oracle SQL rules and examples for joins, NULL semantics, grouping/aggregation, subqueries, set operators, and DDL/DML basics.

On this page

Use this for last‑mile review. The exam rewards precision about NULLs, join semantics, and grouping.

1) Query order of operations (mental model)

1FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

2) NULL rules that break answers

  • NULL is not equal to anything (even NULL).
  • Use IS NULL / IS NOT NULL (not = NULL).
  • Outer join + WHERE filters can turn into an inner join if you filter on the right table incorrectly.

3) Join safety rules

ProblemSymptomFix
Duplicate amplificationtoo many rows after joinjoin keys not unique
Wrong outer join filteringmissing rowsmove filter into join condition or handle nulls
Wrong granularitytotals are offaggregate before joining or join at correct level

4) Aggregation patterns

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;

HAVING vs WHERE

  • WHERE filters rows before grouping.
  • HAVING filters groups after aggregation.

5) Subquery pickers

NeedPattern
Filter by related existenceEXISTS
“Not in set” with null-safe behaviorNOT EXISTS (often safer than NOT IN)
Top‑N per groupwindow function + filter (concept-level)

6) DDL/DML basics to remember

  • Constraints enforce integrity (PK/UK/FK/CHECK/NOT NULL).
  • Transactions: COMMIT persists; ROLLBACK reverts uncommitted changes.
Syllabus
Practice