Oracle 1Z0-071 (Oracle Database SQL): Study Notes and the Traps That Cost Marks

These are the ExamCert team's working notes for the Oracle Database SQL Certified Associate exam, code 1Z0-071. It is one of the few vendor exams that tests whether you can predict what a query will actually return, row by row, rather than whether you remember a product menu. People who write SQL every day still fail it, usually because Oracle's behaviour differs from what they're used to in PostgreSQL, MySQL or SQL Server.

Exam format at a glance

You'll still see the old "78 questions" figure around the web. It comes from the retired 12c SQL Fundamentals listing, so don't plan your timing around it. Full topic breakdown and the current exam facts: https://www.examcert.app/exams/oracle-1z0-071/

The topic groups, in the order worth studying

1. SELECT basics: aliases, concatenation with ||, the q'[...]' alternative quote operator, DISTINCT, and arithmetic with NULL (anything plus NULL is NULL).
2. Restricting and sorting: operator precedence (NOT, then AND, then OR), BETWEEN being inclusive, LIKE with ESCAPE, FETCH FIRST n ROWS ONLY / WITH TIES, and substitution variables (& vs &&, DEFINE, VERIFY).
3. Single-row functions: character functions (SUBSTR, INSTR, LPAD, TRIM), date arithmetic (date minus date = number of days), ROUND/TRUNC on both numbers and dates, MOD.
4. Conversion and conditional expressions: NVL, NVL2, NULLIF, COALESCE, CASE and DECODE, plus TO_CHAR / TO_DATE / TO_NUMBER format models.
5. Group functions: COUNT(*) vs COUNT(column), the rule that every non-aggregated SELECT column must be in GROUP BY, and HAVING vs WHERE.
6. Joins: ANSI joins, NATURAL JOIN and USING (no table prefix on the join column), self-joins, non-equijoins, outer joins and Cartesian products.
7. Subqueries: single-row vs multiple-row operators (ANY, ALL, IN), correlated subqueries, and the NOT IN plus NULL trap.
8. SET operators: UNION vs UNION ALL, INTERSECT, MINUS, column-count and datatype-group matching, and ORDER BY only at the very end.
9. DML and transactions: INSERT, UPDATE, DELETE, MERGE, multi-table INSERT, COMMIT, ROLLBACK and SAVEPOINT, and implicit commits after DDL.
10. DDL, views, sequences, synonyms, indexes, constraints, the data dictionary, privileges and roles.

Seven behaviours that catch experienced developers

1. NOT IN with a NULL returns no rows. If the subquery returns even one NULL, WHERE x NOT IN (subquery) evaluates to UNKNOWN for every row. Use NOT EXISTS or filter the NULLs out.
2. Empty string is NULL in Oracle. WHERE col = '' matches nothing, and LENGTH('') returns NULL, not 0.
3. DDL commits the open transaction. A CREATE or ALTER after an uncommitted UPDATE makes that UPDATE permanent, so a later ROLLBACK can't undo it.
4. Column aliases can't be used in WHERE, but they can be used in ORDER BY. Exam options love to put an alias in WHERE or HAVING.
5. COUNT(column) ignores NULLs; COUNT(*) does not. AVG also ignores NULLs, so AVG(commission) is not the same as SUM(commission)/COUNT(*).
6. NATURAL JOIN and USING forbid a table qualifier on the join column. e.department_id in the SELECT list is an error there.
7. ROUND on dates rounds to the nearest day by default, and TRUNC on SYSDATE removes the time portion. Questions often hinge on whether noon has passed.

A four-week plan that works around a full-time job

• Week 1: SELECT, restricting, sorting and single-row functions. Run every example yourself in a free Oracle environment (Oracle Live SQL or a local Free container). Reading isn't enough.
• Week 2: conversion functions, group functions, joins. Write each join type against the HR sample schema and predict the row count before you run it.
• Week 3: subqueries, SET operators, DML, transactions. This is where most of the "what happens next" questions come from.
• Week 4: schema objects, the data dictionary, privileges, then timed practice. Aim for a steady 85% or better on mixed sets before you book.

For timed practice, the free 1Z0-071 question set covers all 16 topic groups with an explanation for every answer: https://www.examcert.app/exams/oracle-1z0-071/free-practice-test/

Exam-day technique

With 63 questions in 120 minutes you have just under two minutes per question, which is plenty if you don't trace every query in your head twice. On a first pass, answer the ones you can see at a glance and flag anything with a long multi-table query. Multiple-select questions tell you how many answers to choose, so use that count to rule options out.