The output on this statement is really a master detail report     Ammar Sajdi

 

SQL>SELECT dname,CURSOR (select ename from emp e
                                where e.deptno = d.deptno
                                order by sal ) the_employees
      FROM    dept d order by  dname

 

The statement looks like it has two fields, effectively a straight forward

Select dname, The_emloyees from Dept order by Dname ;  
 

However, there are several employees per department (One to Many), and it only sounds logical that we want to loop through a EMP cursor.  No problem!  Just substitute  the "The Employee" with  CURSOR(select ename from emp ...)

of course, need to link employees to the current dept, therefore, a join condition a must.

DNAME         THE_EMPLOYEES
------        -------- --------------------
Acc           CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
MILLER
CLARK
KING

HR             CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
JAMES
WARD
MARTIN
TURNER
ALLEN
BLAKE

6 rows selected.



DNAME           THE_EMPLOYEES
------          -------- --------------------
IT             CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
SMITH
ADAMS
JONES
SCOTT
FORD