-- Examples from Section 3.6, Advanced SQL
-- Many of these will not work on some database systems

-- Example 3.6.1

     select distinct cid from orders x
          where pid = 'p01' and exists (select * from orders
               where cid = x.cid and pid = 'p07');

     select distinct cid from orders x
          where pid = 'p01' and cid in (select cid from orders
               where cid = x.cid and pid = 'p07');

     select distinct x.cid from orders x, orders y
          where x.pid = 'p01' and x.cid = y.cid and y.pid = 'p07';

     select cid from orders where pid = 'p01'
          intersect select cid from orders where pid = 'p07';



-- Example 3.6.2
-- for Oracle, use minus instead of except

    select c.cname from customers c
        except
        select c.cname from customers c, orders x
            where c.cid = x.cid and x.aid = 'a05';

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

-- Example 3.6.3

   select cname from 
     (select o.cid as spcid from orders o, orders x where o.cid = x.cid
        and o.pid = x.pid and o.ordno <> x.ordno) y, customers c
        where y.spcid = c.cid;

-- Example 3.6.4

    select distinct cname from (orders o join products p on o.pid = p.pid)
        join customers c on o.cid = c.cid where p.price < 0.50;

    select distinct cname from (orders join products using (pid))
        join customers using (cid) where price < 0.50;

