Subqueries in WHERE clause in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees whose department ID is in the list returned by the subquery.

MySQL

SELECT * FROM employees WHERE department_id [1] (SELECT id FROM departments WHERE location = 'New York');

Drag options to blanks, or click blank then click option'

ANOT

BLIKE

C=

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'IN' causes an error because '=' expects a single value.

Using 'LIKE' is for pattern matching, not for lists.

Using 'NOT' alone is incomplete and causes syntax errors.

The IN keyword is used to check if a value matches any value in a list returned by a subquery.

2fill in blank

medium

Complete the code to find products with prices greater than the average price returned by the subquery.

MySQL

SELECT product_name FROM products WHERE price [1] (SELECT AVG(price) FROM products);

Drag options to blanks, or click blank then click option'

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' selects products cheaper than average, which is not the goal.

Using '=' selects products exactly at average price, which is too narrow.

Using '<=' includes products priced below or equal to average, not just above.

The query selects products priced higher than the average price, so the comparison operator should be >.

3fill in blank

hard

Fix the error in the query to select customers who have placed orders with total amount greater than 1000.

MySQL

SELECT customer_id FROM orders WHERE total_amount [1] (SELECT SUM(total_amount) FROM orders GROUP BY customer_id);

Drag options to blanks, or click blank then click option'

AIN

B=

CANY

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' alone causes an error because the right side is multiple values.

Using '=' expects a single value, causing errors.

Using 'IN' checks for equality, not comparison.

The subquery returns multiple sums (one per customer), so to compare with any of these values, use ANY.

4fill in blank

hard

Fill both blanks to select employees whose salary is higher than the average salary in their department.

MySQL

SELECT employee_id, salary FROM employees WHERE salary [1] (SELECT AVG(salary) FROM employees WHERE department_id [2] employees.department_id);

Drag options to blanks, or click blank then click option'

A>

B=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' or '=' in the first blank selects wrong employees.

Using '!=' in the second blank compares wrong departments.

Mixing up the department_id references causes errors.

The query compares each employee's salary to the average salary in their own department. The first blank uses > to find higher salaries. The second blank uses = to match the department IDs.

5fill in blank

hard

Fill all three blanks to select products whose price is between the minimum and maximum prices in the same category.

MySQL

SELECT product_name, price FROM products WHERE price [1] (SELECT MIN(price) FROM products WHERE category_id [2] products.category_id) AND price [3] (SELECT MAX(price) FROM products WHERE category_id = products.category_id);

Drag options to blanks, or click blank then click option'

A>=

B=

C<=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' or '<' excludes products priced exactly at min or max.

Using '!=' in the second blank causes wrong category matching.

Mixing up comparison operators leads to incorrect results.

The query finds products priced between the minimum and maximum in their category. The first blank uses >= to include prices equal or above the minimum. The second blank uses = to match category IDs. The third blank uses <= to include prices equal or below the maximum.