[Solved] You want to find employees whose salary is above the average salary of their department, but only for departments with more than... Which query correctly uses a correlated subquery to achieve this? | SQL

SQL - Subqueries

You want to find employees whose salary is above the average salary of their department, but only for departments with more than 5 employees. Which query correctly uses a correlated subquery to achieve this?

ASELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) AND department IN (SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 5);

BSELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) AND (SELECT COUNT(*) FROM employees WHERE department = e.department) > 5;

CSELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department AND COUNT(*) > 5);

DSELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = employees.department) AND COUNT(*) > 5;

Step-by-Step Solution

Solution:

Step 1: Use correlated subqueries for average and count
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) AND (SELECT COUNT(*) FROM employees WHERE department = e.department) > 5; uses two correlated subqueries referencing e.department to filter by average salary and department size.
Step 2: Validate syntax and logic
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) AND (SELECT COUNT(*) FROM employees WHERE department = e.department) > 5; correctly applies conditions; others misuse aggregation or correlation.
Final Answer:
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) AND (SELECT COUNT(*) FROM employees WHERE department = e.department) > 5; -> Option B
Quick Check:
Multiple correlated subqueries can filter complex conditions [OK]

Quick Trick: Use separate correlated subqueries for different filters [OK]

Common Mistakes:

MISTAKES

Combining aggregation incorrectly in one subquery
Using non-correlated subqueries for per-row filters
Misplacing HAVING clause

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Which query correctly uses a correlated subquery to achieve this?

Step 1: Use correlated subqueries for average and count

Step 2: Validate syntax and logic

Final Answer:

Quick Check:

Want More Practice?