# 81. List the details of the department where maximum number of emps are working.

A) select * from dept where deptno in

(select deptno from emp group by deptno

having count(*) in

(select max(count(*)) from emp group by deptno) ); (OR)

B) select d.deptno,d.dname,d.loc,count(*) from emp e ,dept d

where e.deptno = d.deptno group by d.deptno,d.dname,d..loc

having count(*) = (select max(count(*) ) from emp group by deptno);

82. Display the emps whose manager name is jones.

A) select * from emp where mgr in

(select empno from emp where ename = ‘JONES’); (OR)

B) select * from emp where mgr =

(select empno from emp where ename = ‘JONES’);

83. List the employees whose salary is more than 3000 after giving 20% increment.

A) SELECT * FROM EMP WHERE (1.2*SAL) > 3000 ;

## 84. List the emps with dept names.

A) select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname

from emp e ,dept d where e.deptno = d.deptno;

85. List the emps who are not working in sales dept.

`A) select * from emp where deptno not in `

(select deptno from emp where dname = ‘SALES’);

86. List the emps name ,dept, sal and comm. For those whose salary is between 2000

and 5000 while loc is Chicago.

A) select e.ename,e.deptno,e.sal,e.comm from emp e ,dept d where e.deptno =

d.deptno and

d.loc = 'CHICAGO' and e.sal between 2000 and 5000;

87. List the emps whose sal is greater than his managers salary

A) select * from emp w,emp m where w.mgr = m.empno and w.sal > m.sal;

88. List the grade, EMP name for the deptno 10 or deptno 30 but sal grade is not 4

while they joined the company before ’31-dec-82’.

A) select s.grade ,e.ename from emp e,salgrade s where e.deptno in (10,20) and

hiredate < ('31-DEC-82') and (e.sal between s.losal and s.hisal and s.grade not in

(4));

89. List the name ,job, dname, location for those who are working as MGRS.

A) select e.ename,e.job,d.dname,d.loc from emp e ,dept d

where e.deptno = d.deptno and

e.empno in (select mgr from emp ) ;

90. List the emps whose mgr name is jones and also list their manager name.

A) select w.empno,w.ename,w.job,w.mgr,w.hiredate,w.sal,w.deptno,m.ename

from emp w ,emp m

where w.mgr = m.empno and m.ename = 'JONES';

91. List the name and salary of ford if his salary is equal to hisal of his grade.

A) select e.ename,e.sal from emp e ,salgrade s where e.ename = 'FORD' and e.sal

between s.losal and s.hisal and e.sal = s.hisal ;

92. Lit the name, job, dname ,sal, grade dept wise

A) select e.ename,e.job,d.dname,e.sal,s.grade from emp e,dept

d,salgrade s

where e.deptno = d.deptno and e.sal between s.losal and s.hisal

order by e.deptno ;

93. List the emp name, job, sal, grade and dname except clerks and sort on the basis

of highest sal

A) select e.ename,e.job,e.sal,s.grade,d.dname from emp e ,dept d

,salgrade s where e.deptno = d.deptno and e.sal between s.losal

and s.hisal and

e.job not in('CLERK')

order by e.sal desc;

94. List the emps name, job who are with out manager.

A) select e.ename,e.job from emp e where mgr is null;

95. List the names of the emps who are getting the highest sal dept wise.

A) select e.ename,e.deptno from emp e where e.sal in

(select max(sal) from emp group by deptno) ;

96. List the emps whose sal is equal to the average of max and minimum

A) select * from emp where sal =(select (max(sal)+min(sal))/2 from emp);

97. List the no. of emps in each department where the no. is more than 3.

A) select deptno,count(*) from emp group by deptno having count(*) < 3;

98. List the names of depts. Where atleast 3 are working in that department.

A) select d.dname,count(*) from emp e ,dept d where e.deptno =

d.deptno

group by d.dname

having count(*) >= 3 ;

99. List the managers whose sal is more than his employess avg salary.

A) select * from emp m where m.empno in (select mgr from emp)

and m.sal > (select avg(e.sal) from emp e where e.mgr = m.empno )

The subquery does the same as (select (avg(e.sal)),m.ename from emp

e,emp m where e.mgr=m.empno group by e.mgr,m.ename);

100. List the name,salary,comm. For those employees whose net pay is greater than

or equal to any other employee salary of the company.

A) select e.ename,e.sal,e.comm from emp e where

