50+ Interview Questions
In Microsoft SQL Server
Sai Reddy 4/20/25 MS SQL
50+ Interview questions
1. Print all even numbers between 1 and N
DECLARE @N INT = 100;
WITH Numbers AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM Numbers WHERE num + 1 <= @N
)
SELECT num FROM Numbers WHERE num % 2 = 0 OPTION (MAXRECURSION 0);
2. Print all odd numbers between 1 and N
SELECT num FROM Numbers WHERE num % 2 <> 0;
3. Print all prime numbers from 1 to N
WITH Numbers AS (
SELECT 2 AS num
UNION ALL
SELECT num + 1 FROM Numbers WHERE num + 1 <= @N
)
SELECT num FROM Numbers n
WHERE NOT EXISTS (
SELECT 1 FROM Numbers d WHERE d.num < n.num AND n.num > 1 AND n.num % d.num = 0
) OPTION (MAXRECURSION 0);
4. Factorial of a number
DECLARE @Fact INT = 5, @Result INT = 1;
WHILE @Fact > 1
BEGIN
SET @Result = @Result * @Fact;
SET @Fact = @Fact - 1;
END
SELECT @Result AS Factorial;
5. Fibonacci series
DECLARE @a INT = 0, @b INT = 1, @n INT = 10, @i INT = 0;
WHILE @i < @n
BEGIN
PRINT @a;
DECLARE @temp INT = @a + @b;
SET @a = @b;
Sai Reddy
saireddy-dotnetfs
SET @b = @temp;
SET @i = @i + 1;
END
6. Reverse a number
DECLARE @Num INT = 1234, @Rev INT = 0;
WHILE @Num > 0
BEGIN
SET @Rev = @Rev * 10 + @Num % 10;
SET @Num = @Num / 10;
END
SELECT @Rev AS ReversedNumber;
7. Check if a number is palindrome
DECLARE @num INT = 121, @original INT = @num, @rev INT = 0;
WHILE @num > 0
BEGIN
SET @rev = @rev * 10 + @num % 10;
SET @num = @num / 10;
END
SELECT CASE WHEN @rev = @original THEN 'Palindrome' ELSE 'Not Palindrome' END AS Result;
8. Check if a number is Armstrong
DECLARE @num INT = 153, @sum INT = 0, @temp INT = @num;
WHILE @temp > 0
BEGIN
DECLARE @digit INT = @temp % 10;
SET @sum = @sum + POWER(@digit, 3);
SET @temp = @temp / 10;
END
SELECT CASE WHEN @sum = @num THEN 'Armstrong' ELSE 'Not Armstrong' END AS Result;
9. Highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employees;
10. Second highest salary
SELECT MAX(Salary) AS SecondHighest FROM Employees WHERE Salary < (SELECT MAX(Salary)
FROM Employees);
11. Nth highest salary using CTE
WITH CTE AS (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees
)
Sai Reddy
saireddy-dotnetfs
SELECT Salary FROM CTE WHERE Rank = 3; change 3 to N
12. Nth highest salary using TOP
SELECT DISTINCT TOP 1 Salary FROM (
SELECT DISTINCT TOP 3 Salary FROM Employees ORDER BY Salary DESC
) AS Temp ORDER BY Salary ASC;
13. Employees earning more than average salary
SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
14. Count employees in each department
SELECT DepartmentId, COUNT(*) AS EmpCount FROM Employees GROUP BY DepartmentId;
15. Employees with same salary
SELECT Salary, COUNT(*) FROM Employees GROUP BY Salary HAVING COUNT(*) > 1;
16. Employees without managers
SELECT * FROM Employees WHERE ManagerId IS NULL;
17. Departments with more than 3 employees
SELECT DepartmentId FROM Employees GROUP BY DepartmentId HAVING COUNT(*) > 3;
18. Highest paid employee in each department
SELECT * FROM Employees e
JOIN (
SELECT DepartmentId, MAX(Salary) AS MaxSalary FROM Employees GROUP BY DepartmentId
) d ON e.DepartmentId = d.DepartmentId AND e.Salary = d.MaxSalary;
19. Minimum salary in each department
SELECT DepartmentId, MIN(Salary) AS MinSalary FROM Employees GROUP BY DepartmentId;
20. Employees who joined in last 7 days
SELECT * FROM Employees WHERE JoinDate >= DATEADD(DAY, -7, GETDATE());
21. Names starting with 'A'
SELECT * FROM Employees WHERE Name LIKE 'A%';
22. Names in uppercase
SELECT UPPER(Name) FROM Employees;
23. Names with exactly 5 characters
SELECT * FROM Employees WHERE LEN(Name) = 5;
Sai Reddy
saireddy-dotnetfs
24. Names containing 'a'
SELECT * FROM Employees WHERE Name LIKE '%a%';
25. Duplicate rows
SELECT Name, COUNT(*) FROM Employees GROUP BY Name HAVING COUNT(*) > 1;
26. Delete duplicate rows (keep one)
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS rn FROM Employees
)
DELETE FROM CTE WHERE rn > 1;
27. Count NULLs in a column
SELECT COUNT(*) - COUNT(Email) AS NullCount FROM Employees;
28. Replace NULL with default
SELECT ISNULL(Email, '
[email protected]') FROM Employees;
29. Rows with NULL in any column
SELECT * FROM Employees WHERE Name IS NULL OR Salary IS NULL OR DepartmentId IS NULL;
30. Max, Min, Avg salary
SELECT MAX(Salary), MIN(Salary), AVG(Salary) FROM Employees;
31. Total employees
SELECT COUNT(*) FROM Employees;
32. Current date and time
SELECT GETDATE();
33. Employees joined this year
SELECT * FROM Employees WHERE YEAR(JoinDate) = YEAR(GETDATE());
34. Birthdays today
SELECT * FROM Employees WHERE MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate)
= DAY(GETDATE());
35. Age > 30
SELECT * FROM Employees WHERE DATEDIFF(YEAR, BirthDate, GETDATE()) > 30;
36. Experience > 2 years
SELECT * FROM Employees WHERE DATEDIFF(YEAR, JoinDate, GETDATE()) > 2;
Sai Reddy
saireddy-dotnetfs
37. Calculate age
SELECT Name, DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age FROM Employees;
38. First and last day of current month
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS FirstDay,
EOMONTH(GETDATE()) AS LastDay;
39. Show month and year from date
SELECT Name, FORMAT(JoinDate, 'MMMM yyyy') AS JoinMonthYear FROM Employees;
40. Remove duplicates using ROW_NUMBER
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Id) AS rn FROM Employees
)
DELETE FROM CTE WHERE rn > 1;
41. Top 2 salaries per department
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS rn
FROM Employees
)
SELECT * FROM CTE WHERE rn <= 2;
42. Rank salaries using RANK()
SELECT *, RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank FROM
Employees;
43. Rank salaries using DENSE_RANK()
SELECT *, DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank
FROM Employees;
44. Running total of salary
SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Id) AS RunningTotal FROM Employees;
45. Salary difference using LAG()
SELECT Name, Salary, LAG(Salary) OVER(ORDER BY Id) AS PrevSalary FROM Employees;
46. Previous and next salary
SELECT Name, Salary,
LAG(Salary) OVER(ORDER BY Id) AS PrevSalary,
LEAD(Salary) OVER(ORDER BY Id) AS NextSalary
FROM Employees;
Sai Reddy
saireddy-dotnetfs
47. Print 1 to 100 using CTE
WITH Numbers AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM Numbers WHERE num < 100
)
SELECT * FROM Numbers OPTION (MAXRECURSION 0);
48. Factorial using recursive CTE
WITH FactCTE AS (
SELECT 1 AS n, 1 AS Fact
UNION ALL
SELECT n + 1, Fact * (n + 1) FROM FactCTE WHERE n < 5
)
SELECT * FROM FactCTE;
49. Hierarchical data (employee-manager)
SELECT e.Name AS Employee, m.Name AS Manager FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;
50. Employee + department names using JOIN
SELECT e.Name, d.Name AS DeptName FROM Employees e
JOIN Departments d ON e.DepartmentId = d.Id;
51. Employees without departments
SELECT * FROM Employees WHERE DepartmentId IS NULL;
52. Employees with same manager
SELECT ManagerId, COUNT(*) FROM Employees GROUP BY ManagerId HAVING COUNT(*) > 1;
53. Employees earning more than their manager
SELECT e.Name FROM Employees e
JOIN Employees m ON e.ManagerId = m.Id
WHERE e.Salary > m.Salary;
54. Max salary using correlated subquery
SELECT * FROM Employees e WHERE Salary = (SELECT MAX(Salary) FROM Employees);
55. Employee-manager hierarchy (self-join)
SELECT e.Name AS Employee, m.Name AS Manager FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;
Sai Reddy
saireddy-dotnetfs
56. PIVOT: subject marks to columns
SELECT * FROM (
SELECT StudentId, Subject, Marks FROM Marks
) src
PIVOT (
MAX(Marks) FOR Subject IN ([Maths], [Science], [English])
) AS pvt;
57. UNPIVOT: columns to rows
SELECT StudentId, Subject, Marks
FROM Marks
UNPIVOT (
Marks FOR Subject IN ([Maths], [Science], [English])
) AS unpvt;
58. Dynamic SQL filter
DECLARE @SQL NVARCHAR(MAX), @Col NVARCHAR(100) = 'Name';
SET @SQL = 'SELECT * FROM Employees WHERE ' + QUOTENAME(@Col) + ' LIKE ''%a%''';
EXEC sp_executesql @SQL;
59. CASE for salary band
SELECT Name, Salary,
CASE
WHEN Salary >= 100000 THEN 'High'
WHEN Salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS SalaryBand
FROM Employees;
60. Split comma-separated string
CREATE FUNCTION dbo.SplitString (@String NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Output TABLE (Item NVARCHAR(100))
AS
BEGIN
DECLARE @start INT = 1, @end INT;
WHILE CHARINDEX(@Delimiter, @String, @start) > 0
BEGIN
SET @end = CHARINDEX(@Delimiter, @String, @start);
INSERT INTO @Output(Item) VALUES(SUBSTRING(@String, @start, @end - @start));
SET @start = @end + 1;
END
INSERT INTO @Output(Item) VALUES(SUBSTRING(@String, @start, LEN(@String)));
RETURN;
END
Usage: SELECT * FROM dbo.SplitString('A,B,C,D', ',');
Sai Reddy
saireddy-dotnetfs
