Using Subqueries Flashcards
6 cards from real 1Z0-071 practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Using Subqueries flashcards as text
When a NULL is returned by a subquery used with NOT IN, what is the result?
Answer: No rows are returned
If a subquery used with NOT IN returns any NULL, the entire NOT IN condition evaluates to UNKNOWN and no rows are returned.
Which of the following subqueries is a valid scalar subquery in a SELECT clause?
Answer: SELECT e.name, (SELECT dept_name FROM departments WHERE dept_id = e.dept_id) FROM employees e
A scalar subquery in the SELECT list must return exactly one column and one row per outer row — correlating on dept_id achieves this.
What is the purpose of using NOT EXISTS instead of NOT IN in a subquery?
Answer: NOT EXISTS correctly handles NULLs unlike NOT IN
NOT EXISTS avoids the NULL-related pitfall of NOT IN because EXISTS only checks row existence, not column values.
Which of the following is an example of a pairwise multiple-column subquery?
Answer: WHERE (dept_id, job_id) IN (SELECT dept_id, job_id FROM employees WHERE salary > 5000)
A pairwise multiple-column subquery compares a tuple of columns as a unit against tuples returned by the subquery.
In which clause is it MOST common to write a correlated subquery?
Answer: WHERE
Correlated subqueries most commonly appear in the WHERE clause to filter outer query rows based on related inner query data.
A subquery that contains another subquery inside it is called a:
Answer: Nested subquery
A nested subquery is a subquery placed inside another subquery, creating multiple layers of query nesting.