In today’s data-driven world, SQL Server remains one of the most powerful relational database systems. However, writing complex SQL queries is often what separates beginners from experts. Complex queries are the backbone of business reports, analytics dashboards, and performance-driven applications.
In this article, we’ll explore real-world complex SQL Server queries, step-by-step — covering joins, subqueries, window functions, CTEs, and pivoting — with easy-to-understand examples.
🧩 1. Using Subqueries for Conditional Data Retrieval
Scenario:
You need to find all employees whose salary is higher than the average salary of their department.
✅ Query:
SELECT
EmpName,
DepartmentId,
Salary
FROM Employees E
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentId = E.DepartmentId
);
Explanation:
The inner query calculates the average salary per department.
The outer query compares each employee’s salary to that departmental average.
This is a correlated subquery, as it depends on the outer query.
🔗 2. Combining Multiple Tables with JOINS
Scenario:
Retrieve all orders along with customer names and product details.
✅ Query:
SELECT
C.CustomerName,
O.OrderId,
P.ProductName,
O.OrderDate,
OD.Quantity,
(OD.Quantity * OD.UnitPrice) AS TotalAmount
FROM Orders O
INNER JOIN Customers C ON O.CustomerId = C.CustomerId
INNER JOIN OrderDetails OD ON O.OrderId = OD.OrderId
INNER JOIN Products P ON OD.ProductId = P.ProductId
WHERE O.OrderDate >= '2025-01-01'
ORDER BY O.OrderDate DESC;
Explanation:
This query combines four tables using inner joins to give a comprehensive view of orders placed in 2025.
🧮 3. Ranking Data Using Window Functions
Scenario:
List top 3 highest-paid employees in each department.
✅ Query:
SELECT
DepartmentId,
EmpName,
Salary,
RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
FROM Employees
WHERE Salary IS NOT NULL
Then wrap it to filter top 3:
SELECT *
FROM (
SELECT
DepartmentId,
EmpName,
Salary,
RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
FROM Employees
) AS Ranked
WHERE SalaryRank <= 3;
Explanation:
The
RANK()function assigns ranking per department.The outer query filters the top 3 salaries per department.

Join the conversation! Your thoughts help the community grow.