SQL Query and Answers - Part5

101. List out all the employee name, job, and slary grade and department name for every one in the company except 'CLERK'. sort on salary display the highest salary.

select empno, ename, sal, dname, grade from emp e, dept d, salgrade s where e.deptno=.deptno and e.sal between s.losal and s.hisal and e.job<>'CLEAR" order by sal;

102. Display employee name, his job and his manager.  Display also employees who are without manager.

select e.ename, e.job, m.ename Manager from emp e, emp m where e.mgr = m.empno union select ename, job, 'no manager' from emp where mgr is null;

103. Find out the top 5 earner of company.

select * from emp e where 5 >  (selet count (*) from emp where sal > e.sal) order by sal desc;

104. Display the name of those employees who are getting highest salary.

select empno, ename, sal from emp where sal=(select max(sal) from emp);

105. Display those empoyees whose salary is equal to average of maximum and minimum.

select * from emp where sal=(select(max(sal)+min(sal))/2 from emp);

106. Display count of employees in each department where count greater than 3.

select deptno, count(*) from emp group by deptno having count(*)>3;

107. Display dname where at least 3 are working and display only dname.

            select dname from dept where deptno in (select deptno from emp.group by deptno having count(*)>3);

108. Display name of those managers name whose salary is more than average salary of company.

select ename, sal from emp where empno in ( select mgr from emp) and sal >(select avg(sal) from emp);

109  Display those managers name whose salary is more than an average salary of his employees.

select ename, saly from emp e where empno in (select mgr from emp) and e.sal>(select avg(sal) from emp where mgr=e.empno);

110. Display employee name, sal, comm and net pay for those employees whose net pay are greater than or equal to any othere employees salary of the company?

select ename, sal, comm, sal+nv(comm,0) netPay from emp where sal+nv(comm,0)>=any(select sal from emp);

111. Display those employees whose salary is less than his manager but more than salary of any other managers.

select * from emp e where sal<(select sal from emp where empno=emgr) and sal>any(select sal from emp where empno!=e.mgr);

112. Display all employees names with total sal of company with employee name.

           

113. Find out the last 5 (least) earner of the company?

select * from emp e where 5>(select count(*) from emp where sal<e.sal) order by sal;

114. Find out the numbe of employees whose salary is greater than there manager salary?

select count(*) from emp e whee sal > (select sal from emp whee empno = e.mgr);

115. Display those manager who are not working under president but they are working under any other manager?

select * from emp e where mgr in ( select empno from emp where ename <> 'KING;);

116. Display those employees who joined the company before 15th of the month?

select empno, ename from emp whee hiredate<(to_date('15-'|| to_char(hiredate,'mon) || '-' || to_char(hiredate,'yyyy')));

117. Delete those records where no of employee in a particular department is less than 3?     

delete from emp where deptno in ( select deptno emp group by deptno having count(*)>3);

118. List ename,job, annual sal,deptno,dname and grade who earn 30000 per year and who are not clerks

select e.ename,e.job, (e.sal+nvl(e.comm,0))*12, e.deptno,d.dname,s.grade from emp e, salgrade s, dept d  where e.sal betweem s.lsal and d.deptno and (e.sal+nvl(cmomm,0))*12> 30000 and e.job<> 'clerk';

119. Display name and salary of ford if his sal is eual to high sal of his grade.

select ename, sal from emp e where ename='FORD' and sal=(select hisal from salgrade where grade=(select grade from sallgrade where e.sal>=losal and e.sal<=hisal));

120.Find out the jon that was failed in the first half of 1983 and the same job that was failed during the same periond in 1984

121. find out the all employees who joined the company before thiere manager

select * from emp e where hiredata<(select hiredate from emp where empno=e.mgr);

122.list out the all employees byu name and number along with their manager's name and number also display 'No Manager'

who has no manager.

select e.empno,e.ename,m.empno manager,m.ename managername from emp e, emp m where e.mgr=m.empno

from emp e, emp m where e,mgr=m.empno

union

select empno,ename,mgr,'No Manager' from emp where mgr is null;

123 Find out the employees who earned the highest Sal in each job typed sort in descending sal order.

select * from emp e where sal=(select max(sal) from emp where job=e.job);

124  Find out the employees who earned the min sal for there job in ascending order

select * from emp e where sal=(select min(sal) from emp where job=e.job) order by sal;

125. find out the most recently hired employees in each dept order by hire date.

select * from emp e where hiredate=(select max(hiredate) from emp where deptno=e.deptno) order by hiredate;