28. List the emps those who joined in 80’s.

A) select * from emp where to_char(hiredate,’yy’) like ‘8%’;

29. List the emps who does not belong to Deptno 20.
A) select * from emp where deptno not in (20); (OR)
B) select * from emp where deptno != 20; (OR)
C) select * from emp where deptno <>20; (OR)
D) select * from emp where deptno not like ‘20’;
30. List all the emps except ‘PRESIDENT’ & ‘MGR” in asc order of Salaries.
A) Select * from emp where job not in (‘PRESIDENT’,’MANAGER’) order
by sal asc;
B) select * from emp where job not like ‘PRESIDENT’ and job not like
‘MANAGER’ order by sal asc;
C) Select * from emp where job != ‘PRESIDENT’ and job <> ‘MANAGER’
order by sal asc;
31. List all the emps who joined before or after 1981.
A) select * from emp where to_char (hiredate,’YYYY’) not in (‘1981’); (OR)
B) select * from emp where to_char ( hiredate,’YYYY’) != ‘1981’; (OR)
C) select * from emp where to_char(hiredate,’YYYY’) <> ‘1981’ ; (OR)
D) select * from emp where to_char (hiredate ,’YYYY’) not like ‘1981’;
32. List the emps whose Empno not starting with digit78.
A) select * from emp where empno not like ‘78%’;
33. List the emps who are working under ‘MGR’.
A) select e.ename || ‘ works for ‘ || m.ename from emp e ,emp m where e.mgr =
m.empno ; (OR)
B) select e.ename || ‘ has an employee ‘|| m.ename from emp e , emp m where
e.empno = m.mgr;
51. List the Emps who are senior to their own MGRS.
A) select * from emp w,emp m where w.mgr = m.empno and
w.hiredate < m.hiredate ; (OR)
B) select * from emp w,emp m where w.empno= m.mgr and
w.hiredate> m.hiredate;
52. List the Emps of Deptno 20 whose Jobs are same as Deptno10.
A) select * from emp e ,dept d where d.deptno = 20 and e.deptno = d.deptno and
e.job in ( select e.job from emp e,dept d where e.deptno = d.deptno and d.deptno
=10);
53. List the Emps whose Sal is same as FORD or SMITH in desc order of Sal.
A)
Select * from emp where sal in (select sal from emp where ( ename = ‘SMITH’
or ename = ‘FORD’ )) order by sal desc;
54. List the emps Whose Jobs are same as MILLER or Sal is more than ALLEN.
A) select * from emp where job = (select job from emp where ename =
‘MILLER’ ) or sal>(select sal from emp where ename = ‘ALLEN’);
55. List the Emps whose Sal is > the total remuneration of the SALESMAN.
A) select * from emp where sal >(select sum(nvl2(comm,sal+comm,sal)) from
emp where job = ‘SALESMAN’);
56. List the emps who are senior to BLAKE working at CHICAGO & BOSTON.
A) select * from emp e ,dept d where d.loc in
(‘CHICAGO’,’BOSTON’) and e.deptno = d.deptno and
e.hiredate <(select e.hiredate from emp e where e.ename =
‘BLAKE’) ;
77. List the manage rno and the number of employees working for those mgrs in the
ascending Mgrno.
A) select w.mgr ,count(*) from emp w,emp m
where w.mgr = m.empno
group by w.mgr
order by w.mgr asc;

78. List the department,details where at least two emps are working
A) select deptno ,count(*) from emp group by deptno
having count(*) >= 2;

79. Display the Grade, Number of emps, and max sal of each grade.
A) select s.grade ,count(*),max(sal) from emp e,salgrade s where e.sal between
s.losal and s.hisal
group by s.grade;

80. Display dname, grade, No. of emps where at least two emps are clerks.
A) select d.dname,s.grade,count(*) from emp e,dept d,salgrade s where e.deptno =
d.deptno and
e.job = 'CLERK' and e.sal between s.losal and s.hisal group by d.dname,s.grade
having count(*) >= 2;

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.
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)
 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)
 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 ;
 (length(sal) in (3,4)) and
((months_between(sysdate,hiredate))/12)> 8 and
to_char(hiredate,'MON') not in ('MAR','SEP','APR') and
(mgr not like '%88' and mgr not like '%56');
208) List the empno,ename,sal,job,deptno&exp of all the emps belongs to dept 10 or
20 with an exp 6 to 10 y working under the same mgr with out comm. With a
job not ending irrespective of the position with comm.>200 with exp>=7y and
sal<2500 but not belongs to the month sep or nov working under the mgr whose
no is not having digits either 9 or 0 in the asc dept& desc dept
A)

209) List the details of the emps working at Chicago.

A) select * from emp where deptno in (select deptno from dept where dept.loc =
‘CHICAGO’);

210) List the empno,ename,deptno,loc of all the emps.

A) select e.empno,e.ename,e.deptno,d.loc from emp e ,dept d
where e.deptno = d.deptno ;

211) List the empno,ename,loc,dname of all the depts.,10 and 20.

A) select e.empno,e.ename,e.deptno,d.loc,d.dname from emp e ,dept d
where e.deptno = d.deptno and e.deptno in (10,20);

212) List the empno, ename, sal, loc of the emps working at Chicago dallas with an
exp>6ys.
A) select e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d
where e.deptno = d.deptno and d.loc in ('CHICAGO','DALLAS')
and (months_between(sysdate,hiredate)/12)> 6 ;

213) List the emps along with loc of those who belongs to dallas ,newyork with sal
ranging from 2000 to 5000 joined in 81.
A) select e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d
where e.deptno = d.deptno and d.loc in ('NEW YORK','DALLAS')
and to_char(e.hiredate,'YY') = '81' and e.sal between 2000 and 5000;
214) List the empno,ename,sal,grade of all emps.
A) select e.empno,e.ename,e.sal,s.grade from emp e ,salgrade s
where e.sal between s.losal and s.hisal ;

215) List the grade 2 and 3 emp of Chicago.

A) select * from emp where empno in
(select empno from emp e,salgrade s where e.sal between s.losal and
s.hisal and s.grade in (2,3));