Oracle 1Z0-071 cheat sheet

A relational table stores rows (records) and columns (attributes); each column has a data type and each row is uniquely identified by a primary key.

The SELECT statement's logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY; only ORDER BY can reference SELECT aliases.

The SELECT list can contain expressions, literals, column aliases (AS or space), and the DISTINCT keyword to eliminate duplicate result rows.

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

NULL represents unknown or missing data; any arithmetic expression involving NULL evaluates to NULL, and NULL is not equal to any value including another NULL.

The dual table is a one-row, one-column dummy table used to evaluate expressions or call functions: SELECT sysdate FROM dual;

Column aliases containing spaces or special characters must be enclosed in double quotes; unquoted aliases are stored in uppercase in the data dictionary.

The concatenation operator || joins two character strings; it treats NULL as an empty string, so 'A' || NULL returns 'A'.

The WHERE clause filters rows before grouping; it cannot contain group functions (use HAVING for those).

Comparison operators include =, !=, <>, >, >=, <, <=; logical operators AND, OR, NOT combine predicates, with NOT evaluated before AND, and AND before OR.

BETWEEN x AND y is inclusive of both endpoints; IN (list) tests membership; LIKE uses % (any sequence) and _ (single character) wildcards, with ESCAPE to treat them literally.

IS NULL and IS NOT NULL test for nulls; = NULL and != NULL never return true because NULL is not equal to anything.

ORDER BY sorts the final result set; ASC is the default, DESC reverses order, and multiple columns can be specified with individual directions.

NULLs sort last in ascending order and first in descending order by default; NULLS FIRST or NULLS LAST overrides this behavior.

ORDER BY can reference a column alias, an expression, or a column position number (e.g., ORDER BY 2 DESC).

The FETCH FIRST n ROWS ONLY clause (Oracle 12c+) limits rows returned; it is applied after ORDER BY.

Character functions: UPPER, LOWER, INITCAP change case; SUBSTR(str, start, length) extracts; INSTR finds position; LENGTH returns character count; LPAD/RPAD pad strings; TRIM removes characters.

Numeric functions: ROUND(n, d) rounds to d decimals; TRUNC(n, d) truncates; MOD(m, n) returns remainder; ABS, CEIL, FLOOR, POWER, SQRT perform mathematical operations.

Date functions: SYSDATE returns current date/time; MONTHS_BETWEEN(d1, d2) returns number of months; ADD_MONTHS(date, n) shifts months; NEXT_DAY(date, 'DAY') returns next named weekday; LAST_DAY returns last day of month.

Conversion functions: TO_CHAR, TO_DATE, TO_NUMBER convert between data types; TO_CHAR(date, 'format') uses format models like YYYY, MM, DD, HH24:MI:SS.

General functions: NVL(expr, replacement) substitutes NULL; NVL2(expr, if_not_null, if_null) returns different values; NULLIF(a, b) returns NULL if equal else a; COALESCE returns first non-null argument.

CASE and DECODE provide conditional logic; CASE is ANSI-compliant and can be used in both SQL and PL/SQL, while DECODE is Oracle-specific and performs equality checks only.

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

Implicit conversion occurs when Oracle automatically converts data types (e.g., comparing a number to a string); explicit conversion uses TO_CHAR, TO_DATE, TO_NUMBER.

TO_CHAR(number, 'format') uses format elements such as 9 (digit), 0 (leading zero), $ (currency), L (local currency), . (decimal), , (group separator).

TO_DATE(char, 'format') converts a string to a date; if the format is omitted, the default date format (typically DD-MON-RR) is used.

The RR date format interprets two-digit years: 00-49 as 2000-2049 and 50-99 as 1950-1999; YY uses the current century.

TO_NUMBER(char, 'format') converts a formatted string to a number; it raises ORA-01722 if the string does not match the format.

CASE expression syntax: CASE WHEN condition THEN result [WHEN ...] [ELSE result] END; it can be used in SELECT, WHERE, and ORDER BY.

DECODE(expr, search1, result1 [, search2, result2, ...] [, default]) returns the result for the first matching search; if none match, returns default or NULL.

NVL and COALESCE handle NULLs: NVL takes exactly two arguments; COALESCE takes two or more and returns the first non-null value.

Group functions (AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE) operate on sets of rows and return one result per group; they ignore NULLs except COUNT(*).

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

The GROUP BY clause groups rows with the same values into summary rows; every column in the SELECT list that is not inside a group function must appear in the GROUP BY clause.

The HAVING clause filters groups after grouping; it can contain group functions, whereas WHERE cannot.

Group functions can be nested (e.g., MAX(AVG(salary))) but only group functions can be nested inside other group functions.

ROLLUP and CUBE generate subtotals and grand totals; GROUPING SETS specifies multiple grouping sets in one query.

The GROUPING function distinguishes NULLs produced by ROLLUP/CUBE from stored NULLs; it returns 1 for aggregated rows and 0 for detail rows.

A join combines rows from two or more tables; inner joins return only matching rows; outer joins (LEFT, RIGHT, FULL) include non-matching rows with NULLs.

Oracle's traditional join syntax uses the WHERE clause with equality and the (+) operator for outer joins; ANSI syntax uses JOIN ... ON or USING.

A self-join joins a table to itself using two different aliases; it is used to compare rows within the same table.

A cross join produces a Cartesian product of all rows; it occurs when a join condition is omitted or when CROSS JOIN is specified.

A subquery is a SELECT statement nested inside another SQL statement; it can return a single row (scalar), multiple rows (IN, ANY, ALL), or multiple columns (used with EXISTS).

Correlated subqueries reference columns from the outer query and are evaluated once per outer row; they can be used with EXISTS, NOT EXISTS, and comparison operators.

The WITH clause (subquery factoring) defines named temporary result sets that can be referenced multiple times in the main query.

A natural join automatically joins tables on all columns with the same name and data type; it should be used cautiously because it can produce unexpected results.

Set operators combine the results of two or more SELECT statements: UNION (all distinct rows), UNION ALL (all rows including duplicates), INTERSECT (common rows), MINUS (rows in first query not in second).

The SELECT statements must have the same number of columns and compatible data types; column names are taken from the first SELECT.

ORDER BY can appear only once at the end of the compound query and applies to the entire result set.

UNION removes duplicates by sorting the result; UNION ALL is faster because it does not eliminate duplicates.

INTERSECT returns rows that appear in both queries; MINUS returns rows from the first query that are not in the second.

Parentheses can change the order of evaluation when mixing set operators; by default, INTERSECT has higher precedence than UNION and MINUS.

Set operators treat NULLs as equal for the purpose of duplicate elimination; two NULLs are considered duplicates.

INSERT adds rows: INSERT INTO table (columns) VALUES (values); a subquery can supply multiple rows: INSERT INTO table SELECT ...

UPDATE modifies existing rows: UPDATE table SET column = value [WHERE condition]; omitting WHERE updates all rows.

DELETE removes rows: DELETE FROM table [WHERE condition]; omitting WHERE deletes all rows but the table structure remains.

TRUNCATE is DDL, not DML; it removes all rows quickly, cannot be rolled back (unless in a transaction that is rolled back before commit? Actually TRUNCATE is DDL and commits implicitly), and does not fire triggers.

Transactions are controlled with COMMIT (make changes permanent), ROLLBACK (undo changes), and SAVEPOINT (set a point to roll back to).

The MERGE statement performs insert or update operations in a single statement based on a condition.

DML statements acquire row-level locks; they are not automatically committed; a transaction ends with COMMIT, ROLLBACK, or a DDL statement (which issues an implicit commit).

CREATE TABLE defines a new table with columns, data types, and constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK).

ALTER TABLE modifies table structure: ADD, MODIFY, DROP COLUMN, ADD CONSTRAINT, DROP CONSTRAINT, ENABLE/DISABLE CONSTRAINT.

DROP TABLE removes the table and its data; the table can be recovered from the recycle bin with FLASHBACK TABLE unless PURGE was specified.

Constraints enforce data integrity: PRIMARY KEY uniquely identifies each row; FOREIGN KEY references a primary key or unique key in another table; CHECK validates a condition.

The DEFAULT clause provides a value when a column is omitted in an INSERT; it does not override explicit NULLs.

CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it with query results; it does not copy constraints unless specified.

A schema is a collection of database objects owned by a user; objects include tables, views, indexes, sequences, and synonyms.

A view is a stored query that acts as a virtual table; it can be simple (one table) or complex (joins, group functions); it does not store data unless materialized.

