# 121. List THE Name of dept where highest no.of emps are working.

A) select dname from dept where deptno in (select deptno from emp group by deptno having count(*) in (select max(count(*)) from emp group by deptno) );

122. Count the No.of emps who are working as ‘Managers’(using set option).

A)select count(*) from(select * from emp minus select * from emp where job != 'MANAGER')

123. List the emps who joined in the company on the same date.

A) select * from emp e where hiredate in (select hiredate from emp where e.empno <> empno);

124. List the details of the emps whose Grade is equal to one tenth of Sales Dept.

A) select * from emp e,salgrade s where e.sal between s.losal and s.hisal and s.grade = 0.1* (select deptno from dept where dname = 'SALES');

125. List the name of the dept where more than average no. of emps are working.

A) select d.dname from dept d, emp e where e.deptno = d.deptno group by d.dname having count(*) > (select avg(count(*)) from emp group by deptno);

126. List the Managers name who is having max no.of emps working under him.

A)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); (OR) B) select * from emp where empno = (select mgr from emp group by mgr having count(*) = (select max(count(*)) from emp group by mgr)) ;

## 127. List the Ename and Sal is increased by 15% and expressed as no.of Dollars.

A) select ename,to_char(1.15*sal,'$99,999') as "SAL" from emp; (only for $ it works) B) select ename,'$'||1.15*sal “SAL” from emp;

128. Produce the output of EMP table ‘EMP_AND_JOB’ for Ename and Job.

A) select ename|| job as "EMP_AND_JOB" from emp ;

129. Produce the following output from EMP.

EMPLOYEE

SMITH (clerk)

ALLEN (Salesman)

A) select ename || ‘(‘|| lower(job)||’)’ as “EMPLOYEE” from emp;

130) List the emps with Hire date in format June 4, 1988.

A) select empno,ename,sal, to_char(hiredate,'MONTH DD,YYYY') from emp;

131) Print a list of emp’s Listing ‘just salary’ if Salary is more than 1500, on target if

Salary is 1500 and ‘Below 1500’ if Salary is less than 1500.

A) select empno,ename,sal|| ‘JUST SALARY’ "SAL" from emp where sal > 1500 union select empno,ename, sal|| ‘ON TARGET’ "SAL" from emp where sal = 1500 union select empno,ename, sal|| ‘BELOW 1500’ "SAL" from emp where sal < 1500; (OR) B)select empno,ename,sal,job, case when sal = 1500 then 'ON TARGET' when sal < 1500 then 'BELOW 1500' when sal > 1500 then 'JUST SALARY' else 'nothing' end "REVISED SALARY" from emp;

132) Write a query which return the day of the week for any date entered in format

‘DD-MM-YY’.

A) select to_char(to_date('& s','dd-mm-yy'),'day') from dual ;

133) Write a query to calculate the length of service of any employee with the

company, use DEFINE to avoid repetitive typing of functions.

A) DEFINE service = ((months_between(sysdate,hiredate))/12) B) Select empno,ename,&service from emp where ename = ‘& name’;

135) Emps hired on or before 15th of any month are paid on the last Friday of that

month those hired after 15th are paid on the first Friday of the following month.

Print a list of emps their hire date and the first pay date. Sort on hire date.

A) select ename,hiredate,next_day(last_day(hiredate),'FRIDAY')-7 from emp where to_char(hiredate,'DD') <=15 union select ename,hiredate,next_day(last_day(hiredate),'FRIDAY') from emp where to_char(hiredate,'DD') > 15;

136) Count the no. of characters with out considering spaces for each name.

A) select length(replace(ename,’ ‘,null)) from emp;

137) Find out the emps who are getting decimal value in their Sal without using like

operator.

A) select * from emp where instr(sal,’.’,1,1) > 0;

138) List those emps whose Salary contains first four digit of their Deptno.

A) select * from emp where instr(to_char(sal,,9999),deptno,1,1)>0 and instr(to_char(sal,9999),deptno,1,2)> 0 ;

139) List those Managers who are getting less than his emps Salary.

A) select distinct m.ename,m.sal from emp w,emp m where w.mgr = m.empno and w.sal>m.sal; B) select * from emp w where sal < any ( select sal from emp where w.empno=mgr); C) select * from emp w where empno in ( select mgr from emp where w.sal<sal);

