Inline Query, Inline View, Sub Query, Inner Query Nested Query and Co-related Query

Inline Query, Inline View, Sub Query, Inner Query Nested Query and Co-related Query

--=================== TABLE STRUCTURE =======================

XXSD_EMP_TL

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7499	ALLEN	SALESMAN	7698	1600	300	30
7521	WARD	SALESMAN	7698	1250	500	30
7566	JONES	MANAGER	7839	2975	1200	20
7654	MARTIN	SALESMAN	7698	1250	1400	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7782	CLARK	MANAGER	7839	2450	900	10
7788	SCOTT	ANALYST	7566	3000	750	20
7839	KING	PRESIDENT		5000	2000	10
7844	TURNER	SALESMAN	7698	1500	300	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7902	FORD	ANALYST	7566	3000	700	20
7934	MILLER	CLERK	7782	1300	720	10

XXSD_DEPT_TL

DEPTNO	DNAME	LOC	DEPT_YEAR
10	ACCOUNTING	NEW YORK	2020
20	RESEARCH	DALLAS	2019
30	SALES	CHICAGO	2018
40	OPERATIONS	BOSTON	2017

XXSD_SAL_INFO_TL

YEAR_NAME	SAL_GRADE
2015	1000
2016	1500
2017	2000
2018	2200
2019	2500
2020	3000

--===================  In Line Query =======================

 SELECT * FROM XXSD_EMP_TL

 SELECT * FROM XXSD_EMP_TL WHERE DEPTNO = 10

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7782	CLARK	MANAGER	7839	09-Jun-81	2450	900	10
7839	KING	PRESIDENT		17-Nov-81	5000	2000	10
7934	MILLER	CLERK	7782	23-Jan-82	1300	720	10

 --===================  In Line View========================

 select * from (  )

 CREATE OR REPLACE VIEW    XXSD_EMP_INFO_V AS

 select ENAME, JOB, MGR, A.DEPTNO, B.DNAME  from XXSD_EMP_TL A, XXSD_DEPT_TL B WHERE A.DEPTNO = B.DEPTNO

 select * from  XXSD_EMP_INFO_V

 --FROM--

 select * from ( )

 select * from ( select ENAME, JOB, MGR  from XXSD_EMP_TL ) --- SINGLE TABLE --- -- It Runs Only One Time—

ENAME	JOB	MGR
SMITH	CLERK	7902
ALLEN	SALESMAN	7698
WARD	SALESMAN	7698
JONES	MANAGER	7839
MARTIN	SALESMAN	7698
BLAKE	MANAGER	7839
CLARK	MANAGER	7839
SCOTT	ANALYST	7566
KING	PRESIDENT
TURNER	SALESMAN	7698
ADAMS	CLERK	7788
JAMES	CLERK	7698
FORD	ANALYST	7566
MILLER	CLERK	7782

 select * from ( select ENAME, JOB, MGR, A.DEPTNO, B.DNAME  from XXSD_EMP_TL A, XXSD_DEPT_TL B WHERE A.DEPTNO = B.DEPTNO  ) --- Multi TABLE --- -- It Runs Only One Time--

ENAME	JOB	MGR	DEPTNO	DNAME
SMITH	CLERK	7902	20	RESEARCH
ALLEN	SALESMAN	7698	30	SALES
WARD	SALESMAN	7698	30	SALES
JONES	MANAGER	7839	20	RESEARCH
MARTIN	SALESMAN	7698	30	SALES
BLAKE	MANAGER	7839	30	SALES
CLARK	MANAGER	7839	10	ACCOUNTING
SCOTT	ANALYST	7566	20	RESEARCH
KING	PRESIDENT		10	ACCOUNTING
TURNER	SALESMAN	7698	30	SALES
ADAMS	CLERK	7788	20	RESEARCH
JAMES	CLERK	7698	30	SALES
FORD	ANALYST	7566	20	RESEARCH
MILLER	CLERK	7782	10	ACCOUNTING

 --===================  Sub Query =========================

 -- What is the Requirment to Write the Subquery ? 

  

--WHERE--

EX-1:-

select * from XXSD_EMP_TL

select * from XXSD_DEPT_TL

select * from XXSD_EMP_TL

