OCP CBT Question 534

Please solve the following question.

You need to display each employee's `first_name`, `last_name`, `salary`, and the average salary of their respective department.

EMPLOYEES Table Structure (relevant columns)
Name                           Null?    Type
------------------------------ -------- -------------
EMPLOYEE_ID                    NOT NULL NUMBER(6)
FIRST_NAME                              VARCHAR2(20)
LAST_NAME                      NOT NULL VARCHAR2(25)
SALARY                                  NUMBER(8,2)
DEPARTMENT_ID                           NUMBER(4)

SELECT e.first_name, e.last_name, e.salary, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS dept_avg_salary FROM employees e;

SELECT e.first_name, e.last_name, e.salary, d.avg_salary FROM employees e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d ON e.department_id = d.department_id;

SELECT first_name, last_name, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary FROM employees;

SELECT e.first_name, e.last_name, e.salary, AVG(e.salary) FROM employees e GROUP BY e.department_id;