O R A C L E   C E R T I F I C A T I O N

1Z0-071 Oracle Database SQL Practice Exam

Exam Number: 4801 | Last updated April 19, 2026 | 700+ questions across 4 vendor-aligned objectives

The 1Z0-071 Oracle Database SQL certification targets developers, database administrators, data analysts, and BI professionals who write, tune, and maintain SQL against Oracle Database. Candidates confirm practical command of DDL, DML, query composition, built-in functions, joins, subqueries, set operators, and the relational concepts that underpin Oracle’s engine. Passing earns the Oracle Database SQL Certified Associate credential, a durable foundation for every downstream Oracle Database certification.

The heaviest content area is Retrieving and Manipulating Data (roughly 40%), which covers SELECT fundamentals, DML operations, transactions, substitution variables, and controlling output with ORDER BY, DISTINCT, and row-limiting clauses. Working with Joins, Subqueries, and Set Operators accounts for around 25% and includes inner and outer joins, self-joins, correlated and scalar subqueries, and UNION, INTERSECT, and MINUS behavior against NULL data.

Using Single-Row and Group Functions sits near 20% and drills into character, numeric, date, and conversion functions along with GROUP BY, HAVING, and ROLLUP. Managing Objects and Schemas rounds out the remaining weight with CREATE, ALTER, and DROP for tables, views, sequences, and indexes, plus constraint enforcement, privileges, and data dictionary queries.

♥  Memorize how NULL propagates through comparisons, arithmetic, and group functions — the exam repeatedly tests NVL, NVL2, COALESCE, and the difference between WHERE col IS NULL and col = NULL. Be fluent reading an execution flow that mixes inline views, scalar subqueries, and analytic functions, and know which clauses accept correlated references. Also expect questions where the correct answer hinges on implicit data-type conversion and on the default date format mask.

Every answer links to the source. Each explanation below includes a hyperlink to the exact Oracle documentation page the question was derived from. PowerKram is the only practice platform with source-verified explanations. Learn about our methodology →

852

practice exam users

98.0%

satisfied users

96.9%

passed the exam

4.1/5

quality rating

Test your 1Z0 071 Database SQL knowledge

10 of 700+ questions

Question #1 - Retrieving and Manipulating Data

A retail analytics team at a regional grocery chain is pulling a list of active loyalty members for a weekend promotion. The CUSTOMERS table has 4.2 million rows, and the analyst needs unique city values for members whose signup date is in the last 90 days, sorted alphabetically.

Which single SELECT statement returns the correct result set?

A) SELECT DISTINCT city FROM customers WHERE signup_date >= SYSDATE – 90 ORDER BY city;
B) SELECT UNIQUE city FROM customers WHERE signup_date BETWEEN SYSDATE AND SYSDATE – 90;
C) SELECT city FROM customers WHERE signup_date > SYSDATE – 90 ORDER BY city DISTINCT;
D) SELECT city FROM customers WHERE signup_date >= SYSDATE – 90 GROUP BY city ORDER BY 1 DESC;

 

Correct answers: A – Explanation:
Option A combines DISTINCT to remove duplicate cities, a correct WHERE predicate using SYSDATE – 90 for the last 90 days, and ORDER BY city for alphabetical order. Option D sorts descending instead of alphabetically and misuses GROUP BY. Option B reverses the BETWEEN bounds so no rows match, and UNIQUE is a deprecated synonym better avoided. Option C places DISTINCT in the ORDER BY clause, which is invalid syntax. Source: Check Source

At a logistics SaaS startup, an engineer is running an UPDATE to raise freight rates by 3% for a specific customer tier. After executing the UPDATE in SQL Developer, the engineer closes the session without running COMMIT and another session immediately queries the same rows.

What does the second session see, and what happens to the uncommitted changes?

A) The second session sees the new rates immediately because Oracle uses dirty reads by default.
B) The second session sees the old rates, and the uncommitted changes are rolled back when the first session ends.
C) The second session sees the new rates only if it sets ISOLATION LEVEL READ UNCOMMITTED.
D) The second session is blocked until the first session commits or rolls back.

 

Correct answers: B – Explanation:
Oracle enforces read consistency: readers never block writers and never see uncommitted data. When the first session closes without committing, Oracle rolls back its changes automatically, so the second session sees the old rates throughout. Option A is wrong because Oracle does not permit dirty reads. Option D is wrong because SELECT does not wait on row-level locks held by an UPDATE. Option C is wrong because READ UNCOMMITTED is not a supported isolation level in Oracle. Source: Check Source

A media streaming company’s data engineer needs a report of every subscriber together with their most recent payment, including subscribers who have never paid (for example, free-trial users). Two tables are involved: SUBSCRIBERS and PAYMENTS, joined on subscriber_id.

Which join produces one row per subscriber, even for those with no matching payment?

A) SELECT s.name, p.amount FROM subscribers s RIGHT OUTER JOIN payments p ON s.subscriber_id = p.subscriber_id;
B) SELECT s.name, p.amount FROM subscribers s INNER JOIN payments p ON s.subscriber_id = p.subscriber_id;
C) SELECT s.name, p.amount FROM subscribers s LEFT OUTER JOIN payments p ON s.subscriber_id = p.subscriber_id;
D) SELECT s.name, p.amount FROM subscribers s CROSS JOIN payments p WHERE s.subscriber_id = p.subscriber_id;

 

