PROGRAM 3 /* program: program3.sql author: anthony f. ortiz */ /* this file demonstrates an oracle sql insert, update, delete, and select */ /* (using the aggregate functions and group by, having, and order by */ /* clauses). */ /* b) retrieve cid values of customers who order all the products that */ /* they receive in average quantities (by product) of at least 900. */ SQL> select cid 2 from orders 3 group by cid 4 having avg (qty) >= 900; CID ---- c002 c003 c004 /* d) get aid values of agents not taking orders from any customer in */ /* duluth for any product in dallas. */ SQL> select aid 2 from agents 3 where not exists (select * 4 from customers 5 where city = 'Duluth' or city = 'New York' and 6 not exists (select * 7 from orders 8 where orders.aid = agents.aid and 9 orders.cid = customers.cid)); no rows selected /* f) get pid values of products that are ordered by all customers in */ /* dallas. */ SQL> select pid 2 from products 3 where not exists (select * 4 from customers 5 where city = 'Dallas' and 6 not exists (select * 7 from orders 8 where orders.pid = products.pid and 9 orders.cid = customers.cid)); no rows selected /* h) in the agents table, delete the row with the agent named gray. */ /* then, put gray back using the insert statement. */ SQL> delete from agents where aname = 'Gray'; 1 row deleted. SQL> select * 2 from agents; AID ANAME CITY PERCENT --- ------------- -------------------- ---------- a01 Smith New York 6 a02 Jones Newark 6 a03 Brown Tokyo 7 a05 Otasi Duluth 5 a06 Smith Dallas 5 SQL> insert into agents (aid, aname, city , percent) 2 values ('a04', 'Gray', 'New York', 6); 1 row created. SQL> select * 2 from agents; AID ANAME CITY PERCENT --- ------------- -------------------- ---------- a01 Smith New York 6 a02 Jones Newark 6 a03 Brown Tokyo 7 a05 Otasi Duluth 5 a06 Smith Dallas 5 a04 Gray New York 6 6 rows selected. /* j) using a single update statement to raise the prices of all products */ /* warehoused in duluth or dallas by 10%. then restore the original */ /* values to their original state. */ SQL> update products set price = price + price * .10 2 where city = 'Duluth' or city = 'Dallas'; 5 rows updated. SQL> select * 2 from products; PID PNAME CITY QUANTITY PRICE --- ------------- -------------------- ---------- ---------- p01 comb Dallas 111400 .55 p02 brush Newark 203000 .5 p03 razor Duluth 150600 1.1 p04 pen Duluth 125300 1.1 p05 pencil Dallas 221400 1.1 p06 folder Dallas 123100 2.2 p07 case Newark 100500 1 7 rows selected. SQL> start capcre.sql Table dropped. Table dropped. Table dropped. Table dropped. Table created. Table created. Table created. Table created. SQL> start cap.sql 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. SQL> select * 2 from products; PID PNAME CITY QUANTITY PRICE --- ------------- -------------------- ---------- ---------- p01 comb Dallas 111400 .5 p02 brush Newark 203000 .5 p03 razor Duluth 150600 1 p04 pen Duluth 125300 1 p05 pencil Dallas 221400 1 p06 folder Dallas 123100 2 p07 case Newark 100500 1 7 rows selected. /* l) write an sql query to get aid and percent values of agents who */ /* take orders from all customers who live in duluth. the aid */ /* values should be reported in order by decreasing percent. */ SQL> select aid, percent 2 from agents 3 where not exists (select * 4 from customers 5 where city = 'Duluth' and 6 not exists (select * 7 from orders 8 where orders.aid = agents.aid and 9 orders.cid = customers.cid)) 10 order by percent desc; AID PERCENT --- ---------- a06 5
BACK TO CS4660 PAGE.