Sql Server Interview Questions and Answers Part 7

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);





Subscribe

Receive Quality Tutorials Straight in your Inbox by submitting your Email below:

Delivered by FeedBurner

Protected by Copyscape Duplicate Content Checker