Let us consider that I would like to retrieve the department name of the highest paid employee. Let us write the question in a meaningful manner and identify the steps as follows:

From the above figure, you have three steps to go through with the query.  The following is the order you must follow (based on the above figure):

• Find the highest salary (1).
• Based on the value you get, retrieve the department number belonging to that salary (2).
• Based on the value you get, find the department name belonging to that department number (3).

The following is the statement which finds the highest salary:

SELECT MAX(sal) FROM emp

To retrieve a department number based on a given salary (say 2000), the query would be as follows:

SELECT deptno FROM emp

WHERE sal = 2000

To retrieve a department name based on a given department number (say 20), the query would be as follows:

SELECT dname FROM dept

WHERE deptno = 20

Combining all of the above queries according to the order given above, the new query would be as follows:

SQL> SELECT dname FROM dept

WHERE deptno = (SELECT deptno FROM emp

WHERE sal = (SELECT MAX(sal) FROM EMP));

You can observe the following figure to understand how the execution takes place.  You can also observe the underlined columns on how they relate logically:

Now, let us walk through its execution:

• The innermost query gets executed first. 
• In this case, the query “select max(sal) from emp” gets executed first.  It retrieves the highest salary. In this case it would be 5000.
• The entire innermost query gets replaced with the new value returned by it.  In this case, the immediate outer query virtually becomes “select deptno from emp where sal = 5000.”  Once this query gets executed, it returns a department number, which is 10 in this case.
• And finally, the outermost query virtually becomes “select dname from dept where deptno = 10,” which retrieves KING’s department details.

Any bugs, doubts, suggestions, feedback etc. are highly appreciated at http://jagchat.spaces.live.com