What 1Z0-071 actually proves

The 1Z0-071: Oracle Database 12c SQL exam validates that you can write, read, and troubleshoot SQL in an Oracle 12c environment. This is not a database administration exam. It is also not a generic ANSI SQL test. Oracle expects you to know Oracle-specific syntax, function behavior, data dictionary usage, transaction control, and query-writing details that trip up people who only learned SQL from MySQL, PostgreSQL, or SQL Server.

If you work as a junior DBA, developer, report writer, data analyst, ETL engineer, or support engineer touching Oracle databases, this exam is relevant. It is often the SQL foundation exam people use before moving deeper into Oracle tracks.

What to expect:

Multiple-choice format delivered through Oracle's testing channels
Questions that mix syntax recognition, result prediction, and best-choice SQL construction
Heavy focus on writing valid Oracle SQL rather than memorizing architecture
Plenty of distractors based on tiny syntax mistakes: alias placement, order of execution, grouping rules, outer join behavior, date formatting, and transaction outcomes

You should go in expecting the exam to test these broad skill areas:

Retrieving data with SELECT
Restricting and sorting results
Using single-row and group functions
Joining tables and writing subqueries
Using set operators
Manipulating data with INSERT, UPDATE, DELETE, MERGE-style concepts and transaction control
Creating and managing schema objects with DDL
Understanding constraints, views, sequences, synonyms, indexes
Working with Oracle date, number, character, and conversion functions

The exam is passable if you practice by typing SQL. It is much harder if you only read notes.

Build the core query muscle first: SELECT, filtering, sorting, expressions

A large part of 1Z0-071 sits on your ability to write clean retrieval queries. This is the foundation. If this section is shaky, everything else falls apart.

Focus first on the mechanics of a single-table SELECT:

SELECT, FROM, WHERE, ORDER BY
Column aliases and when quotes matter
Arithmetic expressions in the select list
Operator precedence
DISTINCT
Handling NULL
Restricting rows with comparison operators, BETWEEN, IN, LIKE, IS NULL
Sorting by column name, alias, position, and mixed direction

You need to know Oracle behavior, not just general SQL theory. Common exam traps:

Aliases are not available in the WHERE clause because of SQL processing order.
NULL is not equal to anything, including another NULL.
LIKE with % and _ behaves differently than many candidates remember under pressure.
ORDER BY can use aliases, but grouping and filtering rules are stricter.

Be comfortable with expressions like:

SELECT last_name,
       salary,
       salary * 12 AS annual_salary
FROM employees
WHERE department_id IN (10, 20)
ORDER BY annual_salary DESC;

Then move into Oracle's single-row functions, because the exam asks both what they do and what output they produce.

Know these categories cold:

Character functions: UPPER, LOWER, INITCAP, CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, REPLACE
Number functions: ROUND, TRUNC, MOD
Date functions: SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND(date), TRUNC(date)
Conversion functions: TO_CHAR, TO_NUMBER, TO_DATE
General functions: NVL, NVL2, NULLIF, COALESCE

The Oracle exam loves implicit vs explicit conversion. Do not rely on assumptions. Learn what valid code looks like:

SELECT last_name,
       TO_CHAR(hire_date, 'DD-MON-YYYY') AS hire_dt,
       NVL(commission_pct, 0) AS comm
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') = '2015';

Also know why some code is risky even if it might run in one environment. Oracle wants you to prefer explicit conversions, especially with dates.

A separate but related chunk is conditional logic and null handling:

CASE expressions
Difference between NVL and COALESCE
How nulls affect arithmetic and concatenation

Example worth practicing:

SELECT employee_id,
       salary,
       CASE
         WHEN salary < 5000 THEN 'LOW'
         WHEN salary < 10000 THEN 'MID'
         ELSE 'HIGH'
       END AS salary_band
FROM employees;

If you are early in your prep, spend more time here than you think you need. These are the marks you should not lose.

Where most candidates leak points: joins, group results, subqueries, set operators

This is the heart of the exam. Oracle uses these topics to separate people who can write SQL from people who only recognize keywords.

Start with joins. You need to understand both syntax and result shape.

Study these thoroughly:

Inner joins
Equijoins and nonequijoins
Self joins
Outer joins: left, right, full
Oracle join syntax using JOIN ... ON
Older Oracle outer join operator (+ ) may still appear in legacy-style questions, so recognize it even if you would not use it now
Cartesian products and how they happen

Practice reading joins, not just writing them. Many exam items ask what rows are returned when a predicate is moved from WHERE to ON.

Example:

SELECT e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
  ON e.department_id = d.department_id;

Then master aggregation and grouping. Oracle will test both syntax validity and logic.

You must know:

AVG, SUM, MIN, MAX, COUNT
Difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)
GROUP BY
HAVING
Rules for grouped queries: every selected non-aggregated column must appear in GROUP BY
Nesting group functions is restricted

Classic exam pattern: Oracle shows a query and asks whether it errors, and if so, why.

