[Solved] Identify the error in the following query:SELECT d.department_id, d.avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees) d WHERE d.avg_salary > 60000; | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Identify the error in the following query:

SELECT d.department_id, d.avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees) d WHERE d.avg_salary > 60000;

AIncorrect use of WHERE clause outside subquery

BAlias 'd' is missing

CMissing GROUP BY clause in the subquery

DAVG function cannot be used in subqueries

Step-by-Step Solution

Solution:

Step 1: Check subquery aggregation
The subquery uses AVG but lacks GROUP BY, so it aggregates over all rows.
Step 2: Understand the impact
Without GROUP BY, department_id is not grouped, causing an error or unexpected result.
Final Answer:
Missing GROUP BY clause in the subquery -> Option C
Quick Check:
Aggregation needs GROUP BY when selecting non-aggregated columns [OK]

Quick Trick: Use GROUP BY with aggregates selecting other columns [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Identify the error in the following query: