What the 1z0-071 actually feels like under the clock

This exam is mostly straightforward multiple-choice, but the pressure comes from how Oracle phrases SQL validity questions. Expect roughly 63 questions in 120 minutes, which puts you at just under 2 minutes per question. That sounds generous until you hit the items where you have to mentally execute a query, remember Oracle-specific function behavior, and spot one tiny syntax violation.

A practical pacing rule for this exam: if a question requires you to simulate row output across joins, grouping, and NULL handling, it will eat time fast. I treated the exam as three passes:

Pass 1: answer syntax/definition questions immediately: data types, DDL effects, single-row functions, privileges, transaction basics.
Pass 2: work the medium questions: joins, group functions, subqueries, set operators.
Pass 3: spend the remaining time on output-prediction questions involving ROWNUM, date arithmetic, correlated subqueries, and outer joins.

You can usually mark and return, so use that for long query-evaluation items. The trap is not difficult wording; it is Oracle giving you four SQL statements that look almost identical, where only one respects Oracle's exact rules. On this exam, speed comes from recognizing patterns, not from reading faster.

One more exam-structure reality: Oracle likes asking "Which three statements are true?" or "Which two queries execute successfully?" These are riskier than single-answer items because one misunderstood rule ruins the whole selection. For those, do not judge by what seems logically correct in SQL generally; judge by what Oracle 12c specifically allows.

Put most of your effort into the domains Oracle keeps recycling

If your study time is limited, push hardest on the areas that dominate both the blueprint and the actual feel of the test: retrieving data with joins and subqueries, using single-row and group functions, restricting and sorting output, and DDL/DML/transaction behavior. The exam is called "Database 12c SQL," but in practice it is heavily centered on writing and validating SQL statements, not architecture.

The topics that paid off most for me were:

SELECT statement construction
- column aliases, expression output, concatenation, operator precedence
- DISTINCT, ORDER BY, sorting by alias vs position
- WHERE clause logic with AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL
Joins
- inner joins, self joins, outer joins, cross joins
- ANSI join syntax versus older Oracle join notation where it appears in legacy-style questions
Single-row functions
- character, number, date, conversion, and general functions like NVL, NVL2, NULLIF, COALESCE
Group functions and grouping rules
- COUNT(*) vs COUNT(column)
- HAVING vs WHERE
- valid GROUP BY expressions and alias misuse
Subqueries
- single-row, multiple-row, correlated subqueries
- EXISTS, IN, ANY, ALL
Set operators
- UNION, UNION ALL, INTERSECT, MINUS
- column count/type compatibility and ORDER BY placement
DML/DDL/TCL
- INSERT, UPDATE, DELETE, MERGE
- implicit commit behavior around DDL
- savepoints, rollback, transaction visibility
Schema objects and constraints
- primary key, foreign key, unique, check, not null
- sequence behavior and index basics

Topics that sound important but did not feel as dominant as the above: deep object privileges theory, advanced database administration, or anything beyond core SQL language behavior. If you are deciding between memorizing obscure privilege combinations and mastering correlated subqueries plus date functions, pick the SQL behavior every time.

The exam loves queries that are legal in your head but illegal in Oracle

A huge share of 1z0-071 is Oracle testing whether you know the exact grammar. Many wrong answers are not wrong because they return the wrong rows; they are wrong because they won't compile in Oracle.

The patterns I saw repeatedly:

Alias misuse in WHERE
- Oracle does not let you use a SELECT-list alias in the WHERE clause of the same query block.
- It can be used in ORDER BY.
Group function placement
- You cannot put AVG(salary) directly in WHERE unless it is inside a subquery.
- Aggregate filters belong in HAVING.
Mixing grouped and non-grouped columns
- If a selected column is neither aggregated nor listed in GROUP BY, the statement is invalid.
Set operator ordering mistakes
- ORDER BY belongs at the end of the full compound query, not before each branch.
Subquery row-count mismatch
- = with a subquery that returns multiple rows is invalid at runtime.
- IN, ANY, or ALL may be required instead.
Data type conversion assumptions
- Oracle may perform implicit conversion, but exam questions often punish you for relying on it blindly.

A very Oracle-specific gotcha is the difference between empty string and NULL in Oracle character data handling. If a question hints at empty strings in VARCHAR2, remember Oracle treats empty string as NULL. That changes comparisons, NVL outcomes, and IS NULL logic.

Another recurring trap is operator precedence. Oracle will happily let AND bind tighter than OR, and exam writers know candidates mentally add parentheses that are not there. If a filter combines AND and OR, rewrite it mentally with precedence before deciding the result set.

When a question asks "which statement executes successfully," assume at least two options are there purely to exploit one Oracle syntax rule. Do not overthink the business meaning of the query until you have validated the syntax.

Joins, subqueries, and row filtering: where most scores are won or lost

This is the core of the exam. Oracle loves business-style scenarios like employees/managers/departments/customers/orders, then tests whether you know the right join or subquery shape.

For joins, be especially sharp on these:

Self joins for employee-manager style questions. If the same table appears twice, keep aliases straight and decide which alias supplies employee data and which supplies manager data.
Outer joins where Oracle asks for rows with missing related data. If the requirement says "include departments with no employees" or "show products even if never ordered," you need outer join logic, not a normal join.
Cross joins and accidental Cartesian products. Oracle often includes one option missing a join condition; if it executes, it may still be semantically wrong because it multiplies rows.

For subqueries, know the behavioral differences cold:

IN checks membership in a set.
EXISTS checks whether at least one related row exists; Oracle often uses this in correlated subquery scenarios.
ANY and ALL are easy to neglect, but they appear because candidates mix up their comparison semantics.
A correlated subquery runs conceptually per outer row; Oracle uses these in questions asking for employees who earn above their department average or products priced above category average.

The classic high-value pattern is something like:

"Find employees whose salary is greater than the average salary of their department."

That is usually testing whether you can distinguish:

a global average subquery, which is wrong,
a correlated departmental average subquery, which is right,
and a grouped query with invalid placement of aggregate logic.

Another pattern Oracle likes is asking for rows with no match. Candidates often write NOT IN against a subquery without thinking about NULL. If the subquery can return NULL, NOT IN becomes dangerous because it can eliminate everything. If you see nullable foreign keys or nullable columns in the subquery output, consider whether Oracle is steering you toward NOT EXISTS instead.

Also watch for ROWNUM questions. Oracle uses them to test execution order. ROWNUM is assigned before final sorting unless the sort is done in an inline view first. So if an option says "top 5 highest salaries" and uses WHERE ROWNUM <= 5 ORDER BY salary DESC in one query block, that is the classic wrong pattern.

Functions and dates: the section people underestimate

A lot of candidates treat functions as easy points. That is a mistake on 1z0-071 because Oracle turns them into edge-case questions.

The most exam-relevant function families are:

Character functions: UPPER, LOWER, INITCAP, CONCAT, SUBSTR, INSTR, LENGTH, LPAD, RPAD, TRIM, REPLACE
Number functions: ROUND, TRUNC, MOD
Date functions: SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, date arithmetic
Conversion functions: TO_CHAR, TO_DATE, TO_NUMBER
NULL-related/general functions: NVL, NVL2, COALESCE, NULLIF, CASE

The traps are specific:

ROUND vs TRUNC on numbers and dates. Oracle expects you to know which one removes fractional detail and which one rounds.
Date arithmetic returns days when subtracting one date from another. Questions sometimes hide this by asking for elapsed time and giving answers in months or days.
Format models in TO_CHAR and TO_DATE are a favorite source of wrong answers. If the literal and format mask do not match, the statement is invalid.
Implicit conversion can make an answer look valid, but Oracle may still be testing best-practice-valid syntax versus fragile syntax. Prefer explicit conversion when the answer choices differ there.
NVL data type compatibility matters. Oracle converts types, but some combinations are not interchangeable the way candidates assume.

Do not just memorize what a function does; practice predicting exact output. Oracle will ask what a query returns after nested functions, especially with dates or NULL values. If you cannot mentally evaluate NVL(TO_CHAR(commission_pct), 'Not Available') or date expressions with ADD_MONTHS and LAST_DAY, you will burn time.

Grouping, set operators, and DML: the quiet source of avoidable misses

Three areas produce a lot of careless losses because they look simpler than joins.

First, grouping. Oracle is strict here.

WHERE filters rows before grouping.
HAVING filters groups after grouping.
COUNT(*) counts rows, including rows where particular columns are NULL.
COUNT(column) ignores NULL in that column.

Oracle likes to combine these rules in one item. Example pattern: a query groups by department, counts commission values, and filters departments after aggregation. If you do not distinguish row filtering from group filtering, you pick the wrong option.

Second, set operators. The common exam patterns are:

one option fails because the two queries return different numbers of columns,
one fails because incompatible data types are aligned,
one is valid but changes duplicate handling because it uses UNION ALL instead of UNION,
one places ORDER BY in the wrong place.

Know the duplicate behavior exactly:

