Select nested collections

The projects column of the manager table (see The manager table) is a nested collection. A query on a nested collection type returns all the elements that the particular collection contains. The following query 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.

Figure 1: Query

SELECT projects 
   FROM manager
   WHERE mgr_name = 'Sayles'

The query result 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.

Figure 2: Query result

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})}
⋮