# 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 🙂