1Z0-071 — Oracle Database SQL Quick Review

Last revised: June 29, 2026

Quick Review for Oracle Database SQL (1Z0-071): high-yield SQL rules, traps, decision points, and practice focus for Oracle exam candidates.

Quick Review purpose

This Quick Review is for candidates preparing for Oracle Oracle Database SQL (1Z0-071), exam code 1Z0-071. It is IT Mastery review support: use it to refresh high-yield SQL concepts, then validate your understanding with IT Mastery practice, original practice questions, topic drills, mock exams, and detailed explanations.

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.

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`

Core `SELECT` processing

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

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.

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

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

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.

Joins

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

DML and transaction control

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

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

Schema objects

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

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`

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.

Continue in IT Mastery

Use this Quick Review as a final concept map, then move into IT Mastery for focused topic drills, mixed practice sets, timed mock exams, and detailed explanations. The practice questions are original IT Mastery practice items; they are not official Oracle questions, copied live-exam content, or exam dumps.

Study Plan