--ex4_3ora.sql: Oracle examples for Section 4.3
--Note: execute ex4_2ora.sql before this to define needed types

drop table employees;
drop table phonebook;
drop type dependents_t;
drop type extensions_t;

--Example 4.3.1
create type dependents_t as table of person_t;
/

create table employees
  (
      eid       int,
      eperson   person_t,
      dependents dependents_t,
      primary key (eid)
  )  nested table dependents store as dependents_tab;

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

--Example 4.3.3
select dependents from employees;

--Example 4.3.4
select eid from employees e
     where 6 < (select count(*) from table(e.dependents));

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

--Example 4.3.6
select count(*) from 
  (select e.dependents from employees e where e.eid = 101); --UNEXPECTED RESULT

--Example 4.3.7
select count(*) from 
      table (select e.dependents from employees e where e.eid = 101); 

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

--Example 4.3.8
select e.eid, d.ssno as dep_sso from employees e, table(e.dependents) d
      where d.age < 16;

select e.eid, cursor(select d.ssno as dep_ssno 
          from table(e.dependents) d where d.age < 16) dep_tab
      from employees e;

--Example 4.3.9
select eid, d.pname.fname from employees e, table(e.dependents) d1
      where d1.age = (select max(d2.age) from table(e.dependents) d2);      

select eid, cursor(select d1.pname.fname from table(employees.dependents) d1
      where d1.age = (select max(d2.age) from table(employees.dependents) d2))
      from employees;    

--Example 4.3.10
select eid, cursor(select count(*) from table(e.dependents))
      from employees e;

select eid, (select count(*) from table(e.dependents)) 
      from employees e;

select eid, count(*) from employees e, table(e.dependents)
      group by eid;      

--Example 4.3.11
create type extensions_t as varray(4) of int;
/

create table phonebook
  (
      phperson  person_t,
      extensions  extensions_t
  );

--Example 4.3.12
select pb.phperson.pname.fname, pb.extensions from phonebook pb
      where pb.phperson.ssno = 123897766;

--Example 4.3.13
select (select count(*) from table(pb.extensions)) from phonebook pb
      where pb.phperson.ssno = 123440011;

---or, using a table product

select count(*) from phonebook pb, table(pb.extensions)
      where pb.phperson.ssno = 123440011;       

--Example 4.3.14
select phperson from phonebook pb
      where 104 in (select * from table(pb.extensions));

--Example 4.3.15
insert into employees 
    values (101, person_t(123897766, name_t('Smith', 'John', 'P'), 45),
      dependents_t(person_t(322456776,name_t('Smith', 'Michael', 'J'), 8),
          person_t(123822332, name_t('Smith', 'Susan','R'),12)));

insert into phonebook 
     values (person_t(123897766, name_t('Smith', 'John', 'P'), 45),
              extensions_t(345,989));  

update phonebook pb set extensions = extensions_t(345,999) 
    where pb.phperson.ssno = 123897766;

--Example 4.3.16
insert into table(select e.dependents from employees e 
      where e.eperson.ssno = 123897766)
      values (344559112, name_t('Smith', 'Diane', null), 0); 

update table(select e.dependents from employees e where e.eid = 101) d
   set d.pname.lname = upper(d.pname.lname); -- convert last name to uppercase

--Example 4.3.17
insert into table(select e.dependents from employees e 
      where e.eperson.ssno = 123897766)
          select * from people;      
