Oracle 1Z0-084 common mistakes
Oracle 1Z0-084 · Understand the traps and how to avoid them.
Assuming that a WHERE clause can filter on an aggregate such as WHERE COUNT(*) > 5, or placing a group filter in WHERE instead of HAVING.
Candidates learn SELECT ... WHERE ... GROUP BY in that visual order and treat WHERE as the general filter. They forget that WHERE is evaluated before rows are grouped, so aggregate functions are not yet available and the statement raises ORA-00934. The related confusion is that HAVING can reference non-aggregated columns only if they are in GROUP BY.
What to do instead
Memorize the logical evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Use WHERE for row-level predicates and HAVING only for group-level predicates. Practise rewriting a failing query by moving the aggregate predicate from WHERE to HAVING, and test a HAVING clause on a column not in GROUP BY to see the error it produces.
Predicting NULL behaviour incorrectly: expecting WHERE col != 'X' to return rows where col IS NULL, expecting NULL = NULL to be true, or being surprised that COUNT(col) ignores NULLs while COUNT(*) does not.
Three-valued logic is unintuitive for candidates coming from procedural languages. Negated comparisons on nullable columns silently drop NULL rows, and aggregate functions treat NULL differently from each other, so query results look 'wrong' rather than erroring, which hides the misunderstanding.
What to do instead
State the rule explicitly for every nullable column: any comparison with NULL yields UNKNOWN, and only rows evaluating to TRUE are returned. Always add OR col IS NULL when a negated predicate must include nulls. Contrast COUNT(*), COUNT(col), and COUNT(DISTINCT col) on a small table containing nulls, and check NVL/COALESCE handling in the same exercise.
Using implicit joins in the WHERE clause and then adding a second condition that accidentally converts the inner join into a cross join, or forgetting the join predicate entirely and producing a Cartesian product.
The comma-separated FROM list with join predicates in WHERE is syntactically valid, so nothing warns the candidate. When several tables are involved, one missing predicate multiplies row counts, and candidates often do not notice because the query still runs and returns plausible-looking data.
What to do instead
Write ANSI JOIN syntax with the join condition in ON and filters in WHERE, so the two roles are visually separate. When reviewing a legacy comma join, count the join predicates and confirm there is one per additional table. Sanity-check row counts against a known table before trusting the output.
Misjudging outer join direction: putting the outer-joined table on the wrong side of LEFT/RIGHT JOIN, or adding a filter on the outer table in WHERE (rather than ON) and thereby discarding the null-extended rows.
Candidates reason about which table 'should have all rows' but then write the predicate in the wrong clause. A WHERE predicate on the null-supplying table evaluates UNKNOWN for the generated NULLs, so the outer join silently behaves like an inner join and the missing rows are never noticed.
What to do instead
Anchor the direction with a sentence before writing SQL: 'I want all rows from A, matched or not.' Put match conditions in ON and post-join filters in WHERE, and test with a value known to be unmatched. Verify by counting rows from the preserved table alone and comparing with the join result.
Confusing single-row subqueries with multi-row subqueries, using = or != against a subquery that returns several rows (ORA-01427), or using IN where NOT IN with NULLs silently returns no rows.
The subquery's cardinality depends on data, not syntax, so a query that works in testing fails in production when a second row appears. NOT IN with a NULL in the subquery result is a classic trap: the comparison becomes UNKNOWN for every row, so the outer query returns nothing without any error.
What to do instead
Decide cardinality deliberately and encode it: use = with a subquery you can prove returns one row, and IN/ANY/ALL otherwise. Prefer NOT EXISTS over NOT IN when the subquery column may be nullable, or add WHERE col IS NOT NULL inside the subquery. Test each subquery standalone and inspect its row count first.
Assuming UNION removes duplicates while UNION ALL also does, or forgetting that set operators require matching column counts and compatible data types and that the result column names come from the first query.
UNION and UNION ALL look like cosmetic variants, but they differ in sorting/deduplication work and therefore in performance and results. Candidates also expect ORDER BY inside a component query to control the final order, which is not allowed in that position.
What to do instead
Remember the mnemonic: UNION ALL keeps everything; UNION sorts and removes duplicates. Use UNION ALL by default unless deduplication is genuinely required. Align column counts and cast mismatched types explicitly, and place a single ORDER BY at the end of the compound query, referring to first-query column names or positions.
Issuing DML and leaving the transaction open, then wondering why other sessions cannot see the change, or assuming a COMMIT is implied by running a subsequent query or DDL.
Many candidates practise in tools with autocommit enabled or in a single session, so the transactional boundary is invisible. In a second session, uncommitted INSERT/UPDATE/DELETE rows are not visible and locks may block, which looks like a data problem rather than a commit problem.
What to do instead
Treat COMMIT and ROLLBACK as explicit steps in every DML exercise. Open two sessions and observe visibility and locking before and after COMMIT. Note that DDL performs an implicit commit of pending DML, and that a failed statement does not roll back earlier statements in the same transaction.
Writing UPDATE or DELETE without a WHERE clause during practice, or relying on a ROLLBACK that is impossible because the statement already committed.
Practice environments encourage speed and candidates test statements against real tables. A missing WHERE affects every row, and if autocommit or a preceding DDL statement committed the change, ROLLBACK cannot restore it. Candidates also forget that a multi-row UPDATE failing midway leaves earlier rows changed.
What to do instead
Adopt a fixed pattern: run the SELECT with the intended WHERE first, confirm the row count, then convert it to UPDATE or DELETE with the identical predicate. Wrap changes in an explicit transaction, verify with a follow-up SELECT, and only then COMMIT. Use a savepoint before risky multi-statement work.
Misunderstanding constraint behaviour: expecting a NOT NULL constraint to be created as a table-level constraint without a column reference, expecting a disabled constraint to still be enforced, or being surprised that dropping a parent table fails or cascades depending on the FK clause.
Constraints have both a definition and an enable/validate state, and the two are taught together but tested separately. Candidates also confuse ON DELETE CASCADE with ON DELETE SET NULL and forget that a unique or primary key constraint is backed by an index that affects drop behaviour.
What to do instead
Learn each constraint type with its allowed syntax position: NOT NULL is column-level only, while PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY can be either. Practise DISABLE, ENABLE, and ENABLE VALIDATE on an existing constraint and observe enforcement. Test parent-table drops with and without CASCADE CONSTRAINTS to see the difference.
Treating views, sequences, and synonyms as interchangeable conveniences: expecting a simple view to be updatable through joins or aggregates, assuming NEXTVAL returns the same value twice in one statement, or expecting a private synonym to be visible to other users.
These objects are usually introduced briefly, so candidates learn the CREATE syntax but not the restrictions. Updatable-view rules, sequence caching and gap behaviour, and the public versus private synonym scope are exactly the details that distinguish answers.
What to do instead
Memorize the updatable-view restrictions: no aggregate functions, GROUP BY, DISTINCT, or set operators, and no expression columns without aliases. Remember NEXTVAL advances and CURRVAL repeats within a session. Contrast CREATE SYNONYM (private) with CREATE PUBLIC SYNONYM and check resolution through the data dictionary.