Complex Sql Queries - Part 2

1. Display the names of the employees who are working in the company for The past 5 years.
      SELECT ename
      FROM emp
      WHERE TO_CHAR (sysdate,'YYYY')-TO_CHAR(hiredate,'YYYY')>=5;

2. Display your age in days.
      SELECT to_date (sysdate)-to_date ('01-apr-96')
      FROM dual;

3. Display your age in months.
      SELECT months_between (sysdate,'01-apr-96')
      FROM dual;

4. Display the 10th record of emp table without using group by and rowid?
      SELECT *
      FROM EMP
      WHERE ROWNUM<11
      MINUS
      SELECT * FROM EMP WHERE ROWNUM <10;

5. Display the various jobs and total number of employees within each job Group.
      SELECT job,
      COUNT (job)
     FROM EMP
     GROUP BY job;

6. Display the depart numbers and max salary for each department.
      SELECT deptno,
      MAX (sal)
     FROM EMP
     GROUP BY deptno;

7. Display the depart numbers with more than three employees in each dept.
      SELECT deptno,
     COUNT (deptno)
     FROM EMP
     GROUP BY deptno
     HAVING COUNT (*)>3;

8. Display the name of the employee who earns the highest salary.
      SELECT ename
     FROM emp
     WHERE sal=
     (SELECT MAX (sal) FROM emp);

9. Display the names of the employees who earn the highest salary in their respective departments.
     SELECT ename,
                    sal,
                    deptno
     FROM EMP
     WHERE sal IN
     (SELECT MAX (sal) FROM EMP GROUP BY deptno);

10. Find out the top 5 earners of the company?
     SELECT DISTINCT SAL
     FROM EMP E
    WHERE 5>=
    (SELECT COUNT (DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)
    ORDER BY SAL DESC;