1Z0-071 — Oracle Database SQL Quick Reference

Last revised: June 29, 2026

Compact 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.

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

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;

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

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

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;

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')`

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

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

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

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

-- 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
);

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

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;

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

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;

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

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, 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

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.

Practical Next Step

Use this Quick Reference as a checklist while working original Oracle Database SQL (1Z0-071) practice questions. For every missed item, map the mistake back to one rule here: null logic, grouping, join preservation, subquery cardinality, set operator compatibility, or DDL/DML transaction behavior.

Scenario Guide

Relational Database Concepts