SQL Interview Questions - MSSQL

SELECT FName, COUNT(Id) AS CNT  
FROM Employee  
GROUP BY FName 
HAVING COUNT(Id)>1 

SELECT Salary, COUNT(Id) AS CNT  
FROM Employee  
GROUP BY Salary 
HAVING COUNT(Id)>1  

SELECT FName, LName, Address, DOB FROM Employee1
INTERSECT
SELECT FName, LName, Address, DOB FROM Employee2

DELETE FROM Employee WHERE Id NOT IN 
(SELECT MIN(Id) FROM Employee child where child.FName = Employee.FName)

DELETE FROM Employee WHERE Id <>
(SELECT MIN(Id) from Employee b where b.FName = Employee.FName)

WITH CTE AS 
(  
    SELECT 1 No  
    UNION ALL  
    SELECT No + 1 FROM CTE WHERE No <100  
)  
SELECT * FROM CTE

SELECT MAX(Salary) from Employee 
WHERE Salary not in (SELECT MAX(Salary) from Employee)

SELECT * FROM Employee a 
WHERE 2 = (SELECT COUNT(distinct Salary) FROM Employee b where a.Salary<=b.Salary)

SELECT * FROM Employee E    
WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)    
FROM Employee E1   
WHERE E1.SALARY>E.SALARY)

SELECT * FROM (
SELECT ROW_NUMBER() OVER( ORDER BY Id) AS No, FName, Lname FROM Employee
) as tmp
WHERE tmp.No between 4 and 7

SET ROWCOUNT 3  
SELECT * FROM Employee ORDER BY Id ASC
SET ROWCOUNT 0

WITH CTE AS (
     SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS No, FName, LName FROM Employee
)  
SELECT * from CTE WHERE No BETWEEN 1 AND 3

SELECT * FROM Employee  
WHERE datediff(year,DOB, getdate()) >40

SELECT DISTINCT FName, LName, DOB FROM Employee
WHERE DOB BETWEEN '01/01/1980' AND '12/31/1990'

SELECT * FROM Employee
WHERE Salary BETWEEN '18000' AND '20000'

SELECT * FROM Employee 
WHERE Id= (SELECT max(Id) FROM Employee)

SELECT * FROM Employee WHERE Id = (SELECT MIN(Id) FROM Employee)
UNION
SELECT * FROM Employee WHERE Id = (SELECT MAX(Id) FROM Employee)

SELECT DeptId, MAX(Salary) as Salary 
FROM Employee GROUP BY DeptId

How to get the total number of employees working in the 'HR' department?

SELECT COUNT(*) FROM Employee E
INNER JOIN Department D ON D.Id = E.DeptId
WHERE D.Name = 'HR'

How to get a department-wise count of employees?

SELECT D.Name, COUNT(*) AS Employees FROM Employee E
INNER JOIN Department D ON D.Id = E.DeptId
GROUP BY D.Name

10. Create and copied table/data from other tables

How to create a new table which consists of data and structure copied from the other table?

SELECT * 
INTO Employee1
FROM Employee

How to create a new table which consists of only structure without copy data from the other table?

SELECT * 
INTO Employee1
FROM Employee
WHERE 1=2

11. Get odd/even id records

How to get employee IDs are even?

SELECT * FROM Employee WHERE Id%2 = 0

How to get employee IDs are odd?

SELECT * FROM Employee WHERE Id%2 <> 0

12. Get max and min salaries

How to find two minimum salaries from the employees?

