Tough SQL Queries - Part 7

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) s
elect * from emp where job = ‘MANAGER’;
183) List the emps who are either clerks or managers.
A) s
elect * 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) s
elect * 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) s
elect * from emp where ename like ‘M%’ and length (ename) = 5;
191) List the emps end with ‘H’ all together 5 chars.
A) s
elect * from emp where ename like ‘%H’ and length (ename) = 5;
192) List names start with ‘M’.
A) s
elect * from emp where ename like ‘M%’;
193) List the emps who joined in the year 81.
A) s
elect * 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) s
elect * 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) s
elect * 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) s
elect * 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) s
elect * 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) s
elect * 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) s
elect * from emp where job = ‘CLERK’ and (months_between(sysdate,hiredate) /12) > 8;
201) List the mgrs of dept 10 or 20.
A) s
elect * 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) s
elect * from emp where to_char(hiredate,’MON’) = ‘JAN’ and sal
be
tween 1500 and 4000;
203) List the unique jobs of dept 20 and 30 in desc order.
A) s
elect 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) s
elect * 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) s
elect * 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) s
elect 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) s
elect * 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 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) s
elect * 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) s
elect e.empno,e.ename,e.deptno,d.loc from emp e ,dept d
where e.deptno = d.deptno ;