UUS Practice Tests⌕ Search
IT Certifications · 1Z0-071

Oracle Database SQL Exam Practice Test

Oracle Database SQL Exam practice covers writing and interpreting SQL statements for relational data. The available questions touch on data type conversion, date handling, filtering and grouping, aggregate functions, subqueries, set operators, indexes, and table-clearing operations. These concepts help you read query behavior and choose statements that produce or modify the intended database results.

Exam overview

Exam providerOracle
Exam code1Z0-071
CategoryIT Certifications

About the Oracle Database SQL Exam

Work through the effects of clauses and expressions rather than memorizing isolated syntax. Distinguish row filtering with WHERE from group filtering with HAVING, and check how aggregate functions treat NULL values. For subqueries, reason about the number of rows returned and whether the comparison operator can accept that result. Compare set operators by their duplicate-handling behavior, and check that paired SELECT statements have compatible result columns. Date and character conversions depend on the source value and, when supplied, the format model. DDL choices also matter: creating an index changes access structures, while removing table rows does not necessarily remove the table definition. These skills support writing, troubleshooting, and reviewing Oracle SQL used to retrieve and manage relational data.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

Which expression converts the numeric value 42 to character data in Oracle SQL?

  1. ATO_NUMBER(42)
  2. BTO_CHAR(42)
  3. CCAST(42 AS DATE)
  4. DCHARACTER(42)
Show answerCorrect answer: B

TO_CHAR converts a number or date to character data, optionally using a format model. Here it converts the numeric value 42 to its character representation.

Question 2

In Oracle SQL, which predicate tests whether a value falls within an inclusive lower and upper bound?

  1. ABETWEEN
  2. BRANGE
  3. CINBOUND
  4. DWITHIN
Show answerCorrect answer: A

BETWEEN tests whether an expression is greater than or equal to the lower bound and less than or equal to the upper bound. For example, salary BETWEEN 3000 AND 5000 includes both 3000 and 5000.

Question 3

Which expression returns the number of non-NULL values in the COMMISSION column?

  1. ACOUNT(*)
  2. BCOUNT(commission)
  3. CSUM(commission)
  4. DCOUNT(DISTINCT *)
Show answerCorrect answer: B

COUNT(column) counts rows where that column is not NULL. COUNT(*) counts all rows, including rows with NULL commission values.

Question 4

Given a table T with a column CODE, which statement creates an index named T_CODE_IX on that column?

  1. ACREATE INDEX T_CODE_IX ON T (CODE);
  2. BALTER TABLE T ADD INDEX T_CODE_IX (CODE);
  3. CCREATE KEY T_CODE_IX FOR T (CODE);
  4. DBUILD INDEX T_CODE_IX ON T.CODE;
Show answerCorrect answer: A

Oracle creates a conventional index with CREATE INDEX, followed by the index name and the table and column list in parentheses.

Question 5

In Oracle SQL, which clause filters rows before they are grouped?

  1. AHAVING
  2. BWHERE
  3. CORDER BY
  4. DGROUP BY
Show answerCorrect answer: B

WHERE filters individual rows before GROUP BY forms groups. HAVING filters groups after aggregation.

Question 6

A query uses a scalar subquery that returns more than one row in a comparison such as salary = (subquery). What is the usual Oracle Database outcome?

  1. AOracle compares salary to the first row returned
  2. BOracle raises an error because a single-row comparison subquery returned multiple rows
  3. COracle treats the subquery result as NULL
  4. DOracle automatically changes = to IN
Show answerCorrect answer: B

The equals operator in this context expects the scalar subquery to return at most one row. If it returns multiple rows, Oracle raises an error; an operator such as IN can be appropriate when multiple values are intended.

Question 7

Which set operator combines results from two compatible SELECT statements and removes duplicate rows?

  1. AUNION ALL
  2. BUNION
  3. CINTERSECT ALL
  4. DJOIN
Show answerCorrect answer: B

UNION combines the result sets and removes duplicate rows. UNION ALL combines them without removing duplicates.

How to use this practice test

The free US Practice Tests practice bank contains 50 published questions. Start a session of up to 20 randomly selected questions, then use the displayed results to identify which SQL concepts need another pass. For missed items, write a small query or compare the relevant expressions and clauses to confirm why the result follows.

Preparation tips

  • Write paired examples using WHERE and HAVING, then note whether each condition applies to individual rows or grouped results.
  • Test COUNT(column) against COUNT(*) on data containing NULL values so you can see how each expression counts rows.
  • Practice explicit TO_DATE format models and TO_CHAR conversions, checking the input type and expected output type in each case.
  • Compare IN, BETWEEN, set operators, and scalar subqueries; predict behavior for boundary values, duplicates, and subqueries returning multiple rows.
  • Distinguish DELETE, TRUNCATE, and DROP by whether rows, table structure, or both are removed; separately practice the syntax for creating a column index.
Official sourceOfficial exam page ↗