Write a Query to display employees getting salary greater that average salary

Query 1: Using Analytic Function

This query uses AVG(sal) OVER (PARTITION BY deptno) to calculate the average salary department-wise.

SELECT empno, ename, job, mgr, sal, hiredate, comm, deptno, avg_sal
FROM (
    SELECT empno, ename, job, mgr, sal, hiredate, comm, deptno,
           AVG(sal) OVER (PARTITION BY deptno) AS avg_sal
    FROM emp
)
WHERE sal > avg_sal;

Here, Oracle calculates the average salary for each deptno and then filters employees whose salary is greater than that average.

Query 2: Using GROUP BY and Self Join

This query first calculates the average salary for each department using GROUP BY, then joins that result with the emp table.

SELECT *
FROM emp a,
     (SELECT deptno, AVG(sal) sal
      FROM emp
      GROUP BY deptno) b
WHERE a.deptno = b.deptno
  AND a.sal > b.sal;

This approach produces the same logical result as Query 1, but it uses a grouped subquery instead of an analytic function.

How the queries work

• The average salary is calculated department-wise.
• Each employee is compared against the average salary of their own department.
• Only employees with salary greater than the department average are returned.

Which query is better?

In most cases, Query 1 is preferred because analytic functions are often simpler to read and can be more efficient for this kind of calculation.

Example Use Case

Suppose a company wants to find employees who are paid above the average salary in their department. These queries are useful for salary analysis, HR reporting, and performance reviews.

Key Point:
Both queries return employees whose salary is above their department average, but the analytic function version is usually cleaner and easier to maintain.