Oracle 1Z0-182 cheat sheet

A relational table stores rows (tuples) in unordered sets; a primary key uniquely identifies each row, a foreign key references a primary key or unique key in another table.

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

NULL means unknown: any comparison with NULL yields UNKNOWN, so use IS NULL / IS NOT NULL; NULL is never equal to NULL.

Oracle object names are up to 128 bytes, unquoted names are case-insensitive and stored uppercase, and must begin with a letter.

Character literals use single quotes; double quotes delimit identifiers. String concatenation uses || (or CONCAT).

Oracle 12c+ supports VARCHAR2, NUMBER, DATE, TIMESTAMP, CLOB, BLOB; VARCHAR2 length is in bytes by default (CHAR semantics optional).

The dual table is a one-row, one-column dummy table used for expressions: SELECT sysdate FROM dual.

SQL statements are terminated by ; in SQL*Plus/SQL Developer; PL/SQL blocks require a terminating / on its own line.

Logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY; column aliases are therefore not usable in WHERE but are usable in ORDER BY.

WHERE operators: =, <>, !=, >, <, >=, <=, BETWEEN x AND y (inclusive), IN (list), LIKE (with % and _ wildcards), IS NULL.

LIKE escape: use ESCAPE clause, e.g. WHERE name LIKE 'A_%' ESCAPE '' to match a literal underscore.

ORDER BY defaults to ASC; DESC applies per column. NULLs sort last in ASC and first in DESC by default; NULLS FIRST/LAST overrides.

Row limiting: FETCH FIRST n ROWS ONLY, OFFSET m ROWS FETCH NEXT n ROWS ONLY (12c+), and the legacy ROWNUM <= n filter.

ROWNUM is assigned after filtering but before ORDER BY, so ORDER BY with ROWNUM requires an inline view to limit correctly.

DISTINCT applies to the entire select list, not to a single column; SELECT DISTINCT deptno, job returns distinct combinations.

Single-row functions: UPPER, LOWER, INITCAP, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE, ROUND, TRUNC, MOD, ABS, CEIL, FLOOR.

Date functions: SYSDATE, CURRENT_DATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, TRUNC(date,'MM'), EXTRACT.

NVL(a,b) replaces NULL; NVL2(a,b,c) returns b if a is not null else c; NULLIF(a,b) returns NULL if a=b; COALESCE returns first non-null.

CASE and DECODE provide conditional logic; CASE is ANSI-standard and supports any comparison, DECODE uses equality only.

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

GROUP BY groups rows before aggregation; every non-aggregate column in SELECT must appear in GROUP BY.

HAVING filters groups after aggregation; WHERE filters rows before aggregation. A HAVING clause may reference group functions.

AVG(col) ignores NULL rows, so AVG(NVL(col,0)) differs from AVG(col) when NULLs exist.

INNER JOIN returns only matching rows; LEFT OUTER JOIN keeps all left rows with NULLs on the right; RIGHT OUTER JOIN is the reverse; FULL OUTER JOIN keeps all rows from both sides.

Oracle (+) outer-join operator goes on the side lacking rows: WHERE a.deptno = b.deptno(+) keeps all a rows. Do not mix (+) with ANSI JOIN syntax.

A CROSS JOIN produces the Cartesian product; a join with no join condition produces the same result.

NATURAL JOIN joins on all same-named columns; USING (col) joins on a named column and suppresses duplication of that column.

Self join: alias the same table twice (e.g., employee e JOIN employee m ON e.mgr = m.emp_id) to relate rows within one table.

Single-row subqueries use =, <, >; multi-row subqueries require IN, ANY, ALL, or EXISTS.

Correlated subqueries reference the outer query and are re-evaluated per outer row; EXISTS stops at the first match.

Scalar subqueries in the SELECT list must return at most one row and one column, or a runtime error occurs.

INSERT INTO t (cols) VALUES (...); multi-row insert: INSERT ALL ... or INSERT INTO t SELECT ... FROM source.

INSERT with a subquery omits VALUES: INSERT INTO t (a,b) SELECT x,y FROM src WHERE ... .

UPDATE t SET col = value WHERE condition; omitting WHERE updates every row. UPDATE can set a column from a correlated subquery.

DELETE FROM t WHERE condition; omitting WHERE deletes all rows. DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot.

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

DML changes are not visible to other sessions until COMMIT; the session sees its own uncommitted changes.

A read-consistent query never sees uncommitted changes; row locks from DML block conflicting DML in other sessions until commit/rollback.

MERGE cannot update a column used in the ON clause; the UPDATE SET clause must exclude join columns.

