Subqueries in WHERE clause in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees with salary above department average

Given a table employees with columns id, name, department_id, and salary, which query returns all employees whose salary is greater than the average salary of their department?

A SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B SELECT name FROM employees WHERE salary > AVG(salary) FROM employees GROUP BY department_id;

C SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

D SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = employees.department_id);

💡 Hint

Think about how to compare each employee's salary to the average salary of their own department using a subquery.

Option A correctly uses a correlated subquery to calculate the average salary for the employee's department and compares the employee's salary to it. Option A is invalid syntax. Option A compares salary to the average salary of all employees, not per department. Option A compares salary to all salaries in the department, which is not the average.

❓ query_result

intermediate

List products with price higher than any product in category 5

Given a table products with columns product_id, category_id, and price, which query returns products whose price is higher than the price of every product in category 5?

A SELECT product_id FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id = 5);

B SELECT product_id FROM products WHERE price > (SELECT MIN(price) FROM products WHERE category_id = 5);

C SELECT product_id FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 5);

D SELECT product_id FROM products WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 5);

💡 Hint

Use a subquery to compare each product's price to all prices in category 5.

Option C correctly uses ALL to ensure the product's price is greater than every price in category 5. Option C uses ANY, which means greater than at least one price, so it returns more rows. Option C compares to the maximum price, which is equivalent to ALL but uses a scalar subquery; however, the question asks for a subquery in WHERE clause with ALL. Option C compares to the minimum price, which is incorrect.

📝 Syntax

advanced

Identify the syntax error in subquery usage

Which option contains a syntax error when using a subquery in the WHERE clause?

A SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Paris');

B SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE city = 'Paris');

C SELECT * FROM orders WHERE customer_id = ANY (SELECT id FROM customers WHERE city = 'Paris');

D SELECT * FROM orders WHERE customer_id = IN (SELECT id FROM customers WHERE city = 'Paris');

💡 Hint

Check the placement of the IN keyword in the WHERE clause.

Option D is invalid syntax because it uses '= IN' together, which is not allowed. IN is a standalone operator and should not be combined with '='. Options A, B, and D are syntactically valid.

❓ optimization

advanced

Optimize query with subquery in WHERE clause

You have a query that selects employees with salaries above the average salary of their department using a subquery in the WHERE clause. Which rewritten query is more efficient by avoiding repeated subquery execution?

A SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B SELECT e.name FROM employees e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;

C SELECT name FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

D SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

💡 Hint

Try to calculate averages once per department and join instead of using a correlated subquery.

Option B precomputes average salaries per department and joins, avoiding repeated subquery execution for each employee. Option B uses a correlated subquery executed for each row, which is less efficient. Options C and D misuse ALL and ANY with aggregate results and do not produce correct filtering.

🧠 Conceptual

expert

Understanding NULL behavior in subqueries within WHERE clause

Consider a table orders with columns order_id, customer_id, and shipped_date which can be NULL if not shipped. Which query correctly returns all orders where the customer has at least one shipped order?

A SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM orders WHERE shipped_date IS NOT NULL);

B SELECT * FROM orders WHERE shipped_date IS NOT NULL AND customer_id IN (SELECT customer_id FROM orders);

C SELECT * FROM orders WHERE customer_id = ANY (SELECT customer_id FROM orders WHERE shipped_date IS NOT NULL);

D SELECT * FROM orders WHERE customer_id = ALL (SELECT customer_id FROM orders WHERE shipped_date IS NOT NULL);

💡 Hint

Think about how to find customers who have shipped orders and then select all their orders.

Option A correctly selects orders where the customer has at least one shipped order by checking if the customer_id is in the set of customers with shipped orders. Option A unnecessarily filters orders to shipped ones and includes all customers, which is redundant. Option A is valid but equivalent to A; however, = ANY is less common and can be confusing. Option A requires the customer_id to match all shipped orders, which is logically incorrect.