DotNet and AI Materials
This blog will guid you to upskill youself in Microsoft Technology like c#,Dotnet Core, Web API, MS SQL, Azure, Gen AI, RAg, AI, LLm and Interview Preparation.
Wednesday, 18 November 2026
Calculate the moving average of salaries over the last 3 employees ordered by hire date.
Tuesday, 17 November 2026
Write a query to perform a self-join to find pairs of employees in the same department.
Monday, 16 November 2026
Write a query to find the difference in days between two dates in the same table.
Sunday, 15 November 2026
Find employees who don’t have a department assigned.
Saturday, 14 November 2026
Write a query to find the number of employees in each job title.
Friday, 13 November 2026
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
- The CTE
dept_avgcalculates one average per department. - The subquery
SELECT MAX(avg_salary) FROM dept_avgfinds the highest of those averages. - 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.