[Solved] Identify the error in this query:SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = department_id); | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Identify the error in this query:

SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = department_id);

AThe subquery uses an ambiguous column reference.

BThe subquery is missing correlation to the outer query.

CThe subquery should use COUNT instead of AVG.

DThe outer query is missing a GROUP BY clause.

Step-by-Step Solution

Solution:

Step 1: Analyze the subquery condition
The condition department_id = department_id compares the same column without alias, causing ambiguity.
Step 2: Understand the fix
It should reference outer query's alias like department_id = e.department_id to correlate properly.
Final Answer:
The subquery uses an ambiguous column reference. -> Option A
Quick Check:
Ambiguous column in subquery = The subquery uses an ambiguous column reference. [OK]

Quick Trick: Always alias columns to avoid ambiguity in correlated subqueries. [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Identify the error in this query: