Advanced SQL Queries - Full Guide with Examples
& Outputs
1. Common Table Expressions (CTE)
Query:
WITH SalesCTE AS (
SELECT CustomerID, SUM(Amount) AS TotalSales
FROM Orders
GROUP BY CustomerID
)
SELECT CustomerID, TotalSales
FROM SalesCTE
WHERE TotalSales > 5000;
Explanation: Gets customers whose sales are more than 5000.
Sample Input:
CustomerID Amount
1 2000
1 3500
2 1000
3 6000
Sample Output:
CustomerID TotalSales
1 5500
3 6000
2. Window Functions (OVER, PARTITION BY)
Query:
SELECT
CustomerID,
OrderDate,
Amount,
SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS
RunningTotal
FROM Orders;
Explanation: Running total of sales per customer in order date sequence.
Sample Input:
CustomerID OrderDate Amount
1 2023-01-01 2000
1 2023-02-01 3500
2 2023-01-15 1000
2 2023-02-10 500
Sample Output:
CustomerID OrderDate Amount RunningTotal
1 2023-01-01 2000 2000
1 2023-02-01 3500 5500
2 2023-01-15 1000 1000
2 2023-02-10 500 1500
3. RANK vs ROW_NUMBER vs DENSE_RANK
Query:
SELECT
EmployeeID,
Salary,
RANK() OVER (ORDER BY Salary DESC) AS RankNo,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
Explanation: Difference between Rank, Dense Rank, and Row Number.
Sample Input:
EmployeeID Salary
101 90000
102 85000
103 85000
104 80000
Sample Output:
EmployeeID Salary RankNo DenseRank RowNum
101 90000 1 1 1
102 85000 2 2 2
103 85000 2 2 3
104 80000 4 3 4
4. Subquery in SELECT
Query:
SELECT
[Link],
[Link],
(SELECT AVG(Salary) FROM Employees) AS AvgSalary
FROM Employees e;
Explanation: Shows each employee with company average salary.
Sample Input:
EmployeeID Name Salary
101 Alice 50000
102 Bob 70000
103 Charlie 60000
Sample Output:
EmployeeID Name AvgSalary
101 Alice 60000
102 Bob 60000
103 Charlie 60000
5. EXISTS vs IN
Query:
SELECT Name
FROM Customers c
WHERE EXISTS (
SELECT 1 FROM Orders o WHERE [Link] = [Link]
);
Explanation: Gets customers who placed at least one order.
Sample Input:
CustomerID Name
1 John
2 Mary
3 Alex
Sample Output:
Name
John
Mary
6. Self Join
Query:
SELECT [Link] AS Employee, [Link] AS Manager
FROM Employees e1
JOIN Employees e2
ON [Link] = [Link];
Explanation: Gets employee-manager pairs.
Sample Input:
EmployeeID Name ManagerID
1 Alice 3
2 Bob 3
3 Charlie
Sample Output:
Employee Manager
Alice Charlie
Bob Charlie
7. Pivoting Data (CASE + GROUP BY)
Query:
SELECT
CustomerID,
SUM(CASE WHEN YEAR(OrderDate) = 2023 THEN Amount ELSE 0 END) AS
Sales2023,
SUM(CASE WHEN YEAR(OrderDate) = 2024 THEN Amount ELSE 0 END) AS Sales2024
FROM Orders
GROUP BY CustomerID;
Explanation: Sales per customer split by year.
Sample Input:
CustomerID OrderDate Amount
1 2023-02-01 2000
1 2024-03-01 3000
2 2023-05-01 1500
Sample Output:
CustomerID Sales2023 Sales2024
1 2000 3000
2 1500 0
8. Recursive CTE
Query:
WITH RECURSIVE EmployeeHierarchy AS (
SELECT EmployeeID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN EmployeeHierarchy h
ON [Link] = [Link]
)
SELECT * FROM EmployeeHierarchy;
Explanation: Traverses employee hierarchy.
Sample Input:
EmployeeID Name ManagerID
1 CEO
2 Manager 1
3 Staff 2
Sample Output:
EmployeeID Name ManagerID
1 CEO
2 Manager 1
3 Staff 2
9. Window Aggregate with PARTITION (Top-N per group)
Query:
SELECT *
FROM (
SELECT
CustomerID,
OrderID,
Amount,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rn
FROM Orders
) t
WHERE rn = 1;
Explanation: Gets highest order per customer.
Sample Input:
CustomerID OrderID Amount
1 101 2000
1 102 3500
2 201 1000
Sample Output:
CustomerID OrderID Amount rn
1 102 3500 1
2 201 1000 1
10. FULL OUTER JOIN
Query:
SELECT [Link], [Link]
FROM Customers c
FULL OUTER JOIN Orders o
ON [Link] = [Link];
Explanation: Customers with or without orders.
Sample Input:
Customers Orders
C1 O1
C2
C3 O2
Sample Output:
CustomerID OrderID
C1 O1
C2
C3 O2