Useful SQL Queries

Useful SQL Queries,
1. To Find employee experience
Sql>.select eno, ename, months_between (sysdate, doj)/12 from EMP

2. To Find ages of employees.
Sql>select eno, ename, months_between (sysdate, DOB)/12 from EMP.

3. To find recently hired employee.
Sql>select eno, ename, doj from EMP where doj= (select max (doj) from EMP).

4. To place * at recently hired employee.
Sql>select'*', doj from EMP where doj= (select max (doj) from EMP)

5. To find out maximum package offered employee.
Sql>select eno, ename, sal from EMP where sal= (select max (sal) from EMP)

6. To find only repeated duplicate records.
Sql>select * from EMP where eno in (select eno from EMP group by eno having count (*)>1)

7. To find employee information joined in a particular month.
Sql>select eno, ename from EMP where to_char (doj,'mon') ='aug'

8. Display last 3 salaries?
Sql>select * from (select * from EMP order by sal asc) where rownum<=3

9. Display Top 3 salaries
Sql>select * from (select * from EMP order by sal desc) where rownum<=3

10. Display nth max salary
sql>select empno,ename,sal,rownum from (select empno,ename,sal,rownum from emp order by sal desc)group by empno,ename,sal,rownum having rownum=n

11. To display emp names along with manager names.
Sql>select e.ename, m.ename MGR from EMP e, EMP m where e.mgr=m.empno.