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

Leave a Comment