SQL Writing Subqueries 3

Question 1

What does NOT EXISTS return when its subquery yields no rows?

Accepted Answer

TRUE

Answer

NOT EXISTS is TRUE precisely when the subquery returns no rows.

Question 2

Which comparison returns rows greater than EVERY value the subquery returns?

Accepted Answer

> ALL

Answer

> ALL is true only when the value exceeds every value returned by the subquery.

Question 3

A NULL in a NOT IN subquery list can cause what problem?

Accepted Answer

The whole predicate may return no rows unexpectedly

Answer

NOT IN with a NULL in the list can yield UNKNOWN, filtering out rows you expected to keep.

Question 4

Which is generally preferred for performance when checking existence of related rows?

Accepted Answer

EXISTS

Answer

EXISTS can stop at the first matching row, often outperforming counting approaches.

Question 5

In SELECT salary, (SELECT AVG(salary) FROM emp) AS avg_sal, the inner query is a what?

Accepted Answer

Scalar subquery in the SELECT list

Answer

It returns one value placed in the SELECT list, making it a scalar subquery.

SQL Practice Test