--Informix examples for Section 4.2
--Example 4.2.19
drop table scientists;
drop table people;
drop table teachers;
drop table phonebook;
drop row type dependents_t restrict;
drop row type police_officer_t restrict;
drop row type person_t restrict;
drop row type name_t restrict;

create row type name_t
  (
          lname        varchar(30),
          fname        varchar(30),
          mi        char(1)
  );    

--Example 4.2.20

drop row type person_t restrict;
  create row type person_t
   (
          ssno  int,
          pname  name_t,
          age  int  
   );  

--Example 4.2.21

create table people of type person_t
  (
          primary key (ssno)
  );  

--Example 4.2.22

select p from people p
      where p.pname = cast(row('Sanchez', 'Jose','F') as name_t);    

select p from people p
      where p.pname = row('Sanchez', 'Jose','F')::name_t;      

--Example 4.2.23

select pname, age from people
      where pname.fname like 'Pat%' and age > 50;

--Example 4.2.24

  insert into people values (123441998, cast(row('Einstein', 'Albert', 'E') as name_t), 100);
  insert into people values (321341223, null, null);
--  (or equivalently)
  insert into people (ssno) values (321341223);

  update people set pname = cast(row('Gould', 'Ben', cast( null as char(1))) as name_t) where ssno = 321341224;

  update people set pname = cast(row(pname.lname, pname.fname, 'C') as name_t) 
      where ssno = 321341224;

  create table scientists of type name_t;
    insert into scientists 
      select pname.fname, pname.mi, pname.lname from people;      

