What does the IN operator do when used with a subquery?

Checks if a value matches any value in the subquery result set

Checks if a value is greater than all values in the subquery result set

Returns the first matching row from the subquery

Joins the outer table to the subquery result

medium #subqueries #sql #filtering #data-analysis #where-clause #sql-clauses

QUESTION 01 OF 10

SQL Subqueries: Queries Within Queries

Practice writing and interpreting SQL subqueries, including scalar, correlated, and derived table subqueries. This quiz tests your ability to nest queries effectively to solve real-world data problems.

Taken by 11 developers

Answered 0/10

Pass 70%

~8 min

00:00

Note: Sign in to save your quiz progress and results.

A subquery in the WHERE clause that returns a single value is called a:

About this Quiz

This interactive assessment is designed to test your understanding of SQL Subqueries: Queries Within Queries. Whether you are a beginner looking to grasp the basics or an experienced professional aiming to refine your skills, quizzes are an excellent way to validate your knowledge and identify areas for improvement.

Instructions

Read each question carefully before answering.
Some questions may have multiple correct answers.
There is no time limit, so take your time to think.
You will receive immediate feedback after submission.

Learning Outcomes

Self-assess your current skill level.
Reinforce key concepts through active recall.
Identify knowledge gaps to focus your study.
Gain confidence in your technical expertise.
Reinforce memory and recall.

Topic Overview

Questions in this quiz:

1. A subquery in the WHERE clause that returns a single value is called a:

Correlated subquery
Scalar subquery
Derived table
Set subquery

Hint: A scalar subquery returns exactly one row and one column — a single value. It can be used anywhere a single value is expected, such as in a <code>WHERE</code> clause comparison or even a <code>SELECT</code> list.

2. What does the following query return?

All employees with their average salary
Employees whose salary is above the company average
The single employee with the highest salary
An error — subqueries cannot be used with aggregate functions

Hint: The inner query computes the average salary across all employees. The outer query then filters and returns only those employees whose individual salary exceeds that average — a classic use of a scalar subquery.

3. A subquery used in the FROM clause is commonly referred to as a:

Nested query
Correlated subquery
Derived table
Exists subquery

Hint: When a subquery appears in the <code>FROM</code> clause, it acts as a temporary table for the outer query to select from. This is called a derived table (or inline view) and must always be given an alias.

4. Which keyword should you use when you only need to check whether a subquery returns any rows at all, without caring about the actual values?

IN
ANY
EXISTS
SOME

Hint: EXISTS returns <code>TRUE</code> if the subquery produces at least one row and stops scanning as soon as it finds a match — making it very efficient. It doesn't care about the actual column values, so <code>SELECT 1</code> or <code>SELECT *</code> are both commonly used inside it.

5. A correlated subquery references columns from the outer query and is executed once per row of the outer query.

True
False

Hint: True. Unlike a regular subquery that runs once, a correlated subquery depends on values from the outer query's current row and is re-evaluated for each row. This makes them powerful but potentially slower on large datasets.

6. What is the issue with the following subquery?

Subqueries cannot be used with the = operator
If the subquery returns more than one row, the query will error
The subquery must be placed in the FROM clause instead
There is no issue — this is valid SQL in all cases

Hint: The <code>=</code> operator expects a single value. If the subquery returns more than one row (multiple departments in NYC), the database will throw an error. Use IN instead of <code>=</code> when the subquery may return multiple rows.

7. Which query correctly uses <code>NOT EXISTS</code> to find departments that have no employees?

Hint: Option A is the correct correlated NOT EXISTS pattern. For each department, it checks whether any employee row references it — if none exist, the department is included. Option B has a <code>NULL</code> pitfall with <code>NOT IN</code>. Option C uses INNER JOIN incorrectly. Option D's logic is inverted.

8. What does the <code>IN</code> operator do when used with a subquery?

Checks if a value matches any value in the subquery result set
Checks if a value is greater than all values in the subquery result set
Returns the first matching row from the subquery
Joins the outer table to the subquery result

Hint: <code>IN (subquery)</code> checks whether the outer value matches any value in the list returned by the subquery. It is equivalent to a series of <code>OR</code> conditions and is most useful when the subquery returns multiple rows.

9. A subquery in the <code>SELECT</code> clause must return more than one row to be valid.

True
False

Hint: False. A subquery in the <code>SELECT</code> clause must return exactly one row and one column (a scalar value) — otherwise the query will error. It is evaluated once per row of the outer query.

10. What will the following query return?

Products that are cheaper than all books
Products whose price is higher than the most expensive book
Products whose price matches any book price
All products except those in the Books category

Hint: <code>ALL</code> requires the condition to hold true against every value in the subquery result. <code>price > ALL(...)</code> means the product's price must be greater than the highest price among all books — effectively filtering for products more expensive than the priciest book.