140) Print the details of all the emps who are sub-ordinates to Blake.

A) select * from emp where mgr in (select empno from emp where ename = 'BLAKE');

141) List the emps who are working as Managers using co-related sub-query.

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

142) List the emps whose Mgr name is ‘Jones’ and also with his Manager name.

A) select w.ename,m.ename,(select ename from emp where m.mgr = empno) "his MANAGER" from emp w,emp m where w.mgr = m.empno and m.ename = 'JONES'; (or) B) select e.ename,w.ename,m.ename from emp e,emp w,emp m where e.mgr = w.empno and w.ename = ‘JONES’ and w.mgr = m.empno;

143) Define a variable representing the expression used to calculate on emps total

annual remuneration use the variable in a statement, which finds all emps who

can earn 30000 a year or more.

A) Set define on B) Define annual = 12*nvl2(comm.,sal+comm.,sal) (here define variable is a session variable) C) Select * from emp where &annual > 30000;

144) Find out how may Managers are their in the company.

A) select count(*) from emp where job = ‘MANAGER’; (or) B) select count(*) from emp where empno in (select mgr from emp); (or) C) select count(distinct m.empno) from emp w,emp m where w.mgr = m.empno ;

145) Find Average salary and Average total remuneration for each Job type.

Remember Salesman earn commission.secommm

A) select avg(sal),avg(sal+nvl(comm,0)) from emp;

146) Check whether all the emps numbers are indeed unique.

A) select empno,count(*) from emp group by empno;

147) List the emps who are drawing less than 1000 Sort the output by Salary.

```
A)select * from emp where sal < 1000 order by sal;
```

148) List the employee Name, Job, Annual Salary, deptno, Dept name and grade who

earn 36000 a year or who are not CLERKS.

A)selecte.ename,e.job,(12*e.sal)"ANNUALSALARY", e.deptno,d.dname,s.grade from emp e,dept d ,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and (((12*e.sal)>= 36000) or (e.job != 'CLERK'))

149) Find out the Job that was filled in the first half of 1983 and same job that was

filled during the same period of 1984.

A) select * from emp where (to_char(hiredate,'MM ') <= 06 and to_char(hiredate,'YYYY') = 1984) and job in (select job from emp where to_char(hiredate,'MM' ) <= 06 and to_char(hiredate,'YYYY') <= 1983) ;

150) Find out the emps who joined in the company before their Managers.

A) select * from emp w,emp m where w.mgr = m.empno and w.hiredate< m.hiredate;(or) B) select * from emp e where hiredate < (select hiredate from emp where empno = e.mgr)

151) List all the emps by name and number along with their Manager’s name and

number. Also List KING who has no ‘Manager’.

A) select w.empno,w.ename,m.empno,m.ename from emp w,emp m where w.mgr= m.empno(+);

152) Find all the emps who earn the minimum Salary for each job wise in ascending

order.

A) select * from emp where sal in (select min(sal) from emp group by job) order by sal asc;

153) Find out all the emps who earn highest salary in each job type. Sort in

descending salary order.

A) 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.

A) select * from emp e where hiredate in (select max(hiredate) from emp where e.deptno = deptno ) order by hiredate;

155) List the employee name,Salary and Deptno for each employee who earns a

salary greater than the average for their department order by Deptno.

A) select * from emp e where sal > (select avg(sal) from emp where e.deptno = deptno ); B) select e.ename,e.sal,e.deptno from emp e,(select avg(sal) A,deptno D from emp group by deptno) D1 where D1.D = e.deptno and e.sal > D1.A;

156) List the Deptno where there are no emps.

A) select deptno ,count(*) from emp group by deptno having count(*) = 0;

157) List the No.of emp’s and Avg salary within each department for each job.

A) select count(*),avg(sal),deptno,job from emp group by deptno,job;

158) Find the maximum average salary drawn for each job except for ‘President’.

A) select max(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.

A) select * from emp where sal = (select max(sal) from emp) and comm. is not null;

160) List the Name, Job and Salary of the emps who are not belonging to the

department 10 but who have the same job and Salary as the emps of dept 10.

A) 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);