• 78 questions

Oracle 1Z0-071 Practice Test

Questions for the Oracle Database SQL exam, 1Z0-071, each answer linked to the Oracle documentation page behind it.

colors has 4 rows and sizes has 6 rows. How many rows does SELECT * FROM colors CROSS JOIN sizes return?

Pick an answer, then press Check answer. Then you see Correct or Not quite, with the reason. Keys: A to D pick, Enter checks.

Print covers the next 63 questions. Share this set copies a link to the same questions in the same order.

Exam mode: 63 questions, 120 minutes, 40 right to pass (63%).

More options

Unofficial practice questions, not affiliated with Oracle. Bank checked October 9, 2026.

Next step Another IT certification? Pick it

All 78 questions

Relational database concepts 4

  • A design says every row of ORDERS belongs to one existing row of CUSTOMERS. Which constraint on ORDERS makes the database enforce that link? Database Concepts, Data Integrity, Foreign Key Constraints
  • After a query is submitted, the database chooses to read an index to find the rows. In Oracle's terms, what kind of operation is that choice? Database Concepts, Introduction to Oracle Database, Relational Database Management System (RDBMS)
  • In Oracle's description of the relational model, a table shows a relation as rows and columns. What is each row? Database Concepts, Introduction to Oracle Database, Relational Model
  • SQL is called a declarative, nonprocedural language. What does that say about a query you write? Database Concepts, SQL, Introduction to SQL

Retrieving data using the SQL SELECT statement 5

  • A query's select list contains price * qty AS line_total. In which other clause of that same query can the alias line_total be used? SQL Language Reference, SELECT, c_alias
  • In one row, salary is 4000 and bonus is null. What does salary + bonus return for that row? SQL Language Reference, Nulls
  • middle_name is null in one row. What does first_name || middle_name || last_name return for that row? SQL Language Reference, Concatenation Operator
  • What does DISTINCT remove in SELECT DISTINCT city, state FROM offices? SQL Language Reference, SELECT, DISTINCT | UNIQUE
  • Which text literal uses Oracle's alternative quoting mechanism correctly for the text It's due? SQL Language Reference, Literals, Text Literals
Show all 78 questions (69 more)

Restricting and sorting data 7

  • A query ends with ORDER BY commission DESC and has no NULLS clause. Where do rows with a null commission appear? SQL Language Reference, SELECT, order_by_clause
  • A query ends with ORDER BY score DESC FETCH FIRST 3 ROWS WITH TIES. The third and fourth scores are equal and the fifth is lower. How many rows come back? SQL Language Reference, SELECT, row_limiting_clause
  • A SQL*Plus script refers to &&region in three places. When the script is first run in a session, how many times is the user asked for region? SQL*Plus User's Guide and Reference, Using Scripts in SQL*Plus, Avoiding Unnecessary Prompts for Values
  • In SQL*Plus, DEFINE dept = 40 creates a substitution variable. What type of value does it hold? SQL*Plus User's Guide and Reference, DEFINE
  • In SQL*Plus, what changes after SET VERIFY OFF? SQL*Plus User's Guide and Reference, SET System Variable Summary, SET VERIFY
  • Which condition matches part codes of any length whose second character is X? SQL Language Reference, Pattern-matching Conditions, LIKE Condition
  • Which rows does WHERE status = 'OPEN' OR status = 'HELD' AND total > 500 return? SQL Language Reference, About SQL Conditions, Condition Precedence

Using single-row functions to customize output 6

  • ship_date and order_date are DATE columns. What does ship_date - order_date return? SQL Language Reference, Data Types, Datetime/Interval Arithmetic
  • What does ADD_MONTHS(DATE '2026-01-31', 1) return? SQL Language Reference, ADD_MONTHS
  • What does MOD(25, 0) return? SQL Language Reference, MOD
  • What does ROUND(1264.5, -2) return? SQL Language Reference, ROUND (number)
  • What does SUBSTR('WAREHOUSE', -5, 3) return? SQL Language Reference, SUBSTR
  • What does TRUNC(87.68, 1) return? SQL Language Reference, TRUNC (number)

