Scalar Subquery — Concept Check
Questions on Scalar Subquery. A scalar subquery returns exactly one row and one column, and can appear anywhere a single value is expected — SELECT list, WHERE, HAVING, or ORDER BY.
Questions in this quiz
8 questions on Scalar Subquery — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.You have a query with a scalar subquery in the SELECT list that references columns from the outer query. What potential issue might arise?
- AThe query will fail if the subquery returns NULL.
- BThe query will execute faster due to caching of subquery results.
- CThe query may run slowly due to repeated execution of the subquery.
- DThe query will not execute if the subquery returns more than one row.
2.A developer uses a scalar subquery in the WHERE clause which returns no rows. What will be the result of the comparison?
- AThe comparison will evaluate to FALSE, filtering out all rows.
- BThe comparison will throw an error due to no rows being returned.
- CThe comparison will evaluate to UNKNOWN, potentially filtering out rows.
- DThe comparison will evaluate to TRUE, allowing all rows.
3.A query uses a scalar subquery in the HAVING clause to compare department salaries to the company average. What behavior can you expect if the subquery returns NULL?
- AThe HAVING clause will evaluate to UNKNOWN, possibly excluding groups.
- BThe query will throw an error due to NULL comparison.
- CThe query will ignore the NULL and proceed with other conditions.
- DThe HAVING clause will evaluate to TRUE, including all groups.
4.What is a potential performance issue with using a correlated scalar subquery in a SELECT statement?
- AIt caches results, causing outdated data to be used.
- BIt causes the query to fail if the subquery returns more than one row.
- CIt executes only once for the entire query, leading to incorrect results.
- DIt executes once per outer row, leading to high execution time on large tables.
5.A query with a scalar subquery in the SELECT list runs slowly on a table with 10,000 rows. What is a likely reason?
- AThe scalar subquery returns multiple rows, causing execution delays.
- BThe scalar subquery is non-correlated and caches the result, slowing down execution.
- CThe scalar subquery is not using an index, leading to slow execution.
- DThe scalar subquery is correlated and runs for each row, causing N+1 query problem.
6.Which approach can improve performance when using a correlated scalar subquery?
- AUse a non-correlated scalar subquery instead.
- BAdd an index on the subquery columns.
- CIncrease the database cache size.
- DRewrite the query using a JOIN to pre-aggregated results.
7.When a scalar subquery in the WHERE clause returns no rows, what default value can you use to ensure the query behaves as expected?
- AUse ISNULL to provide a default value, such as 1.
- BUse NULLIF to provide a default value, such as 0.
- CUse NVL to provide a default value, such as -1.
- DUse COALESCE to provide a default value, such as 0.
8.What happens when a scalar subquery in a SELECT list returns more than one row?
- AThe query returns the first row and ignores the rest.
- BThe query throws an error due to multiple rows returned.
- CThe query returns NULL, treating it as no rows returned.
- DThe query executes successfully, using the first column of each row.