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:
- Get duplicate records
- Delete duplicate records
- Get 1 to 100 Numbers
- Get second highest salary
- Get N'th highest salary
- Alternative for TOP clause
- Get Between and Greater than records
- Get N last records
- Department wise records
- Create and copied table/data from another table
- Get odd/even id records
- Get max and min salaries
- Get records with the same salary
- Get 50% records
- Get manager name for employees
- Get employee name starting/ending with
- Use of like
- Group concept
- Date related concept
- 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.