Oracle 1Z0-084 cheat sheet

SELECT list evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY; column aliases are not visible in WHERE, only in ORDER BY.

A string literal in the SELECT list without a FROM clause returns one row: SELECT 'text' FROM dual; DUAL is a one-row, one-column table owned by SYS.

WHERE comparison operators: =, !=, ^=, <>, >, >=, <, <=; BETWEEN is inclusive of both bounds; IN is a set membership test and NULL in the list never matches.

LIKE wildcards: % matches zero or more characters, _ matches exactly one character; escape special characters with ESCAPE ''.

NULL tests require IS NULL or IS NOT NULL; any comparison with NULL yields UNKNOWN, and WHERE keeps only rows where the predicate is TRUE.

ORDER BY defaults to ASC; NULLS LAST is the default for ASC and NULLS FIRST for DESC; use ORDER BY col NULLS LAST to override.

Logical operator precedence: NOT before AND before OR; use parentheses to force evaluation order and avoid UNKNOWN propagation.

DISTINCT applies to the entire SELECT list, not to a single column; DISTINCT and a column alias in ORDER BY can conflict if the alias is not in the select list.

FETCH FIRST n ROWS ONLY (12c+) and ROWNUM <= n are not equivalent: ROWNUM is assigned before ORDER BY, so wrap the ordered query in an inline view before filtering.

Single-row functions execute once per row and return one value per row; they can be nested and used in WHERE, SELECT, and ORDER BY.

NVL(expr1, expr2) replaces NULL in expr1 with expr2; NVL2(expr1, expr2, expr3) returns expr2 when expr1 is not NULL, otherwise expr3.

COALESCE returns the first non-NULL argument and short-circuits; it accepts multiple arguments and is standard SQL, unlike NVL.

NULLIF(a, b) returns NULL when a = b, otherwise a; it is useful to avoid division by zero: 1/NULLIF(denominator, 0).

CASE is an expression: simple CASE compares one expression to values, searched CASE evaluates Boolean conditions in order and returns the first match.

TO_CHAR(number) uses format models such as 9999.99, $99,999.99, and L099; TO_CHAR(date) uses formats such as YYYY-MM-DD HH24:MI:SS.

TO_DATE and TO_NUMBER require a matching format model when the string is not in the session default; otherwise ORA-01861 or ORA-01722 can occur.

Implicit conversion depends on the session NLS settings; comparing a VARCHAR2 column to a NUMBER can cause an implicit TO_NUMBER on every row and disable an index.

Date arithmetic: date + n adds n days; date - date returns a number of days; MONTHS_BETWEEN returns a fractional number of months; ADD_MONTHS preserves the day where possible.

ROUND and TRUNC accept a second argument for the number of decimal places; TRUNC(date) truncates to midnight; EXTRACT returns a numeric component from a date or interval.

Group functions: AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE; they ignore NULLs except COUNT(*), which counts rows.

COUNT(column) counts non-NULL values; COUNT(DISTINCT column) counts distinct non-NULL values; COUNT(*) counts all rows including those with NULLs.

A query with a group function and no GROUP BY returns exactly one row, even when the table is empty (SUM returns NULL, COUNT returns 0).

Every non-aggregated column in the SELECT list must appear in the GROUP BY clause; otherwise ORA-00979 is raised.

HAVING filters groups after aggregation; WHERE filters rows before grouping. A group function cannot appear in WHERE.

GROUP BY can group by multiple columns and by expressions; column aliases are not allowed in GROUP BY, but they are allowed in ORDER BY.

ROLLUP produces hierarchical subtotals plus a grand total; CUBE produces all possible combinations of the grouped columns; GROUPING SETS specifies each grouping explicitly.

GROUPING(column) returns 1 when the row is a subtotal for that column and 0 when the column is part of the group; GROUPING_ID returns a bit vector.

Aggregate functions can be nested only when the inner aggregate is inside a group query, e.g., MAX(AVG(salary)) with GROUP BY department_id.

Inner join returns only matching rows; ANSI syntax: FROM a JOIN b ON a.id = b.id; Oracle outer-join syntax uses (+) on the side that may lack rows.

LEFT OUTER JOIN returns all rows from the left table plus matches from the right; RIGHT OUTER JOIN is the reverse; FULL OUTER JOIN returns unmatched rows from both sides.

A join condition in WHERE on an outer-joined table can turn the outer join into an inner join; put non-join filters in the ON clause or use IS NULL checks deliberately.

CROSS JOIN produces a Cartesian product with no join condition; the row count equals the product of the two table row counts.

NATURAL JOIN joins on all columns with the same name and compatible data types; JOIN USING (col) joins on a named column and removes the duplicate column from the output.

Self-join: use two aliases for the same table, e.g., employee e JOIN employee m ON e.manager_id = m.employee_id.

Non-equijoins use operators other than = in the join condition, such as BETWEEN for range lookups.

A join with N tables needs at least N-1 join conditions; missing conditions produce a Cartesian product and potential row explosion.

Single-row subquery returns one row and uses =, <, >, <=, >=, <>; a multi-row result raises ORA-01427.

Multi-row subqueries use IN, ANY, ALL, or EXISTS; NOT IN with a NULL in the subquery result returns no rows.

EXISTS returns TRUE when the subquery returns at least one row; it is often used for semi-joins and can short-circuit on the first matching row.

Scalar subqueries can appear in the SELECT list and in WHERE; they must return at most one row per outer row.

Correlated subqueries reference outer-query columns and are evaluated per outer row; they can be rewritten as joins for performance.

Set operators: UNION removes duplicates, UNION ALL keeps all rows, INTERSECT returns common rows, MINUS returns rows in the first query not in the second.

Set operators require the same number of columns and compatible data types in each query; column names come from the first query.

ORDER BY applies to the entire compound query and must appear only once at the end; parentheses can control evaluation order of set operators.

MINUS is not commutative: A MINUS B differs from B MINUS A; INTERSECT and UNION are commutative.

INSERT INTO t (cols) VALUES (vals) inserts one row; INSERT INTO t SELECT ... inserts many rows; omitted columns receive their default or NULL.

Multi-table INSERT uses INSERT ALL (unconditional) or INSERT FIRST (conditional); each branch can target a different table.

UPDATE t SET col = value WHERE condition; without WHERE, every row is updated. The SET clause can use a subquery, but the subquery must return one row per updated row.

DELETE FROM t WHERE condition; without WHERE, all rows are deleted. TRUNCATE is DDL, cannot be rolled back, and resets the high-water mark.

MERGE INTO target USING source ON (condition) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... performs upsert in one statement.

DML is transactional: changes are visible only to the session until COMMIT; ROLLBACK undoes uncommitted changes; a DDL statement issues an implicit commit before and after.

Read consistency: a SELECT sees data as of the query start SCN; a transaction sees its own uncommitted changes plus committed data as of statement start.

The RETURNING INTO clause returns affected column values into host variables or a record; it is useful with INSERT, UPDATE, and DELETE.

Locking: DML acquires row-level TX locks; a second session updating the same row waits until the first session commits or rolls back.

CREATE TABLE syntax: column definitions, data types, DEFAULT values, and inline or out-of-line constraints; tables are created in the current schema.

Constraint types: NOT NULL (column only), UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK; each can be named with CONSTRAINT name and can be enabled or disabled.

PRIMARY KEY implies NOT NULL and UNIQUE; a table can have only one PRIMARY KEY but many UNIQUE constraints.

FOREIGN KEY references a PRIMARY KEY or UNIQUE column; ON DELETE CASCADE deletes child rows, ON DELETE SET NULL sets them to NULL.

CHECK constraints cannot reference other rows, use subqueries, or call non-deterministic functions such as SYSDATE.

ALTER TABLE can ADD, MODIFY, DROP COLUMN, RENAME COLUMN, ADD CONSTRAINT, DROP CONSTRAINT, ENABLE, or DISABLE a constraint.