nvl2(e.comm.,e.sal+e.comm.,e.sal) >= any (select sal from emp);

(OR)

B) select ename,sal,comm. from emp where sal+nvl(comm.,0) >=

any (select sal from emp);/

102. List the employee names and his average salary department wise.

A)select d.deptno, round(avg(nvl2(e1.comm, e1.sal+e1.comm, e1.sal))) avg,

e2.ename from emp e1, emp e2, dept d where d.deptno =e1.deptno and d.deptno =

e2.deptno group by d.deptno, e2.ename; (or)

B) select d.maxsal,e.ename,e.deptno as "current sal" from emp e,

(select avg(Sal) maxsal,deptno from emp group by deptno) d

where e.deptno=d.deptno;

103. Find out least 5 earners of the company.

A) select * from emp e where 5> (select count(*) from emp where

e.sal >sal); (or)

B) select rownum rank,empno,ename,job,sal from (select * from

emp order by sal asc) where rownum < 6 ; (or)

C) select * from emp e where 5 >(select count(distinct sal) from

emp where e.sal > sal);

104. Find out emps whose salaries greater than salaries of their managers.

A) select * from emp w,emp m where w.mgr = m.empno and w.sal>

m.sal; (OR)

B) select * from emp e ,(select * from emp where empno in (select

mgr from emp)) a

where e.sal >a.sal and e.mgr = a.empno

105. List the managers who are not working under the president.

A) select * from emp where empno in(select mgr from emp) and mgr not in

(select empno from emp where job = 'PRESIDENT')

107. List the Name , Salary, Comm and Net Pay is more than any other employee.

A) Select e.ename,e.sal,e.comm,nvl2(comm,sal+comm,sal)

NETPAY

from emp e

where nvl2(comm,sal+comm,sal) > any (select sal from emp

where empno =e.empno) ;

108. List the Enames who are retiring after 31-Dec-89 the max Job period is 20Y.

A) select ename from emp where add_months(hiredate,240) > '31-DEC-89';

B) select ename from emp

where add_months(hiredate,240) > to_date(’31-DEC-89’,’DD-MON-RR’);

109. List those Emps whose Salary is odd value.

A) select * from emp where mod(sal,2) = 1;

110. List the emp’s whose Salary contain 3 digits.

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

111. List the emps who joined in the month of DEC.

A) select * from emp where to_char(hiredate,’MON’) =’DEC’;

(OR)

B) select * from emp where to_char(hiredate,’MON’) in (‘DEC’);

(OR)

C) select * from emp where to_char(hiredate,’MONTH’) like

‘DEC%’;

112. List the emps whose names contains ‘A’.

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

113. List the emps whose Deptno is available in his Salary.

A) select * from emp where instr(sal,deptno) > 0;

114. List the emps whose first 2 chars from Hiredate=last 2 characters of Salary.

A) select * from emp

where substr(hiredate,1,2) = substr(sal,length(sal)-1,length(sal));

115. List the emps Whose 10% of Salary is equal to year of joining.

A) select * from emp where to_char(hiredate,'YY') in (select .1*sal from emp);

116. List first 50% of chars of Ename in Lower Case and remaining are upper Case.

A)select lower(substr(ename,1,round(length(ename)/2)))

||substr(ename,round(length(ename)/2)+1,length(ename)) from emp ; (OR) B) select lower(substr(ename,1,ciel(length(ename)/2)))

|| substr(ename,ciel(length(ename)/2)+1,length(ename)) from emp ;

117. List the Dname whose No. of Emps is =to number of chars in the Dname.

A) select * from dept d where length(dname) in (select count(*)

from emp e where e.deptno = d.deptno ); (or)

B) select d.dname,count(*) from emp e ,dept d where e.deptno =

d.deptno group by d.dname having count(*) = length (d.dname);

118. List the emps those who joined in company before 15th of the month.

A) select * from emp where to_char(hiredate,'DD') < '15';

119. List the Dname, no of chars of which is = no. of emp’s in any other Dept.

A) select * from dept d where length(dname) in (select count(*)

from emp where d.deptno <> deptno group by deptno ); (or)

B) select * from dept where length(dname) = any (select count(*)

from emp where d.deptno <> deptno group by deptno);

C) select * from dept d , (select count(*) s,e.deptno "M"from emp e

group by e.deptno) d1

where length(dname)=d1.s and d1.M <>d.deptno;

120. List the emps who are working as Managers.

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

B) select * from emp where empno in (select mgr from emp );