Oracle 1Z0-084 study plan

Write 20 SELECT statements that combine WHERE predicates with AND, OR, NOT, IN, BETWEEN, LIKE (including escaped wildcards) and IS NULL, and predict each result set before running it

Order and page results using ORDER BY with ASC/DESC, column aliases, and multiple sort keys, and explain how NULLs sort by default

Apply single-row character, numeric, and date functions (UPPER, SUBSTR, INSTR, LPAD, ROUND, TRUNC, MOD, SYSDATE, MONTHS_BETWEEN) to transform output in at least 15 queries

Use TO_CHAR, TO_DATE, and TO_NUMBER with explicit format models and demonstrate what happens without them on a session with a different NLS_DATE_FORMAT

Handle NULL propagation with NVL, NVL2, COALESCE, and NULLIF, and choose the correct one for three given scenarios

Group rows with GROUP BY and filter groups with HAVING, writing five queries where a WHERE clause would give a wrong answer

Use COUNT(*), COUNT(expr), COUNT(DISTINCT expr), SUM, AVG, MIN, MAX and explain why COUNT(column) ignores NULLs

Write inner joins using both ANSI JOIN ... ON syntax and the older comma/WHERE style, then rewrite each one in the other style

Write LEFT, RIGHT, and FULL OUTER joins and identify rows returned by the outer side that have no match

Join three or more tables and resolve at least two column-name ambiguities using table aliases and qualifying prefixes

Demonstrate self-joins, natural joins, and CROSS JOIN, and state when each is appropriate or dangerous

Write single-row subqueries in WHERE, HAVING, and SELECT lists, and convert three of them into joins

Write multi-row subqueries with IN, ANY, ALL, and EXISTS, and explain the NULL trap in NOT IN

Write correlated subqueries and compare their behavior against an equivalent GROUP BY/HAVING query

Combine result sets with UNION, UNION ALL, INTERSECT, and MINUS, and predict duplicate handling and column-count/type compatibility errors

Place subqueries in the FROM clause (inline views) and reference their aliases in the outer query

Insert rows with INSERT ... VALUES, INSERT ... SELECT, and multi-table INSERT ALL / conditional INSERT FIRST

Update and delete rows using subqueries in WHERE, and verify affected row counts before committing

Perform a MERGE that inserts new rows and updates existing ones, and describe the error raised when the ON clause matches more than one source row

Demonstrate transaction control with COMMIT, ROLLBACK, and SAVEPOINT, including rolling back to a savepoint inside a multi-statement transaction

Create tables with CREATE TABLE ... AS SELECT and with explicit column definitions, choosing appropriate VARCHAR2, NUMBER, DATE, and TIMESTAMP types

Add and drop NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY (with ON DELETE CASCADE and without), and CHECK constraints, and read the resulting error messages

Disable, enable, and drop constraints, and explain the difference between validating and non-validating an enabled constraint

Use DEFAULT values, virtual columns, and read-only tables, and describe how each affects INSERT and UPDATE

Drop and rename tables, recover a dropped table with FLASHBACK TABLE ... TO BEFORE DROP, and use TRUNCATE versus DELETE

Create simple and complex views, and perform INSERT/UPDATE/DELETE through a view where allowed and explain when a view is non-updatable

Add WITH CHECK OPTION and WITH READ ONLY to views and show the errors they raise

Create sequences with START WITH, INCREMENT BY, CACHE, and CYCLE, and use NEXTVAL/CURRVAL in an INSERT

Create B-tree indexes on single and composite columns, and explain when the optimizer will not use an index (function on the column, leading-wildcard LIKE, low selectivity)

Create private and public synonyms and explain name resolution order and why a public synonym can mask an object

Query USER_TABLES, ALL_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS, USER_CONS_COLUMNS, USER_VIEWS, USER_SEQUENCES, USER_INDEXES, and USER_OBJECTS to answer ten schema questions

Distinguish USER_, ALL_, and DBA_ views and predict which rows a low-privilege user sees in each

Create users, grant and revoke system privileges and object privileges, and use WITH GRANT OPTION and WITH ADMIN OPTION correctly

Create and use roles, grant a role to a user, and set a default role

Use column-level privileges and verify them through ALL_TAB_COL_PRIVS or a test query

Re-run every lab script from weeks 1-7 on a clean schema to confirm reproducibility and fix anything that fails

Complete two timed 60-question mixed practice sets covering all ten domains, then categorize each wrong answer by domain

Re-study the two weakest domains using the categorized error list, and rewrite the missed query types from scratch

Memorize the syntax skeletons for MERGE, multi-table INSERT, WITH CHECK OPTION, and WITH GRANT OPTION, and write each from memory without notes

Review Oracle error messages encountered during the plan and state the cause and fix for the ten most common ones