[Solved] Given the tables employees(id, name, department, salary) and the query:SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e1.... | SQL

SQL - Subqueries

Given the tables employees(id, name, department, salary) and the query:

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

What does this query return?

AEmployees whose salary is above the average salary of their own department

BEmployees whose salary is above the average salary of all employees

CEmployees with salary above 50000

DAll employees regardless of salary

Step-by-Step Solution

Solution:

Step 1: Understand the subquery correlation
The subquery calculates average salary for the department of the current employee (e1.department).
Step 2: Compare salaries
The outer query selects employees whose salary is greater than that department average.
Final Answer:
Employees whose salary is above the average salary of their own department -> Option A
Quick Check:
Salary > department average = Employees whose salary is above the average salary of their own department [OK]

Quick Trick: Check which outer column is used inside subquery condition [OK]

Common Mistakes:

MISTAKES

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Given the tables employees(id, name, department, salary) and the query: