Selecting Nested Collections

The projects column of the manager table (see Figure 144) is a nested collection. A query on a nested collection type returns all the elements that the particular collection contains. Figure 147 shows a query that returns all elements from the projects column for a specified row. The WHERE clause limits the query to a single row in which the value in the mgr_name column is Sayles.

SELECT projects 
   FROM manager
   WHERE mgr_name = 'Sayles' 

Figure 148 shows a project column collection for a single row of the manager table. The query returns the names of those projects that the manager Sayles oversees. The collection contains, for each element in the LIST, the project name (pro_name) and the SET of individuals (pro_members) who are assigned to each project.

projects  LIST {ROW(voyager_project, SET{Simonian, Waters, Adams, Davis})}

projects  LIST {ROW(horizon_project, SET{Freeman, Jacobs, Walker, Cannan})}

projects  LIST {ROW(sapphire_project, SET{Villers, Reeves, Doyle, Strongin})}

·
·
·