Subquery in WHERE clause in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees with salary above average

Given a table Employees with columns id, name, and salary, what is the output of this query?

SELECT name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

SQL

SELECT name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

A All employee names regardless of salary

B List of employee names whose salary is less than the average salary

C List of employee names whose salary is greater than the average salary

D Syntax error due to subquery in WHERE clause

💡 Hint

Think about what the subquery returns and how it is used in the WHERE clause.

The subquery calculates the average salary. The main query selects employees with salary greater than that average.

📝 Syntax

intermediate

Identify the syntax error in subquery usage

Which option contains a syntax error when using a subquery in the WHERE clause to find products priced higher than the average price?

SELECT product_name FROM Products WHERE price > (SELECT AVG(price) FROM Products);

SQL

SELECT product_name FROM Products WHERE price > (SELECT AVG(price) FROM Products);

A SELECT product_name FROM Products WHERE price > (SELECT AVG(price) FROM Products);

B SELECT product_name FROM Products WHERE price > SELECT AVG(price) FROM Products;

C ;)stcudorP MORF )ecirp(GVA TCELES( > ecirp EREHW stcudorP MORF eman_tcudorp TCELES

D ELECT product_name FROM Products WHERE price > (SELECT AVG(price) FROM Products);

💡 Hint

Check if the subquery is properly enclosed in parentheses.

Option B misses parentheses around the subquery, causing a syntax error.

❓ optimization

advanced

Optimize query with subquery in WHERE clause

Consider this query to find customers who placed orders with total amount greater than the average order amount:

SELECT customer_id FROM Orders WHERE total_amount > (SELECT AVG(total_amount) FROM Orders);

Which option improves performance by avoiding repeated subquery execution?

SQL

SELECT customer_id FROM Orders WHERE total_amount > (SELECT AVG(total_amount) FROM Orders);

A Add an index on total_amount column to speed up the subquery

B Rewrite as:<br>SELECT customer_id FROM Orders WHERE total_amount &gt; AVG(total_amount);

C Use a correlated subquery:<br>SELECT customer_id FROM Orders WHERE total_amount &gt; (SELECT AVG(total_amount) FROM Orders WHERE Orders.customer_id = customer_id);

D Use a CTE to calculate average once:<br>WITH avg_amount AS (SELECT AVG(total_amount) AS avg_val FROM Orders)<br>SELECT customer_id FROM Orders, avg_amount WHERE total_amount > avg_val;

💡 Hint

Calculate the average once and reuse it.

Using a CTE calculates the average once, avoiding repeated subquery calls for each row.

🔧 Debug

advanced

Debug why query returns no rows

Given tables Orders(order_id, customer_id, amount) and Customers(customer_id, name), this query returns no rows:

SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE amount > 1000);

What is the most likely reason?

SQL

SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE amount > 1000);

A No orders have amount greater than 1000, so subquery returns empty set

B customer_id is not a valid column in Orders table

C The subquery should use EXISTS instead of IN

D The main query should join Customers and Orders instead of using subquery

💡 Hint

Check the data in Orders table for amounts over 1000.

If no orders have amount > 1000, the subquery returns empty, so no customers match.

🧠 Conceptual

expert

Understanding subquery behavior in WHERE clause

What happens if a subquery in the WHERE clause returns NULL when used with a comparison operator like > ?
Example:

SELECT * FROM Products WHERE price > (SELECT MAX(discount) FROM Discounts WHERE product_id = Products.id);

Assuming some products have no matching discount rows, what is the effect on those products in the result?

SQL

SELECT * FROM Products WHERE price > (SELECT MAX(discount) FROM Discounts WHERE product_id = Products.id);

A Products with no matching discount rows are excluded because comparison with NULL yields false

B Products with no matching discount rows are included because NULL is treated as zero

C Query raises an error due to NULL in subquery result

D Products with no matching discount rows are included because NULL is ignored

💡 Hint

Remember how SQL treats NULL in comparisons.

In SQL, any comparison with NULL results in unknown (treated as false in WHERE), so those rows are excluded.

Practice

1. What does a subquery in the WHERE clause do in SQL?

easy

A. Creates a new table from existing data

B. Filters rows based on the results of another query

C. Deletes rows from a table

D. Updates values in a table

Solution

1. Step 1: Understand the role of subqueries in WHERE clause

   A subquery inside a WHERE clause is used to filter rows by comparing values to the results of another query.

2. Step 2: Compare with other SQL operations

   Creating tables, deleting, or updating rows are different SQL operations and not the purpose of subqueries in WHERE.

3. Final Answer:

   Filters rows based on the results of another query -> Option B

4. Quick Check:

   Subquery in WHERE = filter rows [OK]

Hint: Subquery in WHERE filters rows using another query's results [OK]

Common Mistakes:

• Thinking subquery creates or modifies tables
• Confusing subquery with JOIN
• Assuming subquery always returns one value

2. Which of the following is the correct syntax to use a subquery in the WHERE clause to find employees in departments with ID 10 or 20?

easy

A. SELECT * FROM employees WHERE department_id IN SELECT department_id FROM departments WHERE department_id IN (10, 20);

B. SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_id IN (10, 20));

C. SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id = 10 OR 20);

D. SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id IN (10, 20));

Solution

1. Step 1: Identify correct subquery syntax with IN

   The subquery must be enclosed in parentheses and used with IN to match multiple values.

2. Step 2: Check each option's syntax

   SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id IN (10, 20)); correctly uses IN with a subquery in parentheses. SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_id IN (10, 20)); uses = which expects one value, causing error. SELECT * FROM employees WHERE department_id IN SELECT department_id FROM departments WHERE department_id IN (10, 20); misses parentheses around subquery. SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id = 10 OR 20); has incorrect WHERE clause syntax.

3. Final Answer:

   SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id IN (10, 20)); -> Option D

4. Quick Check:

   Subquery with IN needs parentheses [OK]

Hint: Use IN with parentheses for subqueries returning multiple values [OK]

Common Mistakes:

• Using = instead of IN for multiple values
• Omitting parentheses around subquery
• Incorrect WHERE clause conditions inside subquery

3. Given the tables:
employees(emp_id, name, department_id)
departments(department_id, name)
What will this query return?

SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE name = 'Sales');

medium

A. Names of employees who work in the Sales department

B. Names of all employees

C. Names of departments named Sales

D. An error because subquery returns multiple rows

Solution

1. Step 1: Understand the subquery

   The subquery selects department_id from departments where name is 'Sales'. This returns IDs of Sales departments.

2. Step 2: Apply subquery results in main query

   The main query selects employee names where their department_id matches any of those returned by the subquery.

3. Final Answer:

   Names of employees who work in the Sales department -> Option A

4. Quick Check:

   Subquery filters employees by Sales department [OK]

Hint: Subquery filters department IDs, main query filters employees [OK]

Common Mistakes:

• Thinking subquery returns employee names
• Confusing department names with employee names
• Assuming subquery causes error with multiple rows

4. Identify the error in this query:

SELECT * FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE city = 'New York');

medium

A. Incorrect table name 'orders'

B. Missing FROM clause in subquery

C. Subquery returns multiple rows causing an error with '=' operator

D. No error, query is correct

Solution

1. Step 1: Analyze subquery result

   The subquery selects customer_id from customers where city is 'New York'. This can return multiple customer IDs.

2. Step 2: Check operator compatibility

   The main query uses '=' which expects a single value, but subquery returns multiple rows, causing an error.

3. Final Answer:

   Subquery returns multiple rows causing an error with '=' operator -> Option C

4. Quick Check:

   Use IN for multiple subquery results [OK]

Hint: Use IN if subquery returns multiple values, not = [OK]

Common Mistakes:

• Using = with subquery returning multiple rows
• Assuming subquery always returns one value
• Ignoring error messages about subquery results

5. You want to find all products that have never been ordered. Given tables:
products(product_id, name)
orders(order_id, product_id)
Which query correctly uses a subquery in the WHERE clause to find these products?

hard

A. SELECT name FROM products WHERE product_id NOT IN (SELECT product_id FROM orders);

B. SELECT name FROM products WHERE product_id IN (SELECT product_id FROM orders);

C. SELECT name FROM products WHERE product_id = (SELECT product_id FROM orders);

D. SELECT name FROM products WHERE product_id NOT = (SELECT product_id FROM orders);

Solution

1. Step 1: Understand the goal

   We want products that have never been ordered, so their product_id should NOT appear in orders.

2. Step 2: Use NOT IN with subquery

   The subquery selects all product_ids from orders. Using NOT IN filters products not in that list.

3. Step 3: Check other options

   SELECT name FROM products WHERE product_id IN (SELECT product_id FROM orders); finds products that have been ordered (opposite). SELECT name FROM products WHERE product_id = (SELECT product_id FROM orders); uses = which expects one value, causing error. SELECT name FROM products WHERE product_id NOT = (SELECT product_id FROM orders); uses NOT = which is invalid syntax for multiple values.

4. Final Answer:

   SELECT name FROM products WHERE product_id NOT IN (SELECT product_id FROM orders); -> Option A

5. Quick Check:

   NOT IN filters products never ordered [OK]

Hint: Use NOT IN with subquery to find missing matches [OK]

Common Mistakes:

• Using = instead of IN for multiple values
• Confusing NOT IN with NOT =
• Selecting wrong table columns in subquery