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