Top 20 SQL Query Interview Questions and Answers

Hello Friends! In this post, we will discuss most common SQL Query interview questions and answers.

Consider the below tables for reference while practice the SQL queries.

SQL Query interview questions and answers-

Employee Table:

EmpId	Name	ManagerId	DOJ	City
121	John	444	9/12/2010	Pune
421	Ajeet	555	12/08/2016	Delhi
521	Atul	666	7/04/2018	Hyd

 

Salary Table:

EmpId	Project	Salary	Variable
121	P1	40000	1000
234	P2	20000	500
432	P1	15000	250

 

Q1. Write a SQL query to fetch the EmpId and Name of all the employees working under Manager with Id- 555.

Select EmpId, Name from Employee WHERE ManagerId = 555;

 

Q2. Write an SQL query to fetch the different Project available from the salary table.

Select DISTINCT(Project) from Salary;

 

Q3. Write an SQL query to fetch the count of employees working in project ‘P1’

Select COUNT(*) FROM Salary WHERE Project=’P1’;

 

Q4. Write an SQL query to find the maximum, minimum and average salary of the Employees.

SELECT Max(Salary), Min(Salary), AVG(Salary) from Salary;

 

Q5. Write an SQL query to find the employee id whose salary lies in the range of 10000 and 40000.

Select EmpId, Salary FROM Salary WHERE Salary BETWEEN 10000 AND 40000;

 

Q6. Write an SQL query to fetch those employees who live in Pune and work under manager with ManagerId – 444.

SELECT EmpId, City, ManagerId from Employees WHERE City =’Pune’ AND ManagerId = ‘444’;

 

Q7. Write an SQL query to fetch all employees who either live in Pune or work under manager with ManagerId – 444.

SELECT EmpId, City, ManagerId from Employees WHERE City =’Pune’ OR ManagerId = ‘444’;

 

Q8. Write an SQL query to fetch all employees who work on Project other than P1.

Select EmpId From Salary WHERE NOT Project =’P1’;

Or

Select EmpId From WHERE project <> ‘P1’;

 

Q9. Write an SQL query to display the total salary of each employee adding the salary with variable value.

Select EmpId, Salary+Variable as TotalSalary From Salary;

 

Q10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text ‘ee’ and ending with any sequences of characters.

Select Name from Employees where Name LIKE ‘__ee%’;

---

Tricky SQL queries for Interview

Employee Table:

Employee_ID	FIRST_NAME	JOB_ID	MANAGER_ID	SALARY	COMM	DEPARTMENT_ID
7369	JOHN	667	7902	800	NULL	20
7499	KEVIN	670	7698	1600	300	30
7866	JEAN	671	7899	2850	NULL	30
7506	DENNIS	672	7839	2750	NULL	30
7507	LYNN	746	7839	220	NULL	40

 

Department Table:

Department_Id	Name	Location_Id
10	ACCOUTING	122
20	RESEARCH	124
30	SALES	123
40	OPERATIONS	167

 

Q11. List out the employees who are not receiving commission.

Select * from employee where COMM in NULL;

 

Q12. List out the employees who are working in department 10 and draw the salaries more than 3500.

Select * from employee where department_id=10 and salary >3500;

 

Q13. List out the employee id, name in descending order based on salary column.

Select employee_id, last_name, salary from employee order by salary desc;

 

Q14. How many employees who are working in different department wise in the organization.

Select department_id, count(*)  from employee group by department_id;

 

Q15. List out department id having at least 3 employees.

Select department_id, count(*), from employee group by department _id having count(*)>=3;

 

Q16. Display the employee who got the maximum salary.

Select * from employee where salary=(select max(salary) from employee);

 

Q17. Display the employees who are working in sales department.

Select * from employee where department_id IN (select department_Id from department where name=’SALES’);

 

Q18. Display the employee who are working in ‘New York’

Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group=’New York’));

Location Table:

Location_Id	Regional_Group
122	New York
123	DALLAS
124	CHICAGO
125	BOSTON

Q19. Update the employees salary who are working as Manager on the basis of 10%

Update employee set salary =salary*10/100 where job_id=(select job_id from job where function=’MANAGER’)

 

Q20. Delete the employee who are working in accounting department.

Delete from employee where department_id=(select department_id from department where name=’ACCOUNTING’)

EID	ENAME	SALARY
101	AMIT	20000
102	DAVID	30000
103	BHASKAR	25000
104	SMITH	28000
105	JOHN	15000

Q21. Display the highest salary from employee table.

We can find this in 3 ways-

1. Using max
2. Using limit
3. Using top

Using max function:-

select max(salary) from employee;

Using limit function:

First we need to find descending order of salary then apply limit on this.

select salary from employee order by salary desc limit 1;

Using top function:

top function only used in MS sql server

select top 1 salary from employee order by salary desc;

 

Q23. Display 2nd highest salary from employee table.

Using max finction:-

select max(salary) from employee < (select max(salary) from employee);

Using limit function:-

select salary from (select salary from employee order by salary desc limit 2) emp order by salary limit 1;

Using top function:-

select salary from (select top 2 salary from employee order by salary desc) emp order by salary limit 1;

 

 

I hope you have enjoyed this post “SQL Query interview questions and answers”

Happy Learning 🙂

 

Must Read:

API Testing Interview Questions

TestNG Interview Questions