--Informix Examples for Section 4.3

drop table employees;
drop table phonebook;

--Example 4.3.18
create table employees
(
       eid  int,
       eperson  person_t,
       dependents  set(person_t not null)
);

--Example 4.3.19
select dependents from employees     where eid = 101;

select * from employees;      

--Example 4.3.20
select cardinality(dependents) from employees where eid = 101;

select eid from employees
      where cardinality(employees.dependents) > 6;      

--Example 4.3.21
select e1.eid, e2.eid from employees e1, employees e2
      where e1.eid < e2.eid and e1.dependents = e2.dependents;      

--Example 4.3.22
select eid from employees
  where cast(row(565534555,cast(row('Shaw', 'David','M') as name_t),3) as person_t) 
      in employees.dependents;      

--Example 4.3.23
select eid from employees e
      where 322456776 in 
          (select d.ssno from table(e.dependents) d);      

--Example 4.3.24
select d.ssno from 
      table (select e.dependents from employees e where e.eid = 101) d    ;

--Example 4.3.25
select e.eid, (select count(*) from table(e.dependents) d 
          where d.age < 16)
      from employees e;

--Example 4.3.26
create table phonebook
  (
      phperson  person_t,
      extensions  list(int not null)
  );

--Example 4.3.27
select phperson.pname.fname, extensions from phonebook
      where phperson.age > 30;

--Example 4.3.28
select cardinality(extensions) from phonebook
      where phperson.ssno = 123897766;

select phperson from phonebook
      where 123 in extensions;

select pb1.phperson, pb2.phperson from phonebook pb1, phonebook pb2
      where pb1.extensions = pb2.extensions and pb1.phperson <> pb2.phperson;

--Example 4.3.29
select phperson from phonebook
      where phperson.extensions = list{123};

--Example 4.3.30
select pb.phperson from phonebook pb
      group by pb.phperson
      having count(*) > 1; -- remove unduplicated people

--Example 4.3.31
insert into phonebook values     
      (cast(row(123897766,cast(row('Smith','John','P') as name_t),45) as person_t),
      list{345,989});

  insert into employees values (1, 
      cast(row(123897766, cast(row('Smith','John','P') as name_t),45) as person_t),
      set{row(322456776,row('Smith','Michael', 'J'),8), --NOTE NO CASTS HERE
          row(123822332,row('Smith','Susan','R'),12)}); 

