Hi Friends,
In this post, I'm going to share SQL interview questions that have been asked in multiple interviews.
Table - EmployeeDetails
EmpId
|
FullName
|
ManagerId
|
DateOfJoining
|
121
|
John Smith
|
322
|
01/10/2010
|
322
|
James
|
988
|
02/09/2009
|
421
|
Bill Gates
|
122
|
03/08/2008
|
Table - EmployeeSalary
EmpId
|
Project
|
Salary
|
121
|
P1
|
10000
|
322
|
P2
|
11000
|
421
|
P3
|
12000
|
Question 1:
Write a SQL query to fetch the count of employees working in project P1.
Answer:
Select count(*) from EmployeeSalary where project = 'p1';
Question 2:
Write a SQL query to fetch employee names having salary greater than or equal to 5000 and less than or equal to 10000.
Answer:
Select FullName
from EmployeeDetails ed
where EmpId in
(Select EmpId from EmployeeSalary
where Salary between 5000 AND 10000);
Question 3:
Write a SQL query to fetch project-wise count of employees sorted by project's count in descending order.
Answer:
Select Project, count(EmpId) as count
from EmployeeSalary
Group By project
Order by count DESC;
Question 4:
Write a query to fetch only the first name from the FullName column of EmployeeDetails table.
Answer:
Select substring(FullName, 0, CharIndex(' ',FullName)) from EmployeeDetails;
Question 5:
Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.
Answer:
Select ED.FullName, ES.salary
from EmployeeDetails ED
left join EmployeeSalary ES
ON ED.EmpId = ES.EmpId;
Question 6:
Write a SQL query to fetch all the employees who are also managers from EmployeeDetails table.
Answer:
Select Distinct ED.FullName
from EmployeeDetails ED
INNER JOIN EmployeeDetails M
ON ED.EmpId = M.ManagerId;
Question 7:
Write a SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.
Answer:
Select * from EmployeeDetails ED
where EXISTS
(Select * from EmployeeSalary S where ED.EmpId = S.EmpId);
Question 8:
Write a SQL query to fetch duplicate records from a table.
Answer:
Select FullName, EmpId, Count(*)
from EmployeeDetails
Group By EmpId, FullName
Having Count(*) > 1;
Question 9:
Write a SQL query to remove duplicates from a table without using temporary table.
Answer:
Delete from EmployeeDetails
where EmpId IN
(Select EmpId from EmployeeDetails
Group By FullName
Having count(*) > 1);
Question 10:
Write a SQL query to create a new table with data and structure copied from another table.
Answer:
Select * INTO newTable from oldTable;
Question 11:
Write a SQL query to create an empty table with same structure as some other table.
Answer:
Select *INTO newTable from oldTable where 1 = 0;
Question 12:
Write a SQL query to fetch common records between two tables.
Answer:
Select * from EmployeeDetails
INTERSECT
Select * from EmployeeDetails
Question 13:
Write a SQL query to fetch records that are present in one table but not in another table.
Answer:
Select * from EmployeeDetails
MINUS
Select * from EmoloyeeDetails
