SQL & PL/SQL Cheatsheet — Basic →
Advanced
Concise, exam-ready reference for SQL (DDL/DML/TCL/DCL, joins, subqueries, set ops,
windows, transactions) and PL/SQL (blocks, cursors, exceptions, procedures, packages,
triggers, bulk). Carefully formatted tables fit within page width with wrapped content.
1. SQL — Core Concepts
<b>SQL Dialects</b>: ANSI SQL is the baseline; vendors add features (Oracle,
PostgreSQL, MySQL).
<b>Categories</b>: DDL (CREATE/ALTER/DROP/TRUNCATE), DML
(SELECT/INSERT/UPDATE/DELETE), TCL (COMMIT/ROLLBACK/SAVEPOINT), DCL
(GRANT/REVOKE).
<b>Execution Order (SELECT)</b>: FROM → WHERE → GROUP BY → HAVING → SELECT
→ DISTINCT → ORDER BY → LIMIT/OFFSET.
SQL Categories (Quick Reference)
Category Typical Commands Notes / Effects
DDL CREATE, ALTER, DROP, Define/modify schema objects; often
TRUNCATE auto-commit depending on RDBMS
DML SELECT, INSERT, UPDATE, Query and modify data rows
DELETE, MERGE
TCL COMMIT, ROLLBACK, SAVEPOINT Transaction control; atomically group DML
DCL GRANT, REVOKE Permissions/security for users/roles
Common Data Types (Generic)
Type Examples Notes
Numeric INT, BIGINT, DECIMAL(p,s), Use DECIMAL/NUMERIC for money; avoid
NUMERIC float rounding errors
Character CHAR(n), VARCHAR(n), TEXT CHAR fixed-length; VARCHAR
variable-length
Date/Time DATE, TIME, TIMESTAMP Timezone types vary by DB (e.g.,
timestamptz)
Boolean BOOLEAN Some engines emulate with 1/0 or 'Y'/'N'
Binary BLOB, BYTEA, VARBINARY Store files; prefer object storage when
large
Join Types & Use Cases
Page 1
Join Definition Typical Use
INNER JOIN Rows matching in both tables per join Most common; fetch intersecting
predicate data
LEFT OUTER All left rows + matching right rows (NULL Optional relationship from left to
JOIN when no match) right
RIGHT OUTER All right rows + matching left rows Less common; mirror of LEFT
JOIN
FULL OUTER All rows from both, NULL when no match Unions left/right with alignment
JOIN
CROSS JOIN Cartesian product of two tables Generate combinations (use
cautiously)
SELF JOIN Join a table to itself with aliasing Hierarchies,
predecessor/successor
Set Operations (Rows must be union-compatible)
Operator Behavior Duplicates
UNION Union of both sets Removes duplicates
UNION ALL Union with duplicates Keeps duplicates (faster)
INTERSECT Common rows present in both Removes duplicates
EXCEPT / MINUS Left minus right (dialect dependent) Removes duplicates
Aggregate vs Window Functions
Concept Aggregate Window
Scope Groups rows into one result Computes per row over a frame
Examples COUNT(*), SUM(sales), AVG(age) ROW_NUMBER() OVER(PARTITION BY
d ORDER BY s), SUM(s) OVER(...)
Use Case Totals per group Running totals, ranking, lag/lead
comparisons
Example: Window Functions
SELECT emp_id, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary
DESC) AS rn, SUM(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum FROM employees;
Subqueries
Type Description Example (pattern)
Scalar Returns single value WHERE e.dept_id = (SELECT id FROM dept
WHERE name='Sales')
Page 2
Type Description Example (pattern)
Row/Column Returns one row (or one column) WHERE e.dept_id IN (SELECT id FROM dept
for IN/EXISTS WHERE region='APAC')
Correlated References outer query columns WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.emp_id = [Link])
Indexing — Basics
Index Type When to Use Notes
B-tree Equality & range on selective Default index in many DBs
columns
Hash Equality lookups only Fast exact matches; not range
Bitmap (Oracle) Low-cardinality columns in Efficient for complex filters; heavy
warehouses on DML
Full-text Text search Use dedicated search when at scale
(e.g., ES)
Transactions & Isolation Levels (ANSI)
Level Phenomena Prevented Notes
READ UNCOMMITTED Dirty reads only partially prevented Rarely used; many DBs treat
as READ COMMITTED
READ COMMITTED Prevents dirty reads Default in many systems
REPEATABLE READ Prevents dirty + non-repeatable reads Phantoms may occur unless
engine supports
SERIALIZABLE Prevents dirty + non-repeatable + Strongest; can reduce
phantom concurrency
Example: Transaction Control
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts
SET balance = balance + 100 WHERE id = 2; COMMIT; -- or ROLLBACK
2. PL/SQL — Essentials (Oracle)
PL/SQL extends SQL with procedural features. A block: DECLARE (optional) → BEGIN →
EXCEPTION (optional) → END;
Variables use anchors: v_salary [Link]%TYPE; rows: v_emp
employees%ROWTYPE.
Parameter modes: IN (default), OUT, IN OUT; use NOCOPY hint for performance
(pass-by-reference).
Basic PL/SQL Block
DECLARE v_total NUMBER := 0; BEGIN SELECT SUM(salary) INTO v_total FROM employees
WHERE dept_id = 10; DBMS_OUTPUT.PUT_LINE('Total=' || v_total); EXCEPTION WHEN
Page 3
NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No rows'); WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); END;
Cursors (Implicit vs Explicit)
Type How Pros / Cons Example
Implicit SELECT ... INTO ...; Simple; less control SELECT COUNT(*)
FOR ... IN cursor loop INTO v FROM t;
Explicit DECLARE cursor; Fine control; more code OPEN c; FETCH c
OPEN; FETCH; CLOSE INTO ...; CLOSE c;
Explicit Cursor Loop
DECLARE CURSOR c IS SELECT empno, ename FROM emp WHERE deptno = 10; v_emp
emp%ROWTYPE; BEGIN OPEN c; LOOP FETCH c INTO v_emp.empno, v_emp.ename; EXIT
WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.empno || ' ' || v_emp.ename); END
LOOP; CLOSE c; END;
Bulk Processing
Use BULK COLLECT to fetch many rows into collections; use FORALL for bulk DML to
reduce context switches.
Save exceptions with FORALL ... SAVE EXCEPTIONS and inspect
SQL%BULK_EXCEPTIONS.
Bulk Features
Feature Usage Notes
BULK COLLECT SELECT ... BULK COLLECT INTO Fetch many rows at once; limit
collection memory with FETCH ... LIMIT
FORALL FORALL i IN Bulk DML; much faster than
[Link]..[Link] row-by-row
DML_stmt
SAVE EXCEPTIONS FORALL ... SAVE EXCEPTIONS Collect per-row errors; iterate
SQL%BULK_EXCEPTIONS
BULK COLLECT + FORALL Example
DECLARE TYPE t_ids IS TABLE OF [Link]%TYPE; l_ids t_ids; BEGIN SELECT empno
BULK COLLECT INTO l_ids FROM emp WHERE deptno = 10; FORALL i IN 1..l_ids.COUNT
UPDATE emp SET sal = sal * 1.05 WHERE empno = l_ids(i); END;
Exceptions & Error Handling
Technique Description Snippet
RAISE_APPLICATION_ER Custom error with code RAISE_APPLICATION_ERROR(-200
ROR -20000..-20999 01, 'Invalid state');
PRAGMA Map ORA-nnnnn to a named PRAGMA EXCEPTION_INIT(e_dup,
EXCEPTION_INIT exception -00001);
Page 4
Technique Description Snippet
OTHERS + Catch-all with diagnostics WHEN OTHERS THEN
SQLCODE/SQLERRM log(SQLCODE, SQLERRM);
Program Units: Procedures, Functions, Packages
Procedures perform actions; functions return a value and should be side-effect free
when called from SQL.
Packages group related specs & bodies; allow encapsulation, overloading, state (use
sparingly).
Procedures vs Functions
Aspect Procedure Function
Return No return value Returns a value (RETURN)
Use in SQL Cannot be used in SQL directly Can be used if deterministic, no
side effects (no DML on tables)
Parameters IN/OUT/IN OUT IN (usually); OUT via return
Procedure & Function
CREATE OR REPLACE PROCEDURE raise_salary(p_empno IN [Link]%TYPE, p_pct IN
NUMBER) AS BEGIN UPDATE emp SET sal = sal * (1 + p_pct) WHERE empno = p_empno;
END; / CREATE OR REPLACE FUNCTION yearly_comp(p_empno IN [Link]%TYPE)
RETURN NUMBER IS v_total NUMBER; BEGIN SELECT sal*12 + NVL(comm,0) INTO v_total
FROM emp WHERE empno = p_empno; RETURN v_total; END;
Triggers
Row-level vs statement-level; BEFORE vs AFTER; timing points for
INSERT/UPDATE/DELETE.
Avoid business logic in triggers when possible to keep flows explicit.
Trigger Types
Scope Timing Common Use
Statement-level BEFORE/AFTER Audit/logging; enforce rules
Row-level BEFORE EACH ROW / AFTER Derive column values; maintain
EACH ROW denormalized data
Audit Trigger (Row-level)
CREATE OR REPLACE TRIGGER emp_audit_trg AFTER UPDATE OF sal ON emp FOR EACH
ROW BEGIN INSERT INTO emp_audit(empno, old_sal, new_sal, changed_at) VALUES
(:[Link], :[Link], :[Link], SYSTIMESTAMP); END;
Dynamic SQL & Security
Page 5
Use EXECUTE IMMEDIATE for dynamic DDL/DML; bind variables to prevent SQL injection.
Grant least privileges; avoid definer-rights pitfalls unless necessary.
Dynamic SQL Patterns
Pattern Example Notes
EXECUTE IMMEDIATE EXECUTE IMMEDIATE 'ALTER TABLE t DDL auto-commits in many
(DDL/DML) ADD (col NUMBER)'; cases
Bind variables EXECUTE IMMEDIATE 'UPDATE emp Prevents injection; reuses
SET sal = :x WHERE empno = :id' cursors
USING v_sal, v_empno;
RETURNING INTO EXECUTE IMMEDIATE 'INSERT INTO t(x) Fetch generated values
VALUES (:1) RETURNING id INTO :2'
USING v, OUT v_id;
Performance & Best Practices
Prefer set-based SQL over row-by-row loops ('slow-by-slow').
Create selective indexes; avoid functions on indexed columns in predicates unless using
function-based indexes.
Analyze execution plans; keep statistics current.
In PL/SQL, use bulk operations and bind variables; minimize context switches.
Page 6