Oracle · 1Z0-071

1Z0-071 Exam: Complete Oracle Database SQL Guide

Current 1Z0-071 exam guide covering the two-hour Oracle Database SQL exam, query, function, join, DDL, DML, schema-object, and preparation topics.

Last verified September 6, 2026

1Z0-071 exam at a glance

Facts last verified September 6, 2026

Domain breakdown

Who should take the 1Z0-071 exam?

1Z0-071 is for database developers, analysts, application developers, support engineers, and administrators who want to validate Oracle SQL skills. It focuses on retrieving and changing data, combining result sets, applying functions, controlling transactions, and managing common schema objects.

No prerequisite exam is required. You should understand tables, rows, columns, keys, relationships, and data types and be able to write queries without relying exclusively on a visual query builder. The certification is most useful when SQL is part of your real work or a database career path.

The todo list's label calls this “Oracle Database 12c SQL,” but Oracle's current official exam name is Oracle Database SQL. Prepare from the live Oracle topic list and current SQL documentation; do not constrain study to an old 12c-branded course.

1Z0-071 format, price, and current status

Oracle's current learning catalog still provides registration for 1Z0-071 and lists the online exam as two hours. Public details can differ across language-specific deliveries, and Oracle's checkout determines the current regional price.

Confirm the question count, passing threshold, price, identity rules, and delivery policy in Oracle MyLearn before purchasing. This avoids copying stale figures from older exam pages and acknowledges that Oracle can revise exam forms while retaining the code.

Skills measured on 1Z0-071

Oracle does not publish percentage weights for the exam-topic groups. Treat every objective as testable. The best study method is to predict a statement's result, execute it in Oracle Database, and explain why the result occurred.

Basic SELECT, filtering, and sorting

Retrieve full rows or selected columns and build expressions with arithmetic, literals, aliases, and concatenation. Understand null propagation and DISTINCT. Use DESCRIBE to inspect object structure and recognize how client display differs from stored data.

Filter with comparison, logical, range, list, pattern, and null predicates. Operator precedence can change the answer, so use parentheses deliberately. Sort by columns, aliases, positions, and expressions and understand default null placement where applicable.

Limit rows using syntax appropriate to the target database version. SQL*Plus substitution variables, DEFINE, and VERIFY are client features used in reusable scripts; they are not bind variables or stored database objects.

Functions, conversions, and aggregation

Single-row character functions manipulate case, substrings, length, padding, trimming, replacement, and search. Number functions such as ROUND, TRUNC, and MOD transform numeric values. Date arithmetic depends on Oracle date and timestamp semantics, session settings, and explicit formats.

Use TO_CHAR, TO_DATE, and TO_NUMBER with suitable format models. Understand implicit conversion but avoid depending on it in production because session settings can change behavior. Conditional logic includes CASE, DECODE, NVL, NVL2, NULLIF, and COALESCE, with different type and evaluation implications.

Work with CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, database or session time zones, and interval types. Be precise about whether a value includes time-zone information.

Aggregate with COUNT, SUM, AVG, MIN, and MAX; understand null handling and DISTINCT inside aggregates. GROUP BY defines groups and HAVING filters groups, whereas WHERE filters source rows. Every non-aggregated selected expression must be compatible with the grouping.

Joins, subqueries, and set operators

Use inner, left, right, and full outer joins, self-joins, cross joins, natural joins, and non-equijoins. Predict cardinality from relationships and predicates. A missing or incorrect join condition can multiply rows without producing a syntax error.

Subqueries can return one row, many rows, or correlated results. Choose operators compatible with cardinality and understand null behavior with IN, NOT IN, EXISTS, and NOT EXISTS. Scalar subqueries must return at most one row.

Set operators combine compatible query results. UNION removes duplicates, UNION ALL retains them, INTERSECT keeps common rows, and MINUS subtracts the second set from the first. Column count and data types must align, and final ordering applies to the compound result.

DML, DDL, and transactions

Insert, update, delete, and merge rows with correctly scoped predicates. Use subqueries where allowed and understand which rows are affected before executing a change. Practice transaction control with COMMIT, ROLLBACK, and savepoints and observe visibility across two sessions.

DDL creates and changes objects and commonly causes implicit commits. Create tables with suitable data types and defaults, alter their structure, truncate data, and drop or rename objects. Know the difference among DELETE, TRUNCATE, and DROP in logging, transaction behavior, object retention, and privileges.

Constraints implement NOT NULL, uniqueness, primary keys, foreign keys, and check rules. Understand constraint states, referential effects, and why an index is not itself a substitute for a declared data-integrity rule.

Schema objects and data dictionary

Views store query definitions and can simplify access or security, subject to updatability and dependency rules. Sequences generate values independently of transaction rollback. Indexes provide access paths but add storage and write overhead. Synonyms supply alternate names without granting privileges.

Temporary tables have persistent definitions with session- or transaction-specific data. Query data dictionary views to inspect objects, columns, constraints, indexes, and privileges. Distinguish views for objects you own from those visible through granted access or administrative scope.

How to prepare for 1Z0-071

Use Oracle Live SQL or a legal local Oracle Database environment. Create a small schema with customers, orders, products, and dates. Write multiple solutions for each reporting requirement and compare results, execution behavior, null handling, and maintainability.

Practice edge cases intentionally: nulls, duplicate rows, ties, missing relationships, time zones, implicit conversion, empty result sets, and subqueries returning unexpected cardinality. Open two sessions to observe transactions and locks.

Map every official objective to executable examples. Read the SQL Language Reference for syntax and semantics, then use Oracle's learning path for structured review. Confirm live registration facts inside MyLearn.

1Z0-071 practice questions

Predict query output before running it. For syntax questions, identify logical processing, data types, null behavior, cardinality, and transaction state. Explain why each distractor fails rather than only recording the correct letter.

Avoid exam dumps. Unauthorized content does not teach SQL and can be stale or wrong. A disposable schema gives immediate, trustworthy feedback.

1Z0-071 compared with 1Z0-082

1Z0-071 is a dedicated SQL credential. 1Z0-082 includes overlapping SQL foundations plus instance, storage, user, networking, diagnostic, and data-movement administration. Oracle's current Database 19c administrator path does not require 1Z0-071 first, so choose based on the credential and role.

Career value of Oracle Database SQL

The exam can support SQL developer, data analyst, application support, QA, junior database, and Oracle application roles. It demonstrates product-specific SQL knowledge but should be paired with queries and schema designs you can explain.

SQL skill transfers broadly, while Oracle-specific functions, clients, metadata, and transaction semantics add value in Oracle estates. Continue into PL/SQL, performance, administration, or data engineering according to your work.

Exam-day notes

• Oracle's current catalog lists the online exam as two hours.
• Confirm question count, passing requirement, price, and delivery in MyLearn.
• Watch for nulls, implicit conversions, duplicate rows, and missing join predicates.
• Read whether the question asks for valid syntax, actual output, or the best design.
• Never assume old 12c-branded preparation matches the current exam without checking topics.

Prep resources

← More Oracle exams