1Z0-149 cheat sheet

A SELECT statement is processed in this logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; only ORDER BY can reference SELECT-list aliases.

Arithmetic in the SELECT list follows SQL precedence: multiplication and division bind before addition and subtraction; parentheses override.

A column alias with spaces or mixed case needs double quotes; single quotes denote string literals, never identifiers.

NULL in arithmetic yields NULL: 5 + NULL returns NULL, and concatenation with || treats NULL as empty string so 'A' || NULL returns 'A'.

DISTINCT applies to the entire selected row, not to the first column; DISTINCT and a column alias cannot be combined in one SELECT list in the same way as plain DISTINCT.

The DESCRIBE command (or DESC) lists column names, nullability, and data types for a table; it is a SQL*Plus command, not SQL.

Quoted identifiers preserve case and allow spaces; unquoted identifiers are stored uppercase and are case-insensitive.

WHERE filters rows before grouping; HAVING filters groups after GROUP BY. A HAVING clause without GROUP BY treats the whole result as one group.

BETWEEN a AND b is inclusive on both ends and equals a >= value AND value <= b.

IN (list) is equivalent to a chain of OR equalities; NOT IN with a NULL in the list returns no rows because each comparison is UNKNOWN.

LIKE uses % for any sequence of characters and _ for exactly one character; ESCAPE defines a character that makes % and _ literal.

Logical precedence is NOT, then AND, then OR; use parentheses to force evaluation order.

ORDER BY defaults to ASC and places NULLs last for ascending and first for descending; NULLS FIRST / NULLS LAST overrides.

ORDER BY can use a column alias, an expression, or a positional number referring to the SELECT list.

Single-row functions run once per row and return one result per row; they can nest, unlike group functions that cannot nest.

Character functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR(str,pos,len), INSTR, LPAD, RPAD, TRIM, REPLACE, CONCAT.

SUBSTR position 0 is treated as 1; a negative position counts from the end of the string.

Number functions: ROUND(n,d) rounds half away from zero; TRUNC(n,d) cuts without rounding; MOD(m,n) returns the remainder; ABS, CEIL, FLOOR, POWER.

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

MONTHS_BETWEEN(d1,d2) returns a fractional number of months; ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC on dates operate on date parts.

SYSDATE returns the database server date and time; CURRENT_DATE returns the session time zone date.

TO_CHAR, TO_DATE, and TO_NUMBER convert values; implicit conversion happens when data types differ, but explicit conversion is safer and faster.

TO_CHAR(date,'fmt') format elements include YYYY, MM, DD, HH24, MI, SS, DAY, MON, and FM to suppress padding.

TO_NUMBER('1234.5','9999.9') parses a formatted string; a mismatch between format and string raises ORA-01722.

NVL(expr1,expr2) replaces NULL with a substitute; NVL2(expr,not_null_val,null_val) returns different values depending on nullability.

NULLIF(a,b) returns NULL when a equals b, otherwise returns a; COALESCE returns the first non-NULL argument.

CASE is an ANSI expression that can be simple or searched; DECODE is Oracle-specific and treats two NULLs as equal.

DECODE returns the default value, or NULL if no default is supplied, when no match is found.

Group functions are AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE; each returns one value per group.

COUNT(*) counts rows including those with NULLs; COUNT(column) skips NULLs; COUNT(DISTINCT col) counts unique non-NULL values.

AVG and SUM ignore NULLs; AVG(column) divides by the count of non-NULL values, not by the total row count.

Every nonaggregated column in the SELECT list must appear in GROUP BY, or ORA-00979 is raised.

WHERE cannot contain group functions; use HAVING. Group functions can appear in the SELECT list, HAVING, and ORDER BY.

GROUP BY ROLLUP and CUBE produce subtotal and grand-total rows; GROUPING identifies which rows are subtotals.

Nesting group functions requires an inner GROUP BY: MAX(AVG(salary)) is valid only when salary is grouped.

An inner join returns only matching rows; a left outer join keeps unmatched rows from the left table with NULLs on the right.

Oracle outer-join syntax uses (+), placed on the side lacking rows; the (+) operator cannot appear on both sides of a join condition.

A cross join or a join with no condition produces a Cartesian product of m x n rows.

A self join joins a table to itself and requires distinct aliases; it is used for hierarchical comparisons such as employee to manager.

NATURAL JOIN joins on all columns with the same name and data type and removes duplicate join columns from the output.

