28.Using ROWID,ROWNUM and MINUS

SQL
Administrator

SQL> select * from emp;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7839 KING       PRESIDENT            17-NOV-81       5000                    10       5100
  7698 BLAKE      MANAGER         7839 01-MAY-81                               30
  7782 CLARK      MANAGER         7839 09-JUN-81       2700                    10       2550
  7566 JONES      MANAGER         7839 02-APR-81                               20
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100
  7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       3100
  7369 SMITH      CLERK           7902 17-DEC-80        800                    20        900
  7499 ALLEN      SALESMAN        7698 10-MAY-11       1600        300         30       1700
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       1350
  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       1350
  7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30       1600

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20       1200
  7900 JAMES      CLERK           7698 03-DEC-81        950                    30       1050
  7934 MILLER     CLERK           7782 23-JAN-82       1500                    10       1400

14 rows selected.

SQL> select * from emp where rownum=1;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7839 KING       PRESIDENT            17-NOV-81       5000                    10       5100

SQL> select * from emp where rownum=5;

no rows selected

SQL> select * from emp where rownum<=5;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7839 KING       PRESIDENT            17-NOV-81       5000                    10       5100
  7698 BLAKE      MANAGER         7839 01-MAY-81                               30
  7782 CLARK      MANAGER         7839 09-JUN-81       2700                    10       2550
  7566 JONES      MANAGER         7839 02-APR-81                               20
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100

SQL> select rowid from emp;

ROWID

AALhb/AALAAAp2sAAG
AALhb/AALAAAp2sAAH
AALhb/AALAAAp2sAAI
AALhb/AALAAAp2sAAD
AALhb/AALAAAp2sAAJ
AALhb/AALAAAp2sAAB
AALhb/AALAAAp2sAAC
AALhb/AALAAAp2sAAE
AALhb/AALAAAp2sAAA
AALhb/AALAAAp2sAAK
AALhb/AALAAAp2sAAL

ROWID

AALhb/AALAAAp2sAAM
AALhb/AALAAAp2sAAF
AALhb/AALAAAp2sAAN

14 rows selected.

SQL> select rowid, empno, ename, sal from emp;

ROWID EMPNO ENAME SAL

AALhb/AALAAAp2sAAA 7839 KING 5000
AALhb/AALAAAp2sAAB 7698 BLAKE
AALhb/AALAAAp2sAAC 7782 CLARK 2700
AALhb/AALAAAp2sAAD 7566 JONES
AALhb/AALAAAp2sAAE 7788 SCOTT 3000
AALhb/AALAAAp2sAAF 7902 FORD 3000
AALhb/AALAAAp2sAAG 7369 SMITH 800
AALhb/AALAAAp2sAAH 7499 ALLEN 1600
AALhb/AALAAAp2sAAI 7521 WARD 1250
AALhb/AALAAAp2sAAJ 7654 MARTIN 1250
AALhb/AALAAAp2sAAK 7844 TURNER 1500

ROWID EMPNO ENAME SAL

AALhb/AALAAAp2sAAL 7876 ADAMS 1100
AALhb/AALAAAp2sAAM 7900 JAMES 950
AALhb/AALAAAp2sAAN 7934 MILLER 1500

14 rows selected.

SQL> select * from emp where rownum=5;

no rows selected

SQL> select * from emp where rowid=’AALhb/AALAAAp2sAAE’;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100

SQL> select * from emp;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7839 KING       PRESIDENT            17-NOV-81       5000                    10       5100
  7698 BLAKE      MANAGER         7839 01-MAY-81                               30
  7782 CLARK      MANAGER         7839 09-JUN-81       2700                    10       2550
  7566 JONES      MANAGER         7839 02-APR-81                               20
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100
  7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       3100
  7369 SMITH      CLERK           7902 17-DEC-80        800                    20        900
  7499 ALLEN      SALESMAN        7698 10-MAY-11       1600        300         30       1700
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       1350
  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       1350
  7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30       1600

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20       1200
  7900 JAMES      CLERK           7698 03-DEC-81        950                    30       1050
  7934 MILLER     CLERK           7782 23-JAN-82       1500                    10       1400

14 rows selected.

SQL> select * from emp where rownum<=5;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7839 KING       PRESIDENT            17-NOV-81       5000                    10       5100
  7698 BLAKE      MANAGER         7839 01-MAY-81                               30
  7782 CLARK      MANAGER         7839 09-JUN-81       2700                    10       2550
  7566 JONES      MANAGER         7839 02-APR-81                               20
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100

SQL> select * from emp where rownum<=5
2 minus
3 select * from emp where rownum<=4;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100

SQL> select * from emp where rownum<=10
2 minus
3 select * from emp where rownum<=9;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       1350

SQL> select * from emp where rownum<=10
2 minus
3 select * from emp where rownum<5;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SUM GENDER
  7369 SMITH      CLERK           7902 17-DEC-80        800                    20        900
  7499 ALLEN      SALESMAN        7698 10-MAY-11       1600        300         30       1700
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       1350
  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       1350
  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       3100
  7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       3100

6 rows selected.

Post Views: 632
Administrator

http://www.databaselearners.com

Related Posts

27.Indexes

Administrator

20.Functions (Date)

Administrator

1.Introduction to SQL

Administrator