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.