1Z0-149 common mistakes
1Z0-149 · Understand the traps and how to avoid them.
Assuming NULL participates in comparisons and arithmetic like a normal value, so candidates predict that a row with a NULL column will satisfy 'WHERE col <> 5' or that 'NULL = NULL' returns TRUE.
Candidates carry over intuition from other languages where a missing value behaves like zero or an empty string. In Oracle, any comparison with NULL yields UNKNOWN, and the WHERE clause only keeps rows that evaluate to TRUE, so those rows silently disappear. The same confusion appears in NOT IN subqueries, where a single NULL in the subquery list makes the whole predicate UNKNOWN and returns zero rows.
What to do instead
Treat NULL as a three-valued logic problem. When writing predicates, explicitly test with IS NULL / IS NOT NULL, wrap nullable columns with NVL or COALESCE before comparisons, and remember that NOT IN against a subquery containing NULLs returns no rows - rewrite as NOT EXISTS or filter NULLs in the subquery. Verify predictions by running the query and inspecting row counts, not by mental evaluation.
Mixing GROUP BY and non-aggregated columns incorrectly: selecting a column that is not in the GROUP BY clause, or filtering groups with WHERE instead of HAVING.
Candidates learn the SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY order as a list to memorize rather than as an execution pipeline. They then try to filter on an aggregate (SUM(sal) > 1000) in the WHERE clause, which raises ORA-00934, or they add a column to SELECT without adding it to GROUP BY, which raises ORA-00979.
What to do instead
Internalize that WHERE filters rows before grouping and HAVING filters groups after aggregation. Any non-aggregate expression in SELECT must appear in GROUP BY (or be functionally dependent). When you need to filter on an aggregate, move the condition to HAVING. Practice rewriting a failing query both ways to see which is legal.
Predicting the wrong result from outer joins, especially placing the filter for the optional table in the WHERE clause instead of the ON clause, which silently converts the outer join into an inner join.
Candidates treat ON and WHERE as interchangeable. With a LEFT JOIN, a WHERE predicate like 'b.status = ''ACTIVE''' eliminates the NULL-extended rows produced for unmatched left rows, so the result looks like an inner join. Candidates also confuse which side is preserved in LEFT vs RIGHT joins.
What to do instead
Remember the preserved side is the one named in the join direction: LEFT JOIN preserves the left table's rows. Put conditions on the optional (non-preserved) table in the ON clause to keep unmatched rows; put conditions on the preserved table in WHERE. Use (+) syntax and ANSI syntax side by side to confirm the equivalence, and check row counts before and after adding a predicate.
Confusing single-row and multi-row (group) functions: expecting NVL, TO_CHAR, or SUBSTR to work on a column across many rows, or expecting MAX/AVG to work inside a WHERE clause without grouping.
The exam deliberately mixes scalar functions and aggregate functions in similar-looking questions. Candidates see a function name and assume it aggregates, or they nest group functions incorrectly (e.g., MAX(AVG(sal))) without a GROUP BY that makes the inner aggregate valid.
What to do instead
Classify every function before answering: single-row functions return one result per row and can appear anywhere an expression is allowed; group functions collapse many rows to one and cannot appear in WHERE. For nesting, ensure the inner aggregate is grouped first. Read the question's expected number of output rows - that usually reveals which category is required.
Getting implicit and explicit conversion wrong: assuming TO_CHAR/TO_DATE formats are optional, or that comparing a VARCHAR2 date column to a DATE literal works without conversion.
Candidates rely on implicit conversion because simple examples happen to work under their session's NLS_DATE_FORMAT. On the exam, format-model mismatches (e.g., 'DD-MON-YYYY' vs 'YYYY-MM-DD'), two-digit years, and comparing a character column to a number produce wrong results or ORA-01722 rather than an obvious error.
What to do instead
Always supply explicit format models with TO_DATE and TO_CHAR, and never rely on session NLS settings. Remember implicit conversion rules: strings convert to numbers in numeric context, and dates convert using NLS_DATE_FORMAT. When comparing a VARCHAR2 to a DATE, convert the string explicitly with TO_DATE. Check RR vs YY behavior for two-digit years.
Misjudging set operator rules: using UNION when UNION ALL is required for performance, or assuming ORDER BY can appear inside a component query rather than only at the end.
Candidates memorize that UNION removes duplicates but forget that it also sorts, which changes both performance and result ordering expectations. They also place ORDER BY in the first SELECT of a compound query, which raises ORA-00933, or use column names from the second query in the final ORDER BY.
What to do instead
Remember: UNION and INTERSECT remove duplicates and sort; UNION ALL and MINUS do not sort (MINUS still removes duplicates). Column count and data types must match across components, and column names come from the first SELECT. ORDER BY belongs only at the very end and must reference the first query's column names or positions. Choose UNION ALL whenever duplicates are acceptable.
Assuming a DML statement commits automatically, or expecting ROLLBACK to undo a TRUNCATE or DDL that was issued in the same session.
Candidates coming from autocommit environments assume INSERT/UPDATE/DELETE are permanent. They also confuse TRUNCATE (DDL, implicit commit, not rollback-able) with DELETE (DML, rollback-able). Questions about what another session can see before COMMIT expose this gap.
What to do instead
State explicitly that DML requires COMMIT or ROLLBACK and that uncommitted changes are invisible to other sessions and lock the affected rows. Classify every statement as DML or DDL before answering: DDL (CREATE, ALTER, DROP, TRUNCATE) issues an implicit commit. Use SAVEPOINT and ROLLBACK TO for partial undo of DML only.
Confusing the behavior of sequences and synonyms: expecting NEXTVAL to return the same value twice, assuming CURRVAL is available in a new session, or thinking a synonym stores data or grants privileges.
Candidates treat sequences like identity columns and synonyms like views. NEXTVAL always advances; CURRVAL returns the last value fetched in the current session and errors if NEXTVAL was never called in that session. Synonyms are only name aliases - they do not copy data, do not grant privileges, and a public synonym still requires object privileges.
What to do instead
Memorize: NEXTVAL increments and returns; CURRVAL returns the session's last value and requires a prior NEXTVAL in the same session. Sequence values can have gaps after rollback and are not tied to transaction commits. For synonyms, remember they resolve at runtime, can be private or public, and never change privilege requirements. Use USER_SYNONYMS and USER_SEQUENCES to verify definitions.
Assuming a view can always be updated, or forgetting WITH CHECK OPTION and WITH READ ONLY behavior when answering DML-through-view questions.
Candidates learn that simple views are updatable and overgeneralize to views with joins, group functions, DISTINCT, or set operators, which are not inherently updatable. They also miss that WITH CHECK OPTION blocks rows that would fall outside the view's WHERE clause, and that WITH READ ONLY blocks all DML.
What to do instead
Check the view definition first: if it contains joins, aggregates, GROUP BY, DISTINCT, or set operators, it is not inherently updatable unless an INSTEAD OF trigger exists. WITH CHECK OPTION enforces the view's predicate on INSERT and UPDATE; WITH READ ONLY rejects all DML. Read the question for the words 'check option' and 'read only' before choosing an answer.
Querying the wrong data dictionary view: using ALL_TABLES when only the user's own objects are wanted, or expecting DBA_ views to be accessible to a normal user.
Candidates memorize view names without the USER_/ALL_/DBA_ distinction. USER_ shows objects owned by the current schema, ALL_ shows objects the user can access, and DBA_ shows everything but requires privileges. Questions about column definitions versus constraints versus indexes trip candidates who pick TABLES when they need TAB_COLUMNS, CONSTRAINTS, or IND_COLUMNS.
What to do instead
Map the question to the object type first, then to the prefix. Use USER_TAB_COLUMNS for column definitions, USER_CONSTRAINTS and USER_CONS_COLUMNS for constraints, USER_INDEXES and USER_IND_COLUMNS for indexes, USER_VIEWS for view text, and USER_TABLES for table metadata. Remember DBA_ views require DBA privileges. When in doubt, check which view actually contains the column named in the question.