SQLMentor
Oracle SQL Cheat Sheet
Every Oracle SQL pattern you reach for daily, on one page: clause order, operators, string/number/date functions, joins, DDL, DML, MERGE, pagination, and window functions. Bookmark it, or work through the underlying concepts in the free Oracle SQL tutorial.
Query clause order
SQL is written top to bottom but the engine evaluates it in a different order — this is the single most useful thing to memorise about SQL.
| Written order | Clause | Runs |
|---|---|---|
| 1 | SELECT | 5th — after grouping/filtering |
| 2 | FROM | 1st — the source rows |
| 3 | WHERE | 2nd — filters rows |
| 4 | GROUP BY | 3rd — forms groups |
| 5 | HAVING | 4th — filters groups |
| 6 | ORDER BY | 6th — sorts the final result |
Comparison & logical operators
| Operator | Meaning | Example |
|---|---|---|
| =, !=, <> | equals, not equal | salary = 5000 |
| >, <, >=, <= | greater/less than (or equal) | hire_date >= DATE '2020-01-01' |
| BETWEEN a AND b | inclusive range | salary BETWEEN 3000 AND 8000 |
| IN (list) | matches any value in the list | department_id IN (10, 20, 30) |
| LIKE 'pattern' | pattern match | last_name LIKE 'K%' |
| IS NULL / IS NOT NULL | NULL check — never use = NULL | commission_pct IS NULL |
| AND / OR / NOT | combine conditions | salary > 5000 AND department_id = 10 |
LIKE wildcards
| Wildcard | Matches | Example |
|---|---|---|
| % | zero or more characters | 'K%' → Kim, King, K |
| _ | exactly one character | '_a%' → any name with 'a' as 2nd letter |
| ESCAPE 'c' | treat a wildcard as literal | LIKE '50\%' ESCAPE '\' |
String functions
| Function | Purpose | Example |
|---|---|---|
| UPPER(x) / LOWER(x) | case conversion | UPPER('abc') → 'ABC' |
| INITCAP(x) | capitalise each word | INITCAP('john doe') → 'John Doe' |
| LENGTH(x) | character count | LENGTH('Oracle') → 6 |
| SUBSTR(x, start, len) | extract substring (1-based) | SUBSTR('Oracle', 1, 3) → 'Ora' |
| INSTR(x, sub) | position of substring | INSTR('Oracle', 'cle') → 4 |
| TRIM(x) / LTRIM / RTRIM | remove leading/trailing spaces | TRIM(' hi ') → 'hi' |
| LPAD / RPAD(x, len, ch) | pad to a fixed width | LPAD('7', 3, '0') → '007' |
| REPLACE(x, old, new) | substring replacement | REPLACE('2026', '20', '19') → '1926' |
| x || y or CONCAT(x, y) | concatenation | first_name || ' ' || last_name |
Number functions
| Function | Purpose | Example |
|---|---|---|
| ROUND(x, d) | round to d decimal places | ROUND(3.14159, 2) → 3.14 |
| TRUNC(x, d) | truncate (no rounding) | TRUNC(3.999, 0) → 3 |
| MOD(x, y) | remainder | MOD(10, 3) → 1 |
| CEIL(x) / FLOOR(x) | round up / down to integer | CEIL(4.1) → 5, FLOOR(4.9) → 4 |
| ABS(x) | absolute value | ABS(-7) → 7 |
| POWER(x, y) | exponentiation | POWER(2, 10) → 1024 |
Date functions
| Function | Purpose | Example |
|---|---|---|
| SYSDATE | current date and time | SELECT SYSDATE FROM dual; |
| ADD_MONTHS(d, n) | add/subtract whole months | ADD_MONTHS(hire_date, 6) |
| MONTHS_BETWEEN(d1, d2) | months between two dates | MONTHS_BETWEEN(SYSDATE, hire_date) |
| LAST_DAY(d) | last day of the month | LAST_DAY(SYSDATE) |
| NEXT_DAY(d, 'day') | next occurrence of a weekday | NEXT_DAY(SYSDATE, 'MONDAY') |
| TRUNC(d, 'MM'/'YYYY') | truncate to month/year start | TRUNC(SYSDATE, 'MM') |
| EXTRACT(field FROM d) | pull out year/month/day | EXTRACT(YEAR FROM hire_date) |
Conversion & conditional functions
| Function | Purpose | Example |
|---|---|---|
| TO_CHAR(d, fmt) | date/number → formatted string | TO_CHAR(SYSDATE, 'YYYY-MM-DD') |
| TO_DATE(s, fmt) | string → date | TO_DATE('2026-07-16', 'YYYY-MM-DD') |
| TO_NUMBER(s) | string → number | TO_NUMBER('123.45') |
| NVL(x, default) | replace NULL with a value | NVL(commission_pct, 0) |
| NVL2(x, ifNotNull, ifNull) | branch on NULL | NVL2(commission_pct, 'Yes', 'No') |
| COALESCE(a, b, c, ...) | first non-NULL value | COALESCE(commission_pct, bonus, 0) |
| DECODE(x, v1, r1, v2, r2, def) | Oracle's original CASE | DECODE(job_id, 'IT_PROG', 'Tech', 'Other') |
| CASE WHEN ... THEN ... END | standard conditional logic | CASE WHEN salary > 10000 THEN 'High' ELSE 'Std' END |
Aggregate functions
All aggregates except COUNT(*) ignore NULLs — this trips up more interview questions than any other single rule.
| Function | Purpose | Example |
|---|---|---|
| COUNT(*) / COUNT(col) | row count / non-NULL count | COUNT(commission_pct) |
| SUM(x) / AVG(x) | total / average | AVG(salary) |
| MIN(x) / MAX(x) | smallest / largest value | MAX(salary) |
| LISTAGG(x, sep) WITHIN GROUP (ORDER BY y) | concatenate group values | LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) |
Joins
The (+) syntax still appears in older Oracle code and on some exams, but ANSI JOIN ... ON is the modern standard — prefer it in new code.
| Join type | Keeps | Syntax |
|---|---|---|
| Inner join | only matching rows from both tables | FROM a JOIN b ON a.id = b.id |
| Left (outer) join | all of a, matched rows of b | FROM a LEFT JOIN b ON a.id = b.id |
| Right (outer) join | all of b, matched rows of a | FROM a RIGHT JOIN b ON a.id = b.id |
| Full outer join | all rows from both, matched where possible | FROM a FULL JOIN b ON a.id = b.id |
| Cross join | every combination (Cartesian product) | FROM a CROSS JOIN b |
| Self join | a table joined to itself | FROM employees e JOIN employees m ON e.manager_id = m.employee_id |
| Old Oracle outer join | (+) marks the "optional" side | FROM a, b WHERE a.id = b.id(+) |
Set operators
Both queries need the same number of columns with compatible data types. UNION ALL is faster than UNION because it skips the duplicate-elimination sort.
| Operator | Returns |
|---|---|
| UNION | distinct rows from both queries, sorted |
| UNION ALL | all rows from both queries, including duplicates |
| INTERSECT | rows that appear in both queries |
| MINUS | rows in the first query not in the second |
DDL — data definition
Creating and modifying schema objects.
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
last_name VARCHAR2(50) NOT NULL,
salary NUMBER(8,2),
department_id NUMBER REFERENCES departments(department_id)
);
ALTER TABLE employees ADD (email VARCHAR2(100));
ALTER TABLE employees MODIFY (salary NUMBER(10,2));
ALTER TABLE employees DROP COLUMN email;
DROP TABLE employees;
TRUNCATE TABLE employees; -- removes all rows, can't be rolled back
RENAME employees TO staff;DML — data manipulation
Reading and changing data, including the MERGE upsert.
INSERT INTO employees (employee_id, last_name, salary)
VALUES (300, 'Ng', 6500);
UPDATE employees SET salary = salary * 1.1
WHERE department_id = 60;
DELETE FROM employees WHERE employee_id = 300;
-- Upsert: update if the row exists, insert if it doesn't
MERGE INTO employees t
USING new_hires s ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN INSERT (employee_id, last_name, salary)
VALUES (s.employee_id, s.last_name, s.salary);Constraints
| Constraint | Purpose |
|---|---|
| PRIMARY KEY | uniquely identifies each row; implies NOT NULL |
| FOREIGN KEY ... REFERENCES | enforces a link to another table's key |
| UNIQUE | no duplicate values, but NULLs are allowed |
| NOT NULL | column must always have a value |
| CHECK (condition) | custom rule every row must satisfy |
Transaction control
Every DML statement runs inside an implicit transaction until you COMMIT or ROLLBACK.
COMMIT; -- make changes permanent
ROLLBACK; -- undo changes since the last commit
SAVEPOINT before_update;
ROLLBACK TO before_update; -- undo back to a named pointPagination (top-N / paging)
| Approach | Syntax | Notes |
|---|---|---|
| ROWNUM (classic) | WHERE ROWNUM <= 10 | Filters before ORDER BY unless wrapped in a subquery — a classic gotcha |
| FETCH FIRST (12c+) | ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY | Applies after ORDER BY — the modern, safe way to get top-N |
| OFFSET ... FETCH (12c+) | ORDER BY salary DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY | Standard SQL paging, page 2 of results |
Window functions
The pattern is always function() OVER (PARTITION BY ... ORDER BY ...) — PARTITION BY resets the window per group, ORDER BY defines the row sequence within it.
| Function | Purpose |
|---|---|
| ROW_NUMBER() OVER (...) | unique sequential number per row in the window |
| RANK() OVER (...) | rank with gaps after ties (1, 2, 2, 4) |
| DENSE_RANK() OVER (...) | rank with no gaps after ties (1, 2, 2, 3) |
| LAG(col, n) OVER (...) | value from n rows before the current row |
| LEAD(col, n) OVER (...) | value from n rows after the current row |
| NTILE(n) OVER (...) | splits rows into n roughly equal buckets |
| SUM/AVG/COUNT(...) OVER (...) | running or grouped aggregate without collapsing rows |
Common data dictionary views
| View | Shows |
|---|---|
| USER_TABLES | tables you own |
| USER_TAB_COLUMNS | columns of your tables, with data types |
| USER_CONSTRAINTS | constraints on your objects |
| USER_INDEXES | indexes you own |
| USER_SEQUENCES | sequences you own |
| ALL_TABLES / DBA_TABLES | tables you can see / every table in the database (DBA view needs privilege) |
Practise what's on this page
Run any function or query on this page live against a real HR schema — free, no signup, results in under a second.
Open the SQL editor →Frequently asked questions
Is this Oracle SQL cheat sheet free to use?
Yes. Every reference table here, the tutorial it's drawn from, and the free SQL editor are completely free with no sign-up.
Does this cover PL/SQL as well as SQL?
This page is SQL only. For procedures, packages, triggers, and cursors, see the separate PL/SQL tutorial.
Is the FETCH FIRST syntax available in older Oracle versions?
FETCH FIRST / OFFSET (the row-limiting clause) requires Oracle Database 12c or later. On older versions, use the ROWNUM pattern instead.
Can I print or save this cheat sheet?
Yes — it's a normal web page, so your browser's print-to-PDF or bookmark feature works fine. There is no separate download required.
Where can I practise these commands?
Every function and pattern on this page can be run live in the free in-browser SQL editor against a real HR schema, or worked through in the 30 SQL practice exercises.