Oracle 1Z0-071 study plan

Write from scratch 15 inner-join and self-join queries against a practice schema you create yourself, and label each join condition explicitly to prove you can separate it from the filter predicate.

Convert 10 of those joins into ANSI-compliant syntax using JOIN ... ON and USING, then predict the row count of each query before executing it.

Distinguish the three types of subquery (single-row, multi-row, correlated) by writing one working example of each and stating in a comment why that form is required.

Build a one-page mental map of the SELECT execution order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) and use it to explain why a column alias fails in WHERE but works in ORDER BY.

Compose 20 WHERE clauses that combine comparison, BETWEEN, IN, LIKE (including ESCAPE), IS NULL, and logical operators, and verify each against a hand-computed expected result.

Write 10 queries that use character, numeric, and NULL-handling single-row functions (UPPER, SUBSTR, INSTR, TRIM, LPAD, ROUND, TRUNC, MOD, NVL, COALESCE, NULLIF) and note the exact return type of each.

Predict and then confirm how NULL propagates through arithmetic, concatenation, and comparison, documenting at least five surprising outcomes.

Sort result sets with ORDER BY using column position, alias, ASC/DESC, and NULLS FIRST/LAST, and explain how Oracle treats NULLs by default.

Convert values in both directions with TO_CHAR, TO_DATE, TO_NUMBER, and CAST, applying explicit format models including YYYY, MM, DD, HH24, MI, SS, and FM modifiers.

Write 10 queries using CASE and DECODE to implement conditional logic, and rewrite five of them from one form into the other to show equivalence.

Aggregate data with COUNT, SUM, AVG, MIN, MAX, and COUNT(), and demonstrate the difference between COUNT(), COUNT(column), and COUNT(DISTINCT column) with a worked example.

Group with GROUP BY and filter groups with HAVING, then state in writing why a non-aggregated SELECT column must appear in GROUP BY.

Handle NULLs inside aggregates using NVL and GROUPING-style reasoning, and explain which aggregates ignore NULLs.

Combine two result sets with UNION, UNION ALL, INTERSECT, and MINUS, and predict the row count and ordering effect of each before running it.

Diagnose and fix at least five set-operator errors caused by column count, datatype, or column-name mismatches, and record the exact error text.

Perform INSERT with column lists, multi-row INSERT ALL, and INSERT ... SELECT, then verify row counts with a follow-up query.

Execute UPDATE and DELETE with correlated WHERE conditions inside a transaction, use SAVEPOINT and ROLLBACK TO, and confirm the pre- and post-state of the data.

Explain what happens when a DML statement violates a constraint, and demonstrate the failure and recovery in a script.

Create tables with appropriate column datatypes and inline versus out-of-line constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY with ON DELETE options, CHECK), then test each constraint with a violating statement.

Alter existing tables to add, modify, and drop columns and constraints, and note which operations succeed on a populated table.

Create and use views, including a read-only view and a view with a CHECK OPTION, and demonstrate which DML the database rejects through each.

Create sequences and consume NEXTVAL and CURRVAL in INSERT statements, including a sequence with CACHE and one with NOCYCLE, and explain the effect of each option.

Create indexes and synonyms, then compare an execution path with and without an index on a filtered column.

Create users with a password and a default tablespace, then grant and revoke system privileges and object privileges, verifying each change by connecting as the affected user.

Demonstrate the difference between direct grants and grants made WITH GRANT OPTION, and show how REVOKE cascades.

Create and use a role, assign it to a user, and prove that privileges take effect only after the grant.

Store and manipulate dates with DATE and TIMESTAMP, perform date arithmetic (adding days, using MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TRUNC on dates), and convert between time zones with AT TIME ZONE.

Apply regular-expression functions (REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR) to validate and transform at least five sample strings.

Re-solve 20 previously missed join, subquery, and set-operator questions from scratch without looking at earlier notes, and log the time taken for each.

Write three queries that each combine a join, a subquery in the WHERE clause, and a set operator, then verify results by an independent manual method.

Explain in writing the difference between a correlated subquery and a join for the same business question, including performance and NULL-handling implications.

Convert five set-operator queries into equivalent join-based queries and confirm identical result sets.

Take two full-length timed practice exams under exam conditions and record the score and per-domain accuracy for each.

Re-study only the three weakest domains identified by those scores, rewriting the underlying rules in your own words.

Review every missed item from both exams and classify each error as knowledge gap, misread question, or syntax slip.

Rehearse the most error-prone syntax patterns (format models, NULL semantics, constraint options, privilege syntax) by writing them from memory and checking against documentation.