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.