📘 T-SQL Exercises

From basic filtering to advanced window functions, CTEs, PIVOT, APPLY, MERGE and more – with sample data, solutions, expected results and explanations.

🧭 Jump to section:

• SELECT & WHERE
• ORDER BY, TOP, OFFSET-FETCH
• Aggregates & GROUP BY
• JOINs
• Subqueries & EXISTS
• UNION, INTERSECT, EXCEPT
• CASE, COALESCE, NULLIF
• CAST, CONVERT, TRY_*
• CTE (non‑recursive & recursive)
• Window Functions
• PIVOT & UNPIVOT
• APPLY
• MERGE & OUTPUT
• String Functions
• Date Functions
• Advanced Scenarios

Main sample tables used across exercises:

Table	Columns
Employees	EmployeeID, Name, DepartmentID, Salary, ManagerID, HireDate
Departments	DepartmentID, DepartmentName
Orders	OrderID, CustomerID, OrderDate, Amount
Customers	CustomerID, CustomerName
Products	ProductID, ProductName, Price
OrderDetails	OrderID, ProductID, Quantity

1. SELECT & WHERE – Basic Filtering

Q1Basic SELECT with WHERE and BETWEEN

Sample data (Employees):

EmployeeID	Name	Salary	DepartmentID
1	Alice	70000	10
2	Bob	45000	20
3	Carol	52000	10
4	Dave	81000	30
5	Eve	63000	20

Find employees who earn between 50000 and 75000 and belong to department 10.

✅ Solution:

SELECT Name, Salary FROM Employees WHERE Salary BETWEEN 50000 AND 75000 AND DepartmentID = 10;

📋 Result:

Name	Salary
Alice	70000
Carol	52000

Explanation: BETWEEN is inclusive. Combined with AND to filter department.

Q2LIKE pattern matching

List employees whose name starts with 'A' or contains 'ar'.

✅ Solution:

SELECT Name FROM Employees WHERE Name LIKE 'A%' OR Name LIKE '%ar%';

📋 Result:

Name
Alice
Carol

Explanation: % matches any sequence of characters.

Q3IN and NOT IN

Retrieve employees in departments 10 or 20, but not with salary 45000.

SELECT Name, DepartmentID FROM Employees WHERE DepartmentID IN (10,20) AND Salary NOT IN (45000);

📋 Result:

Name	DepartmentID
Alice	10
Carol	10
Eve	20

Q4Handling NULLs

Find employees with no manager (ManagerID IS NULL).

SELECT Name FROM Employees WHERE ManagerID IS NULL;

(Assume Alice has ManagerID NULL)

2. ORDER BY, TOP, OFFSET-FETCH

Q5TOP with ORDER BY

Get the 3 highest paid employees.

SELECT TOP 3 Name, Salary FROM Employees ORDER BY Salary DESC;

Q6OFFSET-FETCH pagination

Return page 2 (rows 4–6) ordered by Salary DESC.

SELECT Name, Salary FROM Employees ORDER BY Salary DESC OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

3. Aggregates & GROUP BY

Q7COUNT, AVG per department

Show department ID, employee count, average salary.

SELECT DepartmentID, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSal FROM Employees GROUP BY DepartmentID;

Q8HAVING filter on aggregates

Departments with average salary above 60000.

SELECT DepartmentID, AVG(Salary) AS AvgSal FROM Employees GROUP BY DepartmentID HAVING AVG(Salary) > 60000;

4. JOINs (INNER, LEFT, RIGHT, FULL, CROSS, SELF)

Sample Departments: (10,'IT'), (20,'HR'), (30,'Finance'), (40,'Marketing')

Q9INNER JOIN

List employees with their department name.

SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Q10LEFT JOIN – departments without employees

SELECT d.DepartmentName, e.Name FROM Departments d LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID WHERE e.EmployeeID IS NULL;

Q11SELF JOIN – employees and their managers

SELECT e.Name AS Employee, m.Name AS Manager FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

Q12CROSS JOIN – all combinations

SELECT e.Name, d.DepartmentName FROM Employees e CROSS JOIN Departments d;

5. Subqueries & EXISTS

Q13Scalar subquery

Employees earning above average salary.

SELECT Name, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Q14EXISTS – departments with at least one employee

