Write an SQL query to retrieve all employees whose salary exceeds the company's average salary.

First calculate the average salary using a subquery, then compare each employee's salary to that value. The query is:

SELECT emp_id, emp_name, salary

FROM Employee

WHERE salary > (

SELECT AVG(salary) FROM Employee

);

The subquery runs once to compute the average salary, and the outer query scans the table once to filter rows. This approach is O(n) in time and O(1) additional space. If you prefer a join, you can write:

SELECT e.emp_id, e.emp_name, e.salary

FROM Employee e

JOIN (SELECT AVG(salary) AS avg_sal FROM Employee) a

ON e.salary > a.avg_sal;

Both queries return the same result set of employees earning above the company average.