Subqueries — Concept Check
Questions on Subqueries. Scalar, row, and table subqueries appear in SELECT, FROM, and WHERE; correlated subqueries reference the outer query and execute per row — often replaceable with JOIN for better performance.
Questions in this quiz
8 questions on Subqueries — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A query is running slowly due to a subquery in the WHERE clause. What should you check first?
- ACheck if the subquery uses a derived table
- BCheck if the subquery is in the SELECT clause
- CCheck if the subquery can be rewritten as a JOIN
- DCheck if the subquery uses a scalar subquery
2.You have a subquery in the SELECT clause that returns multiple rows. What will happen?
- AThe outer query will ignore the subquery
- BAn error will occur because scalar subqueries must return a single value
- CThe subquery will automatically limit to one row
- DThe query will execute but return incorrect results
3.Which subquery type is most likely to cause performance issues on large datasets?
- ADerived table subqueries
- BScalar subqueries
- CIN subqueries
- DCorrelated subqueries
4.A subquery in your query's FROM clause is running slowly. What might improve performance?
- AMoving it to the SELECT clause
- BUsing a CTE instead of a derived table
- CUsing a correlated subquery
- DRewriting it as a scalar subquery
5.Why might EXISTS be preferred over IN for checking existence in large datasets?
- AEXISTS stops at the first match, improving performance
- BEXISTS uses more memory than IN
- CIN is always slower than EXISTS
- DIN cannot be used with correlated subqueries
6.You notice a query with 'Select tables optimized away' in its EXPLAIN plan. What does this indicate?
- AThe query optimizer has de-correlated a subquery
- BThe subquery is not executed at all
- CThe query will run slower due to optimization
- DThe query uses a derived table
7.A developer claims that using a correlated subquery is always more efficient than a JOIN. What's wrong with this belief?
- AJOINs are never slower than correlated subqueries
- BCorrelated subqueries can be inefficient on large datasets
- CJOINs are not suitable for complex queries
- DCorrelated subqueries don't support indexes
8.What is a potential downside of using a correlated subquery in a SELECT clause?
- AIt cannot be used with scalar subqueries
- BIt cannot return multiple columns
- CIt requires a derived table
- DIt may execute once per outer row, causing performance issues