where DEPTNO IN ( select DEPTNO from XXSD_DEPT_TL  WHERE  DNAME in ( 'RESEARCH','SALES') )  -- It Runs Only One Time—

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7902	FORD	ANALYST	7566	3000	700	20
7876	ADAMS	CLERK	7788	1100	220	20
7788	SCOTT	ANALYST	7566	3000	750	20
7566	JONES	MANAGER	7839	2975	1200	20
7369	SMITH	CLERK	7902	800	80	20
7900	JAMES	CLERK	7698	950	110	30
7844	TURNER	SALESMAN	7698	1500	300	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7654	MARTIN	SALESMAN	7698	1250	1400	30
7521	WARD	SALESMAN	7698	1250	500	30
7499	ALLEN	SALESMAN	7698	1600	300	30

EX-2:-

select * from XXSD_EMP_TL

select *  from XXSD_SAL_INFO_TL

select * from XXSD_EMP_TL

where SAL < ( select SAL_GRADE from XXSD_SAL_INFO_TL WHERE YEAR_NAME = 2016 )  -- It Runs Only One Time--

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7521	WARD	SALESMAN	7698	1250	500	30
7654	MARTIN	SALESMAN	7698	1250	1400	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7934	MILLER	CLERK	7782	1300	720	10

--===================  Inner Query =======================

--SELECT--

 select * from XXSD_EMP_TL

 select * from XXSD_DEPT_TL

select A.*, ( )

from XXSD_EMP_TL A

select A.*,  ( select DNAME from XXSD_DEPT_TL Z WHERE Z.DEPTNO = A.DEPTNO ) DEPT_NAME  ,

 ( select LOC from XXSD_DEPT_TL Z WHERE Z.DEPTNO = A.DEPTNO ) DEPT_LOC

from XXSD_EMP_TL A

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO	DEPT_NAME	DEPT_LOC
7369	SMITH	CLERK	7902	800	80	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	7698	1600	300	30	SALES	CHICAGO
7521	WARD	SALESMAN	7698	1250	500	30	SALES	CHICAGO
7566	JONES	MANAGER	7839	2975	1200	20	RESEARCH	DALLAS
7654	MARTIN	SALESMAN	7698	1250	1400	30	SALES	CHICAGO
7698	BLAKE	MANAGER	7839	2850	1100	30	SALES	CHICAGO
7782	CLARK	MANAGER	7839	2450	900	10	ACCOUNTING	NEW YORK
7788	SCOTT	ANALYST	7566	3000	750	20	RESEARCH	DALLAS
7839	KING	PRESIDENT		5000	2000	10	ACCOUNTING	NEW YORK
7844	TURNER	SALESMAN	7698	1500	300	30	SALES	CHICAGO
7876	ADAMS	CLERK	7788	1100	220	20	RESEARCH	DALLAS
7900	JAMES	CLERK	7698	950	110	30	SALES	CHICAGO
7902	FORD	ANALYST	7566	3000	700	20	RESEARCH	DALLAS
7934	MILLER	CLERK	7782	1300	720	10	ACCOUNTING	NEW YORK

--=================== Nested query =======================

--WHERE--

EX-1:-

select * from XXSD_EMP_TL

select * from XXSD_DEPT_TL

select * from XXSD_SAL_INFO_TL

select * from TABLE_NAME

WHERE COND = (     (      (   )   )   )

select * from XXSD_EMP_TL

where DEPTNO = ( select DEPTNO from XXSD_DEPT_TL  WHERE  DEPT_YEAR = ( select YEAR_NAME from XXSD_SAL_INFO_TL WHERE SAL_GRADE = 3000 )  )  -- It Runs Only One Time--

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7782	CLARK	MANAGER	7839	2450	900	10
7839	KING	PRESIDENT		5000	2000	10
7934	MILLER	CLERK	7782	1300	720	10

--===================  Co-Related Query ====================

select * from XXSD_EMP_TL

select * from XXSD_DEPT_TL

select * from XXSD_EMP_TL A

where EXISTS ( select * from XXSD_DEPT_TL B  WHERE  A.DEPTNO = B.DEPTNO AND DNAME = 'RESEARCH' )

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7902	FORD	ANALYST	7566	3000	700	20
7876	ADAMS	CLERK	7788	1100	220	20
7788	SCOTT	ANALYST	7566	3000	750	20
7566	JONES	MANAGER	7839	2975	1200	20
7369	SMITH	CLERK	7902	800	80	20