Oracle 1Z0-071 Exam Guide: Syllabus, Passing Score & How to Prepare

The Oracle Database SQL Certified Associate (1Z0-071) is Oracle's foundational SQL certification and, for most people, the first Oracle credential worth earning. This guide covers exactly what is on the exam, the 63% passing score, the cost and format, a realistic week-by-week study plan, and how to prepare for free with SQLMentor's tutorials and timed practice exams.

If you already know you want to practise, jump straight to the free 1Z0-071 practice exams — three full-length, timed sets with per-question explanations. Otherwise, read on for the full breakdown.

Exam at a glance

• Exam number: 1Z0-071
• Title: Oracle Database SQL Certified Associate
• Questions: 63 multiple choice
• Duration: 120 minutes (just under 2 minutes per question)
• Passing score: 63% (roughly 40 of 63 correct)
• Delivery: Pearson VUE test centre or Oracle Online Proctoring
• Cost: around US$245 (varies by region — confirm on Oracle University)
• Format: closed book, no reference material allowed

Exam fees, booking options, and any renewal policy can change, so always confirm the current details on the official Oracle University 1Z0-071 page ↗ before you book. SQLMentor is an independent study resource and is not affiliated with Oracle.

What the 1Z0-071 certifies

Passing 1Z0-071 shows that you can read, write, and reason about Oracle SQL — not just recite syntax. It validates practical skills that come up daily in development and analytics roles: filtering and sorting result sets, joining multiple tables, nesting subqueries, aggregating with group functions, modifying data with DML, and shaping schema objects with DDL. Because it focuses on the SQL language itself (rather than administration), it is relevant whether you are a developer, data analyst, report writer, QA engineer, or a DBA-in-training.

Is the 1Z0-071 worth it?

For anyone working with Oracle databases, it is one of the more respected entry-level certifications — largely because it is genuinely hard to pass by memorising answers. The questions test whether you understand how SQL behaves (NULL handling, implicit conversions, join semantics, subquery scope), which is exactly the knowledge that transfers to the job. It is also the natural stepping stone before the 1Z0-082 (Database Administration) or 1Z0-149 (PL/SQL) exams. Whether the fee is worth it depends on your role and region, but the underlying skills it forces you to build are valuable regardless of the certificate.

Full syllabus breakdown

The 1Z0-071 blueprint spans the whole core of the SQL language. The topic groups below are ordered roughly the way SQLMentor's Oracle SQL tutorial teaches them, so you can study each area and immediately practise it in the editor.

1. Retrieving and restricting data

SELECT fundamentals, projecting and aliasing columns, the WHERE clause, comparison and logical operators, BETWEEN, IN, LIKE, IS NULL, and sorting with ORDER BY. This is the most heavily represented area on the exam — get it airtight. Study: basic SQL and filtering.

2. Single-row and conversion functions

Character, number, and date functions; NVL, NVL2, COALESCE, and NULLIF; explicit conversion with TO_CHAR, TO_DATE, and TO_NUMBER; and conditional logic with CASE and DECODE. Expect questions that hinge on implicit data-type conversion and NULL propagation.

3. Aggregating data with group functions

COUNT, SUM, AVG, MIN, MAX; GROUP BY and the difference between WHERE and HAVING; and how NULLs behave inside aggregates. Study aggregate functions.

4. Joins and subqueries

Inner joins, outer joins (both ANSI LEFT/RIGHT/FULL JOIN and the Oracle (+) syntax), self-joins, and cross joins; then single-row, multi-row, and correlated subqueries plus EXISTS/NOT EXISTS. This pairing is where most scenario questions live. Study joins and subqueries.

5. Set operators

UNION, UNION ALL, INTERSECT, and MINUS, including column-count/type matching rules and how ORDER BY applies to a compound query. Study set operators.

6. DML and transaction control

INSERT, UPDATE, DELETE, MERGE, and multi-table inserts; plus COMMIT, ROLLBACK, SAVEPOINT, and read consistency. Study DML and transactions & locks.

7. DDL, constraints, and schema objects

Creating and altering tables, Oracle data types, the five constraint types (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK), plus views, sequences, synonyms, and indexes. Study DDL, constraints, views, and sequences & synonyms.

8. Data dictionary and privileges

Querying dictionary views such as USER_TABLES and USER_CONSTRAINTS, and controlling access with GRANT and REVOKE. Study the data dictionary.

Oracle does not publish exact percentage weightings per topic, so treat the ordering above as relative emphasis rather than a fixed mark split. Retrieving/restricting data, functions, joins, and subqueries together make up the bulk of the questions.

A realistic 6-week study plan

This plan assumes a few hours a week. Compress it if you already write SQL daily; stretch it to eight weeks if the language is new.

• Weeks 1–2 — foundations: Work through the Oracle SQL tutorial from SELECT through joins. After each topic, rewrite every example yourself in the in-browser SQL editor against the HR schema. Do not copy-paste — typing builds recall.
• Weeks 3–4 — depth: Cover subqueries, set operators, DML, DDL, constraints, views, sequences, and the data dictionary. Focus on the behaviour that trips people up: NULLs in aggregates and comparisons, implicit conversions, and outer-join semantics.
• Week 5 — first timed run: Take Practice Set 1 under full timing without pausing. Accept the score, then read the explanation for every question you missed and re-study that topic.
• Week 6 — polish: Take Sets 2 and 3 at least a day apart. You are ready when you consistently score 73% or higher — a ~10% cushion above the 63% cut line to absorb exam-day nerves.

Common reasons candidates fail (and how to avoid them)

• Running out of time. Under two minutes per question is tight. Flag anything that takes more than 90 seconds and move on — a reviewed guess beats an unfinished exam.
• NULL surprises. NULL = NULL is not true, aggregates skip NULLs, and NOT IN with a NULL in the list returns no rows. These appear repeatedly.
• Confusing WHERE and HAVING. WHERE filters rows before grouping; HAVING filters groups after. Mixing them up is a classic trap.
• Memorising instead of understanding. The exam rewards knowing how SQL behaves. Running each construct yourself in the editor is the fastest way to make that behaviour stick.