CREATE OR REPLACE VIEW defines or replaces a view; WITH CHECK OPTION prevents DML that would make rows invisible through the view; WITH READ ONLY prohibits DML.

A sequence generates unique numeric values: CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1; use NEXTVAL and CURRVAL (after NEXTVAL in session).

A synonym is an alias for a database object; a private synonym is owned by a user, a public synonym is available to all users.

An index speeds up retrieval of rows based on column values; it is created automatically for PRIMARY KEY and UNIQUE constraints unless an index already exists.

CREATE INDEX idx ON table (column); indexes can be unique, composite, function-based, or bitmap; they add overhead to DML operations.

A materialized view stores the result of a query physically and can be refreshed on demand or on commit.

Privileges are rights to execute SQL statements or access objects; system privileges (e.g., CREATE SESSION, CREATE TABLE) allow actions on the database; object privileges (e.g., SELECT, INSERT, UPDATE, DELETE) allow actions on specific objects.

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

REVOKE privilege FROM user; revoking a privilege also revokes privileges granted by that user with the grant option.

Roles are named groups of privileges; CREATE ROLE role; GRANT privilege TO role; GRANT role TO user; roles simplify privilege management.

The PUBLIC role is a default role granted to all users; granting a privilege to PUBLIC makes it available to every user.

A user can be created with CREATE USER username IDENTIFIED BY password; the user needs CREATE SESSION to connect and quota on a tablespace to create objects.

The data dictionary views DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS show system privileges, object privileges, and role assignments respectively.

Oracle DATE stores date and time to the second; TIMESTAMP stores fractional seconds; TIMESTAMP WITH TIME ZONE stores time zone information.

SYSDATE returns the current date and time from the database server; SYSTIMESTAMP returns the current timestamp with time zone.

Date arithmetic: date + number adds days; date - date returns the number of days between dates; date + hours/24 adds hours.

MONTHS_BETWEEN(date1, date2) returns the number of months between two dates; ADD_MONTHS(date, n) adds n months; NEXT_DAY(date, 'day') returns the next specified weekday.

LAST_DAY(date) returns the last day of the month; TRUNC(date, 'format') truncates the date to the specified unit (e.g., 'MM' for month).

The default date format is set by the NLS_DATE_FORMAT parameter; TO_CHAR and TO_DATE should specify explicit formats to avoid ambiguity.

INTERVAL literals represent durations: INTERVAL '1' YEAR, INTERVAL '2' MONTH, INTERVAL '3' DAY, INTERVAL '4' HOUR, INTERVAL '5' MINUTE, INTERVAL '6' SECOND.

REGEXP_LIKE(source, pattern [, match_parameter]) returns TRUE if the pattern matches; it is used in WHERE clauses for pattern matching.

REGEXP_SUBSTR(source, pattern [, position [, occurrence [, match_parameter]]]) returns the substring that matches the pattern.

REGEXP_INSTR returns the position of the match; REGEXP_REPLACE replaces matched substrings with a replacement string.

REGEXP_COUNT returns the number of occurrences of a pattern in a string.

Match parameters include 'i' (case-insensitive), 'c' (case-sensitive), 'n' (dot matches newline), 'm' (multiline mode).

Regular expression metacharacters: ^ (start), $ (end), . (any character), * (zero or more), + (one or more), ? (zero or one), [] (character class), | (alternation), () (grouping).

POSIX character classes: [:alpha:], [:digit:], [:space:], [:upper:], [:lower:], [:punct:] can be used inside brackets.

ROWNUM is a pseudocolumn that assigns a number to each row returned by a query; it is assigned before ORDER BY, so ordering then limiting requires a subquery.

The FETCH FIRST clause (12c+) or ROWNUM can be used to limit rows; ROWNUM <= n works in the WHERE clause.

The DUAL table is a special one-row, one-column table for evaluating expressions or selecting from a sequence.

Hierarchical queries use CONNECT BY PRIOR and START WITH to traverse parent-child relationships; LEVEL pseudocolumn indicates depth.

The MERGE statement combines INSERT and UPDATE operations based on a condition, often used for upserts.

Flashback Query (AS OF TIMESTAMP) retrieves data as it existed at a past time, using undo data.

The WITH clause (subquery factoring) defines named subqueries that can be referenced multiple times, improving readability and performance.