How To Use Subqueries in the FROM clause?

If you have a query returning many rows of data, and you want to perform another query on those
rows, you can put the first query as a subquery in the FROM clause of the second query. The
following statement shows you how to use a subquery as base table for the main query:
SQL> SELECT * FROM (
SELECT first_name, last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
) WHERE department_name LIKE 'S%' ORDER BY last_name;
FIRST_NAME LAST_NAME DEPARTMENT_NAME
-------------------- ------------------------- --------------------
Ellen Abel Sales
Sundar Ande Sales
Mozhe Atkinson Shipping
Amit Banda Sales
Elizabeth Bates Sales
Sarah Bell Shipping