Subquery with IN operator in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees working in departments with more than 3 employees

Given the tables Employees(emp_id, name, dept_id) and Departments(dept_id, dept_name), which query returns the names of employees who work in departments having more than 3 employees?

A SELECT name FROM Employees WHERE dept_id NOT IN (SELECT dept_id FROM Employees GROUP BY dept_id HAVING COUNT(emp_id) > 3);

B SELECT name FROM Employees WHERE emp_id IN (SELECT emp_id FROM Employees GROUP BY dept_id HAVING COUNT(emp_id) > 3);

C SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Employees GROUP BY dept_id HAVING COUNT(emp_id) > 3);

D SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Employees GROUP BY dept_id HAVING COUNT(emp_id) > 3);

💡 Hint

Use a subquery to find departments with more than 3 employees, then select employees in those departments.

Option D correctly uses a subquery with IN to find all dept_id values where the count of employees is greater than 3, then selects employees in those departments. Option D incorrectly filters by emp_id, which doesn't relate to department counts. Option D uses '=' which expects a single value but the subquery returns multiple rows, causing an error. Option D selects employees NOT in those departments, which is the opposite of the requirement.

❓ query_result

intermediate

List products sold in categories with at least 5 products

Given tables Products(product_id, product_name, category_id) and Categories(category_id, category_name), which query lists product names where their category has 5 or more products?

A SELECT product_name FROM Products WHERE category_id IN (SELECT category_id FROM Products GROUP BY category_id HAVING COUNT(product_id) >= 5);

B SELECT product_name FROM Products WHERE category_id NOT IN (SELECT category_id FROM Products GROUP BY category_id HAVING COUNT(product_id) >= 5);

C SELECT product_name FROM Products WHERE category_id = (SELECT category_id FROM Products GROUP BY category_id HAVING COUNT(product_id) >= 5);

D SELECT product_name FROM Products WHERE product_id IN (SELECT product_id FROM Products GROUP BY category_id HAVING COUNT(product_id) >= 5);

💡 Hint

Use a subquery to find categories with at least 5 products, then select products in those categories.

Option A correctly uses IN with a subquery that groups by category_id and filters categories having 5 or more products. Option A incorrectly filters by product_id which is unrelated to category counts. Option A uses '=' which fails if multiple categories meet the condition. Option A selects products not in those categories, opposite of the requirement.

📝 Syntax

advanced

Identify the syntax error in the subquery with IN operator

Which option contains a syntax error in the SQL query using a subquery with the IN operator?

A SELECT name FROM Employees WHERE dept_id IN SELECT dept_id FROM Employees GROUP BY dept_id HAVING COUNT(emp_id) > 2;

B SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Employees);

C SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Employees WHERE emp_id > 10);

D SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Employees GROUP BY dept_id HAVING COUNT(emp_id) > 2);

💡 Hint

Check if the subquery is properly enclosed in parentheses after IN.

Option A is missing parentheses around the subquery after IN, causing a syntax error. Options A, B, and C correctly use parentheses enclosing the subquery.

❓ optimization

advanced

Optimize query to find customers who placed orders in specific regions

Given tables Customers(customer_id, name), Orders(order_id, customer_id, region), which query is the most efficient to find names of customers who placed orders in regions 'North' or 'East'?

A SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE region IN ('North', 'East'));

B SELECT name FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE region IN ('North', 'East');

C SELECT name FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.customer_id = Customers.customer_id AND region IN ('North', 'East'));

D SELECT name FROM Customers WHERE customer_id = (SELECT customer_id FROM Orders WHERE region IN ('North', 'East'));

💡 Hint

EXISTS can be more efficient than IN when checking for existence.

Option C uses EXISTS which stops searching once a matching order is found, improving efficiency. Option C uses IN which may scan all matching customer_ids. Option C returns duplicates if customers have multiple orders in those regions and may require DISTINCT. Option C uses '=' which fails if multiple orders match, causing an error.

🧠 Conceptual

expert

Understanding behavior of IN operator with NULL values in subquery

Consider the query:
SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Employees WHERE dept_id IS NOT NULL);
What will be the effect if the subquery returns some NULL values and why?

A The query will return employees with NULL dept_id because NULL matches NULL in IN operator.

B The query will return all employees except those with NULL dept_id because NULLs are ignored in IN comparisons.

C The query will return no rows because NULL in the subquery causes the IN condition to always evaluate to false.

D The query will raise a runtime error due to NULL values in the subquery.

💡 Hint

Remember how SQL treats NULLs in IN operator comparisons.

In SQL, NULL values in the list returned by a subquery in IN are ignored for matching. The condition dept_id IN (subquery) returns true for matching non-NULL dept_id values. Employees with NULL dept_id do not match any value and are excluded. No error occurs. NULL does not equal NULL in SQL comparisons.

Practice

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

easy

A. It checks if a value matches any value returned by the subquery.

B. It updates values in the main query based on the subquery.

C. It deletes rows that are returned by the subquery.

D. It creates a new table from the subquery results.

Solution

1. Step 1: Understand the role of IN operator

   The IN operator compares a value to a list of values and returns true if it matches any of them.

2. Step 2: Understand subquery usage

   The subquery returns a list of values that the main query uses to filter rows with the IN operator.

3. Final Answer:

   It checks if a value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN with subquery = match any value [OK]

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

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with JOIN
• Assuming IN creates new tables

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

easy

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

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

C. SELECT * FROM employees WHERE department_id IN (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. Options A, B, and D have syntax errors.

3. Final Answer:

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

4. Quick Check:

   IN syntax = IN (subquery) [OK]

Hint: Use parentheses around subquery after IN [OK]

Common Mistakes:

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

3. Given the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in the Sales department.

B. Names of employees who work in the Sales department.

C. All employee names regardless of department.

D. An error because subquery returns multiple rows.

Solution

1. Step 1: Understand subquery filtering

   The subquery selects dept_id values where dept_name is 'Sales'.

2. Step 2: Main query filters employees

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

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales dept_id [OK]

Hint: IN filters rows matching subquery values [OK]

Common Mistakes:

• Thinking it returns employees outside Sales
• Assuming subquery causes error with multiple rows
• Ignoring subquery filtering condition

4. Identify the error in this query:

SELECT emp_id FROM Employees WHERE dept_id IN SELECT dept_id FROM Departments;

medium

A. Subquery should be in the FROM clause.

B. Using IN instead of EXISTS.

C. dept_id should be compared with = not IN.

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, causing a 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
• Using = instead of IN for multiple values

5. You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables:
Customers(customer_id, name)
Orders(order_id, customer_id, product_id)
Products(product_id, category)
Which query correctly uses a subquery with IN to get these customers?

hard

A. SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

B. SELECT name FROM Customers WHERE customer_id = (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

C. SELECT name FROM Customers WHERE customer_id IN (SELECT product_id FROM Products WHERE category = 'Electronics');

D. SELECT name FROM Customers WHERE customer_id IN (SELECT order_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

Solution

1. Step 1: Understand the relationships

   Customers link to Orders by customer_id; Orders link to Products by product_id.

2. Step 2: Analyze the nested subqueries

   The innermost subquery selects product_ids in 'Electronics'. The middle subquery selects customer_ids from Orders with those product_ids. The outer query selects customer names with those customer_ids.

3. Final Answer:

   SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics')); -> Option A

4. Quick Check:

   Nested IN filters customers by Electronics orders [OK]

Hint: Use nested IN for multi-level filtering [OK]

Common Mistakes:

• Using = instead of IN for multiple customer_ids
• Comparing customer_id with product_id or order_id
• Missing nested subquery for product filtering