PROGRAM 2 /* program: program2.sql author: anthony f. ortiz */ /* this file demonstrates an oracle sql select statements. */ /* 3.1 b) find all (cid, aid, pid) triples for customer, agent, product */ /* combinations that are not all in the same city. */ SQL> select distinct customers.cid, agents.aid, products.pid 2 from agents, customers, orders, products 3 where agents.aid = orders.aid and 4 customers.cid = orders.cid and 5 products.pid = orders.pid 6 minus 7 select customers.cid, agents.aid, products.pid 8 from agents, customers, orders, products 9 where agents.city = products.city and 10 customers.city = products.city; CID AID PID ---- --- --- c001 a01 p01 c001 a02 p02 c001 a03 p04 c001 a04 p05 c001 a05 p06 c001 a05 p07 c001 a06 p03 c002 a03 p03 c002 a05 p03 c003 a03 p05 c004 a06 p01 CID AID PID ---- --- --- c006 a01 p01 c006 a03 p07 c006 a06 p01 14 rows selected. /* 3.1 d) get product names ordered by at least one customer based in */ /* dallas through an agent based in tokyo. */ SQL> select distinct city 2 from agents, orders 3 where agents.aid = orders.aid and orders.cid = 'c002'; CITY -------------------- Duluth Tokyo /* 3.1 f) get pid of products ordered through any agent who makes at */ /* least one order for a customer in kyoto. */ SQL> select distinct pid 2 from orders 3 where orders.aid in (select aid 4 from customers, orders 5 where customers.city = 'Kyoto' and 6 customers.cid = orders.cid); PID --- p01 p03 p04 p05 p07 /* 3.1 h) find cid of customers who did not place an order through agent */ /* a03. */ SQL> select distinct cid 2 from orders 3 where pid <> all (select pid 4 from orders 5 where aid = 'a03'); CID ---- c001 c004 c006 /* 3.1 j) find cid of customers who order all products. */ SQL> select distinct cid 2 from customers 3 where not exists (select * 4 from products 5 where not exists (select * 6 from orders 7 where customers.cid = orders.cid and 8 products.pid = orders.pid)); CID ---- c001 /* 3.1 l) get pnames and pids of products that are stored in the same */ /* city as one of the agents who sold these products. */ SQL> select distinct products.pname, products.pid 2 from agents, orders, products 3 where products.city = agents.city and 4 agents.aid = orders.aid and 5 products.pid = orders.pid; PNAME PID ------------- --- brush p02 comb p01 razor p03 /* 3.1 n) get cid of customers who order both product p01 and product */ /* p07. */ SQL> select distinct cid 2 from orders 3 where pid = 'p01' and cid in (select cid 4 from orders 5 where pid = 'p07'); CID ---- c001 c006 /* 3.1 p) get names of agents who place orders for all products that are */ /* ordered by any customer at all. */ SQL> select distinct aname 2 from agents 3 where not exists (select * 4 from products 5 where not exists (select * 6 from orders 7 where agents.aid = orders.aid and 8 products.pid = orders.pid)); no rows selected /* 3.1 r) get pid of products ordered by all customers who place any */ /* order through agent a03. */ SQL> select distinct pid 2 from products 3 where not exists (select * 4 from agents 5 where agents.aid = 'a03' and 6 not exists (select * 7 from orders 8 where orders.pid = products.pid and 9 orders.aid = agents.aid)); PID --- p03 p04 p05 p07 /* 3.1 t) give all (cname, aname) pairs where the customer places an */ /* order through the agent. */ SQL> select distinct customers.cname, agents.aname 2 from agents, customers, orders 3 where customers.cid = orders.cid and agents.aid = orders.aid; CNAME ANAME ------------- ------------- ACME Brown ACME Smith Allied Brown Basics Brown Basics Otasi Tiptop Brown Tiptop Gray Tiptop Jones Tiptop Otasi Tiptop Smith 10 rows selected. /* 3.2 b) retrieve aid values of agents who recieve the maximum percent */ /* commision. */ SQL> select distinct aid 2 from agents 3 where percent >= all (select percent 4 from agents); AID --- a03
BACK TO CS4660 PAGE.