51. Display those managers name whose salary is more than average salary of his EMPTABLE?
SELECT DISTINCT EMPTABLE.ENAME FROM EMPTABLE,EMPTABLE E WHERE E.SAL <(SELECT AVG(EMPTABLE.SAL) FROM EMPTABLE WHERE EMPTABLE.EMPTABLENO=E.MGR GROUP BY EMPTABLE.ENAME) AND EMPTABLE.EMPTABLENO=E.MGR;
52. Display employees name,sal,comm and net pay for those employees whose net pay is greter than or equal to any other employees of the company?
select ename,sal,comm,sal+nvl(comm,0) as NetPay from EMPTABLE where sal+nvl(comm,0) >any (select sal from EMPTABLE);
53. Display all employees names with total sal of company with each employees name?
SELECT ENAME,(SELECT SUM(SAL) FROM EMPTABLE) FROM EMPTABLE;
54. Find out the number of employees whose salary is greater than their manager salary?
SELECT E.ENAME FROM EMPTABLE ,EMPTABLE E WHERE EMPTABLE.EMPTABLENO=E.MGR AND EMPTABLE.SAL<E.SAL;
55. Display employees name and his salary whose salary is greater than highest average of department number?
SELECT SAL FROM EMPTABLE WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMPTABLE GROUP BY DEPTNO);
56. List all the employees who have at least one person reporting to them.
SELECT ENAME FROM EMPTABLE WHERE EMPTABLENO IN (SELECT MGR FROM EMPTABLE);
57. List the highest salary paid for each job.
SELECT JOB, MAX(SAL) FROM EMPTABLE GROUP BY JOB;
58. Find the most recently hired employees in each department.
SELECT * FROM EMPTABLE WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMPTABLE GROUP BY DEPTNO);
59. In which year did most people join the company? Display the year and the number of employees.
SELECT TO_CHAR(HIREDATE,’YYYY’) “YEAR”, COUNT(EMPTABLENO) “NO. OF EMPLOYEES” FROM EMPTABLE GROUP BY TO_CHAR(HIREDATE,’YYYY’) HAVING COUNT(EMPTABLENO) = (SELECT MAX(COUNT(EMPTABLENO)) FROM EMPTABLE GROUP BY TO_CHAR(HIREDATE,’YYYY’));
60. Which department has the highest annual remuneration bill?
SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) “COMPENSATION” FROM EMPTABLE GROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMPTABLE GROUP BY DEPTNO);