What is Pseudocolumn in Oracle SQL? Learn with Examples

We offer you a brighter future with FREE online courses - Start Now!!

In this tutorial, we will learn more about the Pseudo columns which we have in SQL. Let us dive into the world of pseudo columns.

Demo Database

Let us now view our demo database location.

Query:

USE DataFlair;
SELECT * FROM Location;

Output:

SQL Demo Database

What are Pseudo Columns in SQL?

Pseudo Columns are columns that can be recovered by using queries but do not exist on the table physically. They are the alternate forms of variables in the high-level languages.

Here we can call various pseudo columns and we will get the required result from the system.

We can also compare the Pseudo column to the functions which do not have any arguments. When we use pseudo columns we need to take care that we can’t perform any update, delete or insert queries on these columns.

Some of the important Pseudo Columns are as follows:

1. NEXTVAL in SQL

With the help of Nextval pseudo column we get the flexibility to increment the sequence. We can then access the next value and use it for inserting or any other query on our database. The syntax of the Nextval pseudo column is as follows.

Syntax:

INSERT INTO tableName VALUES (sequence.Nextval(),value1,value2,....);

Example: Let us now modify our location database and insert the next value by using the NEXTVAL pseudo column.
Query:

USE DataFlair;
INSERT INTO location VALUES (location_id.Nextval(),'Noida',5);
SELECT * from the location;

Output:

SQL NextVal Example

2. CURRVAL in SQL

When we use the Currval pseudo column we get the flexibility to find the current value of our sequence. We can then access the current value and use it for functions and operations in our queries.

Syntax:

SELECT sequence.CURRVAL() FROM tableName;

Example: Let us now find the current value of the location id’s that is being used in our location database.
Query:

USE DataFlair;
SELECT location_id.CURRVAL as CURRVAL from location ;

Output:

SQL Currval

3. SYSDATE in SQL

With the help of Sysdate pseudo column we can find the current date of our system. This helps us to run a quick check on our system.

Syntax:

SELECT SYSDATE() as an alias;

Example: Let us now find the current date of the system we are using right now.
Query:

USE DataFlair;
SELECT SYSDATE() as System_Date;

Output:

SQL Sysdate

4. SQL ROWNUM

We use the ROWNUM to get the desired number of rows in the order they are inserted in the database. The syntax of the Rownum pseudo column is as follows.

Syntax:

SELECT * FROM tableName where ROWNUM condition;

Example: Let us now find the data from the database such that the ROWNUM is equal to the value 2.
Query:

USE DataFlair;
SELECT * from location where ROWNUM=2;

Output:

SQL rownum

5. ROWID in SQL

We use the ROWID pseudo column in our queries to find the address of the row which is the result of any particular query. The syntax and usage of the ROWID pseudo column are as follows.

Syntax:

SELECT rowid(columnName) as alias from tableName;

Example: Let us now find the addresses of each row in our database location.
Query:

USE DataFlair;
SELECT rowid(location_id) as address from location;

Output:

Rowid in SQL

6. USER in SQL

We use the USER pseudo column to pull the name of the user of our database. With the help of the USER pseudo column, we can find who is currently accessing our database. This helps us to increase the security of our system and monitor it, to avoid any illegal access to our database.

Syntax:

SELECT USER() AS alias;

Example: Let us now find the user who is accessing the DataFlair database on our system.
Query:

SELECT USER() AS UserName;

Output:

User in SQL

Uses of Pseudo Columns in SQL

Some of the important uses of the Pseudo Columns are as follows:

  • Used to get the system-related data.
  • Helps us to get the metadata of our database.
  • Allow us to get the details of access rights on our database.
  • Helps us to keep a check on our database.

Summary

In this tutorial, we have discussed the Pseudo columns in SQL.

Pseudo columns are columns of the respective table which can’t be used for the updation and deletion. But the values of the Pseudo columns can be used for functions, operations, and insertions.

We have seen each of the pseudo columns in detail with syntax and examples of each. For any queries drop it in the comment section of the tutorial.

If you are Happy with DataFlair, do not forget to make us happy with your positive feedback on Google

follow dataflair on YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *