1Z0-071 practice question 34 of 35

Which three are true about scalar subquery expressions?

Full set: 326 questions
Answer options
A: A scalar subquery expression that returns zero rows evaluates to zoro
B: They cannot be used in the values clause of an insert statement*
C: They can be nested. Correct answer
D: A scalar subquery expression that returns zero rows evaluates to null. Correct answer
E: They cannot be used in group by clauses.
F: They can be used as default values for columns in a create table statement. Correct answer
Correct Answer:
C. They can be nested.; D. A scalar subquery expression that returns zero rows evaluates to null.; F. They can be used as default values for columns in a create table statement.
Explanation

Scalar subqueries are SELECT statements that return exactly one column and at most one row. When a scalar subquery returns no rows, it evaluates to NULL, not zero. They can be nested within other subqueries or expressions. Oracle supports scalar subqueries in DEFAULT clauses of CREATE TABLE statements. They can also be used in VALUES clauses of INSERT statements and GROUP BY clauses, contrary to options B and E.

Why Incorrect

A. Incorrect - Scalar subqueries returning zero rows evaluate to NULL, not zero.
B. Incorrect - Scalar subqueries CAN be used in VALUES clauses of INSERT statements.
E. Incorrect - Scalar subqueries CAN be used in GROUP BY clauses since Oracle 12c.

References

1. Oracle Database SQL Language Reference 19c, Part Number E96310-02, Chapter 9 "SQL Queries and Subqueries", Section "Scalar Subqueries", pages 9-23 to 9-25

2. Oracle Database SQL Language Reference 19c, Part Number E96310-02, Chapter 7 "SQL Statements: CREATE SYNONYM to CREATE TRIGGER", CREATE TABLE section, page 7-42 (DEFAULT clause)

3. Oracle Database New Features Guide 12c Release 1, Part Number E17906-16, Section "Enhanced Subquery Processing", page 1-15

4. Oracle Database SQL Language Reference 19c, Part Number E96310-02, Chapter 8 "SQL Statements: ALTER SYNONYM to COMMENT", INSERT statement section, page 8-67

5. Oracle Database SQL Language Reference 19c, Part Number E96310-02, Chapter 9 "SQL Queries and Subqueries", Section "GROUP BY Clause", page 9-14

Scroll to Top