
connect scott/tiger

drop table customer_tab;
drop type customer;
drop table person_tab;
drop type budoka;
drop type location;

create type location as object (
        num     number,
        street  varchar2(60),
        city    varchar2(30),
        state   char(2),
        zip     char(10)
);
/

create type budoka as object (
        lastname        varchar2(20),
        firstname       varchar(20),
        age             int,
        addr            location
);
/

create table person_tab of budoka;

create type customer as object (
        account_number varchar(20),
        aperson ref budoka
);
/

create table customer_tab of customer;

insert into person_tab values (
        budoka('Seagal', 'Steven', 34,
                location(1825, 'Aikido Way', 'Los Angeles', 'CA', 45300)));
insert into person_tab values (
        budoka('Norris', 'Chuck', 45,
                location(291, 'Grant Avenue', 'Hollywood', 'CA', 21003)));
insert into person_tab values (
        budoka('Wallace', 'Bill', 53,
                location(874, 'Richmond Street', 'New York', 'NY', 45100)));
insert into person_tab values (
        budoka('Van Damme', 'Jean Claude', 32,
                location(12, 'Shugyo Blvd', 'Los Angeles', 'CA', 95100)));

insert into customer_tab
        select 'AB123', ref(p)
          from person_tab p where p.lastname = 'Seagal';
insert into customer_tab
        select 'DD492', ref(p)
          from person_tab p where p.lastname = 'Norris';
insert into customer_tab 
        select 'SM493', ref(p)
          from person_tab p where p.lastname = 'Wallace';
insert into customer_tab
        select 'AC493', ref(p)
          from person_tab p where p.lastname = 'Van Damme';
        
commit work;

exit;
