Employees with Salary Above Department Average You are a data analyst at Nexora , a mid-sized technology company. The HR team is conducting a compensation review and wants to identify employees who are earning above the average salary within their own department — a common first step in spotting pay outliers before annual appraisals. You have access to two tables: Table: departments Column Type Description department_id INT Unique department identifier department_name VARCHAR Name of the department Table: employees Column Type Description employee_id INT Unique employee identifier full_name VARCHAR Full name of the employee department_id INT References the department the employee belongs to salary NUMERIC(10,2) Employee's monthly salary Your Task: Write a query that returns all employees whose salary is strictly above the average salary of their own department . Include the department name and the department's average salary (rounded to 2 decimal places) alongside each matching employee. Sort results by department_name ascending, then by salary descending. Expected Output Columns: department_name , full_name , salary , dept_avg_salary

Table Setup (SQL Schema)

-- DDL: Create tables
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    full_name     VARCHAR(150),
    department_id INT REFERENCES departments(department_id),
    salary        NUMERIC(10, 2)
);

-- DML: Insert sample data
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Finance'),
(4, 'Human Resources');

INSERT INTO employees (employee_id, full_name, department_id, salary) VALUES
(1,  'Alice Tran',      1, 9500.00),
(2,  'Bob Mendes',      1, 7800.00),
(3,  'Carol Ng',        1, 11200.00),
(4,  'David Park',      1, 8400.00),
(5,  'Eva Rossi',       1, 9100.00),
(6,  'Frank Osei',      2, 6200.00),
(7,  'Grace Liu',       2, 7400.00),
(8,  'Henry Marsh',     2, 5800.00),
(9,  'Isla Fernandez',  2, 6900.00),
(10, 'James Patel',     3, 8800.00),
(11, 'Karen Yoo',       3, 10500.00),
(12, 'Leo Andersen',    3, 7600.00),
(13, 'Mia Kowalski',    3, 9300.00),
(14, 'Nora Hassan',     4, 5500.00),
(15, 'Omar Diallo',     4, 6300.00),
(16, 'Paula Svensson',  4, 5900.00),
(17, 'Quinn Blake',     4, 7100.00);