Inner Query in Oracle SQL

Inner Query in Oracle SQL

--=================== 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

--====================================================

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

--==================================================== 

--SELECT--

select ENAME, JOB, SAL , A.DEPTNO, (select DNAME  from XXSD_DEPT_TL Z where Z.DEPTNO = A.DEPTNO) DEPARTMENT_NAME

from XXSD_EMP_TL A

select DNAME  from XXSD_DEPT_TL where DEPTNO = 10

ENAME	JOB	SAL	DEPTNO	DEPARTMENT_NAME
SMITH	CLERK	800	20	RESEARCH
ALLEN	SALESMAN	1600	30	SALES
WARD	SALESMAN	1250	30	SALES
JONES	MANAGER	2975	20	RESEARCH
MARTIN	SALESMAN	1250	30	SALES
BLAKE	MANAGER	2850	30	SALES
CLARK	MANAGER	2450	10	ACCOUNTING
SCOTT	ANALYST	3000	20	RESEARCH
KING	PRESIDENT	5000	10	ACCOUNTING
TURNER	SALESMAN	1500	30	SALES
ADAMS	CLERK	1100	20	RESEARCH
JAMES	CLERK	950	30	SALES
FORD	ANALYST	3000	20	RESEARCH
MILLER	CLERK	1300	10	ACCOUNTING