UNION removes duplicates.
UNION ALL keeps them.
INTERSECT keeps common rows.
MINUS returns rows from the first query not in the second.

Third, DML and transaction control. This is less glamorous but very testable.

DELETE removes rows and can be rolled back before commit.
TRUNCATE removes all rows faster, is DDL, and involves implicit commit behavior.
DROP removes the object itself.
DDL statements generally issue implicit commits before and after execution in ways exam questions expect you to know.
SAVEPOINT lets you roll back partially within a transaction.
MERGE is worth reviewing because Oracle uses it to test conditional insert/update logic in one statement.

If a question asks what data remains after a sequence of INSERT, UPDATE, SAVEPOINT, ROLLBACK TO, and COMMIT, actually trace the transaction state. Oracle exam writers love these mini-timeline questions.

Constraints, sequences, and schema objects: memorize the behavior, not just the names

This exam does not go deep into administration, but it absolutely expects you to know core schema object behavior.

On constraints, the useful distinctions are:

Primary key = unique + not null, one per table.
Unique allows uniqueness but handles nullability differently than many candidates casually assume.
Foreign key references parent key values and drives many of the join questions.
Check enforces a rule on column values.
Not null is column-level mandatory data enforcement.

Oracle may ask which constraint types can exist, which statements create them correctly, or what happens on insert/update when a constraint is violated. Study the syntax enough to recognize valid CREATE TABLE and ALTER TABLE forms.

For sequences, know the practical points:

NEXTVAL advances the sequence and returns a new value.
CURRVAL is only available in a session after NEXTVAL has been referenced.
Sequence values are not gap-free guarantees.

That CURRVAL rule is exactly the kind of detail Oracle turns into a "which statement is true" item.

For indexes and views, the exam usually stays at a conceptual SQL-user level. You do not need DBA-depth tuning theory, but you should know what an index is for, basic view behavior, and how simple DDL around these objects looks.

My last-week checklist for oracle-1z0-071

The final week should be almost entirely about Oracle-specific weak spots, not broad review. I would narrow it to a checklist like this:

Do 20-30 questions a day only on joins and subqueries
- especially correlated subqueries, outer joins, self joins, and NOT EXISTS vs NOT IN
Rebuild your function notes into one page
- include date functions, conversion functions, and NULL handling differences
Practice query validity, not just query output
- ask: does this compile in Oracle 12c SQL?
Memorize grouping rules
- every selected non-aggregate expression must be grouped
- no aggregate in WHERE unless inside subquery
Drill transaction scenarios
- DDL implicit commits, ROLLBACK, SAVEPOINT, TRUNCATE vs DELETE
Review set operators in one sitting
- duplicates, compatibility, final ORDER BY placement
Do a short timed set using the real pace
- target about 35-40 questions in 70-75 minutes to simulate the exam rhythm

Since Truekia has 272 practice questions for this cert, I would not just run them once. Split them by topic and tag every miss by rule violated. On this exam, your misses usually cluster around a handful of Oracle rules: alias scope, aggregate placement, NULL logic, subquery cardinality, and date/conversion behavior. Those clusters are what you fix.

If you only have one night left, review these in order:

joins and outer join scenarios
correlated subqueries and EXISTS
GROUP BY/HAVING
date functions and conversion masks
transaction control and DDL commit behavior
set operators

That order matches where the exam can punish you hardest for half-remembered knowledge.

Oracle testing quirks to expect on exam day

The interface itself is standard enough, but Oracle-style item writing has some quirks worth anticipating.

Multi-select questions are common enough to matter. The wording "Choose two" or "Choose three" is where Oracle often places near-duplicate SQL statements. Treat each option as true/false independently before selecting the required count.
Schema names and sample-table style scenarios are used to distract you. Focus on the SQL rule being tested, not the business story.
Output questions may omit data values and still be solvable because Oracle is testing row count, duplicate handling, null behavior, or syntax validity rather than exact content.
Some options are valid SQL but do not meet the requirement. Others are invalid SQL outright. Separate those two judgments.

My blunt advice for the final minutes: revisit only the questions where Oracle-specific rules can be decisively checked. Good candidates waste end-of-exam time second-guessing semantically plausible join answers when they should be fixing objective mistakes like alias-in-WHERE, invalid grouping, or bad set-operator syntax.

If you walk into 1z0-071 knowing Oracle's exact behavior for NULL, date functions, subquery operators, grouping rules, outer joins, and transaction/DDL effects, the exam becomes much more mechanical. If you rely on generic SQL intuition, Oracle will catch you.