Top 20 SQL Server Scenario-Based Interview Questions with Answers in 2025

 Q1. How would you retrieve the second-highest salary from an Employees table?

Answer

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

 Q2. How can you delete duplicate records from a table while keeping only one instance?

Answer

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

 Q3. How to swap two column values in SQL Server without using a temporary variable?

Answer

UPDATE Employees
SET ColumnA = ColumnB,
    ColumnB = ColumnA;

 Q4. How would you find the nth highest salary from an Employees table?

Answer

SELECT DISTINCT Salary
FROM Employees E1
WHERE N-1 = (SELECT COUNT(DISTINCT Salary) FROM Employees E2 WHERE E2.Salary > E1.Salary);

 Q5. How can you identify and fix orphaned records?

Answer

SELECT E.EmployeeID, E.Name
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID
WHERE D.DepartmentID IS NULL;

 Q6. How can you split a comma-separated string into individual rows?

Answer

SELECT value
FROM STRING_SPLIT('A,B,C,D', ',');

 Q7. How to get the count of employees in each department?

Answer

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

 Q8. How would you find the highest salary in each department?

Answer

SELECT DepartmentID, MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY DepartmentID;

 Q9. How to update data in one table based on conditions in another table?

Answer

UPDATE E
SET E.Salary = E.Salary * 1.1
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID
WHERE D.DepartmentName = 'IT';

 Q10. How to retrieve top 3 records for each category in a table?

Answer

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Sales DESC) AS rn
  FROM SalesData
)
SELECT * FROM CTE WHERE rn <= 3;

 Q11. How to calculate running total in SQL Server?

Answer

SELECT EmployeeID, Salary,
       SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

 Q12. How to pivot rows into columns in SQL Server?

Answer

SELECT *
FROM (
  SELECT EmployeeID, DepartmentName, Salary
  FROM Employees
) AS SourceTable
PIVOT (
  MAX(Salary) FOR DepartmentName IN ([HR], [IT], [Finance])
) AS PivotTable;

 Q13. How to find missing numbers in a sequence?

Answer

WITH Numbers AS (
  SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) AS SeqNo
  FROM Employees
)
SELECT SeqNo
FROM Numbers
WHERE SeqNo NOT IN (SELECT EmployeeID FROM Employees);

 Q14. How to retrieve data from the last 7 days?

Answer

SELECT * FROM Orders
WHERE OrderDate >= DATEADD(DAY, -7, GETDATE());

 Q15. How to calculate the difference between two dates in days?

Answer

SELECT DATEDIFF(DAY, StartDate, EndDate) AS DaysDifference
FROM Projects;

 Q16. How to find employees who earn more than their managers?

Answer

SELECT E.EmployeeID, E.Name
FROM Employees E
JOIN Employees M ON E.ManagerID = M.EmployeeID
WHERE E.Salary > M.Salary;

 Q17. How to identify and remove records with NULL values in specific columns?

Answer

DELETE FROM Employees
WHERE Email IS NULL OR Phone IS NULL;

 Q18. How to find the most frequently occurring value in a column?

Answer

SELECT TOP 1 ProductID, COUNT(*) AS Frequency
FROM Sales
GROUP BY ProductID
ORDER BY COUNT(*) DESC;

 Q19. How to calculate the percentage of sales for each product?

Answer

SELECT ProductID,
       SalesAmount,
       (SalesAmount * 100.0) / SUM(SalesAmount) OVER () AS Percentage
FROM Sales;

 Q20. How to find employees with birthdays in the current month?

Answer

SELECT * FROM Employees
WHERE MONTH(BirthDate) = MONTH(GETDATE());