Tough SQL Queries - Part 6

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) s
elect 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) s
elect * 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) s
elect * 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) s
elect * 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) s
elect * 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) s
elect 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) s
elect 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) s
elect 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) s
elect * 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) s
elect 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);
161) List the Deptno, Name, Job, Salary and Sal+Comm of the SALESMAN who are earning maximum salary and commission in descending order.
A)se
lect deptno,name,job,sal,sal+nvl(comm.,0) from emp where job = ‘SALESMAN’ and sal in (select max(sal+nvl(comm.,0)) from emp where comm. is not null)
Order by (sal +nvl(comm.,0)) desc;
162) List the Deptno, Name, Job, Salary and Sal+Comm of the emps who earn the second highest earnings (sal + comm.).
A) s
elect deptno,ename,sal,job,sal+nvl(comm,0) from emp e where 2 = (select count(distinct sal+nvl(comm,0)) from emp where (e.sal+nvl(comm.,0))<(sal+nvl(comm.,0));
163) List the Deptno and their average salaries for dept with the average salary less than the averages for all department
A) select deptno,avg(sal) from emp group by deptno
ha
ving avg(sal) <(select avg(Sal) from emp);
164) List out the Names and Salaries of the emps along with their manager names and salaries for those emps who earn more salary than their Manager.
A) s
elect w.ename,w.sal,m.ename,m.sal from emp w,emp m
where w.mgr = m.empno and w.sal > m.sal;
165) List out the Name, Job, Salary of the emps in the department with the highest average salary.
A) s
elect * from emp where deptno in
(select deptno from emp e
having avg(sal) =(select max(avg(sal)) from emp group by deptno)
group by deptno);
166) List the empno,sal,comm. Of emps.
A) s
elect 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) s
elect * 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) s
elect unique deptno ,job from emp ;
171) Display the details of the blake.
A) s
elect * from emp where ename = ‘BLAKE’;
172) List all the clerks.
A) s
elect * from emp where job = ‘CLERK’;
173) list all the employees joined on 1st may 81.
A) s
elect * 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) s
elect e.empno,e.ename,e.sal,e.deptno from emp where e.deptno = 10
or
der by e.sal asc;
175) List the emps whose salaries are less than 3500.
A) s
elect * from emp where sal <3500;
176) List the empno,ename,sal of all the emp joined before 1 apr 81.
A) s
elect 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) s
elect * 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) s
elect 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 ;
179) List the empno,ename,hiredate,current date & exp in the ascending order of the exp.
A) s
elect empno,ename,hiredate,(select sysdate from dual),((months_between(sysdate,hiredate))/12) EXP
from emp
order by EXP asc;
180) List the emps whose exp is more than 10 years.
A) select * from emp where ((months_between(sysdate,hiredate))/12) > 10;