Data Set Fabrication

Along with querying existing tables, inline views may be used to fabricate special- purpose data sets that don’t exist in the database. For example, we might want to aggregate orders over the last year by small, medium, and large orders, but the concept of order sizes may not have been defined in our database. We could build a table with three records to define the different sizes and their boundaries, but we only need this information for a single query, and we don’t want to clutter our data- base with dozens of small, special-purpose tables. One solution is to use set operators like UNION to construct a custom-built data set, as in:

SELECT 'SMALL' name, 0 lower_bound, 999 upper_bound

from dual UNION ALL

(SELECT 'MEDIUM' name, 1000 lower_bound, 24999 upper_bound from dual UNION ALL SELECT 'LARGE' name, 25000 lower_bound, 9999999 upper_bound from dual);