Pseudocolumn
🗒 Also known as false column
It is a column which is present in a database in a database for
every table.
If we want it, we need to invoke it
Types of pseudocolumn
1. RowID
2. RowNum
Row ID
It is a pseudocolumn which is present in a database for every table.
characteristics of Row ID
It is a unique
It is not null
It is a combination of unique and not null
RowId can act as primary key not as foreign key
RowId is generated at the time of creation of table or Insertion of data
Row id is static in nature
RowNum
It is a preudocolumn which is present in a database for every table
Characteristics of RowNum
Row num is a number or integer
Row num always starts with 1
Row num is generated at the time of execution
Pseudocolumn 1
Rownum is dynamic in nature.
write a query to display top 5 rows of employee table
select rownum,emp.*
from emp
where rownum<6;
select rownum,emp.*
from emp
where rownum between 1 and 5;
select rownum,emp.*
from emp
where rownum<=5;
select rownum,emp.*
from emp
where rownum In(1,2,3,4,5);
write a query to display top 10 rows of emp table
select rownum,emp.*
from emp
where rownum<=10;
write a query to display exactly first row of emp table
select rownum,emp.*
from emp
where rownum=1;
write a query to display exactly 2nd row of emp table
select rownum,emp.*
from emp
where rownum=2;
Pseudocolumn 2
output is no rows selected (and this is the reson why rownum is dynamic in
nature)
thus we provide alias name for rownum so that it can be treated as normal
column instead of pseudocolumn
but still we get error
select rownum as r1,emp.*
from emp
where r1=2;
hence for that we need to take from clause twice in the query
select *
from (select rownum as r1,emp.*
from emp)
where r1=2;
write a query to display exactly 5th row of emp table
select *
from (select rownum as r1,emp.*
from emp)
where r1=5;
write a query to display exactly 2 4 6 8 10 rows of emp table
select *
from (select rownum as r1,emp.*
from emp)
where r1 in (2,4,6,8,10);
write a query to display bottom 3 rows of emp table
select *
from (select rownum as r1,emp.*
from emp)
Pseudocolumn 3
where r1 In ( (select max(rownum) from emp),
(select max(rownum) from emp where rownum<(select max(rownum) from emp)),
(select max(rownum) from emp where rownum<((select max(rownum) from emp where rownum<
(select max(rownum) from emp)))));
select *
from (select rownum as r1,emp.*
from emp)
where r1 In ((subqury 1),
(subqury 2),
(subqury 3)
);
Bottom 5 rows of employee table
select *
from (select rownum as r1,emp.*
from emp)
where r1 in ((select max(rownum) from emp),
(select max(rownum) from emp where rownum<(select max(rownum) from emp)),
(select max(rownum) from emp where rownum<((select max(rownum) from emp where rownum<
(select max(rownum) from emp)))),
select max(rownum)from emp where rownum<(
(select max(rownum) from emp where rownum<((select max(rownum) from emp where rownum<
(select max(rownum) from emp))))),);
nth max and nth minimum using psudocolumn (rownum)
sal R1 SAL
2000 1 5000
4000 2 4000
3500 3 3500
4000 4 3000
5000 5 2000
3500
Pseudocolumn 4
5000 AFTER ORDER BY
3000 That’s how you can find maximum
salary and minimum salary along
with 3rd maximum salary
WAQTD 4TH MAX SALARY
SELECT *
FROM (SELECT ROWNUM R1,SAL FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC))
WHERE R1=4;
WAQTD 10TH MIN SAL
SELECT *
FROM (SELECT ROWNUM R1,SAL
FROM (SELECT DISTINCT(SAL)
FROM EMP
ORDER BY SAL ASC))
WHERE R1=10;
WAQTD 2ND MAX 4TH MAX 6,8,10
SELECT *
FROM (SELECT ROWNUM R1,SAL
FROM (SELECT DISTINCT(SAL)
FROM EMP
ORDER BY SAL ASC))
WHERE R1 IN (2,4,6,8,10);
WAQTD 5TH MAX AND 6TH MIN SAL
SELECT *
FROM (SELECT ROWNUM R1,SAL
FROM (SELECT DISTINCT(SAL)
FROM EMP
ORDER BY SAL ASC)),(SELECT ROWNUM R2,SAL
FROM (SELECT DISTINCT(SAL)
FROM EMP
ORDER BY SAL DESC)
)
WHERE R1=5 AND R2=6;
Pseudocolumn 5
Pseudocolumn 6
