Solution:Step 1: Understand the subqueryThe subquery calculates average salary per department and aliases it as dept_avg.Step 2: Apply the WHERE filterThe outer query filters departments where average salary is greater than 50000.Final Answer:Departments with average salary greater than 50000 -> Op

[Solved] Given the tables:employees(id, department_id, salary)What will the following query return?SELECT dept_avg.department_id, dept_avg.... | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Given the tables:
employees(id, department_id, salary)
What will the following query return?

SELECT dept_avg.department_id, dept_avg.avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg WHERE dept_avg.avg_salary > 50000;

AAll employees with salary greater than 50000

BDepartments with average salary greater than 50000

CEmployees grouped by department with total salary

DSyntax error due to missing alias

Step-by-Step Solution

Solution:

Step 1: Understand the subquery
The subquery calculates average salary per department and aliases it as dept_avg.
Step 2: Apply the WHERE filter
The outer query filters departments where average salary is greater than 50000.
Final Answer:
Departments with average salary greater than 50000 -> Option B
Quick Check:
Subquery filters departments by avg salary > 50000 [OK]

Quick Trick: Subquery calculates averages; outer query filters results [OK]

Common Mistakes:

Confusing employee rows with department aggregates
Ignoring alias usage
Misreading WHERE condition on derived table

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

Given the tables:

Step 1: Understand the subquery

Step 2: Apply the WHERE filter

Final Answer:

Quick Check:

Want More Practice?