Subqueries with IN operator in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees who work in departments listed in the subquery.

MySQL

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

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

A=

BNOT IN

CIN

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'IN' when comparing to a subquery result.

Using 'LIKE' which is for pattern matching, not list membership.

The IN operator is used to check if a value matches any value in a list or subquery result.

2fill in blank

medium

Complete the code to find all products whose category_id is in the list of categories with more than 100 products.

MySQL

SELECT product_name FROM products WHERE category_id [1] (SELECT category_id FROM products GROUP BY category_id HAVING COUNT(*) > 100);

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

AIN

BNOT IN

C=

DBETWEEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which only compares to a single value, not a list.

Using 'BETWEEN' which is for ranges, not lists.

The IN operator allows filtering products whose category_id matches any category_id from the subquery.

3fill in blank

hard

Fix the error in the query to select customers who have orders in the USA.

MySQL

SELECT customer_name FROM customers WHERE customer_id [1] (SELECT customer_id FROM orders WHERE country = 'USA');

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

AIN

BLIKE

CNOT IN

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which causes an error if subquery returns multiple rows.

Using 'LIKE' which is for pattern matching, not list membership.

The IN operator is needed to compare a column to multiple values returned by the subquery.

4fill in blank

hard

Fill both blanks to select orders where the product_id is in the list of products priced above 50.

MySQL

SELECT order_id FROM orders WHERE product_id [1] (SELECT product_id FROM products WHERE price [2] 50);

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

AIN

B>

C<

DNOT IN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'NOT IN' instead of 'IN' for the first blank.

Using '<' instead of '>' for the price comparison.

Use IN to check if product_id is in the subquery list, and > to filter products priced above 50.

5fill in blank

hard

Fill all three blanks to select employee names who work in departments located in 'London' and have a salary greater than 50000.

MySQL

SELECT employee_name FROM employees WHERE department_id [1] (SELECT department_id FROM departments WHERE location = [2]) AND salary [3] 50000;

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

AIN

B'London'

C>

DNOT IN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'NOT IN' instead of 'IN' for the first blank.

Forgetting quotes around 'London' in the second blank.

Using '<' instead of '>' for the salary comparison.

The query uses IN to filter department_id by those in London, compares location to the string 'London', and filters salary with > for values greater than 50000.

Practice

1. What does the IN operator do when used with a subquery in MySQL?

easy

A. Checks if a value matches any value returned by the subquery

B. Joins two tables based on a common column

C. Updates values in the main table using subquery results

D. Deletes rows that match the subquery results

Solution

1. Step 1: Understand the IN operator

   The IN operator checks if a value exists within a list of values.

2. Step 2: Role of subquery with IN

   The subquery returns a list of values, and IN checks if the main query's value matches any of these.

3. Final Answer:

   Checks if a value matches any value returned by the subquery -> Option A

4. Quick Check:

   IN with subquery = value match list [OK]

Hint: IN checks if value is in subquery result list [OK]

Common Mistakes:

• Confusing IN with JOIN
• Thinking IN updates or deletes rows
• Assuming IN returns a single value

2. Which of the following is the correct syntax to use a subquery with the IN operator in MySQL?

easy

A. SELECT * FROM employees WHERE department_id = IN (SELECT id FROM departments);

B. SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);

C. SELECT * FROM employees WHERE IN department_id (SELECT id FROM departments);

D. SELECT * FROM employees WHERE department_id IN SELECT id FROM departments;

Solution

1. Step 1: Review correct IN syntax

   The IN operator must be followed by parentheses enclosing the subquery.

2. Step 2: Check each option

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); correctly uses IN with parentheses and a subquery. Others have syntax errors.

3. Final Answer:

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); -> Option B

4. Quick Check:

   IN + (subquery) = correct syntax [OK]

Hint: Use IN followed by parentheses around subquery [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subquery
• Placing IN before column name

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

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

medium

A. An error because subquery returns multiple rows

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Names of employees who do not work in Sales

Solution

1. Step 1: Analyze subquery

   The subquery selects department IDs where the department name is 'Sales'.

2. Step 2: Main query filters employees

   The main query selects employee names whose department_id matches any ID from the subquery.

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters IDs; IN matches employees with those IDs [OK]

Common Mistakes:

• Thinking IN causes error with multiple rows
• Ignoring subquery filter condition
• Assuming it returns all employees

4. Identify the error in this query:

SELECT * FROM orders WHERE customer_id IN SELECT id FROM customers WHERE city = 'New York';

medium

A. Subquery returns no rows

B. Incorrect table name 'orders'

C. Using IN instead of EXISTS

D. Missing parentheses around the subquery after IN

Solution

1. Step 1: Check IN operator syntax

   The IN operator requires the subquery to be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around the subquery after IN, causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery after IN -> Option D

4. Quick Check:

   IN needs (subquery) [OK]

Hint: Always put subquery inside parentheses after IN [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS
• Assuming table names cause error

5. You want to find all products that have been ordered by customers from the city 'Chicago'.
Given tables:
products(id, name)
orders(id, product_id, customer_id)
customers(id, city)
Which query correctly uses a subquery with IN to get the product names?

hard

A. SELECT name FROM products WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Chicago'));

B. SELECT name FROM products WHERE id = IN (SELECT product_id FROM orders WHERE customer_id = IN (SELECT id FROM customers WHERE city = 'Chicago'));

C. SELECT name FROM products WHERE id IN SELECT product_id FROM orders WHERE customer_id IN SELECT id FROM customers WHERE city = 'Chicago';

D. SELECT name FROM products WHERE id IN (SELECT id FROM customers WHERE city = 'Chicago');

Solution

1. Step 1: Understand the relationships

   Products link to orders by product_id; orders link to customers by customer_id.

2. Step 2: Build nested subqueries

   Inner subquery gets customer IDs from Chicago; middle subquery gets product_ids from orders by those customers; outer query selects product names with those IDs.

3. Final Answer:

   SELECT name FROM products WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Chicago')); -> Option A

4. Quick Check:

   Nested IN subqueries correctly filter products [OK]

Hint: Use nested IN subqueries to filter related tables stepwise [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subqueries
• Using wrong columns in subqueries