1Z0-071 — Oracle Database SQL Cheat Sheet

Last revised: September 28, 2026

Cheat sheet: Oracle Database SQL 1Z0-071 reference covering query syntax, joins, functions, subqueries, set operators, DML, DDL, and exam traps.

Independent quick reference for candidates preparing for Oracle Oracle Database SQL (1Z0-071). Use it to review high-yield syntax rules, common exam traps, and decision points for real Oracle SQL questions.

Use the tables for a quick pre-exam check. Expand a topic’s notes for explanations, examples, and additional distinctions.

Scope and study context

The exam rewards precision. Many missed questions are not caused by unfamiliar SQL, but by small details: NULL behavior, alias scope, join output, aggregate rules, datatype conversion, transaction control, and Oracle-specific syntax.

Core SELECT Processing

Logical Query Order vs Written Order

Written clause order	Logical evaluation idea	Exam points
`SELECT`	5	Column expressions, aliases, aggregate output
`FROM`	1	Tables, views, joins, inline views
`WHERE`	2	Row filtering before grouping
`GROUP BY`	3	Creates groups for aggregate evaluation
`HAVING`	4	Group filtering after aggregation
`ORDER BY`	6	Final sort; can use select-list aliases
Row limiting	7	Applied after ordering when used correctly

Notes and examples

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 5000
ORDER BY avg_sal DESC;

Alias Rules

Location	Can use select-list alias?	Notes
`ORDER BY`	Yes	Common exam-safe use
`WHERE`	No	`WHERE` is evaluated before `SELECT` alias creation
`GROUP BY`	Usually avoid	Use the original expression for exam-style Oracle SQL
`HAVING`	Usually avoid	Use the aggregate expression
Same select list	No	An alias is not normally reusable by another expression in the same select list

-- Correct
SELECT salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary;

-- Avoid / exam trap
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

Logical clause order

Memorize the logical processing order, not just the written syntax.

Written order	Logical role	Candidate reminder
`SELECT`	Choose expressions to display	Aliases are created here
`FROM`	Identify source tables/views	Joins are resolved here
`WHERE`	Filter individual rows	Cannot use aggregate functions directly here
`GROUP BY`	Form groups	Every non-aggregate selected expression must be grouped
`HAVING`	Filter groups	Use for aggregate conditions
`ORDER BY`	Sort final result	Can use select-list aliases

Logical evaluation is commonly understood as: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Alias rules that often appear in exam questions

Location	Can use select-list alias?	Example issue
`ORDER BY`	Usually yes	`ORDER BY annual_salary` works if alias is in select list
`WHERE`	No	Alias does not exist yet logically
`GROUP BY`	Do not rely on it for 1Z0-071-style questions	Group by the expression or column
`HAVING`	Do not rely on select alias	Use the aggregate expression

If a question gives a calculated alias such as salary * 12 annual_salary, expect a trap where the alias is reused too early.

Filtering, Sorting, and Operators

Comparison and Null Logic

Predicate	Meaning	Trap
`=`, `<>`, `!=`, `<`, `>`, `<=`, `>=`	Standard comparisons	Comparisons with `NULL` return unknown, not true
`BETWEEN a AND b`	Inclusive range	Equivalent to `>= a AND <= b`
`IN (...)`	Matches any listed value	`IN` with `NULL` does not match null rows
`LIKE`	Pattern matching	`%` = any length, `_` = one character
`IS NULL`	Tests null	Use instead of `= NULL`
`IS NOT NULL`	Tests non-null	Use instead of `<> NULL`
`AND`	Both conditions	Evaluated before `OR`
`OR`	Either condition	Use parentheses to control intent
`NOT`	Negates condition	Watch `NOT IN` with nulls

Notes and examples

SELECT last_name
FROM employees
WHERE commission_pct IS NULL;

SELECT last_name
FROM employees
WHERE last_name LIKE 'Smi_h%' ESCAPE '\';

Boolean Precedence

Higher to lower	Example
Comparisons and pattern tests	`salary > 5000`, `job_id LIKE 'SA%'`
`NOT`	`NOT department_id = 10`
`AND`	`a AND b`
`OR`	`a OR b`

Exam trap:

-- Means: department_id = 10 OR (department_id = 20 AND salary > 5000)
WHERE department_id = 10 OR department_id = 20 AND salary > 5000

-- Clearer:
WHERE (department_id = 10 OR department_id = 20)
  AND salary > 5000

Sorting Rules

Syntax	Result
`ORDER BY col ASC`	Ascending; default
`ORDER BY col DESC`	Descending
`ORDER BY 2`	Sort by second select-list expression
`ORDER BY alias`	Sort by select-list alias
`NULLS FIRST` / `NULLS LAST`	Explicit null placement

SELECT employee_id, last_name, salary * 12 AS annual_pay
FROM employees
ORDER BY annual_pay DESC NULLS LAST;

Single-Row Functions

Character Functions

Function	Purpose	Example result idea
`LOWER(char)`	Lowercase	`LOWER('SQL')` → `sql`
`UPPER(char)`	Uppercase	`UPPER('sql')` → `SQL`
`INITCAP(char)`	Initial capitals	`INITCAP('oracle sql')`
`CONCAT(a,b)`	Concatenate two values	Only two arguments
`a		b`
`SUBSTR(char,start,len)`	Substring	Positions start at 1
`LENGTH(char)`	Character length	Counts characters
`INSTR(char,search)`	Position of substring	0 if not found
`LPAD` / `RPAD`	Pad left/right	Formatting output
`TRIM`	Remove leading/trailing chars	Spaces by default
`REPLACE`	Replace substring	Case-sensitive