SELECT DepartmentName FROM Departments d WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID);

Q15IN with subquery

Employees in departments where no one earns < 50000.

SELECT Name FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING MIN(Salary) >= 50000 );

6. UNION, INTERSECT, EXCEPT

Q16UNION vs UNION ALL

Combine distinct names from two employee lists.

SELECT Name FROM Employees WHERE DepartmentID=10 UNION SELECT Name FROM Employees WHERE Salary>60000;

Q17INTERSECT – common names

SELECT Name FROM Employees WHERE DepartmentID=10 INTERSECT SELECT Name FROM Employees WHERE Salary>60000;

Q18EXCEPT – in first but not second

SELECT Name FROM Employees EXCEPT SELECT Name FROM Employees WHERE Salary>70000;

7. CASE, COALESCE, NULLIF, ISNULL, IIF

Q19CASE for salary categories

SELECT Name, CASE WHEN Salary > 70000 THEN 'High' WHEN Salary BETWEEN 50000 AND 70000 THEN 'Medium' ELSE 'Low' END AS SalaryLevel FROM Employees;

Q20COALESCE to replace NULL

SELECT Name, COALESCE(ManagerID, 0) AS Mgr FROM Employees;

Q21NULLIF example

Return NULL if salary equals 0, otherwise salary.

SELECT Name, NULLIF(Salary,0) AS ValidSalary FROM Employees;

8. Data Type Conversion

Q22CAST and CONVERT

SELECT CAST(Salary AS VARCHAR(10)) + ' USD' FROM Employees; SELECT CONVERT(VARCHAR, HireDate, 103) AS UKDate FROM Employees;

Q23TRY_CAST safe conversion

SELECT TRY_CAST('abc' AS INT) AS Result; -- returns NULL instead of error

9. Common Table Expressions (CTE)

Q24Non‑recursive CTE for readability

Find departments with more than 2 employees using CTE.

WITH DeptCount AS ( SELECT DepartmentID, COUNT(*) AS cnt FROM Employees GROUP BY DepartmentID ) SELECT d.DepartmentName, dc.cnt FROM DeptCount dc JOIN Departments d ON dc.DepartmentID = d.DepartmentID WHERE dc.cnt > 2;

Q25Recursive CTE – number series

Generate numbers 1 to 5.

WITH NumSeries AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM NumSeries WHERE n < 5 ) SELECT n FROM NumSeries;

Q26Recursive CTE – Employee hierarchy

Show full reporting chain from top manager (ManagerID NULL).

