Designing sub-queries to deal with more than one table (or different tables) - Oracle

Let us consider that I would like to retrieve KING's department name.  All department names are available in the table “dept,” which is quite different from “emp” table.  Let us write the question in a meaningful manner and identify the steps as follows:

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

• Find KING's department number (1).
• Based on the value you get, retrieve the department details like dname, loc. etc belonging to that department number (2)

The following is the statement which retrieves KING's department number:

SELECT deptno FROM emp WHERE ename = ‘KING’

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

SELECT dname FROM dept

WHERE deptno = 30

Just replace the value 30 with the query that gives you KING's department number.  The complete statement would be as follows:

SQL> SELECT dname FROM dept

WHERE deptno = (SELECT deptno FROM emp WHERE ename=’KING’);

Now, let us walk through its execution:

• The innermost query gets executed first. 
• In this case, the query “select deptno from emp where ename=’king’” gets executed first.  It retrieves KING's department number.
• Once the innermost query gets executed, it returns a value to the immediate outer query.  In this case, it is 10.
• The entire innermost query gets replaced with the new value returned by it.  In this case, the outer-query virtually becomes “select dname from dept where deptno = 10.”
• And finally, the outer query gets executed, which retrieves KING’s department details