161) List the Deptno, Name, Job, Salary and Sal+Comm of the SALESMAN who are earning maximum salary and commission in descending order.

A)select deptno,name,job,sal,sal+nvl(comm.,0) from emp where job =

‘SALESMAN’ and sal in (select max(sal+nvl(comm.,0)) from emp where comm. is not null)

Order by (sal +nvl(comm.,0)) desc;

162) List the Deptno, Name, Job, Salary and Sal+Comm of the emps who earn the

second highest earnings (sal + comm.).

A) select deptno,ename,sal,job,sal+nvl(comm,0) from emp e where 2 = (select

count(distinct sal+nvl(comm,0)) from emp where

(e.sal+nvl(comm.,0))<(sal+nvl(comm.,0));

163) List the Deptno and their average salaries for dept with the average salary less

than the averages for all department

A) select deptno,avg(sal) from emp group by deptno

having avg(sal) <(select avg(Sal) from emp);

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.

A) select w.ename,w.sal,m.ename,m.sal from emp w,emp m

where w.mgr = m.empno and w.sal > m.sal;

165) List out the Name, Job, Salary of the emps in the department with the highest

average salary.

A) select * from emp where deptno in

(select deptno from emp e

having avg(sal) =(select max(avg(sal)) from emp group by deptno)

group by deptno);

# 166) List the empno,sal,comm. Of emps.

` A) select empno,sal,comm. from emp;`

167) List the details of the emps in the ascending order of the sal.

A) select * from emp order by sal asc;

168) List the dept in the ascending order of the job and the desc order of the emps

print empno, ename.

A) select * from emp e order by e.job asc,e.empno desc ;

169) Display the unique dept of the emps.

A)select * from dept where deptno in (select unique deptno from emp);

170) Display the unique dept with jobs.

A) select unique deptno ,job from emp ;

171) Display the details of the blake.

A) select * from emp where ename = ‘BLAKE’;

172) List all the clerks.

A) select * from emp where job = ‘CLERK’;

173) list all the employees joined on 1st may 81.

A) select * from emp where hiredate = ’01-MAY-81’;

174) List the empno,ename,sal,deptno of the dept 10 emps in the ascending order of salary.

A) select e.empno,e.ename,e.sal,e.deptno from emp where e.deptno = 10

order by e.sal asc;

175) List the emps whose salaries are less than 3500.

A) select * from emp where sal <3500;

176) List the empno,ename,sal of all the emp joined before 1 apr 81.

A) select e.empno ,e.ename .e.sal from emp where hiredate <’01-APR-81’;

177) List the emp whose annual sal is <25000 in the asc order of the salaries.

A) select * from emp where (12*sal) < 25000 order by sal asc;

178) List the empno,ename,annsal,dailysal of all the salesmen in the asc ann sal

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 ;

179) List the empno,ename,hiredate,current date & exp in the ascending order of the exp.

A) select empno,ename,hiredate,(select sysdate from

dual),((months_between(sysdate,hiredate))/12) EXP

from emp

order by EXP asc;

## 180) List the emps whose exp is more than 10 years.

A) select * from emp where ((months_between(sysdate,hiredate))/12) > 10;

182) List the emps who are working as managers.

A) select * from emp where job = ‘MANAGER’;

183) List the emps who are either clerks or managers.

A) select * from emp where job in (‘CLERK’,’MANAGER’);

184) List the emps who have joined on the following dates 1 may 81,17 nov 81,30 dec 81

A) select * from emp where to_char(hiredate,’DD-MON-YY’) in

(’01-MAY-81’,’17-NOV-81’,’30-DEC-81’);

185) List the emps who have joined in the year 1981.

A) select * from emp where to_char(hiredate,’YYYY’) = ‘1981’;

186) List the emps whose annual sal ranging from 23000 to 40000.

A) select * from emp where (12* sal) between 23000 and 40000;

187) List the emps working under the mgrs 7369,7890,7654,7900.

A) select * from emp where mgr in ( 7369,7890,7654,7900);

188) List the emps who joined in the second half of 82.

A)select * from emp where hiredate between ’01-JUL-82’ and ’31-DEC-82’;

189) List all the 4char emps.

A) select * from emp where length (ename) = 4;

190) List the emp names starting with ‘M’ with 5 chars.

A) select * from emp where ename like ‘M%’ and length (ename) = 5;

191) List the emps end with ‘H’ all together 5 chars.

A) select * from emp where ename like ‘%H’ and length (ename) = 5;

192) List names start with ‘M’.

A) select * from emp where ename like ‘M%’;

193) List the emps who joined in the year 81.

A) select * from emp where to_char(hiredate,’YY’) = ‘81’;

194) List the emps whose sal is ending with 00.

A) select * from where sal like ‘%00’;

195) List the emp who joined in the month of JAN.

A) select * from emp where to_char(hiredate,’MON’) = ‘JAN’; (OR)

B) select * from emp where to_char (hiredate,’MM’) = 1;

196) Who joined in the month having char ‘a’.

A) select * from emp where to_char (hiredate,’MONTH’) like’%A%’; (OR)

B) select * from emp where instr(to_char(hiredate,’MONTH’),’A’) >0;

197) Who joined in the month having second char ‘a’

A) select * from emp where to_char(hiredate,’MON’) like ‘_A%’; (OR)

B) select * from emp where instr(to_char(hiredate,’MON’),’A’) = 2;

198) List the emps whose salary is 4 digit number.

A) select * from emp where length (sal) = 4;(OR)

B) select * from emp where sal between 999 and 9999;

199) List the emp who joined in 80’s.

A) select * from emp where to_char(hiredate,’YY’) between ‘80’ and ’89’;

(OR) B) select * from emp where to_char(hiredate,’YY’) >= ‘80’ and

to_char(hiredate,’YY’) < ‘90’;

200) List the emp who are clerks who have exp more than 8ys.

A) select * from emp where job = ‘CLERK’ and

(months_between(sysdate,hiredate) /12) > 8