-- 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;