To Display highest Nth salary of Employee:
--------------------------------------------------
To display 4th row of a table:
----------------------------------
To display alternate ODD rows in a table:--------------------------------------------------
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1234 SHASHI MANAGER 7839 10-JAN-13 4500 200 10
15 rows selected.
SQL> select * from emp A
2 where 4=(select count(B.sal) from emp B where A.sal <= B.sal)
3 order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
SQL> select * from emp order by sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
1234 SHASHI MANAGER 7839 10-JAN-13 4500 200 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
15 rows selected.
SQL> select * from emp A
2 where 5=(select count(B.sal) from emp B where A.sal <= B.sal)
3 order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
To display 4th row of a table:
----------------------------------
SQL>
1 Select * from emp where rownum<=4
2 minus
3* select * from emp where rownum<4 data-blogger-escaped-----------="" data-blogger-escaped----------="" data-blogger-escaped-02-apr-81="" data-blogger-escaped-20="" data-blogger-escaped-2975="" data-blogger-escaped-7566="" data-blogger-escaped-7839="" data-blogger-escaped-comm="" data-blogger-escaped-deptno="" data-blogger-escaped-empno="" data-blogger-escaped-ename="" data-blogger-escaped-hiredate="" data-blogger-escaped-job="" data-blogger-escaped-jones="" data-blogger-escaped-manager="" data-blogger-escaped-mgr="" data-blogger-escaped-sal="" data-blogger-escaped-sql=""> Select * from emp A
2 where 4=(select count(rowid) from emp B where A.rowid>=B.rowid)
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
To Display top N salaries in a employee table:
-----------------------------------------------------
Select * from (select * from emp order by sal desc )
2 where rownum<10 data-blogger-escaped-br="">
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
1234 SHASHI MANAGER 7839 10-JAN-13 4500 200 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
9 rows selected.
To Display Nth lowest salary of employee table:
----------------------------------------------- SQL> select * from emp A
2 Where 2=(Select count(sal) from emp B where A.sal>=B.sal)
3 order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
To display every 4th row in a table,suppose a table contains 21 rows,it has to display 4,8,12,16,20th rows ---------------------------------------------------------------------------------------------------------- SQL>Select * from emp where (rowid,0) in (select rowid,mod(rownum,4) from emp)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
To display alternates EVEN rows of a table. ------------------------------------------ SQL> select * from emp
where (rowid,0) in (select rowid,mod(rownum,2) from emp)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7 rows selected.
-----------------------------------------
SQL>select * from emp
2* where (rowid,1) in (select rowid,mod(rownum,2) from emp)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1234 SHASHI MANAGER 7839 10-JAN-13 4500 200 10
8 rows selected.
0 comments:
Post a Comment