Notes and examples

SELECT UPPER(last_name),
       SUBSTR(phone_number, 1, 3),
       first_name || ' ' || last_name AS full_name
FROM employees;

Number Functions

Function	Purpose	Key distinction
`ROUND(n, d)`	Rounds to `d` decimals	May increase value
`TRUNC(n, d)`	Truncates to `d` decimals	Does not round
`MOD(n, m)`	Remainder	Useful for divisibility

SELECT ROUND(45.926, 2), TRUNC(45.926, 2), MOD(10, 3)
FROM dual;

Date Functions and Date Arithmetic

Expression	Meaning	Exam point
`SYSDATE`	Current database server date/time	Includes time component
`CURRENT_DATE`	Current date in session time zone	Different from `SYSDATE` in some environments
`date + n`	Add `n` days	`n` may be fractional
`date - n`	Subtract `n` days
`date1 - date2`	Difference in days	Numeric result
`MONTHS_BETWEEN(d1,d2)`	Months between dates	May return fractional months
`ADD_MONTHS(d,n)`	Add months	Handles month boundaries
`NEXT_DAY(d,'MONDAY')`	Next named weekday	Language-sensitive
`LAST_DAY(d)`	Last day of month
`ROUND(date,'MONTH')`	Round date	Date granularity
`TRUNC(date,'YEAR')`	Truncate date	Common for grouping

SELECT hire_date,
       hire_date + 7 AS one_week_later,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed
FROM employees;

Single-row functions

Single-row functions return one result per input row. They can be nested, used in SELECT, WHERE, and ORDER BY, and often appear in conversion or date questions.

Character functions

Function	Purpose	Example result idea
`LOWER`, `UPPER`, `INITCAP`	Change case	Useful for case-insensitive comparisons
`CONCAT(a,b)`	Concatenate two values	Similar to `a
`SUBSTR(char, start, length)`	Extract part of a string	Oracle positions are character-based
`LENGTH(char)`	Count characters	Spaces count
`INSTR(char, search)`	Find position	Returns position of search string
`LPAD`, `RPAD`	Pad to a length	Formatting output
`TRIM`	Remove leading/trailing characters	Default trims spaces
`REPLACE`	Replace matching text	Character substitution

Candidate trap: CONCAT takes two arguments, while || can chain multiple values.

Number functions

Function	Purpose	Trap
`ROUND(number, n)`	Round to `n` decimal places	Negative `n` rounds left of decimal
`TRUNC(number, n)`	Truncate to `n` decimal places	Does not round
`MOD(m, n)`	Remainder	Useful for divisibility checks

Date functions

Oracle DATE values include date and time components.

Function	Purpose	Review point
`SYSDATE`	Current database server date/time	Includes time
`MONTHS_BETWEEN(d1, d2)`	Months between dates	Can return fractional months
`ADD_MONTHS(date, n)`	Add months	Handles month boundaries
`NEXT_DAY(date, char)`	Next named weekday	Depends on date language settings
`LAST_DAY(date)`	Last day of month	High-yield date function
`ROUND(date, fmt)`	Round date to format unit	Format matters
`TRUNC(date, fmt)`	Truncate date to format unit	Common for removing time portion
`EXTRACT(part FROM date)`	Extract year, month, day, etc.	Syntax differs from normal functions

Conversion functions and format models

Function	Converts	Typical use
`TO_CHAR(date, fmt)`	Date to formatted text	Display dates
`TO_CHAR(number, fmt)`	Number to formatted text	Currency, decimal display
`TO_DATE(char, fmt)`	Text to date	Avoid implicit date conversion
`TO_NUMBER(char, fmt)`	Text to number	Controlled numeric conversion

High-yield format elements include YYYY, YY, RR, MM, MON, MONTH, DD, DAY, DY, HH, HH24, MI, SS, and numeric elements such as 9, 0, comma, decimal, and currency symbols.

Candidate trap: implicit conversion may work in one environment and fail in another because date and numeric formats can depend on session settings. For exam questions, explicit conversion with the correct format model is safer.

Conditional expressions

Expression	Use	Notes
`CASE`	Standard conditional logic	Supports searched and simple forms
`DECODE`	Oracle-specific conditional comparison	Often shorter but less flexible
`NVL`	Replace `NULL`	Two arguments
`COALESCE`	First non-null	Short-circuits conceptually
`NULLIF`	Return null when equal	Useful for avoiding divide-by-zero patterns
`NVL2`	Null-dependent branching	Three arguments

Conversion and Conditional Expressions

Conversion Functions

Function	Purpose	Example
`TO_CHAR(date, fmt)`	Date to text	`TO_CHAR(hire_date,'YYYY-MM-DD')`
`TO_CHAR(number, fmt)`	Number to text	`TO_CHAR(salary,'999,999')`
`TO_DATE(char, fmt)`	Text to date	`TO_DATE('2026-06-18','YYYY-MM-DD')`
`TO_NUMBER(char, fmt)`	Text to number	`TO_NUMBER('1,200','9,999')`

Notes and examples

Common format model elements:

Element	Meaning
`YYYY`	Four-digit year
`YY`	Two-digit year
`RR`	Two-digit year with Oracle RR century logic
`MM`	Month number
`MON`	Abbreviated month name
`MONTH`	Full month name
`DD`	Day of month
`DY`	Abbreviated day name
`DAY`	Full day name
`HH24`	Hour 0-23
`MI`	Minute
`SS`	Second
`AM` / `PM`	Meridian indicator

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
FROM dual;

Null and Conditional Functions

Function	Returns	High-yield distinction
`NVL(expr1, expr2)`	`expr2` if `expr1` is null	Oracle-specific; data types must be compatible
`NVL2(expr1, expr2, expr3)`	`expr2` if not null, else `expr3`	Reverses common intuition
`NULLIF(expr1, expr2)`	`NULL` if equal, else `expr1`	Useful to avoid divide-by-zero logic
`COALESCE(a,b,c,...)`	First non-null expression	ANSI-style; multiple expressions
`CASE`	Conditional result	Searched or simple form
`DECODE`	Oracle conditional comparison	Older Oracle-specific style

SELECT last_name,
       NVL(commission_pct, 0) AS commission_pct,
       CASE
         WHEN salary >= 10000 THEN 'HIGH'
         WHEN salary >= 5000  THEN 'MID'
         ELSE 'LOW'
       END AS salary_band
FROM employees;

Aggregate Functions and Grouping

Aggregate Function Behavior

Function	Counts nulls?	Notes
`COUNT(*)`	Yes	Counts rows
`COUNT(expr)`	No	Counts non-null expression values
`COUNT(DISTINCT expr)`	No	Counts distinct non-null values
`SUM(expr)`	No	Numeric/date interval-style use depends on expression
`AVG(expr)`	No	Nulls excluded from denominator
`MIN(expr)`	No	Works on comparable data
`MAX(expr)`	No	Works on comparable data

Notes and examples

SELECT department_id,
       COUNT(*) AS row_count,
       COUNT(commission_pct) AS commission_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

GROUP BY Rules

Rule	Example / trap
Every non-aggregate select expression must be grouped	`SELECT department_id, job_id, AVG(salary) ... GROUP BY department_id, job_id`
`WHERE` filters rows before grouping	Cannot use aggregate functions in `WHERE`
`HAVING` filters groups after grouping	Use `HAVING AVG(salary) > 5000`
Grouping by expression requires the expression	`GROUP BY TRUNC(hire_date,'YEAR')`
Nulls form a group	Null department values group together

-- Correct
SELECT department_id, AVG(salary)
FROM employees
WHERE salary > 0
GROUP BY department_id
HAVING AVG(salary) > 5000;

-- Incorrect: aggregate in WHERE
SELECT department_id
FROM employees
WHERE AVG(salary) > 5000
GROUP BY department_id;

Joins

Join Type Selection

Need	Use	Notes
Matching rows in both tables	`INNER JOIN`	Default when `JOIN` without outer keyword
All rows from left table plus matches	`LEFT OUTER JOIN`	Unmatched right columns become null
All rows from right table plus matches	`RIGHT OUTER JOIN`	Less common; can often rewrite as left join
All rows from both sides	`FULL OUTER JOIN`	Unmatched columns become null
Join a table to itself	Self join	Requires aliases
Join on non-equality condition	Non-equijoin	Example: ranges
All combinations	Cross join	Cartesian product; often accidental
Join same-named columns automatically	`NATURAL JOIN`	Risky: uses all same-name columns
Join same-named selected columns	`JOIN ... USING (col)`	Cannot qualify `col` with table alias in select list

Notes and examples

SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id;

Outer Join Filter Trap

-- Preserves departments with no employees
SELECT d.department_name, e.last_name
FROM departments d
LEFT OUTER JOIN employees e
  ON d.department_id = e.department_id;

-- Trap: WHERE condition on right table can turn it into an effective inner join
SELECT d.department_name, e.last_name
FROM departments d
LEFT OUTER JOIN employees e
  ON d.department_id = e.department_id
WHERE e.job_id = 'SA_REP';

-- Safer when the filter belongs to the matching condition
SELECT d.department_name, e.last_name
FROM departments d
LEFT OUTER JOIN employees e
  ON d.department_id = e.department_id
 AND e.job_id = 'SA_REP';

USING and NATURAL JOIN Traps

-- With USING, do not qualify the joined column in the select list
SELECT department_id, e.last_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);

-- NATURAL JOIN joins on every column with the same name in both tables
SELECT employee_id, department_name
FROM employees
NATURAL JOIN departments;

Trap	Why it matters
Missing join condition	Produces Cartesian product
`NATURAL JOIN`	New same-name columns can silently change results
Qualifying a `USING` column	Invalid in common Oracle exam syntax
Filtering outer-joined table in `WHERE`	May remove null-extended rows

Join types

Join type	Purpose	Trap
Inner join	Rows with matching values	Nonmatching rows disappear
Left outer join	All rows from left table plus matches	Predicate placement can turn it into an inner join
Right outer join	All rows from right table plus matches	Same predicate trap
Full outer join	All matching and nonmatching rows from both sides	Nulls appear for missing side
Self-join	Table joined to itself	Requires aliases
Cross join	Cartesian product	Usually wrong unless intentional
Natural join	Joins same-name columns automatically	Dangerous if multiple same-name columns exist

`ON`, `USING`, and natural joins

Syntax	Best use	Watch for
`JOIN ... ON t1.col = t2.col`	Most explicit and safest	Qualify columns clearly
`JOIN ... USING (col)`	Same column name in both tables	The joined column is referenced once
`NATURAL JOIN`	Quick join on all same-name columns	Can silently join on unintended columns

For exam safety, prefer reasoning with ON because it makes the join condition explicit.

Outer join predicate trap

A common missed question places an outer join in the FROM clause but then filters the optional table in the WHERE clause.

Pattern	Effect
Left join plus `WHERE right_table.status = 'A'`	Often removes null-extended rows
Left join with condition in `ON` clause	Preserves left rows while limiting matches
`WHERE right_table.col IS NULL` after left join	Finds unmatched rows

When reading an outer join question, ask: “Is this condition part of the match, or is it filtering the final result?”

Subqueries

Subquery Types

Type	Returns	Operators
Single-row subquery	One row, one column	`=`, `>`, `<`, `>=`, `<=`, `<>`
Multiple-row subquery	Many rows, one column	`IN`, `ANY`, `ALL`
Multiple-column subquery	Many columns	Row/value comparisons
Correlated subquery	References outer query	Executes logically per outer row
Scalar subquery	One value	Can appear where a single value is valid
Inline view	Subquery in `FROM`	Acts like a derived table

Notes and examples

-- Single-row subquery
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Multiple-row subquery
SELECT last_name
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = 1700
);

ANY, ALL, IN, EXISTS

Operator	Meaning	Exam interpretation
`IN`	Equal to any value in list	Same idea as `= ANY`
`> ANY`	Greater than at least one returned value	Greater than the minimum is enough
`> ALL`	Greater than every returned value	Greater than the maximum
`< ANY`	Less than at least one returned value	Less than the maximum
`< ALL`	Less than every returned value	Less than the minimum
`EXISTS`	True if subquery returns at least one row	Common with correlated subqueries
`NOT EXISTS`	True if no row returned	Safer than `NOT IN` when nulls may appear

SELECT e.employee_id, e.last_name
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM dependents d
  WHERE d.employee_id = e.employee_id
);

Subquery Traps

Situation	Result / issue
Single-row operator with multi-row subquery	Error
`NOT IN` and subquery returns `NULL`	Often returns no rows due to unknown comparison
Correlated subquery missing correlation	May become uncorrelated and change result
Subquery in `ORDER BY`	Must be valid scalar expression
Subquery in `FROM`	Needs aliasing/column handling for readability

-- Safer anti-match pattern
SELECT d.department_id
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.department_id
);

Subquery types

Type	Returns	Operators
Single-row subquery	One row, one column	`=`, `>`, `<`, `>=`, `<=`, `<>`
Multiple-row subquery	Multiple rows, one column	`IN`, `ANY`, `ALL`
Multiple-column subquery	Multiple columns	Tuple-style comparisons
Correlated subquery	Depends on outer query row	Often used with `EXISTS`
Scalar subquery	One value	Can appear where a single expression is valid

Operator decision table

If the subquery can return…	Use
Exactly one value	Single-row operator such as `=`
Multiple values	`IN`, `ANY`, `ALL`, or `EXISTS`
Existence only matters	`EXISTS`
Nonexistence matters	`NOT EXISTS` is often safer than `NOT IN` with nullable data

`NOT IN` and `NULL`

This is one of the highest-yield traps.

If a subquery used with NOT IN returns a NULL, the comparison can become unknown and return no rows that you expected. When nulls are possible, NOT EXISTS is often the safer logical pattern.

Correlated subquery reading method

For each row in the outer query:

Substitute the outer row’s relevant value into the inner query.
Evaluate the inner query.
Decide whether the outer row qualifies.

This mental model helps with questions using department averages, maximum salary by group, or existence checks.

Set Operators

Set Operator Matrix

Operator	Duplicates	Meaning
`UNION`	Removes duplicates	Rows from either query
`UNION ALL`	Keeps duplicates	Rows from either query, faster conceptually because no duplicate elimination
`INTERSECT`	Removes duplicates	Rows common to both queries
`MINUS`	Removes duplicates	Rows in first query not in second

Notes and examples

SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

Set Operator Rules

Rule	Exam point
Same number of columns	Each query must return same column count
Compatible data type groups	Corresponding columns must be compatible
Column names come from first query	Final output headings use first select
`ORDER BY` appears at the end	Not inside each component query unless using a valid subquery
Use column position or first-query alias in final `ORDER BY`	Especially useful for expressions
Parentheses control evaluation	Avoid relying on precedence assumptions

SELECT employee_id AS id, last_name AS name FROM employees
UNION ALL
SELECT department_id, department_name FROM departments
ORDER BY name;

Set operators

Set operators combine result sets from separate queries.

Operator	Result
`UNION`	Combined distinct rows
`UNION ALL`	Combined rows including duplicates
`INTERSECT`	Rows common to both result sets
`MINUS`	Rows in first result set but not second

Set operator rules

Rule	Candidate reminder
Same number of columns	Each query must return matching column count
Compatible datatype groups	Character with character, numeric with numeric, etc.
Column names	Taken from the first query
`ORDER BY`	Appears at the end for the combined result
Duplicates	Removed unless `UNION ALL` is used
Mixed operators	Use parentheses to make intent clear

Do not assume each individual query can have its own final sort. The final ORDER BY applies to the combined result.

Row Limiting and Top-N Queries

Technique	Use	Trap
`FETCH FIRST n ROWS ONLY`	Modern row limiting after `ORDER BY`	Put after `ORDER BY`
`OFFSET n ROWS`	Skip rows before fetch	Often paired with fetch
`ROWNUM`	Pseudocolumn assigned as rows are returned	`ROWNUM > 1` directly is a classic trap
Inline view with `ROWNUM`	Top-N with older style	Sort inside inline view, filter outside

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

Older top-N pattern:

SELECT *
FROM (
  SELECT employee_id, last_name, salary
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 10;

DML: INSERT, UPDATE, DELETE, MERGE

DML Command Reference

Command	Purpose	Key syntax
`INSERT`	Add rows	`VALUES` or subquery
`UPDATE`	Change rows	Use `WHERE` unless all rows should change
`DELETE`	Remove rows	Use `WHERE` unless all rows should be removed
`MERGE`	Insert/update based on match	Useful for upsert-style logic

Notes and examples

INSERT INTO departments (department_id, department_name)
VALUES (280, 'Research');

INSERT INTO departments (department_id, department_name)
SELECT 281, 'Analytics'
FROM dual;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 60;

DELETE FROM employees
WHERE employee_id = 999;

MERGE Pattern

MERGE INTO bonuses b
USING employees e
   ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET b.salary = e.salary
WHEN NOT MATCHED THEN
  INSERT (employee_id, salary)
  VALUES (e.employee_id, e.salary);

Transaction Control

Statement	Effect
`COMMIT`	Makes current transaction changes permanent
`ROLLBACK`	Undoes uncommitted changes
`SAVEPOINT name`	Marks a point for partial rollback
`ROLLBACK TO name`	Rolls back to savepoint
DDL statement	Causes implicit commit behavior in Oracle

SAVEPOINT before_raise;

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 80;

ROLLBACK TO before_raise;
COMMIT;

DML statements

Statement	Purpose	Exam focus
`INSERT`	Add rows	Column order, default values, subquery inserts
`UPDATE`	Modify rows	Missing `WHERE` updates all qualifying rows
`DELETE`	Remove rows	Missing `WHERE` deletes all qualifying rows
`MERGE`	Insert/update based on match logic	Understand match vs not-match behavior if tested

Transaction control

Statement	Effect
`COMMIT`	Makes transaction changes permanent
`ROLLBACK`	Undoes uncommitted transaction changes
`SAVEPOINT name`	Marks a point to roll back to
`ROLLBACK TO SAVEPOINT name`	Undoes changes after that savepoint

High-yield distinction:

Action	Transaction impact
DML such as `INSERT`, `UPDATE`, `DELETE`	Requires transaction control
DDL such as `CREATE`, `ALTER`, `DROP`, `TRUNCATE`	Has implicit commit behavior in Oracle
`DELETE`	DML; can be rolled back before commit
`TRUNCATE`	DDL; not the same transactional behavior as `DELETE`

Candidate trap: DELETE FROM table_name and TRUNCATE TABLE table_name may both remove rows, but they are not equivalent.

DDL and Schema Objects

Table DDL

Statement	Purpose	Exam note
`CREATE TABLE`	Create table	Define columns and constraints
`ALTER TABLE`	Modify table	Add/drop/modify columns or constraints
`DROP TABLE`	Remove table definition	DDL; commits
`TRUNCATE TABLE`	Remove all rows efficiently	DDL; commits; no row-by-row delete
`RENAME`	Rename object	Object name change

Notes and examples

CREATE TABLE projects (
  project_id   NUMBER CONSTRAINT projects_pk PRIMARY KEY,
  project_name VARCHAR2(100) NOT NULL,
  start_date   DATE DEFAULT SYSDATE,
  budget       NUMBER(10,2),
  status       VARCHAR2(20)
);

Constraint Reference

Constraint	Purpose	Column-level?	Table-level?
`NOT NULL`	Requires value	Yes	No
`UNIQUE`	Prevents duplicate non-null values	Yes	Yes
`PRIMARY KEY`	Unique row identifier; not null	Yes	Yes
`FOREIGN KEY`	Enforces parent-child relationship	Yes	Yes
`CHECK`	Enforces condition	Yes	Yes

CREATE TABLE order_items (
  order_id    NUMBER,
  line_id     NUMBER,
  product_id  NUMBER NOT NULL,
  quantity    NUMBER CHECK (quantity > 0),
  CONSTRAINT order_items_pk PRIMARY KEY (order_id, line_id),
  CONSTRAINT order_items_product_fk
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Constraint Actions and Traps

Feature	Meaning
`ON DELETE CASCADE`	Deleting parent deletes child rows
`ON DELETE SET NULL`	Deleting parent sets child foreign key to null
No delete action specified	Parent delete fails if child rows exist
`CHECK (col IS NOT NULL)`	Similar effect to `NOT NULL`, but not the same declaration
`UNIQUE` with nulls	Null handling differs from ordinary equality intuition; do not treat null as a normal duplicate value

Views

A view is a stored query. It can simplify complex joins, restrict displayed columns, or present derived data.

View concept	Review point
Simple view	Often based on one table; may be updatable if rules are met
Complex view	Includes joins, groups, functions, or aggregates; update restrictions likely
`WITH CHECK OPTION`	Prevents changes through the view that violate the view condition
`WITH READ ONLY`	Prevents DML through the view

Candidate trap: a view does not automatically store a separate copy of ordinary query data like a table. It is generally a query definition unless materialized view concepts are explicitly involved.

Sequences

Sequences generate numeric values, commonly for surrogate keys.

Pseudocolumn	Meaning
`sequence_name.NEXTVAL`	Gets next sequence value
`sequence_name.CURRVAL`	Current value in session after `NEXTVAL` has been used

Review sequence options conceptually: START WITH, INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, NOCYCLE, CACHE, and NOCACHE.

Candidate traps:

Sequence numbers can have gaps.
Rolling back a transaction does not necessarily “put back” a sequence value.
CURRVAL is not available in a session before that session has used NEXTVAL.

Indexes and synonyms

Object	Purpose	Trap
Index	Speeds access paths and supports uniqueness	Too many indexes can affect DML overhead conceptually
Unique index	Enforces uniqueness when used for constraints	Constraint and index are related but not identical concepts
Synonym	Alternative name for an object	Does not grant object privileges
Private synonym	Available to its owner	Name scope matters
Public synonym	Available database-wide by name	Still requires privileges

Views, Sequences, Synonyms, and Indexes

Views

View concept	Meaning
Simple view	Based on one table, no grouping/functions; more likely DML-capable
Complex view	Joins, groups, functions, expressions, or aggregates; DML may be restricted
`CREATE OR REPLACE VIEW`	Recreates view without dropping privileges in the same way as drop/create
`WITH CHECK OPTION`	DML through view must satisfy view predicate
`WITH READ ONLY`	Prevents DML through view
`FORCE`	Create view even if base object is not currently valid
`NOFORCE`	Requires base object validity

Notes and examples

CREATE OR REPLACE VIEW emp80 AS
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = 80
WITH CHECK OPTION;

Sequences

Pseudocolumn	Meaning	Trap
`sequence_name.NEXTVAL`	Generates next sequence value	Advances the sequence
`sequence_name.CURRVAL`	Current session’s sequence value	Requires prior `NEXTVAL` in session

CREATE SEQUENCE project_seq
  START WITH 1
  INCREMENT BY 1;

INSERT INTO projects (project_id, project_name)
VALUES (project_seq.NEXTVAL, 'Migration');

Sequence exam points:

Point	Explanation
Sequences are independent objects	Not automatically tied to one table unless used that way
Gaps can occur	Rollbacks, caching, or failed statements may leave gaps
`CURRVAL` is session-specific	Not valid before `NEXTVAL` in that session

Indexes and Synonyms

Object	Purpose	Notes
Index	Improves access path for queries	Oracle may create indexes for some constraints
Unique index	Enforces uniqueness when used for unique constraints	Distinguish object from constraint
Function-based index	Index on expression	Query must use matching expression conceptually
Synonym	Alternative name for object	Does not copy the object
Public synonym	Available broadly	Privileges still matter

Privileges and Access Control Basics

Concept	Meaning
System privilege	Allows an action, such as creating objects
Object privilege	Allows access to a specific object, such as `SELECT` on a table
Role	Named group of privileges
`GRANT`	Gives privilege or role
`REVOKE`	Removes privilege or role
`WITH GRANT OPTION`	Lets grantee grant object privilege to others
`WITH ADMIN OPTION`	Lets grantee administer a role/system privilege

GRANT SELECT ON employees TO analyst_role;
REVOKE SELECT ON employees FROM analyst_role;

High-Yield Error Patterns

Pattern	Likely problem
`WHERE col = NULL`	Should use `IS NULL`
Aggregate in `WHERE`	Use `HAVING`
Non-grouped column in aggregate query	Add to `GROUP BY` or aggregate it
Single-row subquery returns multiple rows	Use multi-row operator or restrict subquery
`NOT IN` subquery returns null	Use `NOT EXISTS` pattern
Missing join condition	Cartesian product
Filtering outer-joined table in `WHERE`	Removes unmatched rows
`CONCAT(a,b,c)`	Oracle `CONCAT` accepts two arguments
Using alias in `WHERE`	Alias not available there
`ROWNUM > 1` directly	No first row can satisfy it
Set operator column mismatch	Same column count and compatible types required
DDL followed by rollback expectation	DDL has implicit commit behavior

Mini Decision Tables

WHERE vs HAVING

Need	Clause
Filter individual rows before aggregation	`WHERE`
Filter groups after aggregation	`HAVING`
Use aggregate condition	`HAVING`
Improve grouping input set	`WHERE`

Join vs Subquery

Need	Prefer
Return columns from multiple tables	Join
Test existence	`EXISTS` / `NOT EXISTS`
Compare to aggregate value	Scalar or single-row subquery
Anti-match with possible nulls	`NOT EXISTS`
Combine similar result sets vertically	Set operator

DELETE vs TRUNCATE vs DROP

Need	Use
Remove selected rows and allow transaction control	`DELETE ... WHERE ...`
Remove all rows as DDL-style operation	`TRUNCATE TABLE`
Remove the table object itself	`DROP TABLE`

Exam-Day SQL Checklist

Before selecting an answer on Oracle 1Z0-071 SQL questions, check:

Are nulls involved? If yes, verify IS NULL, aggregate behavior, and NOT IN logic.
Are aggregates mixed with detail columns? If yes, verify GROUP BY.
Is the filter row-level or group-level? Choose WHERE or HAVING.
Is an alias used before it exists? Alias is safest in ORDER BY.
Does a subquery return one row or many rows? Match the operator.
Does an outer join still preserve unmatched rows after filtering?
Do set operator queries have the same number and compatible types of columns?
Does DDL appear in a transaction question? Remember implicit commit behavior.
Is a date compared as text? Prefer explicit conversion with a format model.
Is the question asking for syntax validity or result behavior? Check both.

High-yield review map

Area	Know cold	Common trap
`SELECT` statements	Clause order, aliases, sorting, filtering	Using a column alias in `WHERE`
`NULL` handling	`IS NULL`, `NVL`, `COALESCE`, `NULLIF`, `NVL2`	Comparing with `= NULL` or `<> NULL`
Single-row functions	Character, number, date, conversion, conditional	Confusing `ROUND` and `TRUNC`; implicit conversion surprises
Group functions	`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `GROUP BY`, `HAVING`	Selecting non-grouped columns with aggregates
Joins	Inner, outer, self, cross, `ON`, `USING`, natural joins	Natural joins matching unintended same-name columns
Subqueries	Single-row, multiple-row, correlated, `EXISTS`	`NOT IN` with `NULL` returning no expected rows
Set operators	`UNION`, `UNION ALL`, `INTERSECT`, `MINUS`	Wrong column count or incompatible datatype groups
DML and transactions	`INSERT`, `UPDATE`, `DELETE`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`	Forgetting DDL causes implicit commit behavior
DDL and objects	Tables, constraints, views, sequences, indexes, synonyms	Assuming a synonym grants privileges
Privileges	`GRANT`, `REVOKE`, system vs object privileges	Confusing `WITH GRANT OPTION` and `WITH ADMIN OPTION`

Filtering, sorting, and row conditions

Predicates and operators

Need	Use	Watch for
Exact match	`=`	Case-sensitive for character data unless transformed
Range	`BETWEEN low AND high`	Inclusive on both ends
List match	`IN (...)`	Equivalent to multiple `OR` checks
Pattern match	`LIKE`	`%` means any length; `_` means one character
Null test	`IS NULL` / `IS NOT NULL`	Never `= NULL`
Negative logic	`NOT`, `<>`, `!=`, `NOT IN`	`NULL` can change expected results
Combined logic	`AND`, `OR`	`AND` has higher precedence than `OR`

Notes and examples

Use parentheses when a condition mixes AND and OR. The exam often checks whether you know how a condition is actually evaluated.

`NULL` rules

NULL means unknown or unavailable, not zero, blank, or false.

Expression	Result concept
`salary + NULL`	`NULL`
`commission_pct = NULL`	Not true; use `IS NULL`
`commission_pct <> NULL`	Not true; use `IS NOT NULL`
`NVL(commission_pct, 0)`	Replace null with `0`
`COALESCE(a, b, c)`	First non-null expression
`NULLIF(a, b)`	Returns `NULL` if `a = b`, else `a`
`NVL2(expr, value_if_not_null, value_if_null)`	Two-branch null handling

Sorting rules

Syntax	Meaning
`ORDER BY col ASC`	Ascending; often default
`ORDER BY col DESC`	Descending
`ORDER BY 2`	Sort by second select-list item
`ORDER BY alias`	Sort by select-list alias
`NULLS FIRST` / `NULLS LAST`	Explicit null placement

If a query does not include ORDER BY, do not assume output order.

Group functions and aggregation

Aggregate function essentials

Function	What it does	Null behavior
`COUNT(*)`	Counts rows	Includes rows with nulls
`COUNT(expr)`	Counts non-null expression values	Ignores nulls
`COUNT(DISTINCT expr)`	Counts distinct non-null values	Ignores nulls
`SUM(expr)`	Adds values	Ignores nulls
`AVG(expr)`	Averages values	Ignores nulls
`MIN(expr)`	Lowest value	Ignores nulls
`MAX(expr)`	Highest value	Ignores nulls

Notes and examples

Important difference:

AVG(commission_pct) averages only rows where commission_pct is not null.
AVG(NVL(commission_pct, 0)) treats null commission values as zero.

`GROUP BY` decision rule

If the SELECT list contains both aggregate expressions and non-aggregate expressions, every non-aggregate expression must be included in the GROUP BY.

Select-list item	Must be in `GROUP BY`?
`department_id`	Yes, if selected with aggregates
`UPPER(job_id)`	Yes, as the expression if selected with aggregates
`COUNT(*)`	No
`AVG(salary)`	No
Literal such as `'Total'`	No

`WHERE` vs `HAVING`

Clause	Filters	Can use group functions?
`WHERE`	Rows before grouping	No
`HAVING`	Groups after grouping	Yes

Example decision:

Need employees with salary > 10000 before calculating department average? Use WHERE.
Need departments with AVG(salary) > 10000? Use HAVING.

DDL, data types, and constraints

Common Oracle data types

Data type	Use	Review point
`VARCHAR2(size)`	Variable-length character data	Common text type
`CHAR(size)`	Fixed-length character data	Pads to fixed length
`NUMBER(p,s)`	Numeric data	Precision and scale matter
`DATE`	Date and time to seconds	Not just date-only
`TIMESTAMP`	More precise date/time	Fractional seconds
`CLOB`	Large character data	Large text
`BLOB`	Binary large object	Binary data

Notes and examples

Constraint types

Constraint	Purpose	Key trap
`NOT NULL`	Column must have value	Column-level only in typical syntax
`UNIQUE`	Values must be unique	Multiple nulls may be allowed depending on columns
`PRIMARY KEY`	Unique row identifier	Implies uniqueness and not null
`FOREIGN KEY`	Enforces parent-child relationship	Child value must match parent or be null if allowed
`CHECK`	Enforces condition	Cannot rely on invalid expressions
`DEFAULT`	Supplies value when omitted	Not the same as inserting explicit `NULL`

Foreign key delete actions

Clause	Effect
No special clause	Parent delete blocked if child rows exist
`ON DELETE CASCADE`	Deletes dependent child rows
`ON DELETE SET NULL`	Sets child foreign key values to null

Data dictionary and metadata

Oracle data dictionary views are frequently grouped by prefix.

Prefix	Meaning
`USER_`	Objects owned by the current user
`ALL_`	Objects accessible to the current user
`DBA_`	Database-wide administrative views, if privileged

Examples you may see conceptually include table, column, constraint, view, sequence, index, and synonym metadata. Know the difference between owning an object and merely having access to it.

Privileges and security basics

Privilege types

Type	Examples	Granted on
System privilege	`CREATE SESSION`, `CREATE TABLE`	Capability in the database
Object privilege	`SELECT`, `INSERT`, `UPDATE`, `DELETE`	Specific object
Role	Named collection of privileges	Granted to users or other roles depending on rules

Grant option distinctions

Clause	Applies to	Meaning
`WITH GRANT OPTION`	Object privileges	Recipient can grant that object privilege to others
`WITH ADMIN OPTION`	System privileges or roles	Recipient can administer/grant it further

Candidate trap: revoking a privilege can have cascading effects for object privileges granted onward through WITH GRANT OPTION.

Common 1Z0-071 mistake checklist

Before answering, check these items:

Is there an ORDER BY? If not, do not assume row order.
Is NULL involved? Replace normal comparison thinking with three-valued logic.
Is an alias used too early? WHERE cannot see select-list aliases.
Are aggregate and non-aggregate columns mixed? Check GROUP BY.
Is the filter row-level or group-level? Choose WHERE vs HAVING.
Is the join natural? Look for unintended same-name columns.
Is an outer join filtered in WHERE? It may remove null-extended rows.
Can a subquery return multiple rows? Use the right operator.
Can a NOT IN subquery return null? Consider the null trap.
Do set operator queries align? Same column count and compatible datatype groups.
Is a date literal or conversion format ambiguous? Prefer explicit conversion.
Is the statement DML or DDL? Transaction behavior differs.
Does a synonym exist? That does not mean the user has privileges.
Is a sequence expected to be gap-free? Do not assume that.
Is COUNT(*) being confused with COUNT(column)? Null handling differs.

Fast decision tables

Which clause should solve the problem?

Requirement	Likely clause
Choose displayed columns or expressions	`SELECT`
Choose source tables	`FROM`
Connect tables	`JOIN ... ON` or `USING`
Filter rows before grouping	`WHERE`
Group rows	`GROUP BY`
Filter groups	`HAVING`
Sort final output	`ORDER BY`

Notes and examples

Which SQL feature should solve the problem?

Requirement	Feature
Replace null commission with zero	`NVL` or `COALESCE`
Display date as text	`TO_CHAR`
Convert text to date	`TO_DATE`
Compare to department average	Subquery or analytic logic if provided
Return departments with no employees	Outer join plus null check or `NOT EXISTS`
Combine two result sets and remove duplicates	`UNION`
Combine two result sets and keep duplicates	`UNION ALL`
Find rows in first query but not second	`MINUS`
Generate new numeric key values	Sequence
Prevent invalid child rows	Foreign key constraint
Restrict DML through a view	`WITH CHECK OPTION` or `WITH READ ONLY`

Practice plan after this review

Use this page as a final concept pass, then move into active recall:

Topic drills: Work in focused sets: joins, subqueries, group functions, DML, DDL, and set operators.
Original practice questions: Prioritize questions that require predicting output or identifying invalid SQL.
Detailed explanations: For every missed item, identify the exact rule: alias scope, null behavior, datatype conversion, grouping rule, transaction behavior, or privilege rule.
Mixed question bank sessions: After topic drills, use mixed sets to practice switching between concepts under time pressure.
Mock exams: Use full-length practice only after your weak topics are improving; otherwise, mock exams mostly confirm the same gaps.

Final quick review routine

In your last study block before more practice, review in this order:

NULL rules and conditional functions.
Group functions, GROUP BY, and HAVING.
Join types and outer join predicate placement.
Subquery operators, especially IN, ANY, ALL, EXISTS, and NOT IN.
Set operator alignment rules.
DML vs DDL transaction behavior.
Constraints, views, sequences, synonyms, and privileges.

Next step: move from reading to doing—start a focused 1Z0-071 question bank session with topic drills and detailed explanations, then use your missed questions to drive the next review pass.

Put the review into practice

Study Plan

1Z0-071 — Oracle Database SQL Cheat Sheet

Core SELECT Processing

Logical Query Order vs Written Order

Alias Rules

Logical clause order

Alias rules that often appear in exam questions

Filtering, Sorting, and Operators

Comparison and Null Logic

Boolean Precedence

Sorting Rules

Single-Row Functions

Character Functions

Number Functions

Date Functions and Date Arithmetic

Single-row functions

Character functions

Number functions

Date functions

Conversion functions and format models

Conditional expressions

Conversion and Conditional Expressions

Conversion Functions

Null and Conditional Functions

Aggregate Functions and Grouping

Aggregate Function Behavior

GROUP BY Rules

Joins

Join Type Selection

Outer Join Filter Trap

USING and NATURAL JOIN Traps

Join types

ON, USING, and natural joins

Outer join predicate trap

Subqueries

Subquery Types

ANY, ALL, IN, EXISTS

Subquery Traps

Subquery types

Operator decision table

NOT IN and NULL

Correlated subquery reading method

Set Operators

Set Operator Matrix

Set Operator Rules

Set operators

Set operator rules

Row Limiting and Top-N Queries

DML: INSERT, UPDATE, DELETE, MERGE

DML Command Reference

MERGE Pattern

Transaction Control

DML statements

Transaction control

DDL and Schema Objects

Table DDL

Constraint Reference

Constraint Actions and Traps

Views

Sequences

Indexes and synonyms

Views, Sequences, Synonyms, and Indexes

Views

Sequences

Indexes and Synonyms

Privileges and Access Control Basics

High-Yield Error Patterns

Mini Decision Tables

WHERE vs HAVING

Join vs Subquery

DELETE vs TRUNCATE vs DROP

Exam-Day SQL Checklist

High-yield review map

Filtering, sorting, and row conditions

Predicates and operators

NULL rules

Sorting rules

Group functions and aggregation

Aggregate function essentials

GROUP BY decision rule

WHERE vs HAVING

`ON`, `USING`, and natural joins

`NOT IN` and `NULL`

`NULL` rules

`GROUP BY` decision rule

`WHERE` vs `HAVING`