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.
Table of Contents
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-
- Using max
- Using limit
- 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: