Oracle 1Z0-116 study plan

Write 25 SELECT statements against a local practice schema that project specific columns, rename every output column with an alias, and include at least five arithmetic or concatenation expressions.

Build 15 WHERE clauses that combine comparison operators with AND, OR, and NOT, including at least three predicates that explicitly test for NULL and three that use BETWEEN, IN, or LIKE.

Sort 10 result sets with ORDER BY using column aliases, ordinal positions, ASC/DESC, and NULLS FIRST/NULLS LAST, then predict each row order before execution.

Diagnose and correct 8 intentionally malformed SELECT statements covering missing FROM, bad aliases, and misplaced WHERE or ORDER BY clauses.

Apply 20 character, numeric, and date single-row functions in SELECT and WHERE clauses, including UPPER, SUBSTR, INSTR, LPAD, ROUND, TRUNC, MOD, ADD_MONTHS, LAST_DAY, and MONTHS_BETWEEN.

Handle NULLs with NVL, NVL2, NULLIF, and COALESCE in 10 queries, and explain the difference between each function in a written note.

Convert values explicitly with TO_CHAR, TO_DATE, and TO_NUMBER using format models, and identify five cases where implicit conversion succeeds or fails.

Rewrite 8 DECODE expressions as CASE expressions and 8 CASE expressions as DECODE where feasible.

Write 15 grouped queries using COUNT, SUM, AVG, MIN, and MAX, including COUNT(*), COUNT(column), COUNT(DISTINCT column), and nested group functions where valid.

Construct 10 GROUP BY queries with HAVING filters that use aggregate conditions, and distinguish WHERE row filtering from HAVING group filtering.

Write 12 join queries covering inner equijoins, non-equijoins, LEFT and RIGHT outer joins, self-joins, and cross joins using both ON and USING syntax.

Debug 6 join queries that return duplicated or missing rows by checking join keys, NULLs, and outer-join placement.

Write 12 single-row and multiple-row subqueries using =, >, IN, ANY, and ALL, and state the expected number of rows returned by each subquery.

Build 6 correlated subqueries with EXISTS and NOT EXISTS, and compare each result with an equivalent join.

Use inline views and scalar subqueries in SELECT and FROM clauses to answer 6 reporting questions.

Combine result sets with UNION, UNION ALL, INTERSECT, and MINUS in 8 queries, matching column counts and data types and applying ORDER BY only once.

Execute 20 INSERT, UPDATE, and DELETE statements, including default values, subquery-driven inserts, and WHERE clauses that affect a verified number of rows.

Demonstrate transaction control with COMMIT, ROLLBACK, and SAVEPOINT in a sequence of 8 statements, and record which changes survive each boundary.

Create 6 tables with appropriate data types and constraints, including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and DEFAULT.

Alter 4 existing tables by adding, modifying, and dropping columns and constraints, then use TRUNCATE and DROP and explain the differences.

Create 5 views, including one simple view and one complex view with a join, and perform DML through a view only where the view is key-preserved.

Create 4 sequences with different START WITH, INCREMENT BY, and CYCLE settings, and use NEXTVAL and CURRVAL in INSERT statements.

Create 5 indexes on appropriate columns and explain when an index can improve or degrade a query.

Summarize Oracle Database architecture and SQL statement processing steps in a one-page concept map that links the SELECT, DML, and DDL lessons from weeks 1-5.

Re-solve 20 missed join and subquery items from weeks 3-4 without notes, then classify each error as syntax, logic, or cardinality.

Write 10 mixed queries that require at least one join and one subquery, and verify each against a manually derived expected result.

Complete a 30-question timed drill on joins, subqueries, and set operators, targeting at least 80%.

Explain in writing when to choose a join, a subquery, or a set operator for the same business question.

Take a 60-question timed mock exam covering all 12 domains, and record raw score, time per section, and every missed objective.

Re-study the three lowest-scoring domains from the mock and write 10 new practice questions with answer keys for each.

Recreate 15 SQL statements from memory that demonstrate SELECT, WHERE, GROUP BY, JOIN, subquery, set operator, INSERT, UPDATE, DELETE, CREATE TABLE, view, sequence, and index.

Build a one-page exam-day checklist of syntax traps, including NULL comparisons, GROUP BY rules, outer-join placement, and set-operator column matching.