Complex SQL Queries

1. Display Salary in Words.
    SELECT ename,
                   sal,
               TO_CHAR(to_date(sal,'jsp'),'jsp')"in words"
    FROM emp;

2. Find the 3rd MAX salary in the emp table.
    SELECT DISTINCT sal
    FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e1.sal <= e2.sal);

3. Find the 3rd MIN salary in the emp table.
     SELECT DISTINCT sal
     FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 where e1.sal >= e2.sal);

4. Select FIRST n records from a table.
     SELECT * FROM emp WHERE rownum <= &n;

5. Select LAST n records from a table.
     SELECT * FROM emp
     MINUS
    SELECT * FROM emp WHERE rownum <=
   (SELECT COUNT(*) - &n FROM emp);

6. How to get 3 Max salaries?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
   (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal <= b.sal  )
   ORDER BY a.sal DESC;

7. How to get 3 Min salaries ?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

8. How to get nth max salaries?
   SELECT DISTINCT hire_date
   FROM emp a
   WHERE
   &n =
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal );