Which Statements Are True About Single-Row Subqueries in Oracle?

1Z0-071 By islever Editorial Last updated

Single-row subqueries Restricting group results

Short answer

BDE Single-row subqueries may appear only to the right of the comparison operator, can be repeated as multiple blocks, and are valid in HAVING.

Question

Which three statements are true regarding single row subqueries? (Choose three.)
  1. They must be placed on the left side of the comparison operator or condition.
  2. They must be placed on the right side of the comparison operator or condition. Correct Answer
  3. They must return a row to prevent errors in the SQL statement.
  4. A SQL statement may have multiple single row subquery blocks. Correct Answer
  5. They can be used in the HAVING clause. Correct Answer

Key Takeaways

The question tests where a single-row subquery may be positioned and which clauses accept it, and the trap is option C — the belief that a subquery returning zero rows raises an error.

Single-row subqueries in Oracle SQL follow fixed placement and clause rules: they sit to the right of the comparison operator, a statement may contain several single-row subquery blocks, and they are valid inside the HAVING clause. This page establishes that B, D and E are the three true statements and explains why the 'must return a row' claim is wrong.

Common mistake: Most candidates choose C, assuming that an empty single-row subquery aborts the SQL statement; in reality zero rows simply yield NULL and no error — only a result of more than one row triggers ORA-01427.

Expert Analysis

Why the Answer Is Correct

Single-row subqueries are compared against an outer column or expression, and Oracle's documented pattern places the subquery on the right of the comparison operator, for example WHERE salary > (SELECT AVG(salary) FROM employees), so option B states the exam's rule correctly. Option D is true because a single SQL statement can nest several independent single-row subquery blocks — one in the WHERE clause and another in the SELECT list or HAVING clause of the same query. Option E is true because aggregate results can be filtered against a single-row subquery, as in HAVING AVG(salary) > (SELECT AVG(salary) FROM employees WHERE department_id = 50). Together, B, D and E describe the placement rule, the repeatability of the construct, and an accepted clause location, which is exactly what the three-answer question asks for.

Why the Other Options Are Wrong

Option A is the mirror-image distractor: nothing in Oracle requires a single-row subquery to be placed on the left of the comparison operator, and the exam doctrine always shows the outer column on the left and the subquery on the right. Option C is factually wrong — a single-row subquery that returns no rows does not raise an exception; it produces a NULL comparison so the outer query simply returns no matching rows. The error most often confused with this is ORA-01427 ("single-row subquery returns more than one row"), which belongs to the multi-row case, not the empty case. That distinction is why C reads plausibly but fails the test of actual server behaviour.

Community Comment Notes

ArslanAltaf rejects C directly, noting that "if no row is returned, it will not throw an error", which matches the server behaviour described above and supports the D/E half of the verdict. kay000001 lists B among the correct answers, consistent with the right-side placement rule chosen here. salander71 writes "EF are correct. I tried", but the letter F does not exist among this question's five options, so that vote came from a different option set and cannot overturn the B/D/E conclusion for this version. Another learner's "CDF is correct" claim conflicts with the ORA-01427-only error rule and should be treated as a misreading rather than evidence.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When a question says "Choose three", count your selections before submitting — the source key here listed only two letters, which is an immediate red flag. Eliminate options containing absolute wording such as "must be placed on the left" or "must return a row" by testing them against real Oracle behaviour before you spend time on the remaining candidates.

Frequently Asked Questions

Does a single-row subquery that returns no rows cause an error?

No. Zero rows produce a NULL comparison and no exception; only a result of more than one row raises ORA-01427, which is why option C is false.

Why is option A (subquery on the left of the operator) considered wrong?

Oracle's documented examples and exam doctrine place the subquery on the right of the comparison operator, with the outer column or expression on the left.

More 1Z0-071 FAQ →

Discussion 4 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 4

  1. ArslanAltaf 👍 2
    C is not correct. if no row is returned, it will not throw an error. DEF is ok
  2. salander71 👍 1
    EF are correct. I tried
  3. kay000001 👍 1
    Answers are B, E, F.
  4. ArslanAltaf 👍 1
    CDF is correct.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

System and Object Privileges in Oracle Database

S-grade

Which Three Actions Can Be Performed Only With System Privileges?

S-grade

How Do You Calculate Days from 1 Jan 2019 to SYSDATE in Oracle?

S-grade

Which Two SQL Statements Execute on TRANSACTIONS Table?

S-grade

← Back to 1Z0-071 Study Guide