SELECT DISTINCT Salary FROM Employee E1 
WHERE 2 >= (SELECT COUNT(DISTINCT Salary)FROM Employee E2
WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC

How to find two maximum salaries from the employees?

SELECT DISTINCT Salary FROM Employee E1 
WHERE 2 >= (SELECT COUNT(DISTINCT Salary)FROM Employee E2
WHERE E1.Salary <= E2.Salary) ORDER BY E1.Salary DESC

How to get minimum and maximum from the employees?

SELECT MIN(Salary) FROM Employee
UNION
SELECT MAX(Salary) FROM Employee

13. Get records with the same salary

How to find employees with the same salary?

SELECT DISTINCT e.Id,e.FName, e.LName, e.Salary  
FROM Employee e, Employee e1  
WHERE e.salary = e1.salary 
and e.Id != e1.Id 

How to get employees working in the same department?

SELECT DISTINCT e.Id,e.FName, e.LName, e.DeptId  
FROM Employee e, Employee e1  
WHERE e.DeptId = e1.DeptId 
and e.Id != e1.Id

14. Get 50% records

How to get 50% records from the employee?

SELECT * 
FROM Employee WHERE
Id <= (SELECT COUNT(Id)/2 from Employee)

15. Get manager name for employees

How to get manager names for employees?

;with empCTE as    
(    
SELECT e.Id, e.FName, e.LName, e.ManagerId,  
      CAST('' as varchar(50)) as MName from Employee e    
where ManagerId = 0    
UNION ALL    
SELECT e1.Id, e1.FName, e1.LName, e1.ManagerId,     
       CAST(c.FName as varchar(50)) as MName FROM Employee e1    
inner join empCTE as C on e1.ManagerId=c.Id    
where e1.ManagerId>0
) SELECT * FROM empCTE ORDER BY Id

How to find employees who hold managerial positions?

SELECT DISTINCT e.Id, e.FName, e.LName
FROM Employee e    
INNER JOIN Employee m ON m.ManagerId = e.Id

16. Get employee name starting/ending with

How to get employee names starting with a vowel?

SELECT * FROM Employee WHERE FName LIKE '[aeiou]%'

How to get employee names ending with a vowel?

SELECT * FROM Employee WHERE FName LIKE '%[aeiou]'

How to get employee names starting and ending with a vowel?

SELECT * FROM Employee WHERE FName LIKE '[aeiou]%[aeiou]' 

How to get the first four characters of FName from employees?

SELECT SUBSTRING(FName, 1, 4) FROM Employee

How to find the names begin with 'S' from employees?

SELECT * FROM Employee WHERE FName LIKE 'S%'

How to find employees whose name starts with an alphabet ‘A’ and contains six alphabets?

SELECT * FROM Employee WHERE FName LIKE 'A_____'

17. Use of like

How to get all employees excluding the employees with first names, 'Anil' and 'Sachin' from the employee?

SELECT * FROM Employee WHERE FName NOT IN ('Anil','Sachin')

How to find employees with the address as 'DELHI(DEL)'?

SELECT * FROM Employee WHERE Address LIKE 'DELHI(DEL)%'

18. Group concept

How to find salaries paid for each department?

SELECT DeptId, SUM(Salary) FROM Employee GROUP BY DeptId

19. Date related concept

How to get the current date?

SELECT GETDATE()

How to retrieve date in 'dd/mm/yyyy' format?

SELECT CONVERT(varchar(10),GETDATE(),103) 

How to get the last day of the previous month?

SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

20. Some random related and other concepts

How to get prime numbers from 1 to 25?

DECLARE  @i INT,  @j INT,  @count INT,  @data varchar(1000)  

SET @i = 1  
SET @data=''  

WHILE (@i <= 25)  
BEGIN  
	SET @count = 0  
	SET @j = 1 

	WHILE (@j <= @i)  
	BEGIN  
		IF (@i % @j = 0)  
			SET @count = @count + 1  

		SET @j = @j + 1  
	END  

	IF (@count = 2)  
		set @data = @data+cast(@i as varchar(10))+' , '  

	SET @i = @i + 1  
END  
  
set @data = (select substring(@data, 1, (len(@data) - 1))) 
 
select @data

How to get random employee?

SELECT TOP 1 * FROM Employee Order By NewId()

Summary

Here, I have kept practical and real-life implementation examples. Hence, I believe you will be able to properly answer/understand the most popular MSSQL interview questions and these SQL interview questions will be useful to both beginners and professional MSSQL developers.