Why Study with PlanetCert?
The Latest Questions
Practice questions and exam topics aligned with the current exam objectives.
Detailed Explanations
Go beyond the answer. Master the material with comprehensive learning and professional explanations for every concept.
AI-Powered Insights
Personalized preparation guidance that adapts to your performance and identifies weak spots automatically.
Exam Information
Official specifications published by Oracle
Exam Format
Registration
Validity
Skills this practice test covers
- Write SELECT statements with column aliases, the DISTINCT keyword and the concatenation operator.
- Predict how NULL values behave in arithmetic, comparisons and group functions.
- Convert data types with TO_CHAR, TO_NUMBER and TO_DATE and their format models.
- Join tables with inner, outer, self and non-equijoins, and spot an accidental Cartesian product.
- Write single-row, multiple-row and correlated subqueries.
- Create tables, constraints, sequences and views, and control transactions with COMMIT and ROLLBACK.
Explore related topics
1z0-071 exam content
16 domains · 21 topics · 505 questions
Relational Database Concepts
This short domain covers the ideas under every query: tables, keys and relationships. Candidates read an entity relationship diagram and connect it to the parts of a SELECT statement.
Skills tested
- Explain the difference between the logical and the physical structure of a relational database.
- Relate the clauses of a SELECT statement to the entities and relationships in an ERD.
- Identify primary keys and foreign keys and the relationships they create.
Official topics
- Database Fundamentals
Retrieving Data Using SQL SELECT Statement
This domain covers the basic SELECT statement and how Oracle displays its results. Small details matter here, such as how an alias is quoted or how NULL behaves in arithmetic.
Skills tested
- Give columns aliases, with and without double quotes.
- Join text with the concatenation operator and write literals with the alternative quote operator.
- Remove duplicate rows with the DISTINCT keyword.
- Predict the result of an arithmetic expression that involves NULL.
Official topics
- Basic SELECT Statement Components
Restricting and Sorting Data
Here candidates filter and order rows. The domain covers the WHERE clause and its operators, sorting, row limiting and substitution variables.
Skills tested
- Apply the rules of precedence for AND, OR and NOT in a WHERE clause.
- Limit rows with FETCH FIRST n ROWS ONLY, and with the OFFSET and WITH TIES options.
- Use substitution variables with & and &&, and control them with DEFINE and VERIFY.
- Sort results by column, alias or position, with NULLS FIRST or NULLS LAST.
Official topics
- Filtering and Ordering Results
Using Single-Row Functions
Single-row functions return one value for each row. Candidates use character, number and date functions in SELECT and WHERE clauses.
Skills tested
- Manipulate strings with functions such as SUBSTR, INSTR, LPAD and CONCAT.
- Round and truncate numbers with ROUND and TRUNC, and find remainders with MOD.
- Do date arithmetic and use functions such as ADD_MONTHS, MONTHS_BETWEEN and LAST_DAY.
Official topics
- Character Functions
- Numeric Functions
- Date Functions
Using Conversion Functions and Conditional Expressions
This domain is about data types. Candidates convert values with explicit functions, predict implicit conversion and handle NULL values with conditional functions.
Skills tested
- Convert values with TO_CHAR, TO_NUMBER and TO_DATE and the right format model.
- Predict when Oracle converts a data type implicitly.
- Replace or compare NULL values with NVL, NULLIF and COALESCE.
- Nest functions and work out the order in which they run.
Official topics
- Data Type Conversion
- Conditional Functions
Reporting Aggregated Data Using Group Functions
Group functions turn many rows into one result. Candidates group data, filter the groups and avoid the errors caused by mixing grouped and ungrouped columns.
Skills tested
- Use COUNT, SUM, AVG, MIN and MAX, and explain how each one treats NULL.
- Group rows by one or more columns with GROUP BY.
- Filter groups with HAVING and explain how it differs from WHERE.
Official topics
- Aggregate Functions
Displaying Data from Multiple Tables
This domain combines rows from several tables. Candidates use each join type in ANSI syntax and in Oracle's own syntax, and recognize the result of a missing join condition.
Skills tested
- Write inner joins with ON, USING and NATURAL JOIN.
- Return unmatched rows with LEFT, RIGHT and FULL OUTER JOIN.
- Join a table to itself with a self-join.
- Join on a range of values with a non-equijoin.
- Recognize a Cartesian product and write a CROSS JOIN on purpose.
Official topics
- Join Operations
Using Subqueries
A subquery is a query inside another statement. Candidates choose the right kind and use subqueries to update and delete rows as well as to filter them.
Skills tested
- Write single-row subqueries with comparison operators.
- Write multiple-row subqueries with IN, ANY and ALL.
- Use a correlated subquery to update or delete rows.
- Explain what NOT IN returns when the subquery contains a NULL.
Official topics
- Subquery Types
Using SET Operators
Set operators combine the results of two queries. Candidates match the column lists and know how each operator treats duplicates and sorting.
Skills tested
- Combine results with UNION and UNION ALL and explain the difference.
- Return common or missing rows with INTERSECT and MINUS.
- Match the number and data types of the columns in each SELECT.
- Place ORDER BY correctly in a compound query.
Official topics
- Set Operations
Managing Tables Using DML Statements
DML statements change data. Candidates insert, update, delete and merge rows, and control when those changes become permanent.
Skills tested
- Insert rows with VALUES and with a subquery, including multitable inserts.
- Update and delete rows that meet a condition.
- Combine insert and update logic in one MERGE statement.
- Control transactions with COMMIT, ROLLBACK and SAVEPOINT, and know which statements commit implicitly.
Official topics
- Data Manipulation
- Transaction Control
Managing Indexes, Synonyms, and Sequences
This domain covers three supporting objects. Indexes speed up queries, synonyms give objects another name, and sequences generate numbers.
Skills tested
- Create and drop indexes, including composite and function-based indexes.
- Create public and private synonyms.
- Create a sequence and use NEXTVAL and CURRVAL.
Official topics
- Database Objects
Using DDL to Manage Tables and Relationships
DDL statements define the tables themselves. Candidates create and change tables and columns, add constraints and remove tables safely.
Skills tested
- Create tables with suitable data types, including with CREATE TABLE AS SELECT.
- Add, modify and drop columns, or set them UNUSED, with ALTER TABLE.
- Define PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL constraints.
- Create and use temporary tables and external tables.
- Tell DROP, TRUNCATE and DELETE apart.
Official topics
- Table Management
- Constraints
Managing Views
A view is a stored query that behaves like a table. Candidates create and change views and know when DML through a view is allowed.
Skills tested
- Create and change views with CREATE OR REPLACE VIEW.
- Explain which views allow DML and what WITH CHECK OPTION does.
Official topics
- Views
Controlling User Access
This domain covers who may do what in the database. Candidates separate system privileges from object privileges and grant them directly or through roles.
Skills tested
- Tell system privileges from object privileges.
- Grant and revoke privileges on tables.
- Create a role, grant privileges to it and grant the role to users.
Official topics
- Privileges and Roles
Managing Objects with Data Dictionary Views
The data dictionary describes every object in the database. Candidates query it to find tables, columns and constraints.
Skills tested
- Query USER_, ALL_ and DBA_ views to find objects and their owners.
- Use DICTIONARY and DICT_COLUMNS to find the right view.
Official topics
- Data Dictionary
Managing Data in Different Time Zones
This domain covers dates and times across time zones. Candidates compare the functions that return the session's date and time, and store spans of time with interval data types.
Skills tested
- Compare CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP.
- Store and calculate durations with INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.
Official topics
- Time Zone Management
Requirements
- No formal prerequisites: anyone can book the exam.
- Recommended: a basic understanding of relational database concepts such as tables and keys.
- Recommended: general computing knowledge and some experience working at a command line.
About the 1z0-071 exam
1Z0-071 is Oracle's SQL exam for developers, analysts and future database administrators. It checks exact knowledge of how Oracle SQL behaves: what a query returns, how NULL values change a result and when a statement fails. The topics run from SELECT basics and functions to joins, subqueries, DDL, user access and time zones. A pass earns the Oracle Database SQL Certified Associate, which does not expire.
Every question in the PlanetCert practice test has an explanation that points to the rule behind the answer, such as how NULL behaves in a comparison. When a query still looks right, Brainy AI Assistant can explain why it fails, or rewrite an Oracle-style join in ANSI syntax. Study mode suits the join and subquery domains, and flashcard mode suits function names and format models. Running a missed query in Oracle Live SQL and comparing the output makes each rule stick.
How to earn the certification
- Earns
- Oracle Database SQL Certified AssociateAssociate
The certification does not expire.
- After
- 1Z0-149 Oracle Database Program with PL/SQL
The next step for developers: procedures, functions, packages and triggers written in PL/SQL.
- 1Z0-182 Oracle Database 23ai Administration Associate
Oracle's associate exam for administering Oracle Database 23ai.
- 1Z0-149 Oracle Database Program with PL/SQL
How to prepare for 1z0-071
- 1
Set up a place to run SQL
Use Oracle Live SQL in a browser or a local Oracle database with a sample schema. Every topic on this exam can be tried there.
- 2
Learn the NULL rules first
NULL affects arithmetic, comparisons, group functions, NOT IN subqueries and sorting. Write a small test query for each case and keep the results as notes.
- 3
Rewrite joins in both syntaxes
Write each join in ANSI syntax and, where possible, in Oracle's own syntax, which marks outer joins with (+). Compare the rows each version returns.
- 4
Practise DDL and transactions together
Create a table, insert rows, add a savepoint, run an ALTER TABLE and then roll back. The rows that survive show how implicit commits work.
- 5
Drill by domain, then time it
Work through the 1Z0-071 practice test domain by domain in study mode, then take timed runs. Run any query that went wrong in the database to see the actual output.
Start a timed 1z0-071run
Oracle's own preparation is the learning path "Earn the Oracle Database SQL Certified Associate Credential", which is part of the Oracle Technology Learning Subscription.
Who 1z0-071 is for
- Application developers who write SQL against Oracle databases.
- Analysts who pull reports from Oracle data with their own queries.
- Junior database administrators who plan to take Oracle's DBA exams next.
- Students who want a recognized first certification in SQL.
Typical job titles: SQL developer · Database developer · Data analyst · Junior database administrator · Business intelligence developer
1z0-071 glossary
18 terms from the exam objectives and the vendor's official material, each defined in a sentence.
- Alternative quote operator
- Oracle's q operator, written as q'[...]', which lets a string contain single quotes without doubling them.
- Cartesian product
- The result of joining every row of one table to every row of another, usually caused by a missing join condition.
- COALESCE function
- A function that returns the first value in its list of arguments that is not NULL.
- Column aliases
- Other names for columns in a query's output, written after the column, with or without the AS keyword.
- Correlated subquery
- A subquery that refers to a column of the outer query, so it runs once for each row of the outer query.
- Entity relationship diagram (ERD)
- A diagram of a database's entities, their attributes and the relationships between them.
- External tables
- Tables whose data stays in files outside the database, which Oracle reads through an access driver.
- Global temporary tables (GTT)
- Tables whose definition is permanent but whose rows are private to one session or transaction and disappear at its end.
- Implicit data type conversion
- A conversion Oracle makes on its own, such as turning the string '10' into a number in a comparison.
- INTERVAL DAY TO SECOND
- A data type that stores a span of time in days, hours, minutes and seconds.
- MERGE statement
- A DML statement that updates matching rows and inserts new ones in a single step.
- MINUS operator
- A set operator that returns the rows of the first query that the second query does not return.
- Non-equijoins
- Joins on a condition other than equality, such as matching an order total to a discount band with BETWEEN.
- NVL
- A function that replaces NULL with a value given in the call, such as NVL(commission_pct, 0).
- Object privileges
- Rights to act on one specific object, such as SELECT on a single table.
- SAVEPOINT
- A named point inside a transaction to which a ROLLBACK can return without undoing the whole transaction.
- Self-joins
- Joins of a table to itself, used for example to list each employee next to that employee's manager.
- Substitution variables
- Placeholders such as &dept_id that SQL*Plus or SQL Developer replaces with a value before the statement runs.