Practice SQL Queries with Solutions For Employee Table – Part 4
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 4
| 151. | List all the emps by name and number along with their manager’s name and number also list KING who has no ‘Manager’ |
| SQL> | |
| select distinct A.empno, A.ename, A.mgr, B.empno, B.ename
from emp A, emp B where A.mgr = B.empno ;
HOW TO RETRIEVE KING’S INFO
select * from emp where empno in (select distinct A.empno from emp A, emp B where A.mgr = B.empno) union (select B.empno from emp B where ename=’KING’) ; ERRRO : Query Block has incorrect number of result columns. |
|
| 152. | Find all the emps who earn minimum sal for each job wise in asc order of sal |
| SQL> | select * from emp where sal in (select min(sal) from emp
group by job) order by sal ; |
| Also Read: Best PL/SQL and SQL Tips To Read Before Interview | |
| 153. | Find out all the emps who earn highest sal in each job type. Sort in des order of sal |
| SQL> | select * from emp where sal in (select max(sal) from emp
group by job) order by sal desc ; |
| 154. | Find out the most recently hired emps in each dept order by hiredate |
| SQL> | select * from emp where hiredate in (select max(hiredate) from emp
group by job) order by hiredate desc ; |
| |
|
| 155. | List the emp name, sal and deptno for each emp who earns a sal greater than the avg for their dept order by deptno |
| SQL> | |
| select ename, sal, deptno from emp where sal in
(select sal from emp where sal > (select avg(sal) from emp group by deptno) group by deptno) order by deptno
NOT A GROUP BY EXPRESSION
|
|
| 156. | List the deptno where there are no emps |
| SQL> | select deptno, dname from dept where deptno not in
(select deptno from emp) ; |
| |
|
| 157. | List the no of emps and avg salary within each dept for each job. |
| SQL> | select deptno, job, count(empno), avg(sal) from emp group by deptno, job ; |
| 158 | Find the max avg salary drawn for each job except for ‘PRESIDENT’ |
| SQL> | select job, max(sal), avg(sal) from emp
where job <> ‘PRESIDENT’ group by job ; |
| 159. | Find the name and job of the emps who earn Max salary and Commission. |
| SQL> | select ename, job, sal, comm from emp
where sal=(select max(sal) from emp) or comm=(select max(comm) from emp) ; |
| 160. | List the name, job and salary of the emps who are not belonging to the dept 10 but who have the same job and salary as the emps of dept 10. |
| SQL> | select ename, job, sal from emp where deptno <> 10
and job in (select job from emp where deptno=10) and sal in (select sal from emp where deptno=10) ;
NO SUCH RECORDS, SO GIVE : select ename, job, sal from emp where deptno <> 10 and job in (select job from emp where deptno=10) or sal in (select sal from emp where deptno=10) ; |
| |
|
| 161. | List the deptno, name, job, salary and sal+comm. Of the emps who earn the second highest earnings (sal+comm) |
| SQL> | select ename, job, sal, sal+decode(comm,null,0), deptno from
emp where empno = (select empno from emp where sal+decode(comm,null,0) = (select max(sal+decode(comm,null,0)) from emp where sal+decode(comm,null,0) in (select sal+decode(comm,null,0) from emp where sal+decode(comm,null,0) < (select max(sal+decode(comm,null,0)) from emp)))) ; |
|
Select ename, job, sal, sal+decode(comm,null,0), deptno from emp where sal = (select max(sal) from emp where sal in (select sal from emp where sal < (select max(sal) from emp))) ;
Select ename, job, sal, sal+comm, deptno from emp where sal+comm = (select max(sal+decode(comm,null,0)) from emp where sal+decode(comm,null,0) in (select sal+decode(comm,null,0) from emp where sal+decode(comm,null,0) < (select max(sal+decode(comm,null,0)) from emp)))
NO ROWS SELECTED |
|
| 162. | List the deptno, name, job, salary and sal+comm. Of the salesman |
| SQL> | select ename, job, sal, comm, deptno from emp where job = ‘SALESMAN’ |
| 163. | List the deptno and their avg salaries for dept with the avg salary less than the avg for all departments. |
| SQL> | select deptno, avg(sal) from emp having avg(sal) =
(select min(avg(sal)) from emp having avg(sal) in (select avg(sal) from emp group by deptno) group by deptno) group by deptno ; |
| |
|
| 164. | List out the names and salaries of the emps along with their manager names and salaries for those emps who earn more salary than their manager. |
| SQL> | select A.ename, A.sal, B.ename, B.sal from emp A, emp B
where A.mgr = B.empno and A.sal > B.sal ; |
| 165. | List out the name, job, sal of the emps in the department with the highest avg sal. |
| SQL> | select ename, job, sal from emp where sal >=
(select max(avg(sal)) from emp group by deptno) ; |
| 166. | List the empno, sal and comm. of emps |
| SQL> | Select empno, sal, comm From emp ; |
| 167. | List the details of the emps in the asc order of salary. |
| SQL> | Select * from emp order by sal ; |
| 168. | List the dept in asc order of job and the desc order of emps print empno, ename |
| SQL> | select empno, ename, job, sal, deptno from emp order by job, empno desc ; |
| 169. | Display the unique dept of emps. |
| SQL> | select * from emp where deptno = (select deptno from emp
having count(deptno) = 1 group by deptno);
To get the answer enter a new record with deptno as 40. |
| 170. | Display the unique dept with jobs. |
| SQL> | select dname,job from emp, dept where emp.deptno = (select deptno from emp
having count(deptno) = 1 group by deptno) ; |
| |
|
| 171. | Display the details of BLAKE. |
| SQL> | Select * from emp where ename = ‘BLAKE’ ; |
| 172. | List all CLERKs |
| SQL> | Select * from emp where job = ‘CLERK’ ; |
| 173. | List all emps joined on 1 May 81. |
| SQL> | Select * from emp where hiredate = ’01-MAY-81’ ; |
| 174. | List empno, ename,sal, deptno of dept 10 emps in the asc order of sal. |
| SQL> | Select empno, ename,sal, deptno from emp where deptno = 10 order by sal ; |
| |
|
| 175. | List the emps whose salaries are less than 3500. |
| SQL> | Select * from emp where sal < 3500 ; |
| 176. | List the emps Empno, Ename,sal for all emp joined before 1 apr 81 |
| SQL> | |
| Select Empno, Ename,sal,hiredate from emp where hiredate in (
select hiredate from emp where hiredate > ’01-APR-81′) ;
Select Empno, Ename,sal,hiredate from emp where hiredate < ’01-APR-81′
Giving wrong output. |
|
| 177. | List the emps whose ann sal is < 25000 in the asc order of salaries |
| SQL> | Select * from emp where sal*12 < 25000 order by sal ; |
| 178. | List the empno, ename, ann sal, daily sal of all salesman in the asc ann sal. |
| SQL> | Select empno, ename, sal*12 AnnSal, sal/30 DailySal
from emp where job = ‘SALESMAN’ order by sal ; |
| 179. | List the empno, ename, hiredate, current date & exp in the asc order of exp. |
| SQL> | Select empno, ename, hiredate, sysdate,
months_between(sysdate,hiredate)/12 Exp from emp order by hiredate ; |
| 180. | List the emps whose exp is more than 10 years. |
| SQL> | select * from emp where
months_between(sysdate,hiredate)/12 > 10 ; |
| |
|
| 181. | List the empno, ename,sal TA 30%, DA 40%, HRA 50%, gross, LIC, PF, net deductions, net allowances and net sal in the asc order of net sal. |
| SQL> | select empno, ename,sal,sal*.3 TA,sal*.4 DA,sal*.5 HRA, 80 IT,
sal*.04 LIC, sal*.12 PF, (sal*.16)-80 NetDeductions, sal*1.2 NetAllowances, sal+sal*1.2 Gross, (sal+sal*1.2 – (sal*.16)-80) NetSal from emp order by sal ; |
| 182. | List the emps who are working as Mgrs. |
| SQL> | Select * from emp where job = ‘MANAGER’ ; |
| 183. | List the emps who are either CLERKs or MANAGERs. |
| SQL> | Select * from emp where job = ‘MANAGER’ or job = ‘CLERK’; |
| 184. | List the emps who joined in the following dates : 1 may 81, 17 nov 81, 30 dec 81. |
| SQL> | select * from emp where hiredate in
(’01-MAY-81′, ’17-NOV-81′, ’30_DEC-81′) ;
NO ROWS WILL BE SELECTED. THERE IS NO SUCH HIREDATES |
| |
|
| 185. | List the emps who joined in year 81. |
| SQL> | select * from emp where hiredate like ‘%81’ ; |
| 186. | List the emps whose ann sal ranging from 23000 to 40000. |
| SQL> | Select * from emp where sal*12 between 23000 and 40000 ; |
| 187. | List the emps working under the Mgrs 7369, 7890, 7654, 7900. |
| SQL> | Select * from emp where Mgr in (7369, 7890, 7654, 7900) ;
NO ROWS WILL BE SELECTED. THERE IS NO SUCH EMPNOs |
| |
|
| 188. | List the emps who joined in the second half of 82. |
| SQL> | |
| Select * from emp where hiredate in (select hiredate
from emp where hiredate > ’30-JUN-82′) ;
Select * from emp where hiredate > ’30-JUN-82′;
NO ROWS SELECTED |
|
| 189. | List all the 4 chars emps. |
| SQL> | Select ename from emp having
length(ename) = 4 group by ename ; |
| |
|
| 190. | List the emps names starting with ‘M’ with 5 chars |
| SQL> | Select ename from emp where ename like ‘M____’ ;
NO ROWS WILL BE SELECTED. THERE IS NO SUCH EMPs. GIVE ONE MORE ‘_’ IN ENAME. |
| 191. | List the emps names end with ‘H’ all together 5 chars. |
| SQL> | Select ename from emp where ename like ‘____H’ ; |
| 192. | List the names start with ‘M’ |
| SQL> | Select ename from emp where ename like ‘M%’ |
| 193. | List the emps who joined in the year 81 |
| SQL> | Select * from emp where hiredate like ‘%81’ ; |
| 194. | List the emps whose sal is ending with 00. |
| SQL> | Select * from emp where sal like ‘%00’ ; |
| |
|
| 195. | List the emps who joined in the month of jan |
| SQL> | Select * from emp where hiredate like ‘%JAN%’ ; |
| 196. | List the emps who joined in the month having a char ‘a’ |
| SQL> | Select * from emp where hiredate like ‘%A% ;’ |
| 197. | List the emps who joined in the month having a second char is ‘A’ |
| SQL> | Select * from emp where hiredate like ‘%-_A%’ ; |
| |
|
| 198. | List the emps whose salary is 4 digit no. |
| SQL> | Select * from emp where sal like ‘____’ ; |
| 199. | List the emps who joined in 80’s. |
| SQL> | Select * from emp where hiredate like ‘%8_’ ; |
| |
|
| 200. | List the emps who are either clerks with an exp more than 8y |
| SQL> | Select * from emp where job = ‘CLERK’ or
Months_between(sysdate,hiredate)/12 > 8 ; |