USING (col) joins on one named column and returns that column once; ON accepts arbitrary conditions.

A non-equijoin uses operators other than = such as BETWEEN or < to match rows across tables.

A single-row subquery returns one row and uses =, >, <, >=, <=, or <>. If it returns more than one row, ORA-01427 is raised.

A multiple-row subquery uses IN, ANY, ALL, or EXISTS; = with a multiple-row subquery fails.

EXISTS returns TRUE when the subquery returns at least one row; NOT EXISTS is NULL-safe, unlike NOT IN.

A correlated subquery references a column from the outer query and is re-evaluated for each outer row.

Subqueries can appear in the SELECT list (scalar subquery must return one row), WHERE, HAVING, FROM, and UPDATE/DELETE statements.

The WITH clause defines a named subquery block that can be referenced multiple times in the same statement.

A scalar subquery returning no rows yields NULL in the SELECT list but makes a WHERE comparison UNKNOWN.

UNION returns all distinct rows from both queries; UNION ALL returns all rows including duplicates and is faster because it skips the sort.

INTERSECT returns rows present in both query results; MINUS returns rows in the first result not present in the second.

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

ORDER BY can appear only once, at the end of the compound query, and must reference columns by name or position from the first SELECT.

Set operators other than UNION ALL remove duplicates; parentheses can group queries to control evaluation order.

NULLs are treated as equal for set operations: a NULL row in both queries appears once in INTERSECT.

MINUS is not commutative: A MINUS B differs from B MINUS A.

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

UPDATE t SET col = value WHERE cond changes matching rows; without WHERE, every row is updated.

DELETE FROM t WHERE cond removes matching rows; TRUNCATE removes all rows faster but is DDL and cannot be rolled back.

A transaction ends with COMMIT or ROLLBACK; DDL statements issue an implicit commit.

SAVEPOINT name marks a point; ROLLBACK TO name undoes only work after that point, leaving the transaction open.

A read-consistent query sees data as of the statement start; uncommitted changes by other sessions are not visible.

DEFAULT values are applied when a column is omitted from INSERT or assigned the keyword DEFAULT.

A unique index enforces uniqueness on its key columns; a nonunique index does not. A primary key automatically creates a unique index.

A composite index is usable only when the leading column appears in the predicate; skipping the leading column usually prevents index use.

A function-based index stores an expression result and enables index access for predicates on that expression.

CREATE SYNONYM creates an alias for a table, view, or other object; a public synonym is visible to all users, a private one only to its owner.

A sequence generates unique numbers via NEXTVAL and CURRVAL; CURRVAL is valid only in a session that has already called NEXTVAL.

Sequence options include START WITH, INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, and CACHE; CYCLE wraps after MAXVALUE.

Dropping a table drops its indexes and triggers but not its synonyms; dependent views become invalid.

A simple view is based on one table and supports DML if it contains no group functions, GROUP BY, DISTINCT, or expressions.

A complex view contains joins, group functions, or set operators and is generally not directly updatable.

WITH CHECK OPTION prevents inserting or updating rows that the view's WHERE clause would not return.

WITH READ ONLY blocks all DML through the view.

CREATE OR REPLACE VIEW changes a view definition without dropping it; dependent objects may become invalid.

A private temporary table (ORA$PTT_) is session-private, requires the ORA$PTT_ prefix, and is dropped at commit or session end.

A global temporary table persists its definition; rows are private to each session and deleted at commit or session end per ON COMMIT.

USER_ views show objects owned by the current user; ALL_ views show accessible objects; DBA_ views show all objects and require privileges.

USER_TABLES, USER_VIEWS, USER_INDEXES, USER_SEQUENCES, and USER_SYNONYMS list the corresponding objects.

USER_TAB_COLUMNS stores column names, data types, and nullability; USER_CONSTRAINTS and USER_CONS_COLUMNS describe constraints.

USER_OBJECTS lists every object with OBJECT_NAME, OBJECT_TYPE, STATUS, and timestamps; STATUS can be VALID or INVALID.

USER_DEPENDENCIES records which objects reference which; it is used to find views invalidated by a table change.

USER_TAB_COMMENTS and USER_COL_COMMENTS hold comments added with the COMMENT statement.

V$ views are dynamic performance views stored in memory; DICTIONARY and DICT_COLUMNS describe the data dictionary itself.