DROP TABLE removes the table and its indexes; DROP TABLE ... CASCADE CONSTRAINTS also drops dependent foreign keys; the table moves to the recycle bin unless PURGE is specified.

A table with a NOT NULL column and no default cannot accept INSERT statements that omit that column.

Temporary tables: CREATE GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS (default) or ON COMMIT PRESERVE ROWS; data is private to the session.

A view is a stored query; a simple view has one base table and supports DML unless it contains functions, GROUP BY, DISTINCT, or a join; a complex view is generally read-only.

CREATE OR REPLACE VIEW replaces a view without dropping it; WITH CHECK OPTION prevents DML that would make rows invisible through the view; WITH READ ONLY blocks DML.

An inline view is a subquery in the FROM clause; a materialized view stores the result and can be refreshed ON COMMIT or ON DEMAND.

A sequence generates unique numbers: NEXTVAL advances and returns the next value; CURRVAL returns the last value for the session and is invalid before NEXTVAL.

Sequence options: START WITH, INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, CACHE n, ORDER; CACHE improves performance but can leave gaps after a restart.

An index speeds up access by ROWID; B-tree indexes suit high-cardinality columns; bitmap indexes suit low-cardinality columns and are common in data warehouses.

A unique index is created automatically for PRIMARY KEY and UNIQUE constraints; a function-based index requires the function to be deterministic.

A synonym is an alias for a table, view, sequence, or another synonym; a public synonym is visible to all users, a private synonym only to its owner.

Indexes are automatically maintained on DML but add overhead; dropping an index does not affect the table data, only access paths.

Data dictionary views: USER_* shows objects owned by the current user, ALL_* shows objects accessible to the user, DBA_* shows all objects and requires DBA privileges.

USER_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS, USER_CONS_COLUMNS, USER_INDEXES, USER_VIEWS, USER_SEQUENCES, and USER_SYNONYMS are common metadata sources.

USER_CONSTRAINTS.CONSTRAINT_TYPE values: P (primary key), U (unique), R (foreign key), C (check or NOT NULL), V (with check option on a view).

USER_TAB_COLUMNS includes COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, and DATA_DEFAULT; join to USER_TABLES on TABLE_NAME.

USER_OBJECTS lists all schema objects with OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED, and LAST_DDL_TIME.

USER_DEPENDENCIES shows dependencies between objects, such as a view referencing a table or a procedure referencing a package.

V$ views are dynamic performance views; V$SESSION, V$SQL, and V$LOCK are commonly used for tuning and diagnostics.

DICTIONARY and DICT_COLUMNS describe the data dictionary itself; USER_TAB_COMMENTS and USER_COL_COMMENTS store comments added with COMMENT ON.

CREATE USER username IDENTIFIED BY password; the user needs CREATE SESSION to connect and a default tablespace and quota to create objects.

System privileges allow actions on any object of a type (CREATE TABLE, CREATE SESSION, SELECT ANY TABLE); object privileges allow actions on a specific object (SELECT, INSERT, UPDATE, DELETE, EXECUTE).

GRANT privilege ON object TO user [WITH GRANT OPTION]; WITH GRANT OPTION lets the grantee grant the same privilege to others.

REVOKE privilege ON object FROM user; revoking a privilege granted WITH GRANT OPTION cascades to dependent grants.

Roles group privileges: CREATE ROLE, GRANT privileges TO role, GRANT role TO user; roles can be enabled or disabled per session with SET ROLE.

PUBLIC is a special group: privileges granted to PUBLIC apply to every user; avoid granting powerful privileges to PUBLIC.

A user can change their own password with ALTER USER ... IDENTIFIED BY; a DBA can change any password and can force a password change with PASSWORD EXPIRE.

Profiles control resource limits and password policies: FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_LOCK_TIME, and SESSIONS_PER_USER.

Views and synonyms do not grant access by themselves; the grantee still needs the underlying object privilege or the view must be owned by a privileged definer.