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

Problem Symptom Fix
Duplicate amplification too many rows after join join keys not unique
Wrong outer join filtering missing rows move filter into join condition or handle nulls
Wrong granularity totals are off aggregate 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

Need Pattern
Filter by related existence EXISTS
“Not in set” with null-safe behavior NOT EXISTS (often safer than NOT IN)
Top‑N per group window 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