EXERCISE-2.1_EMAIL

SELECT PE.EMAIL_ADDRESS,D.NAME AS DEPARTMENT,S.AVG_SALARY, PE.SALARY, CONCAT(PE.FIRSTNAME, ' ', PE.LASTNAME) AS NAME
FROM (
  SELECT P.WORKS_IN AS DEPT,  AVG(SALARY) AS AVG_SALARY
  FROM EMAIL.PERSON AS P
  GROUP BY(P.WORKS_IN)
)AS S
INNER JOIN EMAIL.PERSON AS PE
ON PE.WORKS_IN = S.DEPT AND PE.SALARY > S.AVG_SALARY OR PE.SALARY < S.AVG_SALARY
INNER JOIN EMAIL.DEPARTMENT AS D
ON S.DEPT = D.ID
INNER JOIN EMAIL.EMAILS AS E
ON E.MESSAGE_FROM = PE.EMAIL_ADDRESS