You want to find all employees who work in departments that have active projects. Given:
hard📝 Application Q8 of 15
PostgreSQL - Subqueries in PostgreSQL
You want to find all employees who work in departments that have active projects. Given: employees(id, name, department_id) departments(id, name) projects(id, department_id, active) Which query correctly uses a subquery with IN to find these employees?
ASELECT name FROM employees WHERE department_id IN (SELECT department_id FROM projects WHERE active = true);
BSELECT name FROM employees WHERE department_id = (SELECT department_id FROM projects WHERE active = true);
CSELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = true);
DSELECT name FROM employees WHERE department_id IN (SELECT id FROM projects WHERE active = true);
Step-by-Step Solution
Solution:
Step 1: Understand the relationships
Employees have department_id; projects have department_id and active status.
Step 2: Identify correct subquery
The subquery must select department_id from projects where active is true.
Step 3: Analyze options
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM projects WHERE active = true); correctly uses IN with a subquery returning department_ids from active projects. SELECT name FROM employees WHERE department_id = (SELECT department_id FROM projects WHERE active = true); uses '=' which expects a single value, but subquery may return multiple. SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = true); incorrectly checks departments table for active column which does not exist. SELECT name FROM employees WHERE department_id IN (SELECT id FROM projects WHERE active = true); selects id from projects which is project id, not department_id.
Final Answer:
Option A -> Option A
Quick Check:
Subquery must return department_ids of active projects [OK]
Quick Trick:Subquery must return matching department_ids for IN clause [OK]
Common Mistakes:
Using '=' instead of IN for multiple results
Selecting wrong column in subquery
Assuming 'active' column exists in departments
Master "Subqueries in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
