1Z0-071 Using Subqueries 2 — Questions and Answers
Question 1: What is a correlated subquery?
- A subquery that references a column from the outer query (Correct answer)
- A subquery with a GROUP BY clause
- A subquery used only in the FROM clause
- A subquery that returns a single value
Correct answer: A subquery that references a column from the outer query
A correlated subquery references a column from the outer query and is re-executed for each row of the outer query.
Question 2: Which error occurs when a single-row operator is used with a multi-row subquery result?
- ORA-00904
- ORA-01427 (Correct answer)
- ORA-00936
- ORA-01403
Correct answer: ORA-01427
ORA-01427 'single-row subquery returns more than one row' is raised when = or < is used against a multi-row subquery.
Question 3: A subquery in the FROM clause is called a:
- Nested subquery
- Inline view (Correct answer)
- Scalar subquery
- Correlated subquery
Correct answer: Inline view
A subquery placed in the FROM clause is called an inline view because it acts like a temporary named table.
Question 4: What does the ALL operator do when used with a subquery?
- Returns TRUE if the condition is met for at least one row
- Returns TRUE only if the condition is met for every row returned by the subquery (Correct answer)
- Returns all rows regardless of the condition
- Acts identically to IN
Correct answer: Returns TRUE only if the condition is met for every row returned by the subquery
ALL requires the comparison to be true for every value returned by the subquery.
Question 5: Which subquery type returns exactly one row and one column?
- Multi-row subquery
- Multiple-column subquery
- Scalar subquery (Correct answer)
- Inline view
Correct answer: Scalar subquery
A scalar subquery returns a single row and a single column and can be used wherever a single value is expected.
Question 6: What is the correct way to find employees who earn more than the average salary?
- SELECT * FROM employees WHERE salary > AVG(salary)
- SELECT * FROM employees HAVING salary > AVG(salary)
- SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) (Correct answer)
- SELECT * FROM employees WHERE salary > ALL(SELECT salary FROM employees)
Correct answer: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
A single-row subquery that returns AVG(salary) is placed in the WHERE clause to compare each employee's salary.
1Z0-071 Using Subqueries 2
What is a correlated subquery?