Pseduo Columns:

 Pseduo Columns:

***************

-->Automatically filled by oracle.

-->A Pseduo columns behaves like a table column,but it is not actually stored in the table.

-->You can select from Pseduo columns,but you can not insert,update and delete their values.   

SQL:

*****

-->USER

-->SYSDATE

-->SYSTIMESTAMP

-->CURRVAL

-->NEXTVAL

-->ROWID

-->ROWNUM

PL/SQL:

*******

-->SQLCODE

-->SQLERRM

-->NEW

-->OLD

USER,UID:

*********

-->SELECT USER,UID FROM DUAL;

SYSDATE:

********

-->It displays the system date.

EX:

***

SELECT SYSDATE FROM DUAL;

SYSTIMESTAMP:

*************

-->It displays the date and time

SELECT SYSTIMESTAMP FROM DUAL;

CURRVAL:

********

-->It returns current value of the sequence.

SYNTAX:

*******

-->SEQUENCENAME.CURRVAL

NEXTVAL:

********

-->It returns the increment value of the sequence.

SYNTAX:

*******

-->SEQUENCENAME.NEXTVAL

-->both CURRVAL and NEXTVAL can be used only in 

   -->SELECT list of a SELECT statement

   -->VALUES clause of and INSERT statement.

   -->SET caluse of an UPDATE statement.

EX:

***

CREATE TABLE DEPT_DEMO

(

DNO NUMBER CONSTRAINT DNO_PK PRIMARY KEY,

DNAME VARCHAR2(20),

LOC   VARCHAR2(20)

);

/

CREATE SEQUENCE DEPT_DNO_SEQ

INCREMENT BY -1

START WITH 10

MINVALUE 0

MAXVALUE 10

NOCACHE

NOCYCLE

;

/

INSERT INTO DEPT_DEMO VALUES (DEPT_DNO_SEQ.NEXTVAL,'HR','HYD');

/

INSERT INTO DEPT_DEMO VALUES (DEPT_DNO_SEQ.NEXTVAL,'SALES','MUM');

/

ROWID:

******

-->ROWID is a pseduo column in a table which store and return row address in hexadecimal format.

-->In the database,each row has a unique address.

-->The ROWID pseudocolumn returns the address of the row.

-->Generated automatically while inserting records into a table

-->ROWID is alphanumeric.

-->ROWID is the permanent unique identifier in the database.

-->A user can access a row quickly and easily using its ROWID.

-->ROWID can also be used to delete the duplicate records from a table.

-->It comprises of Object id, Data file id, Block id & Record id.  It is Re usable.

EX:

***

SELECT ROWID ROW_ID,EMPNO,ENAME,SAL FROM EMP1;

/

INSERT INTO EMP1 VALUES(7839,'KING','PRESIDENT','','17-NOV-1981',5000,'',10);

/

INSERT INTO EMP1 VALUES(7369,'B','CLERK',NULL,SYSDATE,2000,200,30);

/

SELECT *

FROM EMP1 E1

WHERE ROWID=(SELECT MIN(ROWID) FROM EMP1 E2 WHERE E2.EMPNO=E1.EMPNO);

/

--REMOVING DUPLICATE RECORDS

DELETE FROM TABLENAME 

WHERE ROWID NOTIN 

(

SELECT MIN(ROWID)

FROM TABLENAME  

GROUP BY COLUMNNAME);

/

DELETE FROM EMP1 

WHERE ROWID NOT IN 

(SELECT MIN(ROWID)

 FROM EMP1 

 GROUP BY EMPNO

);

/

DELETE FROM TABLENAME1  ALIASNAME

WHERE ROWID NOTIN 

(

SELECT MIN(ROWID)

FROM TABLENAME2  ALIASNAME

WHERE TABLENAME2.ALIASNAME=TABLENAME1.ALIASNAME);

/

DELETE FROM EMP1 E1

WHERE ROWID NOT IN 

