A derived table is a named virtual table containing data obtained
    through the evaluation of the select expression in the FROM
    clause. The derived table is named by specifying the correlation
    name.

    You must specify a correlation name for a derived table. This
    may determine which column names the user can specify in the
    select list or subsequent clauses. The select list and subsequent
    clauses can reference only the correlation name and the column
    names of the derived table and cannot reference the table or
    column names that defined the derived table.

    Following is an example of a derived table using the personnel
    database. This example finds all departments that have less than
    3 rows in the JOB_HISTORY table.

    SQL> SELECT *
    cont> FROM (SELECT DEPARTMENT_CODE, COUNT(*)
    cont>       FROM JOB_HISTORY
    cont>       WHERE JOB_END IS NULL
    cont>       GROUP BY DEPARTMENT_CODE)
    cont>       AS DEPT_INFO (D_CODE, D_COUNT)
    cont> WHERE D_COUNT < 3;
     D_CODE       D_COUNT
     ENG                2
     MCBS               1
     MSMG               1
     MTEL               2
     PERS               2
     SUSA               2
    6 rows selected