Solution:Step 1: Analyze the subquery correlationThe subquery counts employees where e.department_id = d.id, linking to outer query's department.Step 2: Understand the outputFor each department, it returns the department name and employee count in that department.Final Answer:Each department name wi

[Solved] Consider the query:SELECT d.name, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS emp_count FROM departments d;What will this query return? | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Consider the query:

SELECT d.name, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS emp_count FROM departments d;

What will this query return?

AEach employee name with their department name.

BA single row with total employees across all departments.

CAn error because subquery is not correlated.

DEach department name with the count of employees in that department.

Step-by-Step Solution

Solution:

Step 1: Analyze the subquery correlation
The subquery counts employees where e.department_id = d.id, linking to outer query's department.
Step 2: Understand the output
For each department, it returns the department name and employee count in that department.
Final Answer:
Each department name with the count of employees in that department. -> Option D
Quick Check:
Correlated subquery counts per department = Each department name with the count of employees in that department. [OK]

Quick Trick: Correlated subqueries compute per outer row values. [OK]

Common Mistakes:

Thinking subquery returns total count only once.
Assuming subquery is uncorrelated and causes error.
Confusing employee names with counts.

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Consider the query:

Step 1: Analyze the subquery correlation

Step 2: Understand the output

Final Answer:

Quick Check:

Want More Practice?