Using conversion functions and conditional expressions 5

  • For a row with grade 'C', what does CASE grade WHEN 'A' THEN 'Top' WHEN 'B' THEN 'Mid' END return? SQL Language Reference, CASE Expressions
  • In one row mobile is null and office is '555-0100'. What does COALESCE(mobile, office, 'none') return? SQL Language Reference, COALESCE
  • qty is a NUMBER column. How does Oracle evaluate WHERE qty = '12'? SQL Language Reference, Data Type Comparison Rules, Implicit Data Conversion
  • qty_ordered and qty_shipped are both 12 in one row. What does NVL(NULLIF(qty_ordered, qty_shipped), 0) return for it? SQL Language Reference, NULLIF and NVL
  • With American date language settings, what does TO_CHAR(DATE '2026-03-05', 'fmDD Month YYYY') return? SQL Language Reference, Format Models, Format Model Modifiers

Reporting aggregated data using group functions 4

  • A table has 20 rows, and region is null in 5 of them. What does SELECT COUNT(region) return for that table? SQL Language Reference, COUNT
  • bonus holds 100, 200 and null in a table's three rows. What does AVG(bonus) return? SQL Language Reference, Aggregate Functions
  • What happens when this runs: SELECT region, rep, SUM(amount) FROM sales GROUP BY region? SQL Language Reference, SELECT, Restrictions on the Select List
  • What happens when this runs: SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 10000 GROUP BY region? SQL Language Reference, Aggregate Functions

Displaying data from multiple tables 6

  • colors has 4 rows and sizes has 6 rows. How many rows does SELECT * FROM colors CROSS JOIN sizes return? SQL Language Reference, Joins, Cartesian Products
  • crew(crew_id, name, lead_id) stores each member's lead as another row of crew. What must a query do to list each member beside the lead's name? SQL Language Reference, Joins, Self Joins
  • FROM customers c LEFT OUTER JOIN orders o ON c.id = o.customer_id is run. What does the result hold for a customer who has no orders? SQL Language Reference, Joins, Outer Joins
  • orders and customers share two column names: customer_id and region. On what does orders NATURAL JOIN customers match rows? SQL Language Reference, SELECT, NATURAL
  • When can a join be written as orders JOIN customers USING (customer_id)? SQL Language Reference, SELECT, USING (column)
  • Which join condition makes a non-equijoin? SQL Language Reference, Joins, Equijoins

Using subqueries to solve queries 4

  • A select list holds the scalar subquery (SELECT bin FROM bins b WHERE b.part_id = p.part_id). What happens for a part that has two rows in bins? SQL Language Reference, Scalar Subquery Expressions
  • DELETE FROM parts p WHERE NOT EXISTS (SELECT 1 FROM order_lines l WHERE l.part_id = p.part_id) uses a correlated subquery. Conceptually, how often is the subquery evaluated? SQL Language Reference, Using Subqueries
  • One row of blocked has a null supplier_id. What does WHERE supplier_id NOT IN (SELECT supplier_id FROM blocked) return? SQL Language Reference, IN Condition
  • Which parts does WHERE price > ALL (SELECT price FROM parts WHERE bin = 'A4') return? SQL Language Reference, Comparison Conditions, op ALL

Using SET operators 6

  • A statement reads A UNION B MINUS C, with no parentheses. In what order does Oracle evaluate the set operators? SQL Language Reference, The UNION [ALL], INTERSECT, MINUS Operators
  • Query A returns 1, 2 and 3. Query B returns 3 and 4. What does B MINUS A return? SQL Language Reference, Set Operators
  • Query A returns the values 1, 2 and 3. Query B returns 3 and 4. How many rows does A UNION ALL B return? SQL Language Reference, Set Operators
  • Query A returns the values 2, 2, 3 and 5. Query B returns 2, 2 and 7. How many rows does A INTERSECT B return? SQL Language Reference, Set Operators
  • What happens when this runs: SELECT part_id, name FROM parts UNION SELECT kit_id FROM kits? SQL Language Reference, The UNION [ALL], INTERSECT, MINUS Operators
  • Where can an ORDER BY clause be written in a compound query that joins two SELECT statements with UNION? SQL Language Reference, The UNION [ALL], INTERSECT, MINUS Operators, Restrictions on the Set Operators

