pseudo column ,rowid

PSEUDO COLUMNS

==============

Examples of PSEUDO COLUMNS are: ROWNUM, ROWID, NEXTVAL, CURRVAL ETC.

CREATE TABLE pseudo_demo(col1 number(4));

select rownum from pseudo_demo;

select sysdate from dual;

select user from dual;

SELECT ROWNUM, empno, ename, sal FROM emp;

SELECT ROWNUM, empno, ename FROM emp ORDER BY empno DESC;

SELECT ROWNUM, empno, ename FROM emp ORDER BY ename DESC;

SELECT ROWNUM, empno, ename FROM emp WHERE deptno IN(10,20) ORDER BY ename DESC;

ROWNUM -> can be used display top n rows

SELECT ename, sal FROM (SELECT ename, sal FROM emp ORDER BY sal DESC)

WHERE ROWNUM <=5;

SELECT ROWNUM AS RANK, ename, sal FROM (SELECT ename, sal FROM emp ORDER BY sal DESC)

WHERE ROWNUM<=5;

SELECT rk, ename, sal FROM (SELECT ROWNUM AS RK, ename, sal FROM (

SELECT ename, sal FROM emp ORDER BY sal DESC))

WHERE rk=5;

INLINE view: When we write subquery in the place of table it is known as INLINE VIEW.

ROWID -> Uniquely identifies a row (physical storage address for each row)

SELECT ROWID, empno, ename FROM emp;

SELECT empno, ename FROM emp WHERE deptno=10;

SELECT empno, ename FROM emp WHERE ROWID='AAAS1RAAEAAAAI2AAA';

Uses of ROWID:

1. it is useful to search the data fastly when we used in where clause.

2. it can also be useful to display duplicate rows, to display non-duplicate rows,

to delete duplicate rows.

SELECT * FROM (SELECT rownum AS rank, empno, ename, SAL FROM emp) WHERE rank=2;

SELECT * FROM emp WHERE rownum<=2;

SELECT * FROM emp WHERE rownum<=5;

SELECT * FROM emp WHERE rownum=1;

SELECT * FROM emp WHERE rownum=2;

SELECT * FROM emp WHERE rownum>1;

SELECT * FROM emp WHERE ROWNUM <= 5 ORDER BY sal DESC;

SELECT empno, ename, job, deptno, rownum FROM emp;

SELECT empno, ename, job, deptno, sal, rownum FROM emp WHERE deptno=10;

SELECT empno,ename,job,deptno,sal, rownum FROM emp WHERE deptno=10

ORDER BY sal DESC;

SELECT ename, rowid FROM emp;

rowid:

10 accounting new york  -> rowid1

10 accounting new york  -> rowid2

drop table rowid_demo1;

CREATE TABLE ROWID_DEMO1(DEPTNO NUMBER(4), DNAME VARCHAR2(20), LOC VARCHAR2(20));

INSERT INTO ROWID_DEMO1 VALUES(10,'ACCOUNTING','NEW YORK');

INSERT INTO ROWID_DEMO1 VALUES(10,'ACCOUNTING','NEW YORK');

DESC ROWID_dEMO1; 

SELECT ROWID, DEPTNO, DNAME, LOC FROM ROWID_DEMO1;

SELECT * FROM ROWID_DEMO1 WHERE ROWID='AAAS7DAAEAAAAZTAAA';

SELECT * FROM rowid_demo1 WHERE DEPTNO=10;