[Solved] How can you modify this query to find employees who work in departments that have more than 10 employees?SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments); | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

How can you modify this query to find employees who work in departments that have more than 10 employees?

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

AAdd a JOIN instead of using IN with a subquery.

BReplace IN with EXISTS without any subquery changes.

CUse a subquery with GROUP BY and HAVING to filter departments with count > 10 inside the IN clause.

DUse WHERE department_id = ANY (SELECT id FROM departments);

Step-by-Step Solution

Solution:

Step 1: Identify requirement for departments with >10 employees
We need to count employees per department and filter those with count > 10.
Step 2: Use GROUP BY and HAVING in subquery
The subquery should group by department ID and use HAVING count(*) > 10.
Step 3: Use this filtered list in IN clause
The main query filters employees whose department_id is in this filtered list.
Final Answer:
Use a subquery with GROUP BY and HAVING to filter departments with count > 10 inside the IN clause. -> Option C
Quick Check:
Use GROUP BY and HAVING in subquery for filtering [OK]

Quick Trick: Use GROUP BY and HAVING in subquery for count filters [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

How can you modify this query to find employees who work in departments that have more than 10 employees?