PSEUDO COLUMNS

PSEUDO COLUMNS

--Its not a column inside the table but it act as a column.

SYSDATE 
SYSTIMESTAMP
USER 
UID
ROWNUM
ROWID
NEXTVAL
CURRVAL

SYSDATE -- Its display the sytem date 

SQL> SELECT sysdate FROM DUAL;

SYSDATE
---------
25-SEP-17

SYSTIMESTAMP -- It display the timestamp date with time minutes seconds meridian.

SQL> SELECT systimestamp FROM DUAL;

SYSTIMESTAMP
------------------------------------------
25-SEP-17 02.34.13.201000 PM +05:30

USER -- It display the current user.

SQL> SELECT user FROM DUAL;

USER
------------------------------
HR

UID -- It display the user id.

SQL> SELECT uid FROM DUAL;

       UID
----------
        43

NEXTVAL & CURRVAL 

These are the sequence attributes 

CREATE SEQUENCE s1
START WITH 1
INCREMENT BY 1
MAXVALUE 10

Sequence Created

SQL> SELECT S2.NEXTVAL FROM DUAL;
   NEXTVAL
----------
         1
SQL> SELECT S2.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         2
SQL> SELECT S2.CURRVAL FROM DUAL;
   CURRVAL
----------
         2
SQL> SELECT S2.NEXTVAL FROM DUAL;
   NEXTVAL
----------
         3

ROWNUM

-Its genertes only at run time and always starts with 1.
-Roll number never be stored in database.
-To give row numbers for the output result.

SQL> SELECT first_name, salary,rownum FROM Employees WHERE Department_id= 30;

FIRST_NAME               SALARY     ROWNUM
-------------------- ---------- ----------
Den                       11000          1
Alexander                  3100          2
Shelli                     2900          3
Sigal                      2800          4
Guy                        2600          5
Karen                      2500          6

ROWID

-It is a unique address generated by oracle when user inserts a new row in a table.
-It is permanently stored in a database. 
-Rowid is unique we can retrieve specific row using their rowid.
-Rowid cannot be modified;
-if you delete the row means the row id also deleted
-if you update the data means the row_id is the same for the row.
-rowid length is 18 charecters.

SQL> SELECT first_name, salary,rowid FROM Employees WHERE Department_id= 30;

FIRST_NAME               SALARY ROWID
-------------------- ---------- ------------------
Den                       11000 AAAEAbAAEAAAADNAAO
Alexander                  3100 AAAEAbAAEAAAADNAAP
Shelli                     2900 AAAEAbAAEAAAADNAAQ
Sigal                      2800 AAAEAbAAEAAAADNAAR
Guy                        2600 AAAEAbAAEAAAADNAAS
Karen                      2500 AAAEAbAAEAAAADNAAT