Managing tables using DML statements 6

  • A session inserts three rows, runs CREATE TABLE audit_log (id NUMBER) and then ROLLBACK, with no COMMIT typed. What happened to the three rows? Database Concepts, Transactions, End of a Transaction
  • A session runs, in order: an INSERT, SAVEPOINT a, an UPDATE, SAVEPOINT b, a DELETE, then ROLLBACK TO SAVEPOINT a. Which changes are still part of the open transaction? SQL Language Reference, ROLLBACK, TO SAVEPOINT Clause
  • A statement begins MERGE INTO stock s USING deliveries d ON (s.part_id = d.part_id). Which column can its WHEN MATCHED THEN UPDATE clause not change? SQL Language Reference, MERGE, merge_update_clause
  • In a conditional multitable INSERT FIRST, one source row satisfies both the first and the second WHEN condition. Where is it inserted? SQL Language Reference, INSERT, conditional_insert_clause
  • parts has a column bin with DEFAULT 'A1'. What is stored in bin by INSERT INTO parts (part_id, name) VALUES (7, 'Hinge')? SQL Language Reference, INSERT, column
  • Which rows does UPDATE parts SET bin = 'B2' change when it has no WHERE clause? SQL Language Reference, UPDATE, where_clause

Managing indexes, synonyms and sequences 4

  • After CREATE PUBLIC SYNONYM parts FOR stock.parts, what else does another user need in order to query parts through the synonym? SQL Language Reference, CREATE SYNONYM, PUBLIC
  • In a new session, the first reference to a sequence is order_seq.CURRVAL. What happens? SQL Language Reference, Sequence Pseudocolumns
  • No index exists on bins when a primary key constraint is added on bin_id. What does Oracle do about an index? SQL Language Reference, constraint, Primary Key Constraints
  • order_seq increments by 1 and only one session uses it. A transaction takes order_seq.NEXTVAL, gets 41, and rolls back. What does the next order_seq.NEXTVAL return? SQL Language Reference, CREATE SEQUENCE

Use DDL to manage tables and their relationships 10

  • code is a CHAR(5) column. What is stored when 'AB' is inserted into it? SQL Language Reference, Data Types, CHAR Data Type
  • CREATE GLOBAL TEMPORARY TABLE pick_list (part_id NUMBER) ON COMMIT PRESERVE ROWS creates a temporary table. When does Oracle remove a session's rows from it? SQL Language Reference, CREATE TABLE, ON COMMIT
  • email has a UNIQUE constraint and no NOT NULL constraint. How many rows may hold a null email? SQL Language Reference, constraint, Unique Constraints
  • orders.customer_id references customers, and the foreign key has no ON DELETE clause. What happens on an attempt to delete a customer that has orders? SQL Language Reference, constraint, ON DELETE Clause
  • parts has a primary key on part_id and an explicitly created NOT NULL constraint on name. Which of them does CREATE TABLE parts_copy AS SELECT * FROM parts carry over? SQL Language Reference, CREATE TABLE, AS subquery
  • parts_feed is an external table. Which statement can be run against it? Database Concepts, Tables and Table Clusters, Overview of External Tables
  • price is a NUMBER(5,2) column. What happens when 123.456 is inserted into it? SQL Language Reference, Data Types, NUMBER Data Type
  • What does ALTER TABLE parts SET UNUSED (notes) do? SQL Language Reference, ALTER TABLE, SET UNUSED Clause
  • Which statement about TRUNCATE TABLE log_rows is true? SQL Language Reference, TRUNCATE TABLE
  • Which two rules does a primary key constraint combine in one declaration? SQL Language Reference, constraint

Managing views 2

  • open_orders is created by CREATE VIEW open_orders AS SELECT * FROM orders WHERE status = 'OPEN' WITH CHECK OPTION. What happens to UPDATE open_orders SET status = 'SHIPPED'? SQL Language Reference, CREATE VIEW, WITH CHECK OPTION
  • region_totals is created by CREATE VIEW region_totals AS SELECT region, SUM(amount) total FROM sales GROUP BY region. Can rows of sales be deleted through this view? SQL Language Reference, CREATE VIEW, Notes on Updatable Views

Controlling user access 4

  • To whom can a role be granted? SQL Language Reference, CREATE ROLE
  • What does WITH GRANT OPTION add in GRANT SELECT ON parts TO ana WITH GRANT OPTION? SQL Language Reference, GRANT, WITH GRANT OPTION
  • Which object privilege cannot be limited to named columns in a GRANT statement? SQL Language Reference, GRANT, column
  • Which of these is a system privilege? SQL Language Reference, GRANT, System Privileges

Managing objects with data dictionary views 2

  • Which prefix marks the data dictionary views that are limited to the objects a user has privileges on, the user's own objects included? Database Concepts, Data Dictionary and Dynamic Performance Views
  • Which view holds the names and short descriptions of all data dictionary views? Database Concepts, Data Dictionary and Dynamic Performance Views

Managing data in different time zones 3

  • CURRENT_TIMESTAMP gives back a value of type TIMESTAMP WITH TIME ZONE. What data type does LOCALTIMESTAMP return? SQL Language Reference, LOCALTIMESTAMP
  • What span of time does the literal INTERVAL '2-6' YEAR TO MONTH express? SQL Language Reference, Literals, Interval Literals
  • Which function gives the session time zone's current date as a value of data type DATE? SQL Language Reference, CURRENT_DATE

How this list was compiled: Own questions, written to the sixteen sections of the exam topics that Oracle University publishes for Exam 1Z0-071: Oracle Database SQL at https://mylearn.oracle.com/ou/exam/oracle-database-sql-1z0-071/105037/110647/170369 (read 2026-10-09; the page is linked from Oracle's certification page https://www.oracle.com/education/certification/). Facts were read on 2026-10-09 in Oracle's public documentation for Oracle Database 19c at docs.oracle.com: the SQL Language Reference, Database Concepts and the SQL*Plus User's Guide and Reference. Each question names the page and section it was written from and links to it. Licence: Own work. Every question, wrong choice, table name, statement and explanation was written for this site from the public documentation named in each question. Nothing is copied or reworded from an Oracle exam, a sample or released question, a course quiz or another practice product.

How it works

An unofficial Oracle 1Z0-071 practice test: 78 of our own questions, sorted by the sixteen topic sections Oracle publishes for the Oracle Database SQL exam. This site is not affiliated with Oracle, and nothing here comes from the real exam.

Read the full guide

What the 1Z0-071 exam covers

Oracle's exam page, read on October 9, 2026, lists sixteen topic sections: relational database concepts, the SELECT statement, restricting and sorting, single-row and conversion functions, group functions, joins, subqueries, set operators, DML, indexes, synonyms and sequences, DDL, views, user access, data dictionary views and time zones. It gives no weights, so each section's share here follows its count of listed topics.

Which release, and what is left out

Every answer rests on a page of Oracle's public documentation for Oracle Database 19c: the SQL Language Reference, Database Concepts or the SQL*Plus guide. The exam page names no release. Left out: statements of several lines, optimizer behavior and output that depends on a session setting the question does not state. The tables are our own.

  • A column alias can be used in ORDER BY and in no other clause of the same query.
  • NOT IN returns no rows at all when its list or subquery contains a null.
  • Every DDL statement commits first, so a ROLLBACK after CREATE TABLE cannot undo the inserts that came before it.

Questions and answers

How many questions are on the Oracle 1Z0-071 exam?

63 multiple choice questions in 120 minutes, by Oracle's exam page, which gives the delivery as proctored online. Exam mode here draws 63 of our 78 questions against a 120-minute clock.

What is the passing score for 1Z0-071?

Oracle's exam page gives 63%. Exam mode here marks a result against 63% as well. The questions are ours, so a pass here says nothing certain about the real exam.

Is this an Oracle SQL practice test?

Yes. Exam 1Z0-071 is named Oracle Database SQL, and Oracle's page lists Oracle Database SQL Certified Associate as the certification it earns. The questions cover SQL as Oracle Database runs it, down to SQL*Plus substitution variables.

Are these real 1Z0-071 exam questions?

No. Every question was written for this site from Oracle's public documentation and sorted by the published exam topics. Nothing is copied or reworded from the exam, a sample question or a course.

More tests

Show 7 more

Browse Safety and Trades