QuizzesScalar Subquery — Concept Check
intermediate
sql

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.

8 minutes 8 questions Pass at 70%
Time Limit
8 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

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. 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. 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. 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. 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. 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. 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. 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. 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.