Find departments with the highest average salary.

Let's create table schema and sample data first, after that will write query.

Table Schema and Sample Data:-

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50) NOT NULL,
  department_id INT,
  salary        DECIMAL(10,2),
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO departments (id, name) VALUES
(10, 'Engineering'),
(20, 'Sales'),
(30, 'HR'),
(40, 'Finance');

INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Asha',  10,  95000),
(2, 'Ravi',  10,  85000),
(3, 'Meera', 20,  60000),
(4, 'Kiran', 20,  70000),
(5, 'Neha',  30,  80000),
(6, 'Rohan', 30, 100000),
(7, 'Tara',  NULL, 120000);

The averages are Engineering 90000, HR 90000, and Sales 65000. Finance has no employees. Engineering and HR are tied for the highest, and Tara has no department.

Query 1: Order and take the top row 

SELECT d.name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY avg_salary DESC
LIMIT 1;

Use SELECT TOP 1 ... in SQL Server, or FETCH FIRST 1 ROW ONLY in Oracle. This returns only one row, so with our tie it shows either Engineering or HR, and which one is not guaranteed. Add a tie-breaker such as ORDER BY avg_salary DESC, d.name if you want a predictable pick.

Query 2: Return every department tied for the highest (recommended)

WITH dept_avg AS (
  SELECT d.id, d.name, AVG(e.salary) AS avg_salary
  FROM departments d
  JOIN employees e ON e.department_id = d.id
  GROUP BY d.id, d.name
)
SELECT name, avg_salary
FROM dept_avg
WHERE avg_salary = (SELECT MAX(avg_salary) FROM dept_avg);

Result:-

name	avg_salary
Engineering	90000.00
HR	90000.00

How it works

1. The CTE dept_avg calculates one average per department.
2. The subquery SELECT MAX(avg_salary) FROM dept_avg finds the highest of those averages.
3. The outer query keeps every department whose average equals that maximum, so ties are all returned.

Query 3: Window function (easy to extend to the top N)

WITH dept_avg AS (
  SELECT d.id, d.name, AVG(e.salary) AS avg_salary
  FROM departments d
  JOIN employees e ON e.department_id = d.id
  GROUP BY d.id, d.name
)
SELECT name, avg_salary
FROM (
  SELECT name, avg_salary,
         RANK() OVER (ORDER BY avg_salary DESC) AS rnk
FROM dept_avg
) t
WHERE rnk = 1;

RANK() gives tied departments the same rank, so both Engineering and HR get rank 1. Change rnk = 1 to rnk <= 3 to get the top three salary levels.