Identify all possible bugs in below Scenario based sql code

You have three tables: employees, depar tments, and locat ions. Your task is to write an SQL query that:

Calculates the average salary for each department.

Lists each department's name and location.

For each employee in a department, shows:

Their name and salary.

Their rank within the department based on salary $($highest salary ranks first$).$

The salary of the employee just above them and just below them in the ranking.

Even if a department has no employees, it should still be included in the result with NULL for the average salary.

WITH emp$\_$ranks AS $($

SELECT

e$.$emp$\_$id$,$

e$.$emp$\_$name,