SQL QUERIES TO PRACTICE4

SQL-QUERIES:

181) List the empno,ename,sal,TA30%,DA 40%,HRA 50%,GROSS,LIC,PF,net,deduction,net allow and net sal in the ascending order of the net salary.?

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 ‘’;

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;

201) List the mgrs of dept 10 or 20.
A) select * from emp where job = ‘MANAGER’ and (deptno = 10 or deptno =20);

202) List the emps joined in jan with salary ranging from 1500 to 4000.
A) select * from emp where to_char(hiredate,’MON’) = ‘JAN’ and sal
between 1500 and 4000;

203) List the unique jobs of dept 20 and 30 in desc order.
A) select distinct job from emp where deptno in (20,30) order by job desc;

204) List the emps along with exp of those working under the mgr whose number is starting with 7 but should not have a 9 joined before 1983.
A) select * from emp where (mgr like '7%' and mgr not like '%9%')
and to_char(hiredate,'YY') < '83';

205) List the emps who are working as either mgr or analyst with the salary ranging from 2000 to 5000 and with out comm.
A) select * from emp where (job in (‘MANAGER’ ,’ANALYST’) ) and sal between 2000 and 5000 and comm is null;

206) List the empno,ename,sal,job of the emps with /ann sal <34000 but receiving some comm. Which should not be>sal and desg should be sales man working for dept 30.
A) select empno,ename,sal,job from emp where
12*(sal+nvl(comm,0)) < 34000 and comm is not null and comm<sal and job = 'SALESMAN' and deptno = 30;

207) List the emps who are working for dept 10 or 20 with desgs as clerk or analyst with a sal is either 3 or 4 digits with an exp>8ys but does not belong to mons of mar,apr,sep and working for mgrs &no is not ending with 88 and 56.
A) select * from emp where
deptno in (10,20) and
job in ('CLERK','ANALYST') and
(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 workingunder 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));

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) select e.deptno,e.empno,e.ename,e.sal,d.dname,d.loc,s.grade from emp e,salgrade s,dept d
where e.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) select 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) select * from emp where job = (select job from emp where ename = 'SMITH');

220) List the emps whose job is same as either allen or sal>allen.
A) select * 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) select * 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) select * from emp where sal>(select sal from emp where ename = ‘BLAKE’);

223) List the dept 10 emps whose sal>allen sal.
A) select * 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)select * 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) select 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) select * 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) select * 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) select * 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) select * from emp where sal in
(select sal from emp e where emp.empno <> e.empno);

230) Sal of any clerk of emp1 table.
A) select * 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) select * 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) select * 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) select * from emp5;

235) List the highest paid emp.
A) select * from emp where sal in (select max(sal) from emp);

236) List the details of most recently hired emp of dept 30.
A) select * 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) select * 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)select * from emp where sal in
(select max(sal) from emp where mgr in
(select empno from emp where ename = 'KING'));