INLINE VIEW:

 INLINE VIEW:

***********

-->It works like as a query,which is having the query in from clause or instead of table.

EX:

***

-->SELECT * FROM (SELECT * FROM EMP);

--FIRST 5 RECORDS

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R<=5;

--Top most 5 salaries

SELECT ROWNUM,ENAME,JOB,SAL FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5;

/

--LAST 5 RECORDS

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R>=10;

/

--BETWEEN RECORDS

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R BETWEEN 4 AND 8; --4 TO 8 WILL BE PRINT

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R IN (5,6,7);   --5,6,7 WILL BE PRINT

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R>4 AND R<8 ;   -- 5,6,7 WILL BE PRINT

/

select *

from

(select ROWNUM R,hr_operating_units.* 

from hr_operating_units

)

where r>4 AND r<8;

/

--RANDOM RECORDS

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R IN (1,3,5);

/

--SPECIFIC RECORD

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R=2;  --SPECIFIC RECORD

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R=10;

/

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP ) WHERE MOD(R,2)=1;  --ODD NUMBERS

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP ) WHERE MOD(R,2)=0;  --EVEN NUMBERS

NOTE:

*****

1. GEN. SUBQUERY IS NOT ALLOWED TO USE  "ORDER BY" CLAUSE.SO THAT WE USE "INLINE VIEW".

2. GEN. COLUMN ALIAS NAMES ARE NOT ALLOWED TO USE IN "WHERE" CLAUSE 

CONDITION.SO THAT WE USE "INLINE VIEW ".

USING COLUMN ALIAS NAMES IN WHERE CLAUSE CONDITION :

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

EX:

***

-->WAQ TO DISPLAY EMPLOYEE WHOSE EMPLOYEE ANNUAL SALARY IS MORE THAN 25000 ?

SELECT * FROM(SELECT ENAME,SAL,SAL*12 ANNUSAL FROM EMP) WHERE           ANNUSAL>25000;

USING "ORDER BY" CLAUSE IN SUBQUERY:

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

EX:

***

-->WAQ TO DISPLAY FIRST FIVE HIGHEST SALARIES OF EMPLOYEE FROM EMP TABLE BY USING ROWNUM ALONG WITH INLINE VIEW ?

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5;

EX:

****

-->WAQ TO DISPLAY 5th  HIGHEST SALARY OF EMPLOYEE FROM EMP TABLE BY USING ROWNUM ALONG WITH INLINE VIEW ?

SOL:

****

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5

MINUS

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=4;

USING ROWNUM ALIAS NAME:

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

EX:

***

-->WAQ TO DISPLAY 3rd POSITION ROW FROM EMP TABLE BY USING ROWNUM ALIAS NAME ALONG WITH INLNE VIEW ?

SELECT * FROM (SELECT ROWNUM R,ENAME,JOB,SAL FROM EMP) WHERE R=3;

(OR)

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R=3;

EX:

****

-->WAQ TO DISPLAY 1st,3rd,5th,7th,9th ROWS FROM EMP TABLE BY USING ROWNUM ALIAS NAME ALONG WITH INLINE VIEW ?

SELECT * FROM ( SELECT ROWNUM R,EMP.* FROM EMP) WHERE R IN(1,3,5,7,9);

EX:

****

-->WAQ TO DISPLAY EVEN  POSITION ROWS FROM EMP TABLE BY USING ROWNUM ALIAS NAME ALONG WITH INLINE VIEW ?

SELECT * FROM(SELECT ROWNUM R,EMP.* FROM EMP) WHERE MOD(R,2)=0;

SELECT * FROM(SELECT ROWNUM R,EMP.* FROM EMP) WHERE MOD(R,2)=1;

EX:

****

-->WAQ TO DISPLAY FIRST ROW AND LAST ROW FROM EMP TABLE BY USING ROWNUM ALIAS NAME ALONG WITH INLINE VIEW ?

SELECT * FROM (SELECT ROWNUM R,EMP.* FROM EMP) WHERE R=1 OR R=(SELECT COUNT(*) FROM EMP);

/

SELECT LV1_EMPNO,LV1_ENAME,LV1,LV2_EMPNO,LV2_ENAME,LV2,LV3_EMPNO,LV3_ENAME,LV3,LV4_EMPNO,LV4_ENAME,LV4,EMPNO LV5_EMPNO,ENAME LV5_ENAME,5 LV5

FROM(

SELECT LV1_EMPNO,LV1_ENAME,LV1,LV2_EMPNO,LV2_ENAME,LV2,LV3_EMPNO,LV3_ENAME,LV3,EMPNO LV4_EMPNO,ENAME LV4_ENAME,4 LV4

FROM(

SELECT LV1_EMPNO,LV1_ENAME,LV1,LV2_EMPNO,LV2_ENAME,LV2,EMPNO LV3_EMPNO,ENAME LV3_ENAME,3 LV3

FROM(

SELECT LV1_EMPNO,LV1_ENAME,LV1,EMPNO LV2_EMPNO,ENAME LV2_ENAME,2 LV2

FROM(

SELECT EMPNO LV1_EMPNO,ENAME LV1_ENAME,1 LV1

FROM EMP

WHERE MGR IS NULL)L1,

EMP E2

WHERE E2.MGR(+)=L1.LV1_EMPNO)L2,

EMP E3

WHERE E3.MGR(+)=L2.LV2_EMPNO)L3,

EMP E4

WHERE E4.MGR(+)=L3.LV3_EMPNO)L4,

EMP E5

WHERE E5.MGR(+)=L4.LV4_EMPNO

ORDER BY 1,4

/