List out all the employees whose salary is more than average salary of their respective department

 First, We have to create the Table "Employees" and insert some Records as below:

CREATE TABLE employees (

    emp_id INT PRIMARY KEY,

    name VARCHAR(100),

    department_id INT,

    salary DECIMAL(10, 2)

);

INSERT INTO employees (emp_id, name, department_id, salary) VALUES

(1, 'Alice', 10, 5000.00),

(2, 'Bob', 10, 7000.00),

(3, 'Charlie', 10, 6000.00),

(4, 'David', 20, 4000.00),

(5, 'Eva', 20, 8000.00),

(6, 'Frank', 20, 5000.00),

(7, 'Grace', 30, 3000.00),

(8, 'Heidi', 30, 3000.00),

(9, 'Ivan', 30, 9000.00);

We can write the query in multiple ways. 

1. Window function:

select * from 
(select a.*, avg(salary) over(partition by department_id) avg_salary from employees a) b
where salary > avg_salary 

2. Correlated Subquery:

select * from employees a
where salary > ( select avg(salary) from employees b
         where a.department_id = b.department_id
         group by department_id
         )

3. Joins:

select a.* from
(
select * from employees
) a 
join
(
select department_id ,avg(salary) avg_salary from employees
group by department_id
) b
on salary > avg_salary
and  a.department_id = b.department_id