Correct answers: C – Explanation:
A LEFT OUTER JOIN preserves every row from the left table (subscribers) and returns NULL for columns in the right table (payments) when there is no match, which is exactly what is needed for subscribers with no payments. Option B drops subscribers with no payments. Option A preserves payments but could drop subscribers. Option D is a Cartesian product filtered back down, which also drops subscribers with no match. Source: Check Source

A healthcare reporting analyst needs the number of distinct provider specialties per hospital, excluding any providers whose specialty is NULL. The PROVIDERS table has columns hospital_id and specialty, and she is writing a GROUP BY query.

Which expression correctly returns the distinct, non-NULL specialty count per hospital?

A) SELECT hospital_id, COUNT(*) FROM providers WHERE specialty IS NULL GROUP BY hospital_id;
B) SELECT hospital_id, COUNT(specialty) FROM providers GROUP BY hospital_id;
C) SELECT hospital_id, DISTINCT COUNT(specialty) FROM providers GROUP BY hospital_id;
D) SELECT hospital_id, COUNT(DISTINCT specialty) FROM providers GROUP BY hospital_id;

 

Correct answers: D – Explanation:
COUNT(DISTINCT specialty) counts only non-NULL values and removes duplicates, matching the requirement. Option B counts non-NULL specialties but includes duplicates. Option A inverts the filter and returns only rows where specialty IS NULL. Option C is invalid syntax because DISTINCT cannot be placed before an aggregate function like that. Source: Check Source

A fintech developer is inserting trade records into an AUDIT_LOG table. The application reads three rows, processes them, then attempts to insert a composite row summarizing the batch. Mid-transaction, a constraint violation raises ORA-02290 on the third INSERT, and the developer wants to revert only that failed statement without losing the first two.

Which approach best achieves statement-level rollback without discarding prior inserts in the transaction?

A) Use a SAVEPOINT before each INSERT and ROLLBACK TO SAVEPOINT on failure.
B) Issue ROLLBACK immediately after the exception.
C) Run COMMIT after each INSERT so failures cannot roll back earlier rows.
D) Wrap the INSERT in SET TRANSACTION READ ONLY before executing.

 

Correct answers: A – Explanation:
SAVEPOINT lets you mark a point in the transaction, and ROLLBACK TO SAVEPOINT unwinds only changes made after that marker while keeping earlier inserts in the same transaction. Option B rolls back the entire transaction, losing the first two inserts. Option D makes the transaction read-only and would block the INSERTs outright. Option C commits each row individually, which destroys transactional atomicity and is usually the wrong pattern for an audit batch. Source: Check Source

An HR analyst at a manufacturing firm needs a list of employees who earn more than the average salary within their own department. The EMPLOYEES table has columns employee_id, department_id, and salary.

Which query returns the correct set?

A) SELECT employee_id FROM employees GROUP BY department_id HAVING salary > AVG(salary);
B) SELECT employee_id FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
C) SELECT employee_id FROM employees WHERE salary > AVG(salary);
D) SELECT employee_id FROM employees e1 JOIN employees e2 ON e1.salary > AVG(e2.salary);

 

Correct answers: B – Explanation:
The correct answer uses a correlated subquery: for each row in the outer query, the inner subquery computes the average salary for that employee’s department and compares. Option C is invalid because you cannot use an aggregate directly in WHERE without a subquery. Option A fails because GROUP BY aggregates rows and salary is not a grouping column. Option D cannot use AVG in a join predicate that way. Source: Check Source

A supply chain analyst exports shipment dates to a CSV for an external partner. The source column SHIP_DATE is of type DATE, and the partner’s system expects the text format ‘YYYY-MM-DD’. The analyst writes a SELECT that must produce that exact string.

Which expression produces the required formatted string?

A) FORMAT(ship_date, ‘YYYY-MM-DD’)
B) CAST(ship_date AS VARCHAR2(10))
C) TO_CHAR(ship_date, ‘YYYY-MM-DD’)
D) TO_DATE(ship_date, ‘YYYY-MM-DD’)

 

Correct answers: C – Explanation:
TO_CHAR converts a DATE into a formatted character string using the supplied format mask. Option D is the inverse direction: TO_DATE parses a string into a date. Option B relies on an implicit conversion that uses the session NLS_DATE_FORMAT, which is not guaranteed to be ‘YYYY-MM-DD’. Option A is not a valid Oracle function. Source: Check Source

A DBA at an insurance company is creating a new ORDERS table. The business rule says the ORDER_STATUS column must always contain one of a fixed list of values: ‘PENDING’, ‘SHIPPED’, ‘DELIVERED’, or ‘CANCELLED’. The DBA wants this enforced at the table level.

Which constraint type best enforces the rule?

A) A PRIMARY KEY constraint on ORDER_STATUS.
B) A UNIQUE constraint on ORDER_STATUS.
C) A FOREIGN KEY constraint referencing ORDER_STATUS in another table.
D) A CHECK constraint on ORDER_STATUS listing the four values.

 

Correct answers: D – Explanation:
A CHECK constraint lets you enforce a boolean condition on a column, such as ORDER_STATUS IN (‘PENDING’,’SHIPPED’,’DELIVERED’,’CANCELLED’). Option A would force each status value to appear only once in the entire table, which is not the requirement. Option B has the same problem — it forbids duplicates. Option C works only if there is a master table to reference; a CHECK constraint is simpler and sufficient for a small fixed list. Source: Check Source

A data engineer at an online education platform needs to find course IDs that exist in the LEGACY_CATALOG table but not in the current CATALOG table. Both tables have the same course_id column type.

Which set operator returns the difference efficiently?

A) SELECT course_id FROM legacy_catalog MINUS SELECT course_id FROM catalog;
B) SELECT course_id FROM catalog MINUS SELECT course_id FROM legacy_catalog;
C) SELECT course_id FROM legacy_catalog UNION SELECT course_id FROM catalog;
D) SELECT course_id FROM legacy_catalog INTERSECT SELECT course_id FROM catalog;

 

Correct answers: A – Explanation:
MINUS returns rows from the first query that do not appear in the second, which matches the requirement (legacy rows not in current catalog). Option C returns all distinct course IDs across both tables. Option D returns only those that appear in both. Option B is the opposite direction — course IDs in the new catalog missing from the legacy catalog. Source: Check Source

A gaming company’s analyst is writing a query that lets the user pass in a country code at runtime from SQL*Plus. She wants the query to prompt the user for a value and substitute it into the WHERE clause each time.

Which construct prompts the user at runtime and substitutes the value into the SQL?

A) Use a sequence: WHERE country_code = country_seq.NEXTVAL
B) Use DBMS_OUTPUT.PUT_LINE to request input.
C) Use a substitution variable: WHERE country_code = ‘&country_code’
D) Use a bind variable: WHERE country_code = :country_code

 

Correct answers: C – Explanation:
Substitution variables with the & prefix cause SQL*Plus to prompt the user for a value and textually substitute it into the statement before execution. Option D — bind variables — do not prompt; values are supplied programmatically. Option A calls a sequence, which generates numbers, not user input. Option B writes to server output and cannot read input from the user. Source: Check Source

Get 700+ more questions with source-linked explanations

Every answer traces to the exact Oracle documentation page — so you learn from the source, not just memorize answers.

Start free 24-hour trial — no credit card

Exam mode & learn mode · Score by objective · Updated April 19, 2026

Learn more...

What the 1Z0 071 Database SQL exam measures

  • Retrieving and manipulating data (40%) — write SELECT, INSERT, UPDATE, DELETE, and MERGE statements that pass production-grade data-quality checks, control output order, and commit or rollback transactions with intent.
  • Working with joins, subqueries, and set operators (25%) — combine data from multiple tables using inner, outer, and self-joins, nest correlated and scalar subqueries, and blend results with UNION, INTERSECT, and MINUS while handling NULLs cleanly.
  • Using single-row and group functions (20%) — apply character, numeric, date, and conversion functions, aggregate with GROUP BY and HAVING, and extend grouping with ROLLUP and CUBE for executive-ready reports.
  • Managing objects and schemas (15%) — create and alter tables, views, sequences, and indexes, enforce integrity with primary, foreign, unique, and check constraints, and grant or revoke object privileges per least-privilege principles.

  • Review the official 1Z0-071 exam page to lock in the current objectives, topic weights, and passing score before you study.
  • Complete the Oracle University Oracle Database: SQL Workshop learning path on MyLearn, which walks every objective with guided labs.
  • Practice hands-on in an OCI always-free Autonomous Database instance — spin up SQL Developer Web, load the HR sample schema, and rewrite every example from scratch.
  • Apply the skills to real work: rebuild a reporting query at your day job, volunteer for an open-source data project, or port a legacy report to set-based SQL.
  • Master one objective at a time, beginning with Retrieving and Manipulating Data since it carries the most weight.
  • Run PowerKram learn mode to see feedback after every question with sourced links back to Oracle documentation.
  • Finish with PowerKram exam mode across all four objectives until you clear the threshold on three back-to-back full-length attempts.

Oracle SQL fluency is a prerequisite for most database, analytics, and application-development roles:

Start with the Oracle Database SQL Learning Path on Oracle MyLearn, which packages recorded lectures and labs mapped directly to each objective. Reinforce with the Oracle Database SQL Language Reference and the Oracle Live SQL sandbox for browser-based practice against a real instance.

Related certifications to explore

1Z0-082 Oracle Database Administration I
1Z0-149 Oracle Database Program with PL/SQL
1Z0-116 Oracle Database Security Administration
Microsoft DP-300 Azure Database Administrator
AWS Database Specialty
Google Cloud Professional Database Engineer

Related reading from our Learning Hub

Data Preparation and Feature Engineering
PowerKram vs IT Exams — competitive vendor exam coverage
Platform Administrators Certification Guide
PowerKram vs Exam Topics — most competitive pricing