Post
Ask Question

SQL Interview Questions - MSSQL

Article Overview

  • Background
  • Prerequisites
  • Questions
  • Answers 
  • Summary 

Background

Here is a list of the most popular MSSQL interview questions and answers explained. These SQL interview questions are for both beginners and professional MSSQL developers. 

Prerequisites

Before beginning, let us create tables and insert relevant data into them with the attached script file. 

Questions

Questions are categorized into 20 groups based on their similarity as following:

  1. Get duplicate records
  2. Delete duplicate records
  3. Get 1 to 100 Numbers
  4. Get second highest salary
  5. Get N'th highest salary
  6. Alternative for TOP clause
  7. Get Between and Greater than records
  8. Get N last records
  9. Department wise records
  10. Create and copied table/data from another table
  11. Get odd/even id records
  12. Get max and min salaries
  13. Get records with the same salary
  14. Get 50% records
  15. Get manager name for employees
  16. Get employee name starting/ending with
  17. Use of like
  18. Group concept
  19. Date related concept
  20. Some random related and other concepts 

Answers

Now, let us go through each category in detail along with the query.

1. Get duplicate records

How to find duplicate FName along with their count from Employee?

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

How to find duplicate Salary along with their count from Employees?

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

How to get common records from two different tables which have not any joining conditions?

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

INTERSECT is used to fetch common records from two tables.

2. Delete duplicate records

How to delete duplicate FName and keep only one record?

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

OR

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

3. Get 1 to 100 Numbers

How to fetch 1 to 100 numbers?

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

4. Get second highest salary

How to find the second highest salary?

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

How to get the second highest salaried employee records?

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

5. Get N'th highest salary

How to get 3rd, 4th or 5th i.e. N'th highest Salary?

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

How to display 4 to 7 records?

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

6. Alternative for TOP clause

How to fetch the top 3 records using ROWCOUNT?

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

How to get top 3 records using Common Table Expression (CTE)?

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

 7. Get Between and Greater than records

How to find employees with an age greater than 40 years?

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

How to find distinct employees whose DOB is between 01/01/1980 to 31/12/1990?

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

How to get employees whose salary is greater than 18,000 and less than 20,000?

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

8. Get N last records

How to find the last employee record?

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

How to get the first and last records from employees?

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

9. Department wise records

How to find the maximum salary from each department?

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.


Similar Articles