(SELECT MIN(ROWID)

 FROM EMP1 E2

 WHERE E1.EMPNO=E2.EMPNO

 );

/

--Retrieving Duplicate rows from Table

SELECT EMPNO,COUNT(*)

FROM EMP1

GROUP BY EMPNO

HAVING COUNT(*)>1;

/

select po_header_id,count(*)

from po_lines_all

where rownum<=1000

group by po_header_id

having count(*)>1;

/

SELECT EMPNO,ENAME,SAL FROM EMP1 E1 WHERE

EXISTS ( SELECT COUNT(*) FROM EMP1 E2 WHERE E1.EMPNO =E2.EMPNO HAVING COUNT(*) > 1);

/

--Retrieve the rows which r not duplicated.

SELECT EMPNO,ENAME,SAL FROM EMP1 E1 WHERE

EXISTS ( SELECT COUNT(*) FROM EMP1 E2 WHERE E1.EMPNO =E2.EMPNO HAVING COUNT(*) = 1);

/

--Retrieve all the rows except Last Record

SELECT ROWID ROW_ID,EMPNO,ENAME,SAL,DEPTNO FROM EMP1

WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP1);      

/

--Retrieve all the rows except FIRST Record

SELECT ROWID ROW_ID,EMPNO,ENAME,SAL,DEPTNO FROM EMP1

WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP1);  

/

--Modify all rows except First row in Table.

UPDATE EMP1 SET SAL=SAL+100 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP1);

/

ROWNUM:

*******

-->Generated sequences for query output.

-->It is an dynamic value automatically retrieved along with Select statement output.

-->ROWNUM is numeric.

-->ROWNUM is temporary.It won't be stored in database.

EX:

***

SELECT ROWNUM,ENAME,SAL FROM EMP;

/

SELECT ROWID ROW_ID,ROWNUM,ENAME,SAL FROM EMP;

/

SELECT ROWNUM,ROWID ROW_ID,E.* FROM EMP E;

/

SELECT ROWNUM,ENAME,SAL FROM EMP ORDER BY SAL;

/

SELECT ROWNUM,ENAME,SAL FROM EMP ORDER BY SAL DESC;

/

SELECT RPAD('MOHAN',ROWNUM) FROM EMP WHERE ROWNUM<=5 ORDER BY ROWNUM ASC;

/

SELECT RPAD('MOHAN',ROWNUM) FROM EMP WHERE ROWNUM<=5 ORDER BY ROWNUM DESC;

/

SELECT ROWNUM,LPAD('*',ROWNUM,'*') FROM EMP WHERE ROWNUM<=5;

/

SELECT ROWNUM,LPAD('&STR',ROWNUM,'*') FROM EMP WHERE ROWNUM<=LENGTH('&STR');

/

SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<6 ORDER BY SAL DESC;  --THIS IS WRONG WAY

/

SELECT ROWNUM,ENAME,JOB,SAL FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<6; --PROPER WAY

/

--Retrieving Top 5 Highly paid Employees

SELECT ROWNUM,ENAME,JOB,SAL FROM 

(SELECT   ROWNUM ,ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC)

WHERE ROWNUM<=5;

/

--Retrieving Nth maximum salaried employ details 

SELECT ROWNUM,ENAME,JOB,SAL FROM 

(SELECT   ROWNUM ,ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC)

GROUP BY ROWNUM ,ENAME,JOB,SAL

HAVING ROWNUM=&N;

/

--Retrieving EVEN rows

SELECT ROWNUM,ENAME,JOB,SAL 

FROM EMP

GROUP BY ROWNUM,ENAME,JOB,SAL 

HAVING MOD(ROWNUM,2)=0 ORDER BY ROWNUM;

/

--Retrieving ODD rows

SELECT ROWNUM,ENAME,JOB,SAL 

FROM EMP

GROUP BY ROWNUM,ENAME,JOB,SAL 

HAVING MOD(ROWNUM,2)=1 ORDER BY ROWNUM;

/