1. Subquery
2. Correlated Subquery

  A simple sub query:
  select empno from emp where
  empno=( select max(empno) from emp );
  This is an example of a sub query where the inner query executes only
  once and gives it's result set to the main query and the main query
  uses this emp number to display its results out.

  The purpose of the query below is to provide emphasis on the point that
  when a column's name is given an alias then the alias can only be used
  in the order by clause of the select statement and no where else.
  
  select empno "my emp" from emp where
    empno=( select max(empno) from emp )
  order by "my emp";

  If we had used the following sql statement it would be invalid
  because of the group by clause:
  select empno "my emp" from emp where
    empno=( select max(empno) from emp )
  group by "my emp"
  order by "my emp" ;

  When we change the "my emp" in the group by clause to the
  column name empno the query gives the expected results back.


  A table's alias once given should be used to qualify a tables name
  if you try to access the same table with fully qualified name instead
  of the alias oracle will return a syntax error.

  select emp.empno "my emp" from emp ez where
    empno=( select max(empno) from emp )
  order by "my emp"

  It should be written as :
  select ez.empno "my emp" from emp ez where
    empno=( select max(empno) from emp )
  order by "my emp"

  Consider an expression of the form:
  select ez.empno "my emp" from emp ez where
  empno in ( select max(empno) from emp
             group by empno having count(empno) >= 1)
  order by "my emp"

  We can use a group by expression inside a subquery, the only limiting
  factor is that we are using a in clause for the main query, if we had
  used an equality operator in the sql the sql would fail because a single
  row subquery would return more than one row.

  This query would fail because a sub query with a equality sign is
  expected to return a single value.
  select ez.empno "my emp" from emp ez where
  empno = ( select max(empno) from emp
             group by empno having count(empno) >= 1)
  order by "my emp"

    

    Caution:We cannot use a order by clause in a subquery.
    

  A correlated subquery is a type of subquery in which the subquery
  is evaluated for each row of the parent query.
  Example:
  select deptno from emp
  where exists ( select 1 from dept where dept.deptno=emp.deptno  )

  In this case we have come across the keyword exists, which just
  checks for whether a value is returned from the inner query or not
  therefore if a 1 is returned that would evaluate to a true and the query
  operation would succeed.

  When the query is executed oracle fetches the first row from the emp
  table, matches it against the dept table (it actually queries the
  database), the two values are compared and the result if true is displayed
  back to the user this operation continues for each row of the parent query.
  The same query above when written withouth correlated sub query can
  be written with joins as follows:
  
  select emp.deptno from emp , dept
  where emp.deptno=dept.deptno ;
  
  The = in this case is called a equi join, this is the versatility
  of sql that you can frame your sql statements in many ways, that is
  why it is all the more important to understand the various features
  available to you as a developer / dba.
  

    Caution:A correlated subquery also follows the rules of the
    subquery namely you cannot have a order by clause in a correlated
    subquery.