Top 30+ SQL Queries Questions
Here is the list of Most asked SQL Queries in Interview. This article gives you extensive details so that you can prepare for your interview wisely.
This article is designed from personal experience and with the help of collaborations.
Below are the two tables, with reference to these tables we will be practicing SQL queries that are asked in Actual Interview.
we are using employee and department table .
Most Important SQL Queries Asked in Interview
- Top 30+ SQL Queries Questions
- Select all the data from employee table?
- Select all data from department table?
- 1.Write an SQL query to find maximum, minimum and average of the employee?
- 2. Write an SQL query to find employee numbers whose salary lies in the range of 5000 to 1500?
- 3. Write an SQL Query to find the employees whose name begins with ‘b’?
- 4. Write an SQL query, make employee name in Upper case, and their job designation in lower case?
- 5. Write an SQL query to find current date-time?
- 6. Write an SQL query to fetch all employees details from employee tables who joined in the year 1981?
- 7. Write an SQL query to fetch all the employees who are manager?
- 8. Write an SQL query to fetch even rows from the table?
- 9. Write a SQL query to fetch only odd rows from the table?
- 10. Find the third highest of employee?
- 11. List the highest paid employee working under king?
- 12. Write an SQL query to find the details of most recently hired emp of dept 30?
- 13. List the employees whose salary is same as ford or blake?
- 14. List the managers who are senior to king but who are junior to smith?
- 15. List the department 10 employees whose salary is greater then allen salary?
- 16. Find the employees whose job is same as allen or sal>allen?
- 17. List the empno, ename, sal, job of the employees with annual sal <34000 but receiving some comm. Which should not be>sal and designation should be salesman working for dept 30.
- 18. Write an SQL query to List the employees who are working as either mgr or analyst with the salary ranging from 2000 to 5000 and without commission.
- 19. Write a query to find the employees whose salary is 4 digit number.
- 20. Write an SQL query to find the empno, ename, annsal, dailysal of all the salesmen in the ascending annual salary?
- 21. Write an SQL query to find highest paid employee of sales department?
- 22. Find the total salary given to the MGR?
- 23. List the employees in dept 20 whose sal is greater than the average salary of department 10 employees?
- 24. Write an SQL query to find the employee are with out manager?
- 25. Write an SQL to Find the Managers name who is having max no.of employees working under him?
- 26. Display empno, ename, deptno and location from emp database.(Use method of joining tables)
- 27. Display empno, ename ,sal and grade from emp database where sal lies between losal and hisal ( NON-Equi Join)
- 28. Find the name of each employees manager.(refer self join).
- 29. Display empno, ename, deptno and dname, loc for the entire department in emp database.(Inner Join)?
- 30. Display empno, ename, deptno and dname, loc for the entire department in emp database.( left outer join)?
- 31. Display empno, ename, deptno and dname, loc for the entire department in emp database.( right outer join)?
- 32.Display empno, ename, deptno and dname, loc for the entire department in emp database.( full outer join)?
- 33. Write Alternative Query for Right join?
- 34. Find the names of all employees who are working under Blake?
- More Queries Questions [Click]
Select all the data from employee table?
Select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Select all data from department table?
select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1.Write an SQL query to find maximum, minimum and average of the employee?
Select Max(salary),
min(salary,
Avg(salary)
From emp;
2. Write an SQL query to find employee numbers whose salary lies in the range of 5000 to 1500?
select empno, sal
from emp
where salary BETWEEN 5000 AND 15000;
3. Write an SQL Query to find the employees whose name begins with ‘b’?
Select ename
from emp
where ename like 'b%';
4. Write an SQL query, make employee name in Upper case, and their job designation in lower case?
select UPPER(ename), LOWER(job)
from emp;
5. Write an SQL query to find current date-time?
select getdate();
6. Write an SQL query to fetch all employees details from employee tables who joined in the year 1981?
Using between for the date range ’01-01-1981′ and ’31-12-1981′
select * from emp
where hiredate between '1981/01/01'
AND date '1981-12-31'
7. Write an SQL query to fetch all the employees who are manager?
select ename
from emp
where job='mgr';
8. Write an SQL query to fetch even rows from the table?
select * from emp
where MOD(empno,2)=0;
9. Write a SQL query to fetch only odd rows from the table?
select * from emp
where MOD(empno,2)<> 0;
10. Find the third highest of employee?
select sal
from emp main
where 2=(select count( distinct sal)
from emp
where sal>main.sal);
11. List the highest paid employee working under king?
select * from emp where sal in
(select max(sal) from emp where mgr in )
(select empno from emp where ename='KING' ));
12. Write an SQL query to find the details of most recently hired emp of dept 30?
select * from emp where hiredate in
(select max(hiredate) from emp where deptno=30);
13. List the employees whose salary is same as ford or blake?
select * from emp
where sal in (select sal from emp e where e.ename in
('FORD','BLAKE')and emp.empno<>e.empno);
14. List the managers who are senior to king but who are junior to smith?
select * from emp
where empno in
(select mgr from emp
where hiredate<(select hiredate from emp where ename ='KING')
and hiredate >(select hiredate from emp where ename='SMITH')) and mgr is
not null;
15. List the department 10 employees whose salary is greater then allen salary?
select * from emp
where deptno=10 and
sal>(select sal)
16. Find the employees whose job is same as allen or sal>allen?
select * from emp
where job = (select job from emp where ename = 'ALLEN')
or sal > (select sal from emp where ename = 'ALLEN');
17. List the empno, ename, sal, job of the employees with annual sal <34000 but receiving some comm. Which should not be>sal and designation should be salesman working for dept 30.
select empno,ename,sal,job
from emp where
12*(sal+nvl(comm,0)) < 34000
and comm is not null
and comm<sal
and job = 'SALESMAN'
and deptno = 30;
18. Write an SQL query to List the employees who are working as either mgr or analyst with the salary ranging from 2000 to 5000 and without commission.
A)elect * from emp
where (job in (‘MANAGER’ ,’ANALYST’) )
and sal between 2000
and 5000
and comm is null;
19. Write a query to find the employees whose salary is 4 digit number.
select * from emp
where length (sal) = 4;
20. Write an SQL query to find the empno, ename, annsal, dailysal of all the salesmen in the ascending annual salary?
A)select e.empno,e.ename ,12*sal "ANN SAL", (12*sal)/365 "DAILY SAL"
from emp e
where e.job = 'SALESMAN'
order by "ANN SAL" asc ;
21. Write an SQL query to find highest paid employee of sales department?
select * from emp
where sal in (select max(sal)
from emp
where deptno in (select d.deptno from
dept d where d.dname = 'SALES'));
22. Find the total salary given to the MGR?
select sum (sal)
from emp
where job = ‘MANAGER’;
or
select sum(sal)
from emp
where empno in(select mgr from emp);
23. List the employees in dept 20 whose sal is greater than the average salary of department 10 employees?
select * from emp
where deptno =20
and sal >(select avg (sal)
from emp where deptno = 10);
24. Write an SQL query to find the employee are with out manager?
This is mostly asked question
select e.ename,e.job from emp e
where mgr is null;
25. Write an SQL to Find the Managers name who is having max no.of employees working under him?
select m.ename,count(*)
from emp w,emp m where w.mgr = m.empno
group by m.ename
having count(*) = (select max(count(*))
from emp group by mgr);
SQL Queries On Join
26. Display empno, ename, deptno and location from emp database.(Use method of joining tables)
select empno, ename, emp.deptno,loc
from emp,dept
where dept.deptno = emp.deptno;
27. Display empno, ename ,sal and grade from emp database where sal lies between losal and hisal ( NON-Equi Join)
select empno, ename,sal,grade
from emp inner join salgrade
on sal BETWEEN losal AND hisal;
28. Find the name of each employees manager.(refer self join).
select e.ename || 'works for' || m.ename "Employees and their Managers"
from emp e, emp m
where e.mgr = m.empno;
OUTPUT:
Employees and their Managers
-----------------------------
FORD works for JONES
SCOTT works for JONES
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
WARD works for BLAKE
ALLEN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
CLARK works for KING
BLAKE works for KING
Employees and their Managers
-----------------------------
JONES works for KING
SMITH works for FORD
13 rows selected.
29. Display empno, ename, deptno and dname, loc for the entire department in emp database.(Inner Join)?
select empno,ename,dept.deptno,dept.dname,dept.loc
from emp join dept
on emp.deptno=dept.deptno;
30. Display empno, ename, deptno and dname, loc for the entire department in emp database.( left outer join)?
select empno,ename,dept.deptno,dept.dname,dept.loc
from emp left join dept
on emp.deptno=dept.deptno;
31. Display empno, ename, deptno and dname, loc for the entire department in emp database.( right outer join)?
select empno,ename,dept.deptno,dept.dname,dept.loc
from emp right join dept
on emp.deptno=dept.deptno;
32.Display empno, ename, deptno and dname, loc for the entire department in emp database.( full outer join)?
select empno,ename,dept.deptno,dept.dname,dept.loc
from emp full outer join dept
on emp.deptno=dept.deptno;
33. Write Alternative Query for Right join?
select e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno
order by e.deptno;
34. Find the names of all employees who are working under Blake?
select e.ename || 'works under Blake'
from emp e,emp m
where e.mgr = m.empno and m.ename = 'BLAKE';
E.ENAME||'WORKS UNDER BLAKE'
---------------------------
ALLEN works under Blake
WARD works under Blake
MARTIN works under Blake
TURNER works under Blake
JAMES works under Blake
More Queries Questions [Click]
Latest Posts
- Design Patterns Interview Questions | Java
- 50 Top Networking Interview Questions and Answers
- Data Engineer Interview Questions and Answers | Top 50
- Selenium Interview Questions | Top 50
- SQL Queries On Sailors Schema with Solutions
- Top 35+ | Operating System Interview Questions & Answers
- Top 25 Cybersecurity Interview Questions With Answers.
- Top 30+ SQL Queries Questions Asked In Interview
- Top 40 Computer Network Interview Questions.
- Top 50 SQL Interview Questions And Answers
Conclusion
If you want me to add more queries so that it can help others too. So you can mail queries at @ admin@noteshacker.com. Or you can comment also