SQL> select * from t1;

       ID1
----------
         1
         2
         3
         4

SQL> select * from t2;

       ID2
----------
         1
         1
         1
         1
         1
         2
         2
         2
         3
         3
         3
         3
         3
         3
         3
         3
         3
         3

18 rows selected.

SQL> declare
  2   lv_2 nt_adj := nt_adj();
  3   cnt number;
  4  begin
  5   select * bulk collect into lv_2 from t2;
  6   for v in (select id1 from t1) loop
  7     select count(1)
  8     into cnt
  9     from table(cast(lv_2 as nt_adj)) a
 10     where a.column_value = v.id1;
 11     dbms_output.put_line('Number of values ' || v.id1 || ' is ' || cnt);
 12   end loop;
 13  end;
 14  /
Number of values 1 is 5
Number of values 2 is 3
Number of values 3 is 10
Number of values 4 is 0

PL/SQL procedure successfully completed.

SQL> create type my_obj is object( empno number, sal number, deptno number);
  2  /

Type created.

SQL> create type my_obj_tab is table of my_obj;
  2  /

Type created.

SQL> declare
  2  
  3   my_tab my_obj_tab := my_obj_tab();
  4  
  5   cnt number;
  6   cnt1 number;
  7  
  8  begin
  9  
 10   --Let's fill the object collection using object default constructor
 11  
 12   select my_obj(empno,sal,deptno) 
 13   bulk collect into my_tab
 14   from emp;
 15  
 16   for v in (select deptno from dept) loop
 17    
 18     select count(1) into cnt from 
 19     table(cast(my_tab as my_obj_tab)) a
 20     where a.deptno = v.deptno;
 21  
 22     select count(1) into cnt1 from   
 23     table(cast(my_tab as my_obj_tab)) a
 24     where a.deptno = v.deptno and a.sal > 1000;
 25  
 26     dbms_output.put_line('Number of employee in department ' 
 27     || v.deptno || ' is ' || cnt);
 28  
 29     dbms_output.put_line('Number of employee in department ' 
 30     || v.deptno || ' with salary > $1000 is ' || cnt);
 31  
 32   end loop;
 33  
 34  end;
 35  /
Number of employee in department 10 is 3
Number of employee in department 10 with salary > $1000 is 3
Number of employee in department 20 is 5
Number of employee in department 20 with salary > $1000 is 5
Number of employee in department 30 is 6
Number of employee in department 30 with salary > $1000 is 6
Number of employee in department 40 is 0
Number of employee in department 40 with salary > $1000 is 0
Number of employee in department 50 is 0
Number of employee in department 50 with salary > $1000 is 0
Number of employee in department 60 is 0
Number of employee in department 60 with salary > $1000 is 0

PL/SQL procedure successfully completed.

SQL> select d.deptno, e.ename from emp e, dept d
  2  where e.deptno(+) = d.deptno order by 1;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 SMITH
        20 ADAMS
        20 FORD
        20 SCOTT
        20 JONES
        30 ALLEN
        30 BLAKE
        30 MARTIN

    DEPTNO ENAME
---------- ----------
        30 JAMES
        30 TURNER
        30 WARD
        40
        50
        60

17 rows selected.

SQL> select d.deptno, e.ename from emp e, dept d
  2  where e.deptno = d.deptno 
  3  union all
  4  select deptno, null from dept d where not exists
  5  (select 1 from emp e where d.deptno = e.deptno)
  6  order by 1;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 SMITH
        20 ADAMS
        20 FORD
        20 SCOTT
        20 JONES
        30 ALLEN
        30 BLAKE
        30 MARTIN

    DEPTNO ENAME
---------- ----------
        30 JAMES
        30 TURNER
        30 WARD
        40
        50
        60

17 rows selected.

SQL> select distinct * from (
  2  select 1 a, '19.24.11:11' b from dual
  3  union all
  4  select 1, '19.23.11:12' from dual
  5  union all
  6  select 3, '19.22.11:12' from dual
  7  )
  8  /

             A B
-------------- -----------
             1 19.23.11:12
             1 19.24.11:11
             3 19.22.11:12

SQL> select id, max(b) from (
  2  select 1 id, '19.24.11:11' b from dual
  3  union all
  4  select 1, '19.23.11:12' from dual
  5  union all
  6  select 3, '19.22.11:12' from dual
  7  ) c
  8  group by id
  9  /

        ID MAX(B)
---------- -----------
         1 19.24.11:11
         3 19.22.11:12

SQL> create type my_obj is object(deptno number, job varchar2(10), sal nu
  2  /

Type created.

SQL> create type my_obj_tab is table of my_obj;
  2  /

Type created.

SQL> select deptno, job, sal from emp;

    DEPTNO JOB              SAL
---------- --------- ----------
        20 CLERK           1000
        30 SALESMAN        1800
        30 SALESMAN        1450
        20 MANAGER         2975
        30 SALESMAN        1250
        30 MANAGER         2850
        10 MANAGER         2450
        20 ANALYST         3000
        10 PRESIDENT       5000
        30 SALESMAN        1500
        20 CLERK           1100

    DEPTNO JOB              SAL
---------- --------- ----------
        30 CLERK            950
        20 ANALYST         3000
        10 CLERK           1300

14 rows selected.

SQL> declare
  2   mytab my_obj_tab := my_obj_tab();
  3  begin
  4  
  5   select my_obj(deptno, job, max(sal))
  6   bulk collect into mytab
  7   from emp
  8   group by deptno, job;
  9  
 10   for i in 1..mytab.count loop
 11     dbms_output.put_line(mytab(i).deptno || ' ' 
 12     || mytab(i).job || ' ' || mytab(i).sal);
 13   end loop;
 14  
 15  end;
 16  /
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1100
20 ANALYST 3000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1800

PL/SQL procedure successfully completed.