--Oracle Examples for Section 4.2

drop table police_officers;
drop table scientists;
drop table people;
drop table teachers;
drop table employees1;
drop table phonebook;
drop type dependents_t;
drop type police_officer_t;
drop type person_t;
--in case types don't drop, find their dependencies (see pg. 453)
select name from user_dependencies where referenced_name ='PERSON_T';
drop type name_t;
select name from user_dependencies where referenced_name ='NAME_T';

--Example 4.2.1

create type name_t as object 
  (
    lname  varchar(30),  -- last name
    fname  varchar(30),  -- first name
    mi     char(1)       -- middle initial
  );
/
-- / is for Sql*Plus, not part of SQL syntax

--Example 4.2.2

create table teachers
   (
      tid   int,
      tname name_t,
      room  int
   );
--Example 4.2.3

create type person_t as object     (
      ssno   int,
      pname  name_t,
      age    int
  );    
/

--Example 4.2.4

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

--Example 4.2.5

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

--Example 4.2.6

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

--Example 4.2.6

  create table scientists of name_t;
  insert into scientists values ('Einstein', 'Albert','E');

  update scientists s set s = name_t('Eisenstein', 'Andrew','F')
      where value(s) = name_t('Einstein', 'Albert','E');

  insert into people values (123441998, name_t('Einstein', 'Albert','E'), 100)

  insert into scientists select p.pname from people p

--Example 4.2.8

  insert into people values (321341223, null, null);
  --(this is equivalent to the following)
  insert into people (ssno) values (321341223);

  update people p set p.pname = name_t('Gould', 'Ben', null) where ssno = 321341223;
  update people p set p.pname.mi = 'C' where ssno = 321341223;      

  update people p set p = person_t(332341223, name_t('Gould', 'Glen', 'A'), 55)
      where ssno = 321341223;      

--Example 4.2.9
drop table orders;
drop table orders1;
drop table customers;
drop table customers1;
drop table agents;
drop table agents1;
drop table products;
drop table products1;
drop type order_t;
drop type customer_t;

select name from user_dependencies where referenced_name ='CUSTOMER_T';

drop type product_t;
drop type agent_t;

create type customer_t as object 
(
  cid char(4), cname varchar(13),
  city varchar(20), discnt real
);
/
show errors;

  create type agent_t as object 
  (  
  aid char(3), aname varchar(13),
  city varchar(20), percent smallint
  );
/

  create type product_t as object 
  (
  pid char(3), pname varchar(13),
  city varchar(20), quantity integer, price double precision);
/

create type order_t as object 
  (
      ordno      int,
      month      char(3),
      cid        char(4),
      aid        char(3),
      pid        char(3),
      qty        int,
      dollars    double precision,  
      ordcust    ref customer_t,  -- added column points to customers row object
      ordagent   ref agent_t,  -- added column points to agents row object
      ordprod    ref product_t  -- added column points to products row object
  );
/
show errors;

  create table customers of customer_t (primary key (cid));
  create table products of product_t (primary key (pid));
  create table agents of agent_t (primary key (aid));
  create table orders of order_t
  (
      primary key (ordno),
      scope for (ordcust) is customers,
      scope for (ordagent) is agents,    
      scope for (ordprod) is products
  );      

--Example 4.2.10

  select distinct o.ordcust.cname, o.ordagent.aname from orders 

--Example 4.2.11

  select distinct x1.pid 
      from orders x1, orders x2
          where x1.pid = x2.pid and x1.ordcust < x2.ordcust; 

--Example 4.2.12

  select c.cname from customers c
      where not exists (select * from orders x 
          where x.ordcust = ref(c) and x.aid = 'a05');

--Example 4.2.13

  select c.cid from customers c where     -- select c if
      not exists (select * from agents a  -- there is no agent 
        where a.city = 'New York' and     -- living in N.Y.
        not exists (select * from orders x -- with no order connecting a and c
              where x.ordcust = ref(c) and x.ordagent = ref(a)));        

--Example 4.2.14

select o.cid from orders o where o.ordcust is dangling;

select o.cid from orders o where o.ordcust <> 
      (select ref(c) from customers c where c.cid = o.cid)    ;   

--Example 4.2.15

create type police_officer_t as object
  (
      pol_person   person_t,
      badge_number   integer,
      partner  ref police_officer_t
  );
/

  create table police_officers of police_officer_t
  (
      primary key (badge_number),
      scope for (partner) is police_officers
  );

--Example 4.2.16

  select p.pol_person.pname.lname from police_officers p
      where p.partner.pol_person.age > 60;      

--Example 4.2.17

  update orders o set 
      ordcust = (select ref(c) from customers c where c.cid = o.cid),
      ordagent = (select ref(a) from agents a where a.aid = o.aid),
      ordprod = (select ref(p) from products p where p.pid = o.pid);    

--Example 4.2.18

  insert into police_officers
      select value(p), 1000, ref(p0) from people p, police_officers p0
          where p.ssno = 033224445 and p0.badge_number = 990;

  update police_officers p set p.partner = 
     (select ref(p0) from police_officers p0
         where p0.badge_number = 1000) where badge_number = 990;

