1Z0-071 — Oracle Database SQL Exam Blueprint

How to Use This Exam Blueprint

Use this independent Exam Blueprint as a readiness map for the Oracle Database SQL (1Z0-071) exam. It is not a replacement for Oracle documentation or hands-on SQL practice. Work through each area until you can read a SQL question, predict the result, identify syntax errors, and choose the best statement without guessing.

For each topic, ask:

• Can I explain what the statement does before running it?
• Can I predict the returned rows, column names, sort order, and null behavior?
• Can I identify why an option is invalid SQL?
• Can I choose between similar constructs such as WHERE vs. HAVING, IN vs. EXISTS, UNION vs. UNION ALL, or DELETE vs. TRUNCATE?
• Can I handle Oracle-specific SQL behavior around dates, character functions, conversion functions, aliases, and nulls?

Topic-area readiness table

Core SQL statement checklist

Basic query structure

Make sure you can write and troubleshoot this pattern without hesitation:

SELECT DISTINCT
       column_name AS alias_name,
       expression
FROM   table_name
WHERE  row_filter
ORDER BY alias_name DESC;

Check yourself:

• I know the required clauses and optional clauses in a basic SELECT.
• I can distinguish a column alias from a table alias.
• I know when double quotes make an alias case-sensitive or allow spaces.
• I can identify when DISTINCT applies to the full selected row, not just one column.
• I can predict the result of arithmetic expressions involving NULL.
• I know that string literals use single quotes.
• I can recognize invalid attempts to use a SELECT alias in the WHERE clause.
• I can use ORDER BY with a column name, alias, expression, or position when valid.

Filtering and logical conditions

Can you do this?

• Predict which rows pass a filter with mixed AND and OR.
• Rewrite a confusing condition using parentheses.
• Identify why WHERE bonus = NULL returns no rows.
• Escape or reason through wildcard characters in LIKE scenarios.
• Determine whether NOT BETWEEN and NOT IN are appropriate.
• Explain why NOT IN can be dangerous when the list or subquery contains NULL.

Functions and expression readiness

Character functions

Practice prompt:

SELECT employee_id,
       UPPER(last_name) AS name_key,
       SUBSTR(last_name, 1, 3) AS prefix,
       LENGTH(last_name) AS name_length
FROM   employees;

You should be able to answer:

• What data type does each expression return?
• Is the function applied once per row or once per result set?
• What happens if the input value is NULL?
• Which functions are useful in WHERE filters?
• How can applying a function to a column affect index usage conceptually?

Numeric functions

Review:

• ROUND
• TRUNC
• MOD
• Sign and precision effects
• Difference between rounding and truncating
• Behavior with negative numbers where covered by your practice materials

Can you predict the output?

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

Date functions and date arithmetic

Oracle SQL date questions often test small details. Be ready to work with:

Can you do this?

• Add 7 days to a date.
• Find the number of days between two dates.
• Format a date as a character string with TO_CHAR.
• Convert a character string to a date with TO_DATE.
• Recognize when implicit date conversion is risky.
• Predict whether a date comparison includes a time component.

Example:

SELECT employee_id,
       hire_date,
       ADD_MONTHS(hire_date, 6) AS review_date,
       LAST_DAY(hire_date) AS hire_month_end
FROM   employees;

Conversion and null-handling functions

Checklist:

• I can choose NVL vs. COALESCE.
• I can write both simple and searched CASE expressions.
• I can identify type mismatch problems in null-handling expressions.
• I can explain why explicit conversion is safer than relying on implicit conversion.
• I can predict output when all expressions are null.

Example:

SELECT employee_id,
       salary,
       commission_pct,
       CASE
         WHEN commission_pct IS NULL THEN 'No commission'
         WHEN commission_pct > 0 THEN 'Commissioned'
         ELSE 'Check'
       END AS commission_status
FROM   employees;

Aggregation and grouping checklist

Aggregate functions

Know the difference between row-level expressions and group-level results.

Can you do this?

• Explain why COUNT(*) and COUNT(commission_pct) can return different values.
• Identify every non-aggregated column that must appear in GROUP BY.
• Use WHERE to filter rows before grouping.
• Use HAVING to filter groups after grouping.
• Predict results for groups containing only null values.
• Recognize invalid use of aggregate functions in WHERE.

Grouping decision table

Example:

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG salary_avg
FROM   employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Readiness check:

• Can you spot the syntax issue in the example?
• Can you correct the aggregate expression?
• Can you explain whether department_id may be null and how that affects grouping?
• Can you move conditions between WHERE and HAVING only when logically valid?

Corrected pattern:

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS salary_avg
FROM   employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Join readiness

Join types to master

Core pattern:

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

Outer join pattern:

SELECT d.department_name,
       e.employee_id
FROM   departments d
       LEFT OUTER JOIN employees e
         ON d.department_id = e.department_id;

