Object-Relational Examples

While the relational SQL does not require a qualifier for certain column references, the table alias such as p is required for an object table. Here, the p is in fact an object. 

 SQL> CREATE OR REPLACE TYPE  name_t  AS OBJECT (
   2    lname   VARCHAR(32),
   3    fname   VARCHAR(32),
   4    mi      CHAR(1) );
   5  /

 Type created.

 SQL> CREATE OR REPLACE TYPE  person_t  AS OBJECT (
   2    ssno   INT,
   3    pname  name_t,
   4    age    INT );
   5  /

 Type created.

 SQL> CREATE TABLE  people_tbl  OF  person_t (
   2    PRIMARY KEY ( ssno ) );
   3  /

 Table created.

Question: Find the names of all people whose first names start with “Jame” and are over 20 years of age. 

 SQL> INSERT INTO  people_tbl  VALUES (
   2    person_t ( 345678901, name_t ( 'Bond', 'James', 'C' ), 21 ) );
 1 row created.

 SQL> INSERT INTO  people_tbl  VALUES (
   2    person_t ( 456789012, name_t ( 'Ball', 'Dragon', 'Z' ), 32 ) );

 1 row created.

 SQL> SELECT  p.pname, p.age  FROM  people_tbl p
   2    WHERE  p.pname.fname LIKE 'Jame%'  AND  p.age > 20;

 PNAME(LNAME, FNAME, MI)
 --------------------------------------------------------
 AGE
 ----------
 NAME_T('Bond', 'James', 'C')
    21