Compare each employee's salary with the average salary of the corresponding department.Output the department, first name, and salary of employees along with the average salary of that department.
table name: employee
Solution:
select department, first_name,salary,avg(cast(salary as float)) over (partition by department) as avgSalary from employee
select e.department,e.first_name,e.salary, (select avg(cast(salary as float)) from employee where department= e.department group by department) as AvgSalaryDep from employee as e