1Z0-071 Using Subqueries 1 — Questions and Answers
Question 1: What is a subquery in Oracle SQL?
- A stored procedure that returns a table
- A SELECT statement nested inside another SQL statement (Correct answer)
- A synonym for a VIEW
- A query that uses GROUP BY
Correct answer: A SELECT statement nested inside another SQL statement
A subquery is a SELECT statement embedded within another SQL statement such as a SELECT, INSERT, UPDATE, or DELETE.
Question 2: Where can a single-row subquery be used in a SELECT statement?
- Only in the WHERE clause
- In the WHERE, HAVING, and SELECT clauses (Correct answer)
- Only in the FROM clause
- Only in the ORDER BY clause
Correct answer: In the WHERE, HAVING, and SELECT clauses
Single-row subqueries can appear in the WHERE, HAVING, and SELECT clauses of the outer query.
Question 3: Which comparison operator is INVALID when used with a multi-row subquery?
- IN
- ANY
- ALL
- = (Correct answer)
Correct answer: =
The = operator expects a single value; using it with a multi-row subquery causes an ORA-01427 error.
Question 4: What does the EXISTS operator check in a subquery?
- Whether the subquery returns at least one row (Correct answer)
- Whether all rows in the subquery are non-NULL
- Whether the subquery returns exactly one row
- Whether the subquery has no GROUP BY clause
Correct answer: Whether the subquery returns at least one row
EXISTS returns TRUE if the subquery produces at least one row, regardless of column values.
Question 5: A subquery that returns more than one column is called a:
- Multi-row subquery
- Scalar subquery
- Multiple-column subquery (Correct answer)
- Correlated subquery
Correct answer: Multiple-column subquery
A multiple-column subquery returns more than one column and is typically used in pairwise comparisons.
Question 6: Which keyword pair allows a multi-row subquery to check if a value matches any value in the returned list?
- = ALL
- = ANY
- IN
- Both = ANY and IN (Correct answer)
Correct answer: Both = ANY and IN
Both IN and = ANY check if a value matches at least one value in the subquery's result set and are functionally equivalent.
1Z0-071 Using Subqueries 1
What is a subquery in Oracle SQL?