WITH EmpHierarchy AS ( SELECT EmployeeID, Name, ManagerID, 0 AS Level FROM Employees WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level+1 FROM Employees e INNER JOIN EmpHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmpHierarchy ORDER BY Level;

10. Window Functions (ROW_NUMBER, RANK, LEAD, LAG, etc.)

Q27ROW_NUMBER – top 2 per department

WITH Ranked AS ( SELECT Name, DepartmentID, Salary, ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rn FROM Employees ) SELECT * FROM Ranked WHERE rn <= 2;

Q28RANK vs DENSE_RANK

Rank employees by salary overall.

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;

Q29LAG – previous row salary

SELECT Name, Salary, LAG(Salary,1,0) OVER (ORDER BY Salary) AS PrevSalary FROM Employees;

Q30LEAD – next row

SELECT Name, HireDate, LEAD(HireDate) OVER (ORDER BY HireDate) AS NextHire FROM Employees;

Q31Running total (SUM OVER)

SELECT OrderID, Amount, SUM(Amount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal FROM Orders;

Q32FIRST_VALUE / LAST_VALUE

SELECT Name, DepartmentID, Salary, FIRST_VALUE(Name) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS TopPaid FROM Employees;

11. PIVOT & UNPIVOT

Q33PIVOT – department salary sums

Turn department rows into columns.

SELECT * FROM ( SELECT DepartmentID, Salary FROM Employees ) src PIVOT ( SUM(Salary) FOR DepartmentID IN ([10],[20],[30]) ) pvt;

Q34UNPIVOT – convert columns to rows

SELECT EmployeeID, Quarter, Amount FROM Sales UNPIVOT ( Amount FOR Quarter IN (Q1, Q2, Q3, Q4) ) unpvt;

12. CROSS APPLY & OUTER APPLY

Q35CROSS APPLY – top 2 orders per customer

SELECT c.CustomerName, o.OrderID, o.Amount FROM Customers c CROSS APPLY ( SELECT TOP 2 OrderID, Amount FROM Orders o WHERE o.CustomerID = c.CustomerID ORDER BY o.OrderDate DESC ) o;

Q36OUTER APPLY – customers with or without orders

SELECT c.CustomerName, o.OrderID FROM Customers c OUTER APPLY ( SELECT TOP 1 OrderID FROM Orders WHERE CustomerID = c.CustomerID ) o;

13. MERGE & OUTPUT Clause

Q37MERGE – upsert employees from staging

MERGE Employees AS target USING StagingEmployees AS source ON target.EmployeeID = source.EmployeeID WHEN MATCHED THEN UPDATE SET Name = source.Name, Salary = source.Salary WHEN NOT MATCHED THEN INSERT (EmployeeID, Name, Salary) VALUES (source.EmployeeID, source.Name, source.Salary) OUTPUT $action, inserted.*, deleted.*;

Q38OUTPUT with INSERT / DELETE

INSERT INTO Employees (Name, Salary) OUTPUT inserted.EmployeeID, inserted.Name VALUES ('Frank', 58000);

14. String Functions (STRING_AGG, STRING_SPLIT, etc.)

Q39STRING_AGG – comma separated names per dept

SELECT DepartmentID, STRING_AGG(Name, ', ') AS EmployeesList FROM Employees GROUP BY DepartmentID;

Q40STRING_SPLIT – parse CSV

SELECT value FROM STRING_SPLIT('apple,banana,grape', ',');

15. Date & Time Functions

Q41DATEDIFF, DATEADD

Employees hired more than 5 years ago.

SELECT Name, HireDate FROM Employees WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;

Q42EOMONTH, DATENAME

SELECT OrderID, EOMONTH(OrderDate) AS MonthEnd, DATENAME(WEEKDAY, OrderDate) AS DayName FROM Orders;

16. Advanced Interview Scenarios

Q43Nth highest salary (using window function)

2nd highest salary.

WITH Ranked AS ( SELECT DISTINCT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) rnk FROM Employees ) SELECT Salary FROM Ranked WHERE rnk = 2;

Q44Delete duplicate rows

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, DepartmentID ORDER BY EmployeeID) AS rn FROM Employees ) DELETE FROM CTE WHERE rn > 1;

Q45Gaps and Islands – consecutive dates

Find consecutive logins (simplified).

WITH Grp AS ( SELECT LoginDate, DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY LoginDate), LoginDate) AS grp FROM UserLogins ) SELECT MIN(LoginDate) AS Start, MAX(LoginDate) AS End, COUNT(*) AS Days FROM Grp GROUP BY grp;

Q46Running total with condition

SELECT OrderID, Amount, SUM(Amount) OVER (ORDER BY OrderID) AS RunningSum FROM Orders WHERE CustomerID = 101;

Q47Median calculation (PERCENTILE_CONT)

SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER () AS Median FROM Employees;

Q48Dynamic SQL – pivot unknown values

(Simplified illustration)

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX); SELECT @cols = STRING_AGG(QUOTENAME(DepartmentID),',') FROM (SELECT DISTINCT DepartmentID FROM Employees) t; SET @sql = 'SELECT * FROM (SELECT DepartmentID, Salary FROM Employees) src PIVOT (SUM(Salary) FOR DepartmentID IN ('+@cols+')) p;'; EXEC sp_executesql @sql;

Q49Recursive CTE – calendar table

WITH Dates AS ( SELECT CAST('2025-01-01' AS DATE) AS dt UNION ALL SELECT DATEADD(DAY,1,dt) FROM Dates WHERE dt < '2025-01-10' ) SELECT dt FROM Dates;

Q50CROSS APPLY with string split per row

SELECT e.Name, s.value AS Skill FROM Employees e CROSS APPLY STRING_SPLIT(e.SkillsCSV, ',') s;

✨ More exercises can be added following the same pattern. This document already demonstrates all major T-SQL keywords in interview‑style questions.