1Z0-071 Syllabus — Learning Objectives by Topic

Use this syllabus as your checklist for 1Z0‑071.

What’s covered

• Topic 1: SQL Query Fundamentals
• Topic 2: Filtering and Sorting
• Topic 3: Single-Row Functions and Conditional Logic
• Topic 4: Joins and Relationships
• Topic 5: Aggregation and Group Functions
• Topic 6: Subqueries and Derived Tables
• Topic 7: Set Operators and Advanced Querying
• Topic 8: DDL and Constraints
• Topic 9: DML and Transaction Control
• Topic 10: Views, Security, and Schema Management

Topic 1: SQL Query Fundamentals

Practice this topic →

1.1 SELECT statement basics

• Write SELECT statements that project specific columns and compute simple expressions.
• Use DISTINCT to remove duplicates and explain when it changes result correctness.
• Use column aliases and table aliases to make queries readable and unambiguous.
• Given a query, predict the output columns, aliases, and row shape.

1.2 Expressions, concatenation, and operator behavior

• Use arithmetic operators in SELECT expressions and explain precedence rules.
• Use concatenation to build derived text fields and handle NULLs correctly.
• Explain how NULL propagates through expressions and comparisons.
• Given a query, identify whether an expression evaluates to NULL or a value.

1.3 Data types and literals (concept-level)

• Differentiate common SQL data types (character, numeric, date/time) and typical use cases.
• Write literals in a type-safe way and explain why implicit conversions can be risky.
• Recognize errors caused by incompatible data types and incorrect literal formats.
• Given a scenario, choose a column type that preserves correctness and supports indexing.

Topic 2: Filtering and Sorting

Practice this topic →

2.1 WHERE clause filtering

• Filter rows using comparison operators, BETWEEN, IN, and LIKE.
• Combine conditions with AND/OR and apply parentheses to control precedence.
• Use IS NULL / IS NOT NULL correctly and explain why = NULL does not work.
• Given a scenario, choose a filter strategy that is correct and index-friendly.

2.2 Pattern matching and predicate pitfalls

• Use LIKE with wildcards and ESCAPE and explain common matching surprises.
• Recognize why leading wildcards can prevent efficient index usage (concept-level).
• Avoid common predicate pitfalls: non-sargable expressions and mismatched data types.
• Given a query, identify whether a predicate is logically correct for the requirement.

2.3 ORDER BY and limiting results

• Sort results using ORDER BY with multiple columns and mixed sort directions.
• Explain the difference between sorting by column position, alias, or expression.
• Limit results using row limiting techniques (concept-level) and explain when ordering is required.
• Given a query, predict the final ordering and which rows are returned after limiting.

Topic 3: Single-Row Functions and Conditional Logic

Practice this topic →

3.1 Character and numeric functions

• Use common character functions to transform and extract text (case, substring, trim).
• Use numeric functions for rounding and basic numeric manipulation.
• Explain why function use in WHERE predicates can change index usage (concept-level).
• Given a query, predict function outputs, including edge cases.

3.2 Date/time functions and format models

• Use date/time arithmetic and common date functions to compute intervals and boundaries.
• Convert between strings and dates using explicit format models to avoid implicit conversion issues.
• Recognize common date pitfalls: time components, time zones (concept-level), and NLS settings.
• Given a query, predict date function results for boundary dates (month-end, leap years).

3.3 NULL handling and conditional expressions

• Handle NULLs using COALESCE/NVL-style logic and explain the impact on results.
• Use CASE expressions to implement conditional logic in SELECT and ORDER BY.
• Given a scenario, choose conditional logic that preserves correctness for missing/unknown data.
• Recognize why NULL-aware logic is required in aggregates and joins.

Topic 4: Joins and Relationships

Practice this topic →

4.1 INNER JOIN fundamentals

• Join tables using INNER JOIN with correct join predicates.
• Differentiate join conditions from filters and place predicates correctly.
• Given a scenario, choose join keys that prevent accidental row multiplication.
• Recognize ambiguous column references and resolve them with table aliases.

4.2 OUTER JOINs and self-joins

• Use LEFT/RIGHT OUTER JOINs to keep unmatched rows and interpret NULL-extended results.
• Given a scenario, choose OUTER JOIN vs UNION-based approaches for completeness requirements.
• Write self-joins for parent/child or adjacency relationships and avoid aliasing mistakes.
• Recognize how filters on the outer-joined table can accidentally turn an OUTER JOIN into an INNER JOIN.

4.3 Multi-table joins and join pitfalls

• Join three or more tables and reason about join order effects on row counts.
• Detect and avoid Cartesian products caused by missing join predicates.
• Given a query, predict how duplicate keys or one-to-many relationships affect result size.
• Use table aliases consistently to keep complex joins readable and correct.

Topic 5: Aggregation and Group Functions

Practice this topic →

5.1 GROUP BY and HAVING

• Group rows using GROUP BY and explain why selected columns must be grouped or aggregated.
• Filter groups using HAVING and differentiate it from WHERE.
• Given a scenario, choose groupings that match reporting requirements (per customer, per day, per region).
• Predict query behavior when NULLs are present in grouped columns.

5.2 Aggregate functions and DISTINCT

• Use COUNT, SUM, AVG, MIN, and MAX correctly and explain their NULL handling.
• Use COUNT(*) vs COUNT(column) and explain why results differ.
• Given a scenario, choose COUNT(DISTINCT ...) to answer unique-count questions correctly.
• Recognize aggregation pitfalls with joins (double-counting due to row multiplication).

5.3 Advanced grouping concepts (concept-level)

• Explain the purpose of multi-level totals and subtotals (concept-level).
• Given a scenario, choose an approach to produce totals at multiple granularities (per group and grand total).
• Recognize when to use grouping expressions to avoid multiple separate queries (concept-level).
• Interpret results that include subtotal rows vs detail rows.