CREATE TABLE t (col datatype [DEFAULT val] [constraint], ...); inline constraints are column-level, table-level constraints follow the column list.

Constraint types: NOT NULL (column only), UNIQUE, PRIMARY KEY (implies NOT NULL + UNIQUE), FOREIGN KEY ... REFERENCES, CHECK.

ON DELETE CASCADE removes child rows; ON DELETE SET NULL nulls the foreign key; default is ON DELETE RESTRICT (error if children exist).

A CHECK constraint cannot reference other rows, other tables, or use SYSDATE/ROWNUM; it may use IN, BETWEEN, and IS NOT NULL.

ALTER TABLE t ADD / MODIFY / DROP COLUMN, ADD CONSTRAINT, DROP CONSTRAINT ... CASCADE, DISABLE/ENABLE CONSTRAINT.

DROP TABLE removes structure and data; TRUNCATE TABLE removes all rows and is DDL; both cannot be rolled back (TRUNCATE is auto-commit).

CREATE VIEW v AS SELECT ...; views can be simple (one table, no functions/grouping) or complex; WITH CHECK OPTION prevents rows that violate the view's WHERE.

CREATE OR REPLACE VIEW replaces a view; DROP VIEW removes it. A view with a GROUP BY is read-only for DML.

CREATE SEQUENCE seq START WITH n INCREMENT BY m [MAXVALUE|NOMAXVALUE] [CYCLE|NOCYCLE] [CACHE n|NOCACHE].

NEXTVAL generates the next value; CURRVAL returns the last NEXTVAL for the session. CURRVAL errors if NEXTVAL was never called in that session.

Sequence values are non-transactional: a rollback does not return a used NEXTVAL, so gaps are normal.

CREATE [PUBLIC] SYNONYM name FOR schema.object; a public synonym is visible to all users; a private synonym shadows a public one.

Synonyms are pointers only: dropping the synonym leaves the underlying object; dropping the object leaves the synonym invalid.

CREATE INDEX idx ON t(col); B-tree indexes speed equality/range lookups; a UNIQUE index enforces uniqueness.

A function-based index (e.g., UPPER(name)) supports queries that use the same expression; a bitmap index suits low-cardinality columns.

Indexes are maintained automatically on INSERT/UPDATE/DELETE; a unique constraint or primary key creates a unique index automatically.

CREATE USER u IDENTIFIED BY pwd [DEFAULT TABLESPACE ts] [QUOTA n ON ts] [TEMPORARY TABLESPACE ts] [PROFILE p].

ALTER USER u IDENTIFIED BY newpwd / QUOTA / ACCOUNT LOCK|UNLOCK / PASSWORD EXPIRE changes account attributes.

DROP USER u [CASCADE] removes the user; CASCADE also drops the user's schema objects.

A role bundles privileges: CREATE ROLE r; GRANT priv TO r; GRANT r TO user. Roles simplify privilege administration.

Data dictionary views: USER_TABLES, ALL_TABLES, DBA_TABLES; USER_CONSTRAINTS, USER_VIEWS, USER_SEQUENCES, USER_INDEXES, USER_SYNONYMS.

SQL*Plus commands (DESCRIBE, SET, SPOOL, /) are client commands, not SQL; they do not require a semicolon to execute in the same way.

Data Pump (expdp/impdp) exports/imports schema objects; SQL*Loader loads external files; both are command-line Oracle tools.

A user needs CREATE SESSION to connect and CREATE TABLE to create objects in their own schema.

System privileges act on the database (CREATE TABLE, CREATE SESSION); object privileges act on a specific object (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, INDEX).

GRANT SELECT, UPDATE(col) ON schema.t TO user|role [WITH GRANT OPTION]; WITH GRANT OPTION lets the grantee pass the privilege on.

REVOKE privilege ON object FROM user|role [CASCADE CONSTRAINTS]; revoking a granted role removes its privileges.

Object privileges can be granted per column for INSERT, UPDATE, and REFERENCES; SELECT can be granted on a view to limit row/column exposure.

Transaction control: COMMIT makes changes permanent; ROLLBACK undoes all changes since the last commit; SAVEPOINT sp names a point for ROLLBACK TO sp.

DDL statements (CREATE, ALTER, DROP, TRUNCATE) issue an implicit commit before and after execution.

A session's transaction starts with the first DML and ends at COMMIT or ROLLBACK; a normal exit commits, an abnormal exit rolls back.

READ COMMITTED (default) sees only committed data; SERIALIZABLE gives statement-level read consistency; SET TRANSACTION ISOLATION LEVEL sets it.