
-- Examples from the first sections of Chapter 3

-- Example 3.3.1

     select aid, aname from agents where city = 'New York';

-- Example 3.3.2

     select * from customers;

-- Example 3.3.3

     select pid from orders;
     select distinct pid from orders;
     select distinct aid, pid from orders;

-- Example 3.3.4

     select distinct customers.cname, agents.aname
          from  customers, orders, agents
          where customers.cid = orders.cid and orders.aid = agents.aid;

-- Example 3.3.5

     select ordno, x.cid, x.aid, x.pid,
          .40*(x.qty*p.price) -.01*(c.discnt+a.percent)*(x.qty*p.price)
          from orders x, customers c, agents  a, products p
          where c.cid = x.cid and a.aid = x.aid and p.pid = x.pid;

-- Example 3.3.6

     select  c1.cid, c2.cid
          from  customers c1, customers c2
     where c1.city = c2.city and c1.cid < c2.cid;

-- Example 3.3.7

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

-- Example 3.3.8

     select  distinct  y.cid
          from orders x, orders y
          where y.pid = x.pid and x.aid = 'a06';

-- Example 3.4.1

     select aid from agents
          where city = 'Duluth' or city = 'Dallas';
     select distinct cid from orders
          where aid in ( select aid from agents
          where city = 'Duluth' or city = 'Dallas');

-- Example 3.4.10

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

-- Example 3.4.11

     select distinct cid from orders x
          where pid = 'p01' and exists (select * 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';


-- Example 3.4.12

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

-- Example 3.4.13

     select distinct c.cname from customers c
          where c.cid not in 
          (select cid from orders where aid = 'a05');

     select distinct c.cname from customers c
          where c.cid <>all 
          (select cid from orders where aid = 'a05');

-- Example 3.4.14

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

-- Example 3.4.15

     select distinct city from customers where cid in
          (select cid from orders where pid = 'p01');

     select distinct city from customers where cid =any
          (select cid from orders where pid = 'p01');

     select distinct city from customers c where exists
          (select * from orders where cid = c.cid and pid = 'p01');

     select distinct city from customers c, orders x
          where x.cid = c.cid and x.pid = 'p01';

     select distinct city from customers c where 'p01' in
          (select pid from orders where cid = c.cid);

-- Example 3.4.2

     select * from agents
          where city in ('Duluth', 'Dallas');

-- Example 3.4.3

     select cname, discnt from customers
          where cid in (select cid from orders where aid in 
               (select aid from agents where city in ('Duluth', 'Dallas')));

-- Example 3.4.4

     select distinct cname from customers, orders
          where customers.cid = orders.cid and orders.pid = 'p05';
     select distinct cname from customers where 'p05' in
          (select pid from orders where cid = customers.cid);

-- Example 3.4.5

     select cname from customers 
          where orders.aid = 'a03' and --  ** ILLEGAL SQL SYNTAX **
          'p07' in (select pid from orders where cid = customers.cid); 

-- Example 3.4.6

--This will not work in all products (not Entry SQL-92)
     select ordno from orders where
          (cid, aid) in (select cid, aid from customers c, agents a
               where c.city = 'Duluth' and a.city = 'New York');
--This should work in all products--
     select ordno from orders where exists
          (select cid, aid from customers c, agents a
               where c.city = 'Duluth' and a.city = 'New York');

-- Example 3.4.7

     select aid from agents where percent <=all 
          (select percent from agents);

-- Example 3.4.8

     select cid, cname from customers
          where discnt =some (select discnt from customers
                    where city = 'Dallas' or city = 'Boston');

-- Example 3.4.9

     select cid from customers
          where discnt <all (select discnt from customers
               where city = 'Duluth');

-- Example 3.5.1

     select city from customers 
          union select city from agents;

     select city from customers
          union all select city from agents;

     (select city from customers 
        union select city from agents)
	union all select city from products;

     select city from customers 
        union (select city from agents
	union all select city from products);

-- Example 3.5.2

select c.cid from customers c where     --  select c.cid if...
   not exists (select * from agents a   -- ...there is no agent a.aid
     where a.city = 'New York' and      -- ...living in New York 
     not exists( select * from orders x -- ...where no order row 
     where x.cid = c.cid
     and x.aid = a.aid));               -- ...connects c.cid and a.aid 

-- Example 3.5.3

select a.aid from agents a
  where (a.city = 'New York' or a.city = 'Duluth')
   and not exists( select p.pid from products p
    where p.price > 1.00 and not exists (select * from orders x
     where x.pid = p.pid and x.aid = a.aid));

-- Example 3.5.4

select a.aid from agents a where a.aid in
     (select aid from orders where pid = 'p01')
     and not exists ( select p.pid from products p
          where p.price > 1.00 and not exists (select * from orders x
               where x.pid = p.pid and x.aid = a.aid));

select y.aid from orders y where y.pid = 'p01' and
     not exists (select p.pid from products p
          where p.price > 1.00 and not exists (select * from orders x
               where x.pid = p.pid and x.aid = y.aid));

-- Example 3.5.5

select cid from customers c
  where not exists(select p.pid from products p
     where p.pid in (select pid from orders x
     where x.cid = 'c006') and 
       not exists (select * from orders y
               where y.pid = p.pid and y.cid = c.cid));

select cid from customers c
  where not exists( select z.pid from orders z
     where z.cid = 'c006' and 
       not exists (select * from orders y
               where y.pid = z.pid and y.cid = c.cid));

-- Example 3.5.6
 
     select pid from product p               -- Retrieve product p.pid if  
          where not exists
          (select c.cid from customers c     -- ... there is no customer   
               where c.city = 'Duluth'       -- ... in Duluth              
               and not exists
               (select * from orders x       -- ... where no row in orders 
                    where x.pid = p.pid      -- ... connects p.pid         
                    and x.cid = c.cid));     -- ...  and c.cid             
