--Section 4.2 Dotted Solutions (at back of book), for Oracle 

--[4.1] (a)     
select ssno from people p where p.pname.mi is null;

--(c)
select p1.ssno, p2.ssno from people p1, people p2
       where p1.pname = p2.pname and p1.ssno < p2.ssno;

--[4.2] (a) 
drop table students;
drop type student_t;

create type student_t as object (
    sperson person_t,
    stuid int, 
    emailname varchar(30),
    gradyear int        
);
/
show errors;

create table students of student_t (sperson not null, primary key(stuid));

--[4.3]   (a, c, g, i, q) These don't involve the orders table 

--(e)
select distinct o.ordprod.pname from orders o 
   where o.ordcust.city = 'Dallas' and o.ordagent.city = 'Kyoto';

--(g, i)  Do not involve the orders table.

--(k)     Involves only the orders table, no need for REFs

--(m)
select aid, aname from agents a where a.aname like 'N%'
      and a.aid not in
        (select o.aid from orders o where o.ordprod.city = 'Newark');

--(o)     To eliminate agents with no orders at all, we should use "
--        from orders" in the first line:

select distinct o.ordagent.aname from orders o where not exist
    (select * from orders x where x.cid = 'c002' and not exists
    (select * from orders y where y.ordagent = ref(a) 
                          and y.ordprod = x.ordprod))

--(s)
select aid from orders o where dollars > 500.00 and o.ordcust.city =  'Kyoto';

--(u)
select cid from (select distinct cid, aid from orders) t
       group by cid having count(*) = 1;
