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

SQL - Subqueries

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

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

BSELECT e.name FROM employees e WHERE salary > 50000

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

DSELECT e.name FROM employees e JOIN departments d ON e.department = d.id

Step-by-Step Solution

Solution:

Step 1: Identify correlation in subquery
SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) uses 'e.department' inside the subquery, linking it to the outer query.
Step 2: Check other options
SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees) has no reference to outer query in subquery; the option with 'salary > 50000' lacks a subquery and the JOIN option is not a subquery.
Final Answer:
SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) -> Option A
Quick Check:
Correlation needs outer query column inside subquery [OK]

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

Common Mistakes:

MISTAKES

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

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