Oracle 1Z0-116 cheat sheet

An Oracle instance is the SGA plus background processes; the database is the set of physical files (data files, control files, online redo logs) that the instance mounts and opens.

SGA components: shared pool (library cache for parsed SQL and execution plans, data dictionary cache), database buffer cache, redo log buffer, large pool, Java pool, Streams pool, optional result cache.

Core background processes: DBWn writes dirty buffers to data files, LGWR writes redo log buffer entries, CKPT updates checkpoints, SMON performs instance recovery, PMON cleans up failed processes, ARCn archives redo.

Logical storage hierarchy: database > tablespace > segment > extent > Oracle data block; a segment belongs to exactly one tablespace, and a data file belongs to exactly one tablespace.

Statement categories: DQL (SELECT), DML (INSERT, UPDATE, DELETE, MERGE), DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK, SAVEPOINT).

A schema is the named collection of objects owned by a database user; user and schema share one name, so CREATE USER scott implicitly creates schema SCOTT.

A transaction ends at COMMIT, ROLLBACK, or any DDL/DCL statement (implicit commit before and after); a normal client exit commits, an abnormal termination rolls back uncommitted work.

In a query, the SELECT list defines output columns and headings, FROM names the row sources, WHERE filters rows; ORDER BY is the only clause that can reference select-list aliases.

SQL*Plus commands (DESCRIBE, SET, SPOOL, DEFINE, COLUMN) are client commands, need no terminating semicolon, and are not sent to the server as SQL.

Clause order is fixed: SELECT ... FROM ... [WHERE] [GROUP BY] [HAVING] [ORDER BY]; evaluation order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Column alias: SELECT sal*12 AS annual_sal FROM emp; aliases containing spaces, mixed case or special characters must be double-quoted; single quotes denote string literals only.

DISTINCT applies to the whole select list as a unit, so SELECT DISTINCT deptno, job returns unique combinations, not unique deptno values.

Arithmetic uses + - * / with parentheses; multiplication and division bind tighter than addition and subtraction; a NULL operand makes the entire arithmetic result NULL.

Concatenation uses || (or CONCAT(a,b), which takes exactly two arguments); 'A' || NULL returns 'A' because NULL is treated as an empty string in concatenation.

Character literals are enclosed in single quotes; embed a quote by doubling it ('it''s') or use alternative quoting such as q'[it's]'.

SELECT may omit FROM in Oracle: SELECT SYSDATE FROM dual; DUAL is a one-row, one-column table owned by SYS used to evaluate expressions and call functions.

Date arithmetic: date + n adds n days, date - n subtracts days, date - date returns a number of days; adding hours requires n/24.

DESCRIBE table lists column names, data types and nullability; output headings default to the column name or alias in uppercase unless the alias was double-quoted.

WHERE filters individual rows before grouping; HAVING filters grouped results after GROUP BY and may reference group functions.

Comparison operators: = != ^= <> < > <= >=; BETWEEN x AND y is inclusive at both ends; IN (list) tests set membership; NOT negates any of these predicates.

LIKE uses % for zero or more characters and _ for exactly one character; the ESCAPE clause defines a prefix that makes a wildcard literal, e.g. LIKE 'A_%' ESCAPE ''.

Logical precedence is NOT, then AND, then OR; parentheses override it, so (a OR b) AND c is not the same as a OR (b AND c).

NULL cannot be tested with = or !=; use IS NULL and IS NOT NULL, because any comparison with NULL yields UNKNOWN and the row is filtered out.

ORDER BY sorts ascending by default and DESC reverses it; default null placement is NULLS LAST for ASC and NULLS FIRST for DESC, and NULLS FIRST/LAST overrides both.

ORDER BY accepts a column name, an alias, a numeric position or an expression; it is evaluated after SELECT, so select-list aliases are legal there but not in WHERE.

Character ordering follows the session NLS_SORT (binary by default); under a binary sort 'a' sorts after 'Z' because comparison is case-sensitive.

Substitution variables: &name prompts once per occurrence, &&name reuses the value for the session; DEFINE assigns a value and UNDEFINE removes it.

Character functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR(s,m,n), INSTR(s,sub), LPAD/RPAD, TRIM/LTRIM/RTRIM, REPLACE, TRANSLATE.

