Oracle 1Z0-071 common mistakes
Oracle 1Z0-071 · Understand the traps and how to avoid them.
Assuming NULL behaves like zero or empty string in comparisons and arithmetic, so a candidate writes WHERE commission = NULL or expects bonus + NULL to return bonus.
In Oracle, NULL is unset rather than a value: any comparison with NULL yields UNKNOWN, and any arithmetic expression containing NULL evaluates to NULL. Candidates coming from procedural languages or from databases with different NULL semantics carry the intuition that NULL is a value that can be compared or added.
What to do instead
Use IS NULL / IS NOT NULL for null tests, never = NULL. Remember that a WHERE clause keeps only rows where the predicate is TRUE, so UNKNOWN rows are filtered out. When arithmetic must survive NULLs, wrap columns in NVL or COALESCE, e.g. NVL(commission_pct,0)salary. Also recall that NULL sorts last in ascending order by default (NULLS FIRST in descending) and that COUNT(col) skips NULLs while COUNT() does not.
Building a multi-table query with comma-separated tables in the FROM clause and putting the join condition in WHERE, then producing accidental Cartesian products or losing rows when an outer join is needed.
Older-style implicit joins look syntactically simple and candidates reuse them out of habit. When they add a second predicate or forget one join condition, the row count multiplies silently. They also cannot express outer joins cleanly, so rows with no match disappear.
What to do instead
Write explicit ANSI joins: FROM a JOIN b ON a.id = b.id, and LEFT OUTER JOIN when unmatched rows on the preserved side must survive. Count join conditions: with N tables you need at least N-1 join predicates. If a query returns far more rows than the largest table, suspect a missing predicate. For outer joins, put any filter on the optional table in the ON clause, not in WHERE, or it will silently convert the outer join into an inner join.
Using a group function in the WHERE clause, or selecting a non-aggregated column that is not in GROUP BY, and expecting the statement to run.
Candidates confuse row filtering with group filtering. WHERE is evaluated before grouping, so aggregate functions are not yet available there. Similarly, once GROUP BY is present every selected expression must be either an aggregate or a grouping key.
What to do instead
Filter individual rows in WHERE and filter aggregated results in HAVING, e.g. SELECT dept_id, AVG(salary) FROM emp GROUP BY dept_id HAVING AVG(salary) > 5000. Ensure every non-aggregate item in SELECT and ORDER BY appears in GROUP BY. Remember HAVING without GROUP BY treats the whole table as one group, and that group functions ignore NULLs except COUNT(*).
Mixing single-row and group functions in one expression, such as SELECT ename, MAX(salary) FROM emp, or nesting group functions like MAX(AVG(salary)) without a GROUP BY, and expecting per-row output.
The candidate wants each employee's name alongside a maximum, but an aggregate collapses the whole result set into one row, so a bare column has no defined value. Nested group functions are legal only when the inner aggregate is grouped, which is easy to miss.
What to do instead
If you need per-row values plus an aggregate, use an analytic function such as MAX(salary) OVER (PARTITION BY dept_id) or a subquery in the SELECT list. Nested aggregates are allowed only in the form MAX(AVG(salary)) when the query has GROUP BY dept_id, because the inner aggregate produces one value per group. Check that every column in the SELECT list is either aggregated, grouped, or wrapped in an analytic function.
Applying TO_CHAR or TO_DATE with a format model that does not match the data, or relying on implicit conversion, so comparisons and sorts give wrong results or ORA-01861 errors.
Oracle converts strings to dates and numbers using the session's NLS settings, so code that works on one session fails on another. Candidates also forget that TO_CHAR on a date needs a format model to control output, and that a two-digit year is resolved by RR rules, not simply as 20xx.
What to do instead
Always pass an explicit format model: TO_DATE('2024-03-15','YYYY-MM-DD') and TO_CHAR(hire_date,'DD-MON-YYYY'). For literal dates use DATE '2024-03-15' or ANSI timestamp literals. Remember that TO_CHAR on a number with a format model returns a string, so numeric sorting changes to character sorting. Use RR only when you understand the century rule and prefer YYYY in new code.
Forgetting that a subquery returning more than one row breaks = and > comparisons, or writing a correlated subquery that references an outer column incorrectly, and getting ORA-01427 or unexpected results.
Single-row comparison operators require exactly one row from the subquery. Candidates test with small data where the subquery happens to return one row, then the statement fails in production. Correlated subqueries also invite alias mistakes where the inner query silently binds to the outer table.
What to do instead
Use IN, ANY, or ALL when the subquery can return multiple rows, or add a predicate that guarantees one row. For correlated subqueries, qualify every column with a table alias so the correlation is explicit. Remember that NOT IN with a NULL in the subquery result returns no rows at all; use NOT EXISTS instead. Test with data that includes multiple matches and NULLs.
Assuming UNION, INTERSECT, and MINUS preserve duplicates or ordering, or putting ORDER BY inside a component query of a set operation.
Set operators work on whole row sets, and UNION already removes duplicates, so candidates either lose rows they expected or add DISTINCT unnecessarily. They also expect the output order to follow the first query, which is not guaranteed without ORDER BY.
What to do instead
Use UNION ALL when duplicates must be kept and performance matters; use UNION only when deduplication is required. INTERSECT and MINUS always return distinct rows. Place a single ORDER BY at the very end of the compound query and refer to columns by position or by the first query's aliases. Ensure both queries have the same number of columns with compatible data types.
Treating DML and DDL as equally transactional, expecting a ROLLBACK to undo a TRUNCATE or a CREATE TABLE, or forgetting to COMMIT so changes are invisible to other sessions.
In Oracle, DDL statements issue an implicit commit before and after execution, so they cannot be rolled back. Candidates also assume a client disconnect commits work, when in fact an abnormal disconnect rolls back uncommitted DML.
What to do instead
Remember that TRUNCATE, CREATE, ALTER, DROP, and GRANT all commit implicitly. To remove rows transactionally, use DELETE followed by ROLLBACK if needed. Explicitly COMMIT or ROLLBACK before ending a session. When a table must be emptied and the space released, TRUNCATE is appropriate precisely because it is not rollback-able, so confirm intent first.
Creating a view with a GROUP BY, DISTINCT, or group function and then trying to INSERT or UPDATE through it, or expecting a simple view to store data.
A view is a stored query, not a copy of data. It is only updatable when it maps rows one-to-one to a single base table and contains no DISTINCT, group functions, GROUP BY, or set operators. Candidates also forget that a view without WITH CHECK OPTION accepts rows it cannot later see.
What to do instead
For read-only views containing aggregates, use them only for SELECT. To make a view updatable, base it on one table with no aggregation and no DISTINCT, or use an INSTEAD OF trigger. Add WITH CHECK OPTION to reject rows that would fall outside the view's WHERE clause. Remember that dropping the base table leaves the view invalid, and use CREATE OR REPLACE VIEW to change a definition without dropping dependent grants.
Granting privileges with the wrong scope or forgetting WITH GRANT OPTION, so a user can query a table but cannot pass the privilege on, or a role cannot be granted to another user as expected.
Object privileges and system privileges follow different rules, and roles cannot be granted WITH ADMIN OPTION through another role. Candidates also assume that granting a role gives the grantee the ability to grant that role onward, which it does not unless WITH ADMIN OPTION was specified at creation of the grant.
What to do instead
Use GRANT SELECT ON schema.table TO user WITH GRANT OPTION for object privileges that must be propagated, and GRANT role TO user WITH ADMIN OPTION for roles. Remember that privileges granted through a role are not available inside definer's-rights PL/SQL, so direct grants may be needed. Revoke with REVOKE ... FROM, and check effective privileges through the data dictionary views such as USER_TAB_PRIVS and SESSION_ROLES.