[Solved] Which of the following is the correct syntax for a correlated subquery in PostgreSQL? — Ans: SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM... | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Which of the following is the correct syntax for a correlated subquery in PostgreSQL?

ASELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department);

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

CSELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.salary > 50000);

DSELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.name = 'John');

Step-by-Step Solution

Solution:

Step 1: Identify the correlated subquery condition
SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department); references the outer query alias e.department inside the inner query, making it correlated.
Step 2: Check other options for correlation
Options B, C, and D do not reference outer query columns inside the subquery, so they are not correlated.
Final Answer:
SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department); -> Option A
Quick Check:
Correlation requires outer reference inside subquery [OK]

Quick Trick: Look for outer query column inside subquery WHERE clause [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Which of the following is the correct syntax for a correlated subquery in PostgreSQL?