Tough SQL Queries - Part 8

211) List the empno,ename,loc,dname of all the depts.,10 and 20.
A) s
elect 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) s
elect 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) s
elect 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) s
elect 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) s
elect * 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));
216) List the emps with loc and grade of accounting dept or the locs dallas or Chicago with the grades 3 to 5 &exp >6y
A) s
elect e.deptno,e.empno,e.ename,e.sal,d.dname,d.loc,s.grade from emp e,salgrade s,dept d
wh
eree.deptno = d.deptno and e.sal between s.losal and s.hisal
and s.grade in (3,5)
and ((months_between(sysdate,hiredate))/12) > 6
and ( d.dname = 'ACCOUNTING' or D.loc in ('DALLAS','CHICAGO'))
217) List the grades 3 emps of research and operations depts.. joined after 1987 and whose names should not be either miller or allen.
A) s
elect e.ename from emp e ,dept d,salgrade s
where e.deptno = d.deptno and d.dname in ('OPERATIONS','RESEARCH') and e.sal between s.losal and s.hisal
and e.ename not in ('MILLER','ALLEN')
and to_char(hiredate,'YYYY') >1987;
218) List the emps whose job is same as smith.
A) s
elect * from emp where job = (select job from emp where ename = 'SMITH');
219) List the emps who are senior to mi
220) List the emps whose job is same as either allen or sal>allen.
A) s
elect * from emp
where job = (select job from emp where ename = 'ALLEN')
or sal > (select sal from emp where ename = 'ALLEN');
221) List the emps who are senior to their own manager.
A) s
elect * from emp w,emp m where w.mgr = m.empno and
w.hiredate < m.hiredate;
222) List the emps whose sal greater than blakes sal.
A) s
elect * from emp
where sal>(select sal from emp where ename = ‘BLAKE’);
223) List the dept 10 emps whose sal>allen sal.
A) s
elect * from emp where deptno = 10 and
sal > (select sal from emp where ename = 'ALLEN');
224) List the mgrs who are senior to king and who are junior to smith.
A)se
lect * from emp where empno in
(select mgr from emp
where hiredate<(select hiredate from emp where ename = 'KING' )
and hiredate > (select hiredate from emp where ename = 'SMITH')) and mgr is
not null;
225) List the empno,ename,loc,sal,dname,loc of the all the emps belonging to king dept.
A) s
elect e.empno,e.ename,d.loc,e.sal,d.dname from emp e,dept d
where e.deptno=d.deptno and e.deptno in
(select deptno from emp where ename = 'KING'and emp.empno <> e.empno);
226) List the emps whose salgrade are greater than the grade of miller.
A) s
elect * from emp e,salgrade s
where e.sal between s.losal and s.hisal and s.grade >
(select s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal and e.ename = 'MILLER') ;
227) List the emps who are belonging dallas or Chicago with the grade same as adamsor exp more than smith.
A) s
elect * from emp e ,dept d,salgrade s
where e.deptno= d.deptno and d.loc in ('DALLAS','CHICAGO') and e.sal between s.losal and s.hisal and
(s.grade in (select s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal and e.ename = 'ADAMS')
or months_between (sysdate,hiredate) > (select months_between(sysdate,hiredate) from emp where ename = 'SMITH')) ;
228) List the emps whose sal is same as ford or blake.
A) s
elect * from emp where sal in (select sal from emp e where e.ename in ('FORD','BLAKE')and emp.empno <> e.empno);
229) List the emps whose sal is same as any one of the following.
A) s
elect * from emp where sal in
(select sal from emp e where emp.empno <> e.empno);
230) Sal of any clerk of emp1 table.
A) s
elect * from emp where job = ‘CLERK’;
231) Any emp of emp2 joined before 82.
A) select * from emp where to_char(hiredate,'YYYY') < 1982;
232) The total remuneration (sal+comm.) of all sales person of Sales dept belonging to emp3 table.
A) s
elect * from emp e
where (sal+nvl(comm,0)) in
(select sal+nvl(comm,0) from emp e,dept d where e.deptno=d.deptno
and d.dname = 'SALES'and e.job = 'SALESMAN');
233) Any Grade 4 emps Sal of emp 4 table.
A) s
elect * from emp4 e,salgrade s where e.sal between s.losal and s.hisal and s.grade = 4;
234) Any emp Sal of emp5 table.
A) s
elect * from emp5;
235) List the highest paid emp.
A) s
elect * from emp where sal in (select max(sal) from emp);
236) List the details of most recently hired emp of dept 30.
A) s
elect * from emp where hiredate in
(select max(hiredate) from emp where deptno = 30);
237) List the highest paid emp of Chicago joined before the most recently hired emp of grade 2.
A) s
elect * from emp
where sal = ( select max(sal) from emp e,dept d where e.deptno =
d.deptno and d.loc = ‘CHICAGO’ and
hiredate <(select max(hiredate) from emp e ,salgrade s
where e.sal between s.losal and s.hisal and s.grade = 2))
238) List the highest paid emp working under king.
A)se
lect * from emp where sal in
(select max(sal) from emp where mgr in

(select empno from emp where ename = 'KING'));