Can you do this?

• Choose inner vs. outer join based on whether unmatched rows must be preserved.
• Identify the driving table in a left or right outer join.
• Explain why join predicates belong in ON.
• Explain when row filters belong in WHERE.
• Use aliases consistently in multi-table queries.
• Recognize a Cartesian product.
• Write a self join for employee-manager style data.
• Predict nulls introduced by an outer join.

Outer join filter trap

Be ready for questions like this:

SELECT d.department_name,
       e.employee_id
FROM   departments d
       LEFT OUTER JOIN employees e
         ON d.department_id = e.department_id
WHERE  e.salary > 5000;

Ask:

• Does the WHERE clause eliminate rows where e.salary is null?
• Does this still preserve departments with no employees?
• Should the salary condition be part of the join condition instead?
• What result is the question asking for: all departments, or only departments with qualifying employees?

Subquery checklist

Subquery types

Single-row example:

SELECT employee_id, last_name, salary
FROM   employees
WHERE  salary > (
         SELECT AVG(salary)
         FROM   employees
       );

Multiple-row example:

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  department_id IN (
         SELECT department_id
         FROM   departments
         WHERE  location_id = 1700
       );

Correlated example:

SELECT d.department_id, d.department_name
FROM   departments d
WHERE  EXISTS (
         SELECT 1
         FROM   employees e
         WHERE  e.department_id = d.department_id
       );

Can you do this?

• Match subquery result cardinality to the correct operator.
• Identify when a single-row subquery returns too many rows.
• Choose IN when comparing against a list of values.
• Choose EXISTS when testing whether related rows exist.
• Explain how a correlated subquery is evaluated conceptually.
• Avoid NOT IN surprises when the subquery can return NULL.
• Use subqueries in WHERE, HAVING, FROM, or SELECT when valid.
• Distinguish a subquery from a join solution.

Subquery decision prompts

Set operator readiness

Set operator comparison

Rules to review:

• Each query must return the same number of columns.
• Corresponding columns must be compatible by data type.
• Final column names usually come from the first query.
• ORDER BY belongs at the end of the combined query.
• Duplicates are evaluated across the full row, not one column.
• Parentheses may be needed to make intended order clear in complex combinations.

Example:

SELECT employee_id, email
FROM   employees
WHERE  department_id = 10
UNION
SELECT employee_id, email
FROM   employees
WHERE  department_id = 20
ORDER BY employee_id;

Can you do this?

• Predict whether duplicates remain.
• Identify invalid set operations caused by mismatched column counts.
• Explain why UNION ALL can return more rows than UNION.
• Determine which query controls displayed column aliases.
• Choose MINUS for “in A but not in B” scenarios.

DML, transactions, and data changes

DML statement readiness

Example:

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

Ask before choosing an answer:

• Which rows are affected?
• What happens if the WHERE clause is omitted?
• Are constraints checked?
• Are data types compatible?
• Is the statement DML or DDL?
• Can the change be rolled back in the scenario given?

Transaction control checklist

Review:

• COMMIT makes current transaction changes permanent.
• ROLLBACK undoes uncommitted transaction changes.
• SAVEPOINT marks a point to roll back to.
• Some statements may implicitly end a transaction depending on statement type and Oracle behavior.
• Statement failure is not always the same as full transaction rollback.
• Locks and read consistency may appear conceptually, even if not deeply tested.

Core patterns:

SAVEPOINT before_raise;

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

ROLLBACK TO before_raise;

Can you do this?

• Determine whether data remains changed after ROLLBACK.
• Determine whether a COMMIT prevents later rollback of earlier changes.
• Identify DML vs. DDL transaction implications.
• Explain why transaction order matters in multi-statement scenarios.

DDL and table management checklist

Table creation and data types

Be ready to read and write statements like:

CREATE TABLE project_assignments (
  assignment_id NUMBER,
  employee_id   NUMBER NOT NULL,
  project_code  VARCHAR2(30),
  start_date    DATE DEFAULT SYSDATE,
  status        VARCHAR2(20),
  CONSTRAINT project_assignments_pk PRIMARY KEY (assignment_id)
);

Review:

• Common Oracle data types in exam-level SQL: character, numeric, date/time-related types.
• Difference between CHAR and VARCHAR2 at a practical level.
• Column defaults.
• NOT NULL constraints.
• Naming constraints.
• Creating a table from a query where included in your study scope.
• Effects of DROP, ALTER, RENAME, and TRUNCATE at exam level.

Constraint readiness

Can you do this?

• Add a primary key to an existing table.
• Add a foreign key referencing another table.
• Identify why an insert violates a constraint.
• Determine whether a column can accept NULL.
• Explain difference between a constraint and an index at a high level.
• Recognize valid column-level vs. table-level constraint syntax.

Example:

ALTER TABLE project_assignments
ADD CONSTRAINT project_assignments_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id);

Schema object checks

Depending on the exact preparation materials you are using for Oracle Database SQL (1Z0-071), be ready for exam-level questions involving supporting schema objects.

Sequence example:

INSERT INTO project_assignments (assignment_id, employee_id, project_code)
VALUES (project_assignment_seq.NEXTVAL, 101, 'SQL-UPGRADE');

Checklist:

• I can recognize basic CREATE INDEX syntax.
• I know that indexes are not selected directly in ordinary queries.
• I can use a sequence-generated value in an INSERT.
• I understand that a view stores a query definition, not necessarily a separate copy of rows.
• I can identify when a synonym is used to simplify object references.

Oracle SQL syntax and clause-order checks

Clause-order readiness

Know the normal written order:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

For set operators, the final ORDER BY appears after the combined query.

Can you do this?

• Put clauses in valid written order.
• Identify clauses that cannot appear together in a proposed way.
• Spot missing commas between selected expressions.
• Spot missing parentheses around function arguments.
• Identify invalid alias references.
• Confirm whether a function is single-row or aggregate.
• Check data type compatibility in expressions and comparisons.

Alias visibility

Scenario and decision-point checks

Choose the right construct

Common exam-style cues

Use these prompts when reading a question stem:

• “All departments, including departments with no employees” means outer join.
• “Employees who do not belong to any listed department” may involve null-aware logic.
• “More than the average” usually signals an aggregate subquery.
• “At least one” often maps to EXISTS, IN, or ANY.
• “Every” or “all values” may map to ALL.
• “Remove duplicate rows” points to DISTINCT or UNION.
• “Keep duplicate rows” points to UNION ALL.
• “After grouping” points to HAVING.
• “Format the date as text” points to TO_CHAR.
• “Convert the text into a date” points to TO_DATE.
• “Generate a new numeric key” may point to a sequence.
• “Undo only part of the transaction” points to a savepoint.

Common weak areas and traps

Hands-on practice checklist

Use a small practice schema and make sure you can complete these tasks from memory.

Querying tasks

• Select specific columns with readable aliases.
• Concatenate character values with literals.
• Remove duplicate rows with DISTINCT.
• Filter rows using IN, BETWEEN, LIKE, and null checks.
• Sort by alias and by multiple columns.
• Use character functions in both SELECT and WHERE.
• Use numeric functions and predict rounding/truncation.
• Use date functions and perform date arithmetic.
• Convert dates and numbers to formatted character output.
• Write simple and searched CASE expressions.

Join tasks

• Join two tables with ON.
• Join three tables with clear aliases.
• Write a left outer join that preserves unmatched parent rows.
• Write a self join.
• Identify and correct an accidental Cartesian product.
• Compare ON, USING, and NATURAL JOIN behavior.

Aggregation tasks

• Count all rows and count non-null values.
• Group rows by one column.
• Group rows by multiple columns.
• Filter rows before grouping.
• Filter groups after aggregation.
• Use a subquery with an aggregate result.

Subquery and set-operator tasks

• Write a single-row subquery.
• Write a multiple-row subquery using IN.
• Write a correlated subquery using EXISTS.
• Rewrite a subquery as a join when appropriate.
• Combine results with UNION.
• Preserve duplicates with UNION ALL.
• Return rows in one query but not another with MINUS.

DML and DDL tasks

• Insert a row with all columns listed.
• Insert rows using a subquery.
• Update only selected rows.
• Delete only selected rows.
• Use COMMIT, ROLLBACK, and SAVEPOINT.
• Create a table with primary key and foreign key constraints.
• Add, modify, or drop a column where valid.
• Add constraints with ALTER TABLE.
• Recognize basic index, sequence, view, or synonym usage if included in your review.

Final-week checklist

Seven-day review priorities

Final review “must know” list

• Clause order for SELECT statements.
• Difference between WHERE and HAVING.
• Difference between COUNT(*) and COUNT(column).
• How nulls affect comparisons and expressions.
• How outer joins preserve unmatched rows.
• How subquery result shape controls valid operators.
• How UNION, UNION ALL, INTERSECT, and MINUS differ.
• How explicit conversion functions work.
• How CASE, NVL, COALESCE, and related expressions handle nulls.
• How COMMIT, ROLLBACK, and savepoints affect changes.
• Basic DDL syntax for tables and constraints.
• Common reasons SQL statements are invalid.

Readiness scorecard

Use this quick self-assessment before scheduling or taking a full practice exam.

Practical next step

Pick one weak area from the scorecard and do a focused practice block before attempting another mixed set. For Oracle Database SQL (1Z0-071), the best improvement usually comes from writing SQL, predicting the result, then checking exactly why each wrong answer is wrong: syntax, data type, null behavior, join logic, grouping logic, or transaction effect.