SUBSTR is 1-based, so SUBSTR('ABCDE',2,3) returns 'BCD'; a negative start position counts from the end of the string; INSTR returns 0 when the substring is absent.

Numeric functions: ROUND(n,d) rounds to d decimals, TRUNC(n,d) truncates, MOD(m,n) returns a remainder, plus CEIL, FLOOR, ABS, POWER, SQRT, SIGN.

Negative precision rounds left of the decimal point: ROUND(45.926,-1) = 50 and TRUNC(45.926,2) = 45.92; MOD(1600,300) = 100.

Date functions: SYSDATE, ADD_MONTHS(d,n), MONTHS_BETWEEN(d1,d2), LAST_DAY(d), NEXT_DAY(d,'DAY'), ROUND/TRUNC with a date format unit, EXTRACT(part FROM d).

NEXT_DAY returns the first named weekday strictly after the given date, interpreted using the session NLS_DATE_LANGUAGE; LAST_DAY returns the last day of the month.

ROUND(SYSDATE,'MONTH') returns the nearest first of the month and TRUNC(SYSDATE,'YEAR') returns 1 January of the current year, both at midnight.

NVL(e1,e2) substitutes e2 for NULL; NVL2(e,a,b) returns a when e is not null and b otherwise; COALESCE returns the first non-null of two or more arguments; NULLIF(a,b) returns NULL when a=b.

Single-row functions run once per row and can be nested; the innermost call evaluates first and the outermost function determines the result data type.

Implicit conversion occurs between compatible types: VARCHAR2 to NUMBER or DATE, NUMBER to VARCHAR2, DATE to VARCHAR2; string-to-date conversion uses NLS_DATE_FORMAT and can raise ORA-01843.

TO_CHAR(date,'YYYY-MM-DD HH24:MI:SS') formats dates; elements include YYYY, RR, MM, MON, MONTH, DD, DAY, DY, HH, HH24, MI, SS, AM/PM, and FM to strip padding.

TO_CHAR(number,'$99,999.00') formats numbers; 9 defines digit width, 0 forces leading zeros, L inserts the local currency symbol, MI places a trailing minus, EEEE gives scientific notation, and overflow prints ####.

TO_DATE('17-06-2024','DD-MM-YYYY') parses a string into a date; the format model must match the input order and separators or Oracle raises ORA-01861 or ORA-01843.

RR versus YY: YY always returns the current century; RR maps 00-49 to the current century and 50-99 to the previous century relative to the current year.

TO_NUMBER('1,234.5','9,999.9') converts formatted text to a number; without a format model the conversion depends on NLS_NUMERIC_CHARACTERS.

DECODE(expr, s1, r1, s2, r2, default) returns the first matching result; inside DECODE two NULLs are considered equal, and Oracle implicitly converts result types.

CASE has two forms: simple CASE compares one expression to WHEN values for equality, searched CASE evaluates arbitrary conditions; the first true branch wins and no match returns NULL without ELSE.

CASE and DECODE can appear in the SELECT list, WHERE, GROUP BY and ORDER BY; CASE is ANSI standard and supports full conditions, DECODE only equality matching.

Group functions include AVG, SUM, MIN, MAX, COUNT, STDDEV, VARIANCE and LISTAGG; all of them except COUNT(*) ignore NULL arguments.

COUNT(*) counts all rows including rows whose columns are all null; COUNT(col) counts non-null values of col; COUNT(DISTINCT col) counts distinct non-null values.

GROUP BY rule: every non-aggregated expression in the SELECT list must appear in the GROUP BY clause, otherwise Oracle raises ORA-00979.

Evaluation order inside a grouped query: WHERE filters rows, GROUP BY forms groups, HAVING filters groups, then the select list and group functions are evaluated.

HAVING may appear without GROUP BY, in which case the whole table is one group, e.g. HAVING COUNT(*) > 5 returns the single group or no row.

A group function is illegal in a WHERE clause (ORA-00934); filter individual rows in WHERE and aggregated results in HAVING, or use an inline view.

ROLLUP produces hierarchical subtotals plus a grand total, CUBE produces subtotals for every combination of grouped columns, GROUPING SETS produces only the groupings listed.

GROUPING(col) returns 1 on a super-aggregate row where col has been aggregated and 0 otherwise; GROUPING_ID returns the corresponding bit vector for several columns.

