Bird
0
0

You want to find employees who earn more than the average salary in their department. Which query correctly uses a nested subquery?

hard📝 Application Q8 of 15
SQL - Subqueries
You want to find employees who earn more than the average salary in their department. Which query correctly uses a nested subquery?
ASELECT name FROM Employees WHERE salary > AVG(salary) GROUP BY dept_id
BSELECT name FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id)
CSELECT name FROM Employees WHERE salary > (SELECT salary FROM Employees WHERE dept_id = dept_id)
DSELECT name FROM Employees WHERE salary > ALL (SELECT salary FROM Employees)
Step-by-Step Solution
Solution:

  1. Step 1: Understand the requirement

    We need to compare each employee's salary to the average salary of their own department.

  2. Step 2: Analyze each option

    SELECT name FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id) uses a correlated subquery referencing the outer query's dept_id to calculate average salary per department.

  3. Final Answer:

    SELECT name FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id) -> Option B

  4. Quick Check:

    Correlated subquery compares salary to dept average [OK]
Quick Trick: Use correlated subqueries to compare within groups [OK]
Common Mistakes:
MISTAKES
  • Using aggregate without GROUP BY
  • Ignoring correlation with outer query
  • Using ALL without filtering by department

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Advanced Joins - FULL OUTER JOIN behavior - Quiz 15hard GROUP BY and HAVING - GROUP BY multiple columns - Quiz 15hard GROUP BY and HAVING - GROUP BY with ORDER BY - Quiz 3easy GROUP BY and HAVING - GROUP BY multiple columns - Quiz 4medium Subqueries - Subquery with EXISTS operator - Quiz 14medium Subqueries - Subquery vs JOIN performance trade-off - Quiz 10hard Table Constraints - Constraint naming conventions - Quiz 6medium Table Relationships - ER diagram to table mapping - Quiz 10hard Table Relationships - Foreign key linking mental model - Quiz 6medium Views - View as a saved query mental model - Quiz 9hard