Write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary?

Result table should contain:

  • Month
  • Department
  • Avg dept salary
  • Company avg monthly salary
  • Comparison (higher/lower/same) (avg dept salary compared to company avg salary)

Table Schema:

'salary' table

CREATE TABLE salary(
id INT,
employee_id INT,
amount INT,
pay_date DATETIME
)

INSERT INTO salary(id,employee_id,amount,pay_date)
VALUES(1,1,9000,'2017-03-31'),
(2,2,6000,'2017-03-31'),
(3,3,10000,'2017-03-31'),
(4,1,7000,'2017-02-28'),
(5,2,6000,'2017-02-28'),
(6,3,8000,'2017-02-28')

'emp_dept' table

CREATE TABLE emp_dept(
employee_id INT,
department_id INT
)

INSERT INTO emp_dept(employee_id,department_id)
VALUES(1,1),
(2,2),
(3,2)