Select columns that contain row-type data

A row-type column is a column that is defined on a named ROW type or unnamed ROW type. You use the same SQL syntax to query a named ROW type and an unnamed row-type column.

A query on a row-type column returns data from all the fields of the ROW type. A field is a component data type within a ROW type. For example, the address column of the employee table contains the street, city, state, and zip fields. The following query shows how to construct a query that returns all fields of the address column.
Figure 1. Query
SELECT address FROM employee
Figure 2. Query result
address     ROW(102 Ruby, Belmont, CA, 49932, 1000) 
address     ROW(133 First, San Jose, CA, 85744, 4900)
address     ROW(152 Topaz, Willits, CA, 69445, 1000))
To access individual fields that a column contains, use single-dot notation to project the individual fields of the column. For example, suppose you want to access specific fields from the address column of the employee table. The following SELECT statement projects the city and state fields from the address column.
Figure 3. Query
SELECT address.city, address.state FROM employee
Figure 4. Query result
city             state

Belmont          CA 
San Jose         CA
Willits          CA
You construct a query on an unnamed row-type column in the same way you construct a query on a named row-type column. For example, suppose you want to access data from the s_address column of the student table in SQL syntax that creates the student table.. You can use dot notation to query the individual fields of a column that are defined on an unnamed row type. The following query shows how to construct a SELECT statement on the student table that returns rows for the city and state fields of the s_address column.
Figure 5. Query
SELECT s_address.city, s_address.state FROM student
Figure 6. Query result
city             state

Belmont          CA 
Mount Prospect   IL
Greeley          CO