Topic 6: Subqueries and Derived Tables

Practice this topic →

6.1 Single-row and multi-row subqueries

• Write scalar subqueries and ensure they return at most one row when required.
• Use IN, ANY, and ALL with multi-row subqueries and explain semantic differences.
• Given a scenario, choose a subquery vs a join and justify based on clarity and correctness.
• Recognize runtime errors caused by subqueries returning too many rows.

6.2 Correlated subqueries and EXISTS

• Use EXISTS / NOT EXISTS to test for related rows and explain why it differs from IN with NULLs.
• Write correlated subqueries and identify the correlation predicate.
• Given a scenario, choose correlated subqueries for anti-joins (find rows with no matches).
• Recognize performance and correctness pitfalls in correlated subqueries (missing predicates, duplicates).

6.3 Inline views and common table expressions

• Use inline views (subquery in FROM) to structure complex logic.
• Use WITH clauses (CTEs) to improve readability and reuse computed sets (concept-level).
• Given a scenario, refactor a complex query into layered steps using CTEs.
• Recognize scoping rules for aliases and CTE names.

Topic 7: Set Operators and Advanced Querying

Practice this topic →

7.1 UNION, INTERSECT, and MINUS

• Use UNION vs UNION ALL and explain duplicate handling and cost implications.
• Use INTERSECT and MINUS to compute overlaps and differences between result sets.
• Given a scenario, ensure set operator queries are type-compatible and ordered correctly.
• Recognize how ORDER BY applies to the final combined result set.

7.2 Hierarchical and recursive-style querying (concept-level)

• Explain the goal of hierarchical queries (parent/child traversal) at a conceptual level.
• Given a scenario, choose a hierarchy traversal approach that produces the desired tree output (concept-level).
• Recognize common hierarchy pitfalls: cycles, missing roots, and incorrect join predicates.
• Interpret a hierarchical query output that includes levels and path ordering (concept-level).

7.3 Analytic/window function fundamentals (concept-level)

• Explain the difference between GROUP BY aggregates and analytic/window functions (concept-level).
• Given a scenario, choose analytic functions for running totals, rankings, and moving averages (concept-level).
• Recognize PARTITION BY vs ORDER BY roles in window definitions (concept-level).
• Interpret query results that include both detail rows and analytic calculations.

Topic 8: DDL and Constraints

Practice this topic →

8.1 CREATE and ALTER table basics

• Create tables with appropriate column definitions and NOT NULL constraints.
• Alter table definitions safely (add/modify columns) and understand impact on existing data (concept-level).
• Given a scenario, choose data types that preserve accuracy and support expected query patterns.
• Recognize common DDL errors: invalid identifiers, missing commas, and incompatible type changes.

8.2 Keys, referential integrity, and indexes (concept-level)

• Define PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints and why each matters.
• Given a scenario, choose ON DELETE behavior and understand implications for data integrity (concept-level).
• Explain why CHECK constraints enforce domain rules and prevent bad data.
• Recognize when indexes support performance and when they add write overhead (concept-level).

8.3 Other schema objects (sequences, synonyms) and metadata

• Use sequences to generate surrogate keys and explain why gaps can occur.
• Create and use synonyms to simplify object references (concept-level).
• Given a scenario, find object metadata using data dictionary views (concept-level).
• Recognize ownership and schema qualification rules when referencing objects.

Topic 9: DML and Transaction Control

Practice this topic →

9.1 INSERT, UPDATE, DELETE, and MERGE

• Insert rows using INSERT ... VALUES and INSERT ... SELECT patterns.
• Update and delete rows with correct WHERE predicates to avoid unintended mass changes.
• Given a scenario, choose MERGE to implement upsert logic safely (concept-level).
• Recognize common DML errors: constraint violations, missing columns, and data type mismatches.

9.2 COMMIT, ROLLBACK, and savepoints

• Explain when changes become durable and visible using COMMIT.
• Undo changes using ROLLBACK and use SAVEPOINT for partial rollback control.
• Given a scenario, choose transaction boundaries that preserve consistency for multi-step changes.
• Recognize the risk of long transactions (locks, undo growth) and how to avoid them.

9.3 Concurrency concepts (locks and read consistency)

• Explain basic locking behavior for DML and why concurrent writers can block each other (concept-level).
• Given a scenario, identify symptoms of blocking and choose mitigation (short transactions, proper indexing).
• Recognize anomalies caused by incorrect transaction handling (lost updates, inconsistent reads) at a conceptual level.
• Interpret simple scenarios involving two sessions updating the same rows.

Topic 10: Views, Security, and Schema Management

Practice this topic →

10.1 Views and query encapsulation

• Create views to encapsulate query logic and simplify consumer access.
• Differentiate simple vs complex views and understand basic updatability constraints (concept-level).
• Given a scenario, use views to restrict columns/rows exposed to consumers.
• Recognize when inline views vs named views are more appropriate.

10.2 Privileges, roles, and least privilege

• Grant and revoke object privileges and explain the principle of least privilege.
• Given a scenario, choose which privileges are needed for SELECT vs DML vs DDL actions.
• Recognize security risks of overbroad grants and shared accounts.
• Explain why schema qualification and controlled synonyms reduce accidental cross-schema access.

10.3 Data dictionary usage and basic troubleshooting

• Use data dictionary views to find tables, columns, constraints, and indexes (concept-level).
• Given a scenario, troubleshoot common errors (invalid identifier, constraint violation, too many rows).
• Recognize why privileges and object ownership frequently cause query failures.
• Interpret execution errors and decide whether the issue is syntax, data, or permissions.