Group functions can be nested one level only, as in MAX(AVG(sal)) with GROUP BY; deeper nesting raises ORA-00935.

Two syntaxes exist: ANSI joins (INNER, LEFT/RIGHT/FULL OUTER, CROSS, NATURAL, JOIN ... USING, JOIN ... ON) and Oracle's proprietary outer-join operator (+).

An inner join returns only rows satisfying the join condition; a missing or non-selective condition degenerates into a Cartesian product with m x n rows.

Outer-join rule for (+): place the operator on the table that supplies the NULL-extended rows, meaning the table with no matching row, and never on both sides of one condition.

A LEFT OUTER JOIN can be written with (+) on the right-hand table and a RIGHT OUTER JOIN with (+) on the left-hand table; a FULL OUTER JOIN cannot be expressed with (+) at all.

ON versus WHERE in outer joins: a filter written in ON is applied while matching and preserves the outer rows, whereas the same filter in WHERE removes the NULL-extended rows and makes the join inner.

NATURAL JOIN joins on all identically named columns and returns each once; JOIN ... USING(col) joins on the listed columns, returns them once, and forbids alias-qualifying those columns.

Self join: reference one table twice under different aliases, e.g. FROM emp e JOIN emp m ON e.mgr = m.empno, to compare rows inside a single table.

A cross join has no join condition and can also be written FROM t1, t2 or t1 CROSS JOIN t2; its row count is the product of the two row counts.

Qualify ambiguous columns with the table alias; NATURAL and USING joins cannot also use an ON clause, and a table cannot be outer-joined to two tables in one condition with (+).

A single-row subquery returns one row and pairs with =, >, <, >=, <=; multi-row subqueries pair with IN, ANY, ALL, EXISTS; a single-row operator receiving several rows raises ORA-01427.

ANY semantics: > ANY means greater than the minimum of the list and = ANY equals IN; ALL semantics: > ALL means greater than the maximum and < ALL means less than the minimum.

Subqueries are legal in WHERE, HAVING and FROM (as an inline view), and as a scalar subquery in the SELECT list; ORDER BY belongs only to the outermost query.

A correlated subquery references a column of the outer query, so it is evaluated once per candidate outer row; EXISTS stops scanning at the first matching row.

NOT IN returns no rows when the subquery result contains a NULL; use NOT EXISTS or add WHERE col IS NOT NULL to the subquery.

A scalar subquery must return exactly one column and at most one row; zero rows yields NULL, while more than one row raises ORA-01427.

An inline view is a subquery in the FROM clause and requires an alias: SELECT * FROM (SELECT deptno, AVG(sal) a FROM emp GROUP BY deptno) WHERE a > 2000.

A subquery in HAVING compares group results, e.g. HAVING AVG(sal) > (SELECT AVG(sal) FROM emp), and is evaluated after GROUP BY.

Multi-column subqueries pair with multi-column IN: WHERE (job, deptno) IN (SELECT job, deptno FROM emp WHERE deptno = 10).

Set operators combine queries: UNION returns all distinct rows, UNION ALL returns every row with duplicates, INTERSECT returns rows present in both, MINUS returns rows only in the first query.

Rules for every component query: the same number of columns, compatible data type groups, headings taken from the first query, and a single ORDER BY at the very end.

UNION performs an implicit sort and duplicate elimination; UNION ALL skips both, so UNION ALL is faster when duplicates are impossible or acceptable.

Precedence: INTERSECT binds tighter than UNION and MINUS; operators of equal precedence evaluate left to right; parentheses override the order.

MINUS subtracts the second result from the first and is not commutative, so A MINUS B generally differs from B MINUS A.

ORDER BY in a compound query may reference only the first query's column names or output column positions, not aliases introduced by later component queries.

Set operators compare whole rows; NULLs are treated as equal for duplicate elimination, so two all-NULL rows count as duplicates in UNION and INTERSECT.

Column aliases defined in the first query become the result headings; aliases given in later component queries are ignored.

INSERT forms: INSERT INTO t VALUES (...); INSERT INTO t (col,...) VALUES (...); INSERT INTO t SELECT ...; multi-table INSERT ALL or INSERT FIRST with WHEN conditions.

UPDATE t SET col = value [, col2 = value2] [WHERE condition]; omitting WHERE updates every row, and the SET expressions may include a scalar or correlated subquery.

DELETE [FROM] t [WHERE condition]; omitting WHERE deletes all rows but keeps the table definition, and the deleted rows remain recoverable until COMMIT.

TRUNCATE TABLE is DDL: no WHERE clause, no rollback, no triggers fired, resets the high-water mark; DELETE is DML: rollback-able, fires triggers, supports WHERE.

Transaction control: COMMIT makes changes permanent; ROLLBACK undoes all uncommitted changes; SAVEPOINT s marks a point and ROLLBACK TO s undoes only work done after it.

DDL and DCL statements issue an implicit COMMIT before and after they run, ending the current transaction; a normal client exit also commits, an abnormal exit rolls back.

Oracle uses statement-level read consistency: a query sees data as of the statement's start SCN, and readers never block writers or wait for them.

The RETURNING clause returns affected column values into host variables, e.g. INSERT ... RETURNING id INTO :id, and DELETE ... RETURNING sal INTO :sal.

MERGE performs conditional INSERT and UPDATE in one statement using WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT clauses.

CREATE TABLE defines columns with data types, DEFAULT values, and constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY ... REFERENCES, CHECK, NOT NULL) inline or out of line.

Identifier rules: 1-30 characters (128 bytes from release 12.2), must begin with a letter, may contain letters, digits, underscore, dollar sign and hash; unquoted names are case-insensitive and stored uppercase.

Key data types: CHAR(n) fixed-length and blank-padded, VARCHAR2(n) up to 4000 bytes, NUMBER(p,s), DATE storing time to the second, TIMESTAMP, CLOB/BLOB for large objects, RAW for binary data.

CREATE TABLE t AS SELECT ... (CTAS) copies rows and NOT NULL constraints only; PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY constraints must be added afterwards with ALTER TABLE.

ALTER TABLE supports ADD, MODIFY (data type, default, nullability), DROP COLUMN, SET UNUSED, RENAME COLUMN, ADD CONSTRAINT, and ENABLE/DISABLE CONSTRAINT.

DROP TABLE ... PURGE bypasses the recycle bin; FLASHBACK TABLE t TO BEFORE DROP restores a dropped table with its indexes unless it was purged.

Constraint states combine ENABLE/DISABLE with VALIDATE/NOVALIDATE; ENABLE NOVALIDATE activates a rule for new data while tolerating existing violating rows.

A FOREIGN KEY must reference a PRIMARY KEY or UNIQUE column; ON DELETE CASCADE deletes child rows, ON DELETE SET NULL nulls them, and dropping a referenced parent requires CASCADE CONSTRAINTS.

A table is created in the owner's default tablespace unless TABLESPACE is specified; PCTFREE reserves block space for future growth of existing rows.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW v [(aliases)] AS subquery [WITH CHECK OPTION] [WITH READ ONLY]; FORCE creates the view even when base tables are missing.

WITH CHECK OPTION rejects INSERT and UPDATE statements that would produce rows the view's WHERE clause cannot select; WITH READ ONLY blocks all DML through the view.

DML through a view is allowed only for simple views (no DISTINCT, group functions, GROUP BY, set operators or ROWNUM); complex views need INSTEAD OF triggers to be modifiable.

CREATE SEQUENCE s START WITH n INCREMENT BY m [MAXVALUE x | NOMAXVALUE] [CYCLE | NOCYCLE] [CACHE k | NOCACHE]; NEXTVAL and CURRVAL are pseudocolumns.

CURRVAL returns the last value generated in the current session and raises ORA-08002 when NEXTVAL has not yet been called in that session; gaps occur after rollback and cache loss.

Oracle creates indexes automatically for PRIMARY KEY and UNIQUE constraints; CREATE INDEX idx ON t(col); bitmap indexes suit low-cardinality columns and function-based indexes support expressions.

Indexes speed up lookups and predicate evaluation but add overhead to INSERT, UPDATE and DELETE; a unique index enforces uniqueness of its key values.

Synonyms: CREATE [PUBLIC] SYNONYM name FOR schema.object; a private synonym is visible only to its owner, a public synonym is visible to all users.

A sequence is independent of tables and transactions: values are issued outside transaction control, so rollback does not reclaim them and one sequence can feed several tables.