Practice SQL Queries with Solutions For Employee Table – Part 2
In this blogpost we would be providing Sample Practice SQL Queries with Solutions For Employee Table. If you are an Database Administrator or a Database Analyst oor someone who wants to know how to manipulate data from employee tables in your organization, you can use make use of this SQL solutions. You can use this SQL Queries with Solutions For Employee Table for most common problems related to retrieving data from SQL Database.
We have divided the Practice SQL Queries with Solutions For Employee Table in four different parts, as we do not want to make each post lengthy and boring. Each post will contain 50 Practice SQL Queries with Solutions For Employee Table. Use the following table to browse across different parts.
Here is the List of 50 Practice SQL Queries with Solutions For Employee Table – Part 2
| Q.NO | Practice SQL Queries with Solutions For Employee Table – Part 1 |
| 51. | List the emps who are senior to their own MGRs |
| SQL> | select * from emp A where hiredate <
(select hiredate from emp B where b.Empno = A.Mgr) |
| 52. | List the emps of deptno 20 whose jobs are same as deptno 10 |
| SQL> | Select * from emp where deptno=20 and job in (select job from emp where deptno=10) ; |
| 53. | List the emps whose Sal is same as FORD or SMITH in desc order of Sal. |
| QL> | Select distinct * from Emp where sal in
(select sal from emp where ename in (‘FORD’,’SMITH’)) and ename not in (‘FORD’,’SMITH’) Order By sal Desc ; |
| |
|
| 54. | List the emps whose Jobs are same as MILLER or Sal is more than ALLEN. |
| SQL> | Select * from Emp where job = (select job from emp where ename=’MILLER’) OR Sal > (select sal from emp where ename =’ALLEN’) ; |
| 055. | List the emps whose Sal is > the total remuneration of the SALESMAN. |
| SQL> | Select * from Emp where sal > (select SUM(sal) from emp where JOB=’SALESMAN’);
Note : no rows will be selected. If you choose job=’CLERK’, one record will be fetched. |
| |
|
| 56. | List the emps who are Senior to ‘BLAKE’ working at CHICAGO & BOSTON. |
| SQL> | Select * from Emp,dept where Hiredate <
(select hiredate from emp where ename=’BLAKE’) AND loc in (‘CHICAGO’,’BOSTON’) and emp.deptno = dept.deptno ; |
| 57. | List the emps of Grade 3,4 belongs to the dept ACCOUNTING and RESEARCH whose sal is more than ALLEN and exp more than SMITH in the asc order of Exp. |
| SQL> | Select * from Emp,dept,salgrade where grade in(3,4)
AND dname in (‘ACCOUNTING’,’RESEARCH’) and Sal > (select sal from emp where ename=’ALLEN’) and Hiredate < (select hiredate from emp where ename=’SMITH’) and emp.deptno = dept.deptno and sal between losal and hisal; Note : no rows will be selected. Because, ’SMITH’ is the senior most in the emp table. If you choose ename=’TURNER’, Two record will be fetched. |
| |
|
| 58. | List the emps whose jobs same as ALLEN Or SMITH. |
| SQL> | Select * from Emp where job in
(select distinct job from emp where ename in (‘ALLEN’,’SMITH’)) and ename not in (‘ALLEN’,’SMITH’). |
| 59. | Write a Query to display the details of emps whose Sal is same as of
a) Employee Sal of Emp1 table. b) ¾ Sal of any Mgr of Emp2 table. c) The Sal of any sales person with exp of 5 yrs belongs to the sales dept of emp3 table. d) Any Grade 2 employees of Emp4 table. e) Any Grade 2&3 employee working for SALES dept or OPERATIONS dept joined in 89. |
| SQL> | Select * from emp where sal in (select sal from emp1) OR
sal in (select sal*3 from Emp2 where job=’MANAGER’) OR sal in (select sal from Emp3 where months_between(sysdate,hiredate)/12 > 5) OR sal in (select sal from Emp4,salgrade where grade=2 and sal between losal and hisal) OR sal in (select sal from Emp,salgrade,dept where grade in(2,3) and dname in (‘SALES’,’OPERATIONS’) and hiredate like ‘%89’ and sal between losal and hisal and emp.deptno=dept.deptno) |
| 60. | List the jobs of Deptno 10 those are not found in dept 20. |
| SQL> | Select * from emp where deptno = 10 and job not in (select job from emp where deptno=20) |
| 61. | List the emps of Emp1 who are not found in deptno 20 |
| SQL> | Select * from emp where ename not in (select ename from emp where deptno=20) |
| 62. | Find the highest Sal of the Emp table. |
| SQL> | Select Max(sal) from emp ; |
| 63. | Find the details of highest paid employee. |
| SQL> | Select * from emp where sal = (select Max(sal) from emp) ; |
| 64. | Find the highest paid employee of sales dept. |
| SQL> | Select * from emp where sal = (select Max(sal) from emp,dept where dname=’SALES’ and emp.deptno = dept.deptno ) ; |
| 65. | List the most recently hired emp of grade 3 belongs to the Loc CHICAGO |
| SQL> | Select * from emp,dept where loc=’CHICAGO’ and
hiredate = (Select max(hiredate) from emp); |
| 66. | List the emps who are senior to most recently hired emp who is working under Mgr KING |
| SQL> | Select * from emp where hiredate < (Select max(hiredate) from emp) and
mgr=(select empno from emp where ename=’KING’) ; |
| |
|
| 67. | List the details of emp belongs to New York with the Grade 3 to 5 except ‘PRESIDENT’ whose sal > the highest paid emp of CHICAGO in Group where there is ‘MANAGER’ & ‘ANALYST’ not working for Mgr KING |
| SQL> | Select * from emp,dept,salgrade where loc=’NEW YORK’ and
Grade in (3,4,5) and job <> ‘PRESIDENT’ and Sal > (select Max(sal) from emp,dept where loc=’CHICAGO’ and Job not in (‘MANAGER’,’ANALYST’) and emp.deptno = dept.deptno) and Mgr = (select empno from emp where ename=’KING’) And emp.deptno = dept.deptno and sal between losal and hisal ; |
| 068. | Display the details of most senior employee belongs to 1981. |
| SQL> | select * from emp where hiredate = (select min(hiredate)
from emp where hiredate like ‘%81’) ; |
| 69. | List the emps who joined in 81 with job same as the most senior person of year 81. |
| SQL> | select * from emp where hiredate like ‘%81’ and job =
(select job from emp where hiredate= (select min(hiredate) from emp where hiredate like ‘%81’)) ; |
| |
|
| 70. | List the most senior emp working under KING and Grade is more than 3. |
| SQL> | select emp.* from emp,salgrade where hiredate =
(select max(hiredate) from emp where mgr= (select empno from emp where ename = ‘KING’)) and grade > 3 and sal between losal and hisal |
| |
|
| 71. | Find the total sal given to the ‘MGR’ |
| SQL> | select sum(sal) from emp where job = ‘MANAGER’ |
| 72. | Find the total annual sal to distribute job wise in the year 81 |
| SQL> | select Job,sum(sal*12) from emp GROUP BY JOB ; |
| 73. | Display the total sal of emps belong to Grade 3. |
| SQL> | select grade, sum(sal) from emp,salgrade where
sal between losal and hisal group by grade ; |
| 74. | Display the avg salaries of all CLERKS |
| SQL> | select avg(sal) from emp where job = ‘CLERK’ ; |
| 75. | List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps. |
| SQL> | select * from emp where sal > (select avg(sal) from emp
where deptno=10) and deptno=20 ; |
| |
|
| 76. | Display the number of emps for each job group deptno wise |
| SQL> | select dname,job,count(empno) from emp,dept
where dept.deptno = emp.deptno group by dname,job ; |
| 77. | List the Mgr no & no. of emps working for those Mgrs in the asc Mgrno. |
| SQL> | select mgr,count(*) from emp group by mgr order by mgr asc ; |
| 78. | List the dept details where at least two emps are working. |
| SQL> | select dname, count(empno) from emp,dept
where dept.deptno = emp.deptno group by dname,job having count(empno) >= 2 ; |
| |
|
| 79. | Display the grade, number of emps, max sal of each grade. |
| SQL> | select grade, count(empno),max(sal) from emp,dept,salgrade
where dept.deptno = emp.deptno and sal between losal and hisal group by grade ; |
| 80. | Display dname, grade, no of emps where atleast two emps are ‘CLERKS’ |
| SQL> | select dname,grade, count(empno) from emp,dept,salgrade
where dept.deptno = emp.deptno and sal between losal and hisal group by dname,grade having count(‘CLERK’) >= 2 ; |
| 81. | List the details of the dept where the max no of emps are working |
| QL> | select dname, count(empno) from emp,dept
where dept.deptno = emp.deptno group by dname having count(empno) = (select max(count(empno)) from emp group by deptno) ; |
| 082. | Display the emps whose Mgr name is Jones |
| SQL> | Select * from emp where mgr = (select empno from emp where ename = ‘JONES’) ; |
| 83. | List the emps whose salary is more than 3000 after giving 20% increment |
| QL> | Select EMP.*,SAL+(SAL*.2) incrsal from emp where SAL+(SAL*.2) > 3000 ; |
| 84. | List the emps with their dept names. |
| SQL> | Select emp.*, dname from emp, dept where
emp.deptno = dept.deptno ; |
| |
|
| 85. | List the emps who are not working in sales dept |
| SQL> | Select emp.*, dname from emp, dept where
Dname <> ‘SALES’ and emp.deptno = dept.deptno ; |
| 86. | List the emps name, dept, sal & comm. For those whose salary is between 2000 and 5000 while loc is CHICAGO |
| SQL> | Select ename, dname, sal, comm from emp, dept where
sal between 2000 and 5000 and loc = ‘CHICAGO’ and emp.deptno = dept.deptno ; |
| |
|
| 87. | List the emps whose salary is > his Manager’s salary. |
| SQL> | select A.* from emp A,emp B where
A.mgr=B.empno and A.sal > B.sal ; |
| 88. | List the grade, employee name for the deptno 10 or deptno 30 but salgrade is not 4 while they joined the company before ’31-DEC-82’ |
| SQL> | select grade, ename, hiredate from emp,salgrade where
deptno in (10,30) and grade <> 4 and hiredate in (select hiredate from emp where hiredate < ’31-DEC-82′) and sal between losal and hisal |
| |
|
| 89. | List the name, job, dname, loc for those who are working as a ‘MGRs’ |
| SQL> | select empno,ename, job, dname, loc from emp,dept where
job = ‘MANAGER’ and emp.deptno = dept.deptno ; |
| |
|
| 90. | List the emps whose mgr name is ‘JONES’ and also list their manager name |
| SQL> | select A.*, B.ename from emp A, emp B where
A.mgr = B.empno and b.ename=’JONES’ ; |
| 91. | List the name and salary of FORD if his salary is equal to hisal of his Grade |
| SQL> | select ename, sal from emp where ename = ‘FORD’
and sal = (select hisal from emp, salgrade where ename = ‘FORD’ and sal between losal and hisal) |
| 92. | List the name, job, dname, Manager, salary, Grade dept wise. |
| SQL> | Select ename, job, Mgr, sal, dname, grade from
emp, dept, salgrade where emp.deptno=dept.deptno and sal between losal and hisal and emp.deptno in (select distinct deptno from emp group by emp.deptno) ; |
| 93. | List the emps name, job, salary, grade and dname except ‘CLERK’s and sort on the basis of highest salary. |
| SQL> | select ename, job, sal, grade, dname from emp, dept, salgrade
where job <> ‘CLERK’ and dept.deptno = emp.deptno and sal between losal and hisal order by sal desc ; |
| |
|
| 94. | List the emps name, job and Manager who are without Manager. |
| SQL> | select ename, job, mgr from emp where mgr is NULL ; |
| 95. | List the names of emps who are getting the highest salary dept wise. |
| SQL> | Select ename, job, sal, dname from emp, dept
where emp.deptno=dept.deptno and emp.deptno in (select distinct deptno from emp group by emp.deptno) and sal in (select max(sal) from emp group by deptno) ; |
| 96. | List the emps whose salary is equal to average of maximum and minimum. |
| SQL> |
TO RETRIEVE SAL WHICH IS = TO AVG. SALSelect ename, job, sal from emp where sal = (select avg(sal) from emp) ;
TO RETRIEVE SAL WHICH IS = TO AVG. OF MAX SAL AND MIN SALselect * from emp where sal = (select (max(sal)+min(sal))/2 from emp) ; |
| 97. | List the no of emps in each dept where the number is more than 3. |
| SQL> | Select deptno, count(deptno) from emp
group by deptno having count(deptno) > 3 ; |
| 98. | List the names of depts. Where at least 3 emps are working in each dept. |
| SQL> | Select dname, count(emp.deptno) from emp,dept
where emp.deptno = dept.deptno group by dname having count(emp.deptno) > 3 ; |
| |
|
| 99. | List the managers whose salary is more than his emps avg sal. |
| SQL> | Select distinct A.* from emp A, emp B
where A.empno = B.mgr and A.sal > (select avg(B.sal) from emp group by B.Mgr) ; |
| 100. | List the name, sal, comm. For those emps whose net pay is > or = any other employee salary of the company. |
| SQL> | Select distinct A.ename, A.sal, A.comm from emp A, emp B where
((A.sal + A.sal*1.2) + (A.sal*1.2) -(A.sal*1.6 + 80)) >= (select avg(sal) from emp) ; |