Example:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Subqueries deserve their own disciplined practice block. Know the difference between:

Single-row subqueries using =, <, >
Multiple-row subqueries using IN, ANY, ALL
Correlated subqueries
Subqueries in WHERE, FROM, and HAVING
Null-related issues with NOT IN

That last point matters. A lot. If the subquery can return NULL, NOT IN can produce unintuitive results. Oracle likes this trap because it exposes shallow understanding.

Example:

SELECT last_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

And a correlated pattern:

SELECT e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Now add set operators:

UNION
UNION ALL
INTERSECT
MINUS

Know the rules:

Number of columns must match
Data types must be compatible by position
ORDER BY belongs at the end of the full compound query
UNION removes duplicates; UNION ALL does not

Finally, review the order in which you should study these topics:

Inner and outer joins
Group functions and GROUP BY
Simple subqueries
Correlated subqueries
Set operators
Edge cases with NULL, NOT IN, and outer join filtering

If you can reliably explain why a query returns a given row set, you are on track.

The schema-object domain: DML, transactions, tables, constraints, indexes, views, sequences

This section is where people underestimate the exam. They think it is just query writing, then get hit with object-management questions and transaction behavior.

You need working knowledge of data manipulation language:

INSERT with full and partial column lists
INSERT using substitution from another table with INSERT INTO ... SELECT
UPDATE
DELETE
Transaction control: COMMIT, ROLLBACK, SAVEPOINT

Be clear on when data changes become permanent and what can be rolled back. Oracle expects you to know that DDL issues an implicit commit before and after execution. That matters in scenario questions.

Examples to practice:

INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (280, 'Training', 205, 1700);

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 60;

ROLLBACK;

Then move into DDL and schema objects:

CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE
Adding, modifying, and dropping columns
Setting columns DEFAULT
Understanding datatype basics: VARCHAR2, CHAR, NUMBER, DATE

Constraints are a favorite exam target because Oracle can test syntax, semantics, and consequences.

Know these precisely:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

And know how constraints can be:

Defined at column level or table level
Named explicitly
Enabled or disabled in some administrative contexts

You should be able to read statements like:

CREATE TABLE projects (
  project_id NUMBER CONSTRAINT projects_pk PRIMARY KEY,
  project_name VARCHAR2(100) CONSTRAINT projects_name_nn NOT NULL,
  department_id NUMBER,
  CONSTRAINT projects_dept_fk FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

Also cover these schema objects:

Views: purpose, simple view behavior, DML restrictions through views, WITH CHECK OPTION, WITH READ ONLY
Sequences: NEXTVAL, CURRVAL, caching basics, using sequences in inserts
Synonyms: private vs public, object access abstraction
Indexes: what they do conceptually and why they improve retrieval, without expecting deep optimizer internals

Typical sequence usage:

CREATE SEQUENCE dept_seq
START WITH 300
INCREMENT BY 10;

INSERT INTO departments (department_id, department_name)
VALUES (dept_seq.NEXTVAL, 'Analytics');

Do not ignore data dictionary awareness. You may see references to user metadata views such as:

USER_TABLES
USER_OBJECTS
USER_CONSTRAINTS
USER_SEQUENCES

You are not being tested as a DBA here, but you should recognize where object information comes from.

One more thing: know the difference between DELETE and TRUNCATE in terms of rollback, speed, and transaction behavior. This comes up often because it is simple but easy to confuse under time pressure.

Oracle-specific weak spots to drill until they stop hurting

These are the topics I see candidates miss even after they have done plenty of SQL elsewhere.

1. Date format models

Oracle date handling is unforgiving if you depend on defaults. Learn format masks well enough to spot bad code immediately.

DD-MON-YYYY
YYYY-MM-DD
HH24:MI:SS
Difference between storing a DATE and formatting it for display
Why TO_DATE is for character-to-date conversion and TO_CHAR is for date-to-character output

2. Implicit conversion

Oracle may perform it, but exam questions often punish assumptions. Prefer explicit conversion and learn when comparison between strings and numbers can lead to errors.

3. Null semantics

NVL vs NVL2 vs COALESCE
COUNT(column) ignores nulls
NOT IN with nulls can eliminate all rows unexpectedly
Arithmetic with null returns null

4. Outer join filtering

Candidates often write a left outer join and then place a filter in WHERE that effectively turns it into an inner join. You need to see this instantly.

5. Grouping rules

This is less about creativity and more about discipline. If a selected column is neither aggregated nor grouped, the query is invalid.

6. Transaction side effects of DDL

Oracle commits around DDL. If you forget that, you will miss scenario questions.

7. Legacy Oracle syntax recognition

Even if your daily work uses ANSI joins, be prepared to recognize older Oracle syntax in answer choices.

A productive way to study these is to keep a one-page error log with three columns:

Topic
Wrong assumption I made
Correct Oracle rule

That log becomes far more useful than generic notes.

A realistic prep plan based on your background

This exam is narrow enough to prepare for efficiently, but only if you spend most of your time in SQL*Plus, SQL Developer, Live SQL, or another Oracle-capable environment.

Here is a realistic timeline.

If you already write SQL weekly, but not much Oracle-specific SQL: 3-4 weeks

Week 1:

Review single-table queries, filtering, sorting, aliases, expressions
Drill character, number, date, conversion, and null-handling functions
Practice 40-60 questions only on these topics

Week 2:

Joins, group functions, GROUP BY, HAVING
Write at least 25 join queries and 20 aggregation queries
Start a mistake log

Week 3:

Subqueries, correlated subqueries, set operators
DML, TCL, DDL, constraints, views, sequences, synonyms, indexes
Do mixed practice sets under time pressure

Week 4:

Full review of weak spots
2-3 full practice exams
Rebuild from memory: syntax for creating tables, constraints, views, sequences

If you know SQL basics but are inconsistent: 6-8 weeks

Spend the first two weeks only on SELECT, WHERE, functions, and sorting
Weeks 3-4 on joins, grouping, and subqueries
Weeks 5-6 on DML, DDL, constraints, and schema objects
Final 1-2 weeks on timed practice and patching weaknesses

If you are new to SQL: 10-12 weeks

This is still achievable, but you need repetition.

Weeks 1-3: single-table retrieval and functions
Weeks 4-6: joins and aggregations
Weeks 7-8: subqueries and set operators
Weeks 9-10: DML, DDL, transactions, constraints, views, sequences
Weeks 11-12: exam drills and memorization of Oracle syntax rules

A good weekly rhythm:

2 days learning and note-making
3 days writing SQL by hand in a lab
1 day doing timed questions
1 day reviewing every wrong answer and reproducing it in Oracle

With 272 practice questions available on Truekia, do not just burn through them once. Split them into passes:

Pass 1: untimed, topic-based
Pass 2: mixed and timed
Pass 3: only previously missed questions plus fresh random sets

The point is not to memorize answers. The point is to identify which Oracle rules you still do not apply automatically.

Resources that are actually worth your time

Use a small set of strong resources and work them hard.

Official Oracle resources

Oracle Database SQL Language Reference 12c Release 1 (12.1) — this is the most important source. Use it for exact syntax and Oracle-specific behavior.
Oracle Database 2 Day Developer's Guide — useful for context and examples if you need a gentler ramp.
Oracle Live SQL — excellent for quick hands-on practice without local setup.
Oracle documentation on SQL Functions, Conditions, and Operators — especially useful for function behavior and edge cases.

Books

Oracle Database 12c SQL by Jason Price — practical and aligned with the kind of SQL knowledge the exam expects.
OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) by Steve O'Hearn — one of the few books written specifically for this exam objective set.

Video courses

Oracle University offerings for Database SQL if you have access through work
Udemy courses specifically mapped to 1Z0-071 can help, but use them as secondary material. Verify syntax against Oracle docs.

Practice environment

Best options:

Oracle Live SQL for immediate browser-based practice
Oracle Database Express Edition (XE) if you want a local environment
Oracle SQL Developer as your client tool for writing and saving queries

What to build in your lab

Do not just query the sample HR schema. Also create your own mini-schema with:

4-6 related tables
Primary and foreign keys
A sequence
A view with WITH READ ONLY or WITH CHECK OPTION
Insert, update, delete, commit, rollback scenarios

That gives you direct exposure to the exam's object and transaction topics.

How to use practice questions well

For every missed question:

Recreate the SQL in your lab
Observe the actual Oracle result or error
Write down the rule in one sentence
Solve two more questions on the same objective

That process is slower. It is also why people pass.

How to handle the exam interface and avoid preventable mistakes

Oracle exam questions are often less about obscure knowledge than about catching rushed reading.

On exam day:

Read every SQL statement from SELECT to final clause before looking at answer choices.
Check whether the question asks for valid syntax, actual output, or number of rows returned. Those are different tasks.
Watch for hidden killers: NULL, duplicate elimination, missing GROUP BY, implicit conversion, and predicate placement in outer joins.
If two answers look right, inspect Oracle syntax exactness. One keyword in the wrong place is enough to make an answer invalid.
Do not overthink straightforward DML or function questions. Save time for subqueries and grouped-query validation.

A few tactical habits help:

For grouped queries, immediately ask: Are all non-aggregated selected columns grouped?
For subqueries, ask: Single-row or multiple-row return? Is the comparison operator legal?
For date questions, ask: Is this value being stored as a date or displayed as text?
For transactions, ask: Did a DDL statement force a commit?

If you are unsure, eliminate answers that violate basic Oracle rules first. That often narrows a four-option question to two.

Last point: do not sit the exam until you can do three things without hesitation:

Write joins and subqueries from memory
Predict the result of common functions and null-handling expressions
Explain transaction outcomes after COMMIT, ROLLBACK, SAVEPOINT, DELETE, and TRUNCATE

If you can do that, plus score consistently on timed practice